This is a crap-shoot, but I figured I would pick the brains of the more experienced anyway.
As you probably know, you can use the below query to find the distance between two sets of coordinates.
DECLARE @source geography = 'POINT(-93.54803 39.790132)'
DECLARE @target geography = 'POINT(-105.0207 39.9652)'
SELECT @source.STDistance(@target)/1609.344 AS distance_in_miles
I wanted a way to find the distance between two zip codes without having to use the haversine formula, so I wrote this:
DECLARE @source GEOGRAPHY
DECLARE @target GEOGRAPHY
DECLARE @Szip VARCHAR(10) = '64601'
DECLARE @Ezip VARCHAR(10) = '80023'
SET @source = (SELECT longitude+ ' ' +latitude FROM us_loc_data WHERE @Szip = zip)
SET @target = (SELECT longitude+ ' ' +latitude FROM us_loc_data WHERE @Ezip = zip)
SELECT @source.STDistance(@target)/1609.344 AS distance_in_miles
I didn't think the above would work, and it didn't work for this reason:
Msg 6522, Level 16, State 1, Line 5 A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.FormatException: 24114: The label -93.54803 39.790132 in the input well-known text (WKT) is not valid.
My current understanding is, in order to for this to really work, a pair of coordinates encapsulated in parenthesis needs sandwiched between quotations with POINT at the start of the string.
I'm wondering if what I'm attempting to do is remotely possible. Can I somehow assign select statements to my variables (@source & @target) to query the coordinates instead of explicitly specifying which coordinates I want to use?
Example
DECLARE @Origin GEOGRAPHY
DECLARE @Target GEOGRAPHY
DECLARE @Szip VARCHAR(10) = '64601'
DECLARE @Ezip VARCHAR(10) = '80023'
Select @Origin =GEOGRAPHY::Point([latitude], [longitude], 4326) from us_loc_data WHERE @Szip = zip
Select @Target =GEOGRAPHY::Point([latitude], [longitude], 4326) from us_loc_data WHERE @Ezip = zip
SELECT Distance = @Origin.STDistance(@Target)/1609.344
Returns (using my ZIP Code database)
610.093230351351