SQL Geography data types

If you have X (longitude), Y (latitude), and Z(altitude) properties in a table in a database and you want to run queries on those using the built in SQL geography functions, you can make queries run faster and and simpler by creating a computed column on each row to hold the value as a Geography type. By making this a computed column, you can easily edit the X, Y, and Z values and have it update the Geography value.
* (Sidebar: X = longitude , Y = latitude, which can cause some confusion. Longitude 0 is at the equator so think of it as a horizontal line. This causes confusion because we normally say ‘X Y coordiates’ with X first, and I normally say ‘latitude and longitude’ with latitude first, so I was confused for a while about this. Would be better if I get in the habit of saying ‘longitude latitude’ in that order so I’ll remember that longitude = X and latitude = Y)

With this as the initial table:

CREATE TABLE [dbo].[Location](
	[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Location_Id]  DEFAULT (newid()),
	[WorldId] [uniqueidentifier] NOT NULL,
	[X] [decimal](18, 14) NOT NULL,
	[Y] [decimal](18, 14) NOT NULL,
	[Z] [decimal](24, 8) NULL 
 CONSTRAINT [PK_Location_1] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

You can fill this with values (note that the Z value is optional in this case, but you could make it required).

Now I want to add a Geography datatype column that will be automatically populated when I enter rows. This requires and SRID, and I will generally use the same one, but since I could change it I’m going to add another column to hold the SRID, set it to a default value of 4326, and then use that value to compute my geolocation. If the SRID, X, Y, or Z values are changed the geolocation value will be recomputed on the fly.

Here is the SQL to add an SRID column set to 4326 by default, and add a computed geography type column called Geolocation

--Note that this will work with both 2 dimensional and 3 dimensional locations.

--Add SRID column
Alter Table Location Add SRID int not null 
constraint DefaultValue Default 4326

--Add Geolocation column
Alter table Location ADD GeoLocation AS (CONVERT(Geography, 
CASE WHEN x IS NOT NULL AND y IS NOT NULL AND z IS NOT NULL
	 THEN Geography::STGeomFromText('POINT(' + Convert(nvarchar(20), y) + ' ' + Convert(nvarchar(20), x) + ' ' + Convert(nvarchar(20), z) +')', SRID)
     WHEN x IS NOT NULL AND y IS NOT NULL
	 THEN Geography::Point(x,y,SRID)
ELSE NULL
END
))

If you want to test that your computed Geolocations will give back the expected values you can check them like this (use your Id value in the select below).

--Verifying that my geoloation is right 
--substitue your own ID value to get your row.
DECLARE @g2 geography = (select l.GeoLocation 
from Location l
where Id = '26F02964-5906-4354-873F-37D78CC8DFF3')

SELECT @g2.Lat; -- tell me the latitude
SELECT @g2.Long; --tell me the longitude
SELECT @g2.Z;  --tell me the altitude

--show me the entire line so I can see the values match
select *  from Location l
where Id = '26F02964-5906-4354-873F-37D78CC8DFF3'

Here is a sample of getting data within a certain radius of a given point:


DECLARE @Origin GEOGRAPHY,
-- distance defined in meters
@Distance INTEGER = 40000; --in meters so this is 40km

SET @Origin = GEOGRAPHY::STGeomFromText('POINT(-79.97346300000000 36.10017790000000)', 4326);

-- return all rows from events in 40km radius
SELECT * FROM dbo.Location WHERE @Origin.STDistance(GeoLocation) <= @Distance;

--Show me the exact distance (first column) for items with a radius
SELECT  GeoLocation.STDistance(@Origin), l.* from dbo.Location l
 WHERE @Origin.STDistance(GeoLocation) <= @Distance;

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

w

Connecting to %s