

SSC Veteran
Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287,
Visits: 403


Hi everyone, I have a table that holds UK postcodes with Longitude and Latitude coordinates along with other information. The Lat and Lon are stored as Float numbers. My question is it possible to supply Lat and Lon coordinates 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')




SSC Eights!
Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 890,
Visits: 2,865


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




SSChampion
Group: General Forum Members
Last Login: Today @ 10:38 AM
Points: 12,880,
Visits: 31,798


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




SSC Veteran
Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 291,
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 prefilter 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.




SSC Veteran
Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287,
Visits: 403


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




SSC Veteran
Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 291,
Visits: 519


put a semicolon in front of the with on the CTE




SSC Veteran
Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287,
Visits: 403


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}




SSC Veteran
Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 291,
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




SSC Veteran
Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287,
Visits: 403


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




SSC Veteran
Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 291,
Visits: 519


then the only thing left is @radius



