Extensions - Spatial

Good afternoon everyone!

Today's blog post is going to cover Spatial Data Types, a part of SQL Server which is often referred to as one of the Extended Features which was new to SQL Server 2008.

What is a Spatial Data Type?

Spatial Data Types are special in that they allow you to store geographical information such as latitudes and longitudes or co-ordinates, which can be used by Microsoft Visual Earth in order to provide a mapped representation of the data. This can be incredibly useful if you are plotting locations on a map, or if you are trying to work out distances between points.

SQL Server 2008 onwards already has geometry and geography data types predefined and are ready to use straight away:

Geography - Data type that uses latitude and longitude values.

Geometry - Data type that uses co-ordinates to represent graphical 2D and 3D data.

How to Instantiate the GEOGRAPHY Data Type

Firstly, I created a new table just for the purposes of this exercise. As you can see, it's very easy and straightforward to declare a database field as a GEOGRAPHY type.

Next, I want to INSERT data into my new table. For the purpose of this example, I am putting in a location with the corresponding latitude and longitude points (if you can't find your exact location points, try using Google to find them from your postcode). You must then parse them into the appropriate field, while indicting that this is a geography field.

After executing successfully, I now want to view the results when I perform a SELECT query with the location as a string. In order to do this, I simply pull the location to a string format and give it an alias. WKT is a standard acronym for Well-Known Text (with WKB being the binary equivalent). For the purposes of this, I want to display the location field in WKT:




And this is the result:




Now I'm satisfied with the textual input, I want to SELECT and view the Spatial Results of this query. In order to do this, you simple run a standard SELECT like in the example provided below:



Then click on the new tab that has appeared called "Spatial results":

Now you will see the following grid:


The pinpoint is so tiny you really will need to zoom in to see on this image. However if you hover over the point, it's details will appear in a tool tip window. Looks familiar? Well similar functionality is found using Google Maps API:

And that is how you successfully pinpoint your location data using the GEOGRAPHY data type. If you add more data, you will be able to SELECT all pinpoints found within the database. Then, by customising your SELECT statement with WHERE clauses, you can further drill down to find specific points that have been plotted within your database.

How to Instantiate the GEOMETRY Data Type

Similarly to the previous example, I have created a new table. As you can see, in order to declare a database field as a GEOMETRY type, it's exactly the same as GEOGRAPHY:








Next, I want to INSERT data into my new table. You can parse many different geometry classes into your system, as found here:

http://en.wikibooks.org/wiki/Geospatial_Data_in_SQL_Server

For the purpose of this example, I am going to use POLYGON. You should then parse in the parameters in order to build the shape:

Upon successful execution so my data has been inserted, I want to SELECT and view the Spatial Results of this query. In order to do this, you simple run a standard SELECT like in the example provided below:

Again, like using the GEOGRAPHY data type, you should now click on the "Spatial results" tab that has appeared:

Now you can see your polygon represented on a 2D graph:













Obviously, this is an incredibly basic example. You can also create 3D shapes relative to the data within your database.

Overall...

Personally, I have never used these data types in my working environment, however they are incredibly useful to know about and I will consider using them when designing a database in the future.

What Next...

Next, I will be looking at another of the extended SQL features called the Full-Text Search. I will be explaining what it is, why it can be useful in your database solution and how to implement it. Stay tuned!

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.