Spacial Data in SQL Server

3It’s not very often which you deal with spacial data in SQL. In fact, I find it to be a very rare occurrence, but when it does happen, it’s nice to know that there are two libraries for it: geometry (which is for Euclidean Geometry) and geography (which uses lat/long, and takes into account the curvature of the earth).

The way it works, is you create a shape with a series of points, making sure that you declare the points counter clockwise. you can then run a number of functions (such as collision) off of the shape.
An example of how that can be used is districts in a city. Districts are simple shapes on the map, using geometry we can generate a series of shapes based on the district, and given a lat/long of a house, find out which district the house is in.
Another case is the simple, search for stores within n miles of my location. With geometry, you can declare a point with the target location at it’s center, and declare a buffer area. This will build a shape of a circle you can use and simply check for a collision inside the circle. In other systems, this would require much more effort. Including trig mathematics.
Another great benefit of using sql geography, is that it’s a .NET library being exposed by sql. This means that you can use the same tech in c# code as you can in sql, making in very easy to test things out, without needing to use an actual sql server.
Anything that makes my life simpler is great by me, and geometry makes dealing with spacial data, a task that is normally extremely difficult, very easy.


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 )

Google photo

You are commenting using your Google 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