Natural Earth Data, ESRI Shapefiles and SQL Server 2012

Natural Earth is a beautiful “free” map built by many volunteers and available for free on their web site. The map is provided as a series of standard ESRI shape files and database in one large zip file. To use this map, I created a tool simple to use (but not trivial to make) that upload Natural Earth directly into SQL Server 2012:

Unfortunately, even if it supports geo-spacial data, SQL Server 2008 is not compatible with the polygons provided by Natural Earth. It looks like Microsoft implementation of the “geography” type is far too strict and they fixed it only with the next version.

The uploaded database set all the shapes in one large table called “layer_objects” so a client program can take advantage of the geo-spacial index. Each GIS attributes, from the associate DBF file, is stored in a separate table.

For example, you can get all the French cities in just one query:

SELECT p.NAMEASCII 
FROM layer_objects o INNER JOIN ne_10m_populated_places p ON o.GlobalObjectId = p.GlobalObjectId
WHERE GeoObject.FILTER( 
	(SELECT GeoObject FROM layer_objects WHERE GlobalObjectId= (
		SELECT GlobalObjectId FROM ne_10m_admin_0_sovereignty WHERE sovereignt = 'France')
		)) = 1
ORDER BY p.NAMEASCII

I also create a nice web site to browse the different countries using Open Layers. It uses the SQL function “GetLayerZone” that must be manually deployed. You can check it yourself at http://natearth.dupuis.me/ (I know that Russia and some countries don't work due to the lack of processing power on the server).

As usual the source code is attached:

AttachmentSize
NaturalEarth.zip120.05 KB