Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Nearest match, is this possible ? Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 5:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:49 PM
Points: 287, Visits: 407
Hi everyone, I have a table that holds UK postcodes with Longitude and Latitude co-ordinates along with other information. The Lat and Lon are stored as Float numbers. My question is it possible to supply Lat and Lon co-ordinates from a GPS unit and it returns the nearest match. using the table below if i supplied 51.392400,-0.181435 it would return SM4 6DX


USE MISC
GO
-- Creating Test Table
CREATE TABLE POSTCODESTEST(Latitude float, Longitude float, Postcode VARCHAR(20),Info VARCHAR(20))
GO
-- Inserting Data into Table
INSERT INTO POSTCODESTEST(Latitude,Longitude,Postcode,Info)
VALUES(51.401405,-0.196133,'SM4 5DX','Georges Crib')
INSERT INTO POSTCODESTEST(Latitude,Longitude,Postcode,Info)
VALUES(51.392266,-0.181435,'SM4 6DX','Tonys House')
INSERT INTO POSTCODESTEST(Latitude,Longitude,Postcode,Info)
VALUES(51.35898,-0.182877,'SM2 5DX','My local Pub')
INSERT INTO POSTCODESTEST(Latitude,Longitude,Postcode,Info)
VALUES(52.552235,-0.293517,'PE2 5DX','My Garage')

Post #1545337
Posted Wednesday, February 26, 2014 6:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:27 AM
Points: 1,268, Visits: 4,145
How precise is the data supposed to be? I've dabbled a bit with post code stuff myself and some postcodes only differ at the fourth decimal place. For example EN11 9FP has a latitude of 51.77572N and EN11 9FQ has a latitude of 51.77557N. If you're not careful the closest match might not be the right place.


On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help
Post #1545361
Posted Wednesday, February 26, 2014 6:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
I have a couple of examples saved in my snippets.

here's an example of finding points that are within 5 US Miles; you can change the constants to be in kilometers instead.

declare @radius decimal(18,2)
DECLARE @radians float
SELECT @radians = 57.295779513082323 --> 180/PI
SELECT @radius=5
with cteDistCalc
as(

SELECT t1.*,
DistanceInMiles = 3963.0*acos(sin(t1.Latitude/(@radians)) * sin(t2.Latitude/(57.295779513082323))
+ cos(t1.Latitude/(@radians)) * cos(t2.Latitude/(@radians)) * cos(t2.Longitude/(@radians) - t1.Longitude/(@radians)))

FROM dbo.ZipCodes t1
JOIN dbo.ZipCodes t2
on t1.Latitude between t2.Latitude - @radius and t2.Latitude + @radius
and t1.Longitude between t2.Longitude - @radius and t2.Longitude + @radius
where t1.ZipCode='10023'
)
select * from cteDistCalc WHERE DistanceInMiles < @radius
ORDER BY DistanceInMiles



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1545367
Posted Wednesday, February 26, 2014 7:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
We do this all the times for finding the nearest store from your current location. Yes, the big long formula works, is accurate down to inches, and is feasible on smaller data. When you start tracking thousands of data points it will slow down a bit.

we pre-filter the data before sending it through a calculation to get the approximate distance using simple geometry.
1 ) Calculate the lat/long delta for a reasonable search area.
no sense searching a location on the east coast from a location on the west coast

2) use that as a the first level of filter
where dest.lat within current.lat +/- delta
and dest.long within current.long +/- delta

3) order by the full calculation to get the exact result

If you have an index on lat, long SQL will just be comparing numbers without any calculations until the latter steps.

I used this approach on a job search board and the customer could not break it.

Post #1545379
Posted Wednesday, February 26, 2014 7:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:49 PM
Points: 287, Visits: 407
Many thanks for the info, the thing is I can't get the code to work, when I paste it into the query analyser it reports the following error message

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I'm using SQL 2008
Post #1545383
Posted Wednesday, February 26, 2014 7:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
put a semicolon in front of the with on the CTE
Post #1545387
Posted Wednesday, February 26, 2014 8:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:49 PM
Points: 287, Visits: 407
Thanks, but now I'm getting a different error : -
Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type float.

my actual query is : -

[code]
declare @radius decimal(18,2)
DECLARE @radians float
SELECT @radians = 57.295779513082323 --> 180/PI
SELECT @radius=5
;with cteDistCalc
as(

SELECT t1.*,
DistanceInMiles = 3963.0*acos(sin(t1.Latitude/(@radians)) * sin(t2.Latitude/(57.295779513082323))
+ cos(t1.Latitude/(@radians)) * cos(t2.Latitude/(@radians)) * cos(t2.Longitude/(@radians) - t1.Longitude/(@radians)))

FROM dbo.postcodes t1
JOIN dbo.postcodes t2
on t1.Latitude between t2.Latitude - @radius and t2.Latitude + @radius
and t1.Longitude between t2.Longitude - @radius and t2.Longitude + @radius
where t1.postcode='SM4 6DX'
)
select * from cteDistCalc WHERE DistanceInMiles < @radius
ORDER BY DistanceInMiles



[/code}
Post #1545430
Posted Wednesday, February 26, 2014 8:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
Is the lat/long stored as float? you want to make sure all the data types are matching to avoid conversions in the query itself
Post #1545445
Posted Wednesday, February 26, 2014 9:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:49 PM
Points: 287, Visits: 407
EricEyster (2/26/2014)
Is the lat/long stored as float? you want to make sure all the data types are matching to avoid conversions in the query itself


Yes, both Latitude and longitude
Post #1545452
Posted Wednesday, February 26, 2014 9:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
then the only thing left is @radius
Post #1545454
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse