none
How to generate "geometry" format data in SQL server 2008 to draw colorized administration boundaries such as zip code in bing map? RRS feed

  • Question

  • Hi,

    I would like to draw colorized administration boundaries such as zip code, couties on Bing map.

    I have studied "Dynamic Tile Layers in the Bing Maps Silverlight Control" at

    http://blogs.msdn.com/b/rbrundritt/archive/2013/11/08/dynamic-tile-layers-in-the-bing-maps-silverlight-control.aspx

    I have downloaded the code from

    http://cid-e7dba9a4bfd458c5.skydrive.live.com/self.aspx/VE%20Sample%20code/Silverlight.DynamicTileLayers.zip

    In the DataBase/ folder, there is a database file called "CountryBoundaryDB.bak".

    I have imported it to SQL server 2008.

    In the table "CountryBorders", the column of "[BOUNDARY]" hold latitude and longitude of all boundary points.

    Now, I would like to draw state, county and zip code boundaries of USA on the map.

    How to generate the "BOUNDARY" column data in the format of "geometry" ?

    Thanks !

    Friday, January 1, 2016 6:21 PM

Answers

  • There are a lot of options here, but it really depends on if you have the data or not. Finding State boundaries will be fairly easy, counties shouldn't be too difficult. Zip codes will be difficult unless you are fine with using the lower detail boundaries from the US Census for zip Code Tabulation Areas (ZCTA5). The easiest way to get this data and add it to SQL Spatial (2008+, Azure) is to find Shapefiles that contain this information. Shapefiles are a common binary file format used for storing spatial data. A lot of government agencies make data available in this format. Once you have data in this file format you can use the free shape2sql tool here: http://sharpgis.net/page/Shape2SQL

    All these boundaries can be downloaded from the US Census here: ftp://ftp2.census.gov/geo/tiger/TIGER2015/ You will want the STATE, COUNTY and ZCTA5 files.

    The blog post you referenced above is a bit out dated. Since then one of my colleagues and I have written a white paper that goes into more depth and shows a lot more options for doing things like this. You can find the whitepaper here:https://onedrive.live.com/redir?resid=D35222484A76A01!361218&authkey=!AMEJsKW8h_HUbOg&ithint=file%2cpdf

    The code samples for the white paper are here: https://onedrive.live.com/redir?resid=D35222484A76A01!323177&authkey=!AId01rJ-JPDPZMw&ithint=file%2czip

    This following info is unlikely to be an issue if you use the files I pointed to above, but is useful to know if you want to make use of other data files.

    Now, things can get complicated very fast as there is a lot of different ways that spatial data is measured and stored (projections/datum). You will want data that is in WGS84 datum using a Mercator projection and decimal degrees. This is one of the most common format you will likely come across. If you come across data in a different projection/datum and you want to still use it with Bing Maps you will need to reproject it. A common tool for this is called Ogr2Ogr which is part of the GDAL library: http://www.gdal.org/. In addition to being able to reproject data it can also be used to copy the data into other formats such as KML, GPX, SQL and so on.

    Now, the above is all based on the assumption that you are only interested in getting the boundary data into SQL. If you just want to be able to retrieve boundary data as needed another option is the Bing Maps GeoData API which provides boundary data through a REST service. This is useful if you needed detailed boundaries for Zip Codes, or if you want to be able to generate different country boundaries based on a user regions. In many countries the accepted boundaries of their county is different from how the rest of the world sees it and it can be a big issue if your app shows the wrong boundary. This will only be an issue for you if you are planning to make your app available in countries that have these sorts of issues (i.e. India, Pakistan, China, Nepal, Morocco are some countries where I see this come up the most often). That said, going this route would likely be a significant change from what you are doing now. If you do decide to go this route you might find this code sample useful: https://code.msdn.microsoft.com/Creating-terriorties-from-73c7ebc5

     

    http://rbrundritt.wordpress.com

    Monday, January 4, 2016 7:00 PM