CODEWITHSUNDEEP
sqlsql-server
Casey Crookston
Casey Crookston

Reputation: 13955

SQL Query that uses GEOGRAPHY to select record where distances match

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.

Upvotes: 0

Views: 958

Answers (2)

Ben Thul
Ben Thul

Reputation: 32707

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.

Upvotes: 1

Casey Crookston
Casey Crookston

Reputation: 13955

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

Upvotes: 1

Related Questions