SQL Server 2008 included the addition of two data types to handle spatial data: geometry and geography. Geometry handles two dimensional shapes, while geography is used when working with round-earth data, like points at certain places on the Earth’s surface.
I’ve never used either data type, so I wanted to run through an example of each one. We’ll start with Geometry.
Geometry:
The Geometry data type works with points and shapes in a two-dimensional space. For this example, we’ll draw two squares. We’ll use the STGeomFromText function, which will convert our text coordinates into a shape. We’ll use 5 sets of coordinates on the x and y axis (The last set is the same as our first set, giving us a closed shape).
DECLARE @Shape1 geometry = geometry::STGeomFromText ('POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
DECLARE @Shape2 geometry = geometry::STGeomFromText ('POLYGON ((2 2, 4 2, 4 4, 2 4, 2 2))', 0);
SELECT @Shape1;
SELECT @Shape2;
When we select each shape, we’ll see a new Spatial Results tab added to the Results section. This will display each shape on a grid.
The 2nd parameter for the STGeomFromText function is the SRID (Spatial Reference Identifier). In this case, we use the default of 0. This defines the system used to encode the shape or other object that we define. We’ll look at this further in the Geography section, it will make more sense there.
We’ll use these same two shapes, and use the STIntersection function to determine where the two shapes intersect.
DECLARE @Shape1 geometry = geometry::STGeomFromText ('POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
DECLARE @Shape2 geometry = geometry::STGeomFromText ('POLYGON ((2 2, 4 2, 4 4, 2 4, 2 2))', 0);
SELECT @Shape1;
SELECT @Shape2;
DECLARE @Shape3 geometry = @Shape1.STIntersection(@Shape2);
SELECT @Shape3, @Shape3.STAsText();
So the three selects will each draw a shape, our two original squares, and then the space where they intersect. The select using the STAsText function will return this value:
POLYGON ((2 2, 3 2.0000000000000142, 3 3, 2.0000000000000142 3, 2 2))
Which gives us the definition of the intersection. I’m not quite sure why we got the fractional result for a couple of points, I’m guessing some sort of rounding issue.
Geometry is interesting, but I see more real-world use for the Geography type. We’ll look at that, but first just a bit of background.
Geography Background:
If we look at the Wikipedia page for the city of Atlanta, Georgia, USA, we’ll see the coordinates for the city listed:
33°44′56″N 84°23′24″W
The first measurement is the latitude. So Atlanta is 33 degrees, 44 minutes, and 56 seconds North of the equator. The second measurement is the longitude. Atlanta is 84 degrees, 23 minutes, and 24 seconds West of the Prime Meridian.
This system of representing coordinates is the DMS (Degrees Minutes Seconds) system.
If we click on the coordinates on the Wikipedia page, we’re taken to a GeoHack page. Here we can also see the Decimal coordinates for Atlanta, which are:
33.748889, -84.39
The first number is the Latitude. A positive number is North of the Equator, a negative number is South. The second number is the Longitude. A negative number is West of the Prime Meridian, and positive is East.
In the Geometry section, we used a value for the SRID (Spatial Reference Identifier). DMS and Decimal are examples of two different systems to encode our data, so they would both have a different SRID.
We can query the sys.spatial_reference_systems system table to see the available systems. 4326 is for the decimal system, which is what we’ll used for the Geography example.
Geography:
We’ll create a table to store US cities, along with their coordinates, using the Geography data type. We’ll add some cities in the Southeastern US that have pro football teams.
DROP TABLE IF EXISTS dbo.TestGeography;
CREATE TABLE dbo.TestGeography (
ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
City varchar(30) NOT NULL,
StateCode char(2) NOT NULL,
Coordinates geography NOT NULL
);
INSERT INTO dbo.TestGeography(City, StateCode, Coordinates)
VALUES
('Atlanta', 'GA', geography::Point(33.748889, -84.39, 4326)),
('Charlotte', 'NC', geography::Point(35.227222, -80.843056, 4326)),
('Tampa', 'FL', geography::Point(27.94752, -82.45843, 4326)),
('New Orleans', 'LA', geography::Point(29.95465, -90.07507, 4326));
Each set of coordinates are stores as a point, using the 4326 SRID for the decimal format.
CREATE SPATIAL INDEX ixTestGeographyCoordinates ON TestGeography(Coordinates);
We don’t have many records, but we’ll still create a Spatial Index for the Geography data.
Let’s select our records:
SELECT ID, City, StateCode, Coordinates, Coordinates.ToString() as DecimalCoordinates, Coordinates.Lat as Latitude, Coordinates.Long as Longitude FROM dbo.TestGeography;
After running the query, you’ll see a Spatial Results tab on the results bar. You’ll see a grid with the points that were returned in the query (Although the points may be difficult to see).
We see that the Geography data in the Coordinates column is stored as binary. We can call the ToString function to see the Point data we passed in. We can also query the Lat and Long properties on that column to return Latitude and Longitude.
So suppose we’re a football fan in Montgomery, Alabama, and we would like to find the closest city with an NFL team. Using the coordinates for Montgomery, we’ll compare those to each city in our table to determine the distance as returned by the STDistance function.
-- Montgomery, AL DECLARE @Location geography = (geography::Point(32.3675, -86.3, 4326)); SELECT ID, City, StateCode, Coordinates.STDistance(@Location) as DistanceMeters, ROUND(Coordinates.STDistance(@Location) / 1609.344, 1) as DistanceMiles FROM dbo.TestGeography ORDER BY Coordinates.STDistance(@Location);
STDistance returns the distance between two points in meters. We can use this, and also convert that values to miles.
Wrap Up:
Method names are case sensitive.
There’s definitely a lot more functionality available, but this is a good start for the Geography and Geometry data types. Perhaps I’ll come back in a future post and go deeper into these types.
Links:
Microsoft – Spatial Data Types Overview
SQL Rob 