Spatially enable a column in a SQL Server table

Lately I’ve had to dig up my knowledge of SQL from university for some tasks at work, as well as the module I recently completed through UNISA.

The coordinates are in two columns, and I had to present the data back to the client as a spatially enabled view. Thanks to my colleague Grobbelaar for the code, and for showing me some cool stuff in SQL Server Management Studio (in which I am a total n00b).

SELECT GIS_ID, GEOMETRY::STPointFromText('POINT(' + CONVERT(varchar(50), Longitude) + ' ' + CONVERT(varchar(50), Latitude) + ' )', 4326) AS Location
FROM tbl_random
WHERE Longitude > 0 # Checks if there are valid coordinates for Eastern Hemisphere

The where clause will fail if the coordinates are not in the Eastern Hemisphere. I could have gone for Latitude < 0 as well, but that still leaves me in the same situation. As all of the data for this client is in South Africa, it was safe to use this expression. It's not robust though.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.