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;