Geography (Part II)

In the first article about spatial information, we have seen an example of transforming some textual based location information into a longitude and latitude with a webservice. But even this information is not ready for using in a map. Therefore we need to convert the longitude and latitude into some sort of geometrical points.

SQL Server 2008 introduced the geometrical datatype, which gives some more flexibility and a better use for maps. As stated in an article on developerfusion, there is also another type Geometry. The SQL Server team has concluded that this type based on open standards wasn’t adequate for some scenarios. In the artivle Siddharth Mehta he introduced a script task in SSIS in which he updates the geocoded data with an UPDATE statement. I decided not to use this and decided to code this in T-SQL.

First i had to decode the , in . because i had my regional settings set on Dutch and the POINT expects . as an inputparameter. After that i used the static function geography::STPointFromText.

This gives the following results:

SSMS sees that we are working with a spatial column in our result set and displays an new tab : Spatial results:

At first i didn't see anything, after that i thought that i saw some dirt on the screen but when i hovered over the points a tooltip showed up.

Ok, next time we're gone built a report with report builder 3.0 and convert this information into some visual information.

So that's it for now!

Greetz,

Hennie