How to convert a SQL geography column back to individual latitude and longitude?

Question

I have some routes stored in a sql 2008 database as geography linestrings, basically a bunch of lat\long points.  I need to draw these routes on bing maps, bing needs them as individual lat\long points.

So how would I take them from the database into C# .net \ typed data set and iterate them.  I can see them in the typed data set but it comes across as LINESTRING( -2.3234, 54.2323 ....).  I could build a parser that takes the dataset LINESTRING and converts it into lat\long but I'd hate to reinvent the wheel if there is built in way to do it.  Thanks for any help 

Answer 1

You can use Microsoft.SqlServer.Types.dll from the client as well. Just install the package from the correct SQL Server Feature Pack on the client (here’s the 2008 link: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=3522), don’t just copy the DLL. I’m not sure if the typed dataset (or untyped for that matter) supports Geography directly, if you end up with a string, you can use the SqlGeography static Parse method (or STGeomFromText or STLineFromText) to create a SqlGeography instance (it’s named SqlGeography in the library). Then use STNumPoints/STPointN methods to iterate it and break it up.

 

Cheers, Bob

 

"strmshadow" wrote in message news:8f053f4d-01d6-4550-99c5-72fa38b0a26c...

I have some routes stored in a sql 2008 database as geography linestrings, basically a bunch of lat\long points.  I need to draw these routes on bing maps, bing needs them as individual lat\long points.

So how would I take them from the database into C# .net \ typed data set and iterate them.  I can see them in the typed data set but it comes across as LINESTRING( -2.3234, 54.2323 ....).  I could build a parser that takes the dataset LINESTRING and converts it into lat\long but I'd hate to reinvent the wheel if there is built in way to do it.  Thanks for any help