SQL Query that uses GEOGRAPHY to select record where distances match

Question

On this thread, I found this example:

DECLARE @source geography = 'POINT(-94.25 45.46)'
DECLARE @target geography = 'POINT(-94.19 45.57)'
SELECT (@source.STDistance(@target)/1000) * 0.62137

This accurately tells me that there are ~8+ miles between the two points. That's VERY helpful. But, now what I am trying to do is a bit more complex.

I have a table, Criteria that looks like this:

ID    State    Zip    Lat     Long    Radius
------------------------------------------------
1       MN    56301   45.46  -94.25    25

There are more records that this, but that's enough for our purposes. Now, I need to query for record where either there is a direct State match, or a direct Zip match, or the range matches. So...

DECLARE @CompareState VARCHAR(2) = NULL
DECLARE @CompareZip VARCHAR(5) = NULL
DECLARE @CompareLon DECIMAL = -94.19
DECLARE @CompareLat DECIMAL = 45.57

SELECT
    *
FROM
    Criteria c
WHERE 
    c.State = @CompareState
    OR c.Zip = @CompareZip
    OR (Distance between two sets of Lat and Long is <= c.Radius)

In the query above, the row with ID of 1 should be returned. I'm struggling with the syntax.

score 1 · Accepted Answer · answered Jan 31 '17 at 20:40

Got it.

DECLARE @CompareState VARCHAR(2) = NULL
DECLARE @CompareZip VARCHAR(5) = NULL
DECLARE @CompareLon DECIMAL = -94.19
DECLARE @CompareLat DECIMAL = 45.57

SELECT
    *
FROM
    LeadSalesCampaignCriterias c
    JOIN LeadSalesCampaignCriterias c2 
        ON c.LeadSalesCampaignCriteriaID = c2.LeadSalesCampaignCriteriaID 
        AND c2.Latitude IS NOT NULL 
        AND c2.Longitude IS NOT NULL
WHERE 
    c.State = @CompareState
    OR c.Zip = @CompareZip
    OR 
    (
        ((geography::Point(c2.Latitude, c2.Longitude, 4326).STDistance(geography::Point(@CompareLat, @CompareLon, 4326))/1000) * 0.62137) < c.Radius
    )

I honestly don't know what 4326 is.
See: https://msdn.microsoft.com/en-us/library/bb933811.aspx

score 1 · Answer 2 · answered Jan 31 '17 at 22:58

I cribbed your answer and changed it a bit for efficiency.

DECLARE @CompareState VARCHAR(2) = NULL
DECLARE @CompareZip VARCHAR(5) = NULL
DECLARE @CompareLon DECIMAL = -94.19
DECLARE @CompareLat DECIMAL = 45.57
-- you appear to be wanting to find things within 1 mile
-- the magic number 1609.34 is the number of meters in a mile
DECLARE @RangeDisk geography = geography::Point(@CompareLat, @CompareLon, 4326).STBuffer(1609.34);

SELECT
    *
FROM
    LeadSalesCampaignCriterias c
    JOIN LeadSalesCampaignCriterias c2 
        ON c.LeadSalesCampaignCriteriaID = c2.LeadSalesCampaignCriteriaID 
        AND c2.Latitude IS NOT NULL 
        AND c2.Longitude IS NOT NULL
WHERE 
    c.State = @CompareState
    OR c.Zip = @CompareZip
    OR geography::Point(c2.Latitude, c2.Longitude, 4326).STIntersects(@RangeDisk) = 1

A couple of other notes. If you can alter your table to have the geography column pre-computed, that will make this even better as you won't have to convert it on the fly in the where clause (that predicate would take the form of new_column.STIntersects(@RangeDisk) = 1). A spatial index on that new column will do wonders for the efficiency of the query!

I'm also a little confused by the self join. Is LeadSalesCampaignCriteriaID the primary key in the table? If so, I don't think that join is necessary (and is very likely hurting your performance).

Lastly, in your self-answer, you mentioned not knowing what the magic number 4326 was. It's called a spatial reference id (aka SRID). Essentially, there have been multiple attempts historically to model the earth. When you get representations of geographic features from external sources, they will have been created with one of those systems in mind. Even if you're creating them whole cloth though, you need to know what the unit of measure is (when you compute something like distance, for example). You can see properties of the SRIDs that SQL knows about in sys.spatial_reference_systems.

Thank you for this! I'm not sure I understand adding a column in the table for the geography. What data would that column hold. — Casey Crookston, Feb 01 '17 at 14:18
Yes, LeadSalesCampaignCriteriaID is the primary key. I did that join because it's possible that LAT and LON are null, so I needed a way to only do the join when they are not null. I am open to better ways! — Casey Crookston, Feb 01 '17 at 14:18
And last of all, I am curious why you used =1. I need to compare to the value in the column Radius, and not to a hard coded value. — Casey Crookston, Feb 01 '17 at 14:23
In turn, 1) That column would hold the geography value `geography::Point(Latitude, Longitude)`. This way, you don't have to compute it at run time. 2) you can just do a select from the table `where Latitude is not null and Longitude is not null`. 4) because I changed the test from a distance check to an intersection check with a disk of that radius. Intersection is binary; two geography instances either intersect or they don't. So, that predicate says "… where the point and the disk intersect". Said another way "… where the point is inside the disk". — Ben Thul, Feb 01 '17 at 15:55

SQL Query that uses GEOGRAPHY to select record where distances match

2 Answers2