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

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

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 ...

Solution

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;