## Nearest match, is this possible ?

 Author Message mick burden Old Hand Group: General Forum Members Points: 305 Visits: 431 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')` BWFC SSCrazy Group: General Forum Members Points: 2418 Visits: 9639 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 Lowell SSChampion Group: General Forum Members Points: 14958 Visits: 38966 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 floatSELECT @radians = 57.295779513082323 --> 180/PISELECT @radius=5with cteDistCalcas(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 t1JOIN dbo.ZipCodes t2 on t1.Latitude between t2.Latitude - @radius and t2.Latitude + @radius and t1.Longitude between t2.Longitude - @radius and t2.Longitude + @radiuswhere t1.ZipCode='10023')select * from cteDistCalc WHERE DistanceInMiles < @radiusORDER BY DistanceInMiles` Lowell--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! EricEyster SSC Veteran Group: General Forum Members Points: 296 Visits: 520 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 coast2) use that as a the first level of filter where dest.lat within current.lat +/- delta and dest.long within current.long +/- delta3) order by the full calculation to get the exact resultIf 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. mick burden Old Hand Group: General Forum Members Points: 305 Visits: 431 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 messageIncorrect 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 EricEyster SSC Veteran Group: General Forum Members Points: 296 Visits: 520 put a semicolon in front of the with on the CTE mick burden Old Hand Group: General Forum Members Points: 305 Visits: 431 Thanks, but now I'm getting a different error : - Msg 8115, Level 16, State 2, Line 5Arithmetic overflow error converting expression to data type float.my actual query is : -[code]declare @radius decimal(18,2)DECLARE @radians floatSELECT @radians = 57.295779513082323 --> 180/PISELECT @radius=5;with cteDistCalcas(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 t1JOIN dbo.postcodes t2 on t1.Latitude between t2.Latitude - @radius and t2.Latitude + @radius and t1.Longitude between t2.Longitude - @radius and t2.Longitude + @radiuswhere t1.postcode='SM4 6DX')select * from cteDistCalc WHERE DistanceInMiles < @radiusORDER BY DistanceInMiles[/code} EricEyster SSC Veteran Group: General Forum Members Points: 296 Visits: 520 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 mick burden Old Hand Group: General Forum Members Points: 305 Visits: 431 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 itselfYes, both Latitude and longitude EricEyster SSC Veteran Group: General Forum Members Points: 296 Visits: 520 then the only thing left is @radius