In this blog series we will dig into spatial data available in SQL Server. This second post will be creating spatial data manually. The three base objects will be reviewed and see different examples of queries.
Let’s start by opening up SSMS and connecting to a SQL Server instance that is 2008 or higher. Spatial data was made available in the release of SQL Server 2008. If you are still running SQL Server 2008 please review the following link and give the nice folks at KiZAN Technologies a call. We can help upgrade your SQL Server instances and many other things.
First lets look at creating a Point. This is the fundamental object that all other objects are based on. To create a point we can reference the following two links. Geography Point (Latitude and Longitude) and a Geometry Point (X and Y). To see an example of the geography point we can review the following code.
This is the location of the Washington State Convention Center where the PASS Summit takes place. I recommend checking it out and attending.
SELECT geography::Point(47.611755,-122.331425,4326)
Running the query will result in a single column that has binary information. There is also a new tab called Spatial results. However when switching to that tab it is a blank grid.
But wait a minute. Why isn’t anything showing up? There is a point there, but it is one dimensional! In order to see something show up here let’s call a method on this new Point. This method is called Buffer and will generate new points that are a given distance from the origin. Simply put it creates a larger circle instead of a single point.
This query contains a subquery of the manually entered latitude and longitude. The Point method is called by passing the values from the subquery. Then the geo_buffer column calls the STBuffer method and passes a value of 1. The results are shown below.
SELECT
Latitude ,
Longitude ,
Location ,
geography::Point(Latitude,Longitude,4326) geo ,
geography::Point(Latitude,Longitude,4326).STBuffer(1) geo_buffer
FROM (
SELECT
47.611755 AS Latitude ,
-122.331425 AS Longitude ,
'Washington State Convention Center' AS Location
) sub
Make sure to change the spatial column from geo to geo_buffer. Also, in the image above, notice when hovering over the object it will display the other columns in the result set.
Remember that points are the building blocks for the spatial data. It is good to remember that all objects are made up of a list of points. The next spatial object we will review is Line. These coordinates are the two points of the Golden Gate Bridge in San Francisco, California.
SELECT
Latitude_Start ,
Longitude_Start ,
Latitude_End ,
Longitude_End ,
Location ,
geometry::STLineFromText (
CONCAT (
'LINESTRING (' ,
Longitude_Start , ' ' ,
Latitude_Start , ' , ' ,
Longitude_End , ' ' ,
Latitude_End ,
')' ) ,
4326 ) as geo
FROM (
SELECT
37.829611 AS Latitude_Start ,
-122.479721 AS Longitude_Start ,
37.810053 AS Latitude_End ,
-122.477429 AS Longitude_End ,
'Golden Gate Bridge' AS Location
) sub
Another method of writing this type of query can be a short hand version like the one below. Like most things SQL Server there are a number of ways to write a single query. This is an attempt to show the different methods I have come across and share them. Please write your own query and pick what works best for you.
SELECT
geometry::STLineFromText(
'LINESTRING (-122.479721 37.829611 ,
-122.477429 37.810053)', 4326);
The final object we will create is a Polygon. To create Polygons let’s reference another location in Florida. The Vehicle Assembly Building at NASA’s Kennedy Space Center. This building is the largest single-store building in the world.
SELECT
geography::STPolyFromText(
'POLYGON ((
-80.589135 28.449309 ,
-80.588880 28.447099 ,
-80.585771 28.447375 ,
-80.586014 28.449595 ,
-80.589135 28.449309
))', 4326);
That ends this post on how to create spatial data. In this lesson we reviewed how to make a single Point, Line, and Polygon. In future posts we will see how to create other objects and load sample datasets.
SQL Stack 