Converting Long,Lat to SQL Server Spatial Data

In my last post I pulled across the Postcode database for the UK, I wanted to display some information using this data on the New Map functionality of Report Builder 3, although unfortunately it requires Spatial data for this to work. Having looked around I came across the information, the GEOGRAPHY datatype in SQL Server looks to allow you to store point information in a single column of SQL Server, and there are a few functions for allowing you to convert longitudinal and latitudinal values into this new data type... So without further ado...

Using the previous table we created we need to expand it to create a new column

ALTER TABLE Postcodes ADD [GeoLocation] GEOGRAPHY

next we need to convert the longitude and latitude into point information

UPDATE Postcodes SET GeoLocation = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20))
+ ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

As you can see we're using the inbuilt STPointFromText function, this requires a point reference and a SRID reference (something to do with initial map referencing, which for our case if 4326), you can view the function in more detail here.

Additional function you can use are geography::STGeomFromText Same as above by the looks of it, geography::Point again similar but without the text modification or geography::Parse again same as the rest but with a SRID (assumes 4326 anyway by the looks of it)

My next post will probably be about placing this stuff on a map in Report Builder 3