How can I show some shapefile / Well-Known-Text data on Google Maps / MS Virtual Earth?

Question

Hi Folks,

I have some shapefile (demographic/heat map data in the USA, such as crime in New York) data imported into a sql server 2008 database, field data type: Geography.

How can i get this data, from a select query, in a format which i can then display on google maps or microsoft virtual earth?

thanks!

Edit 1: So far, the best solution has been to use a (free) 3rd Party dll (SharpMap). I'm hoping someone might suggest some sql tricks in sql2008 to return it in a compatible format ...

Answer 1

The easiest way to do this is to select to XML and then transform the data into GeoRSS or KML. Virtual Earth and Google Maps both have support for KML so that would probably be the easiest way to do it.

There are considerations though. If you have many many vertices in your data you probably want to implement some sort of AJAX method to retrieve only the data in the map view. You can generalize data in SQL 2008 to make a fewer amount of vertices depending on your zoom level. If you are zoomed out to a country level you don't need nearly as much detail as zoomed into a town.

MapDotNet has a lot of features for getting your data in a Virtual Earth map more easily. It is relatively easy to perfrom a select query and display the data as you desire. I'm sure there are other similar products for Google maps but I haven't personally used them. It has support for polygons as well as heat maps and gives you a lot more functionality than just using Virtual Earth. Virtual Earth is powerful but there are a lot of GIS tools someone from a mapping background would expect that you have to program in yoursef.

Answer 2

Take a look at this post -- I successfully used this to read shapefiles and display them on to Virtual Earth. This should give you the basic idea of how to overlay shape file data on to Virtual Earth.

To use sql server 2008 spatial data I would look at the SharpMap project on codeplex. These are the tools used the post that I referenced and they do support sql server datatypes.

Answer 3

Geri Langlois's Sharpmap link is to 0.9 If you want bleeding edge, the repository is here: http://sharpmapv2.googlecode.com/svn

Also, you have to make sure your data is Lat/Long WGS84 and not something else before you start placing coordinate info into your KML. I don't know of a way to do that in SQLServer2008 (might need to use ogr2ogr).

using STX and STY, you can probably generate some coordinate tags for the KML

select SHAPE.STX as X
    ,SHAPE.STY as Y
    ,SHAPE.STAsText() as WKT
    ,SHAPE.AsGml() as GML
from dpu.SW_SERVICE_LOCATIONS

maybe something like:

SELECT '<coordinates>'+convert(varchar,convert(decimal(20,6), SHAPE.STX),1)+
    ','+ convert(varchar,convert(decimal(20,6), SHAPE.STY),1) 
    + '</coordinates>'
FROM sw_service_locations;