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;

Winmerge configuration settings

This note is to myself, about how we setup Winmerge. First install Winmerge

Then in VS 2015

Tools

Options

Source Control “Tab”

Visual Studio Team foundation Server “Tab”

 

Click Configure User Tools

Configure User Tools dialog appears:

Click Add:

  • Extension: *
  • Operation: Compare
  • Command: Your full path to WinMergeU.exe
  • Arguments: /e /u /wl /wr /dl %6 /dr %7 %1 %2

 

Click Add:

  • Extension: *
  • Operation: Merge
  • Command: Your full path to WinMergeU.exe
  • Arguments: /e /u /wl /dl %6 /dr %7 %1 %2 %4

Super fast SQL Batch Updates

How to avoid slow SQL updates when updating many rows of data.

If you have ever needed to update 12000 rows of data in a database, using 12,000 individual updates, you probably had to wait quite a while for that update to run. Let’s say your update was something like:

UPDATE people set changeDate = GetUTCDate(), firstName = 'Sally Mae' where firstName = 'Sally'
UPDATE people set changeDate = GetUTCDate(), firstName = 'John Bob' where firstName = 'John'
UPDATE people set changeDate = GetUTCDate(), firstName = 'Billy Bob' where firstName = 'Billy'
... (and 11,997 more rows)

You were smart and create a script or some code to generate your 12,000 lines of updates so congrats for that, but running them takes FOREVER!

My friend Paul showed me this method of doing updates and it is incredible how much faster this batch update runs:

UPDATE p
Set changeDate = GetUTCDate(), firstName = newFirstName
FROM (
SELECT newFirstName = 'Sally Mae', oldFirstName = 'Sally' UNION ALL
SELECT newFirstName = 'John Bob', oldFirstName = 'John' UNION ALL
-- ... (and 11,997 more rows)
SELECT newFirstName = 'Billy Bob', oldFirstName = 'Billy' 
) subQ 
JOIN people p on p.firstName = subQ.oldFirstName 

What is the above doing?

First you can think of it creating a temporary result set like this called ‘subQ’:

newFirstName oldFirstName
Sally May Sally
John Bob John
Billy Bob Billy

Then it joins subQ to the people table on the subQ.oldFirstName = people.firstName. Then it updates each row of that joined set in a single action rather than 12,000 separate actions.

How do I update multiple tables at the same time?

Taking the above example a step further, let’s say that while your updating everyone’s name, you also want to update their zipcodes which is stored in an Address table tied to the person table with the person.AddressId foreign key.

You can add it in to the same query like this so it all runs in a single action:

UPDATE p
Set p.changeDate = GetUTCDate(), p.firstName = newFirstName, a.zipcode = newZipCode
FROM (
SELECT newFirstName = 'Sally Mae', oldFirstName = 'Sally', newZipCode = '11111' UNION ALL
SELECT newFirstName = 'John Bob', oldFirstName = 'John', newZipCode = '11111'
-- ... (and 11,997 more rows)
UNION ALL SELECT newFirstName = 'Billy Bob', oldFirstName = 'Billy', newZipCode = '11111' 
) subQ 
JOIN people p on p.firstName = subQ.oldFirstName
JOIN address a on a.AddressId = p.AddressId