February 26, 2014 at 5:16 am
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')
February 26, 2014 at 6:20 am
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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 26, 2014 at 6:40 am
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
February 26, 2014 at 7:13 am
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.
February 26, 2014 at 7:23 am
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
February 26, 2014 at 7:27 am
put a semicolon in front of the with on the CTE
February 26, 2014 at 8:18 am
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 : -
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}
February 26, 2014 at 8:54 am
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
February 26, 2014 at 9:08 am
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
February 26, 2014 at 9:12 am
then the only thing left is @radius
February 26, 2014 at 9:32 am
are you sure?
based on the error, i'd think thatthe columns for
dbo.postcodes.Latitude and dbo.postcodes.Longitude
are being converted to match the data type for @Radians(float)
are they possible decimals or varchars?
Lowell
February 26, 2014 at 11:38 am
Hi
You could always use the Geography datatype for this. I've taken your sample and added a primary key, geography column and indexed it. This should work on 2008+.
This technet article has a description of the rules. It refers to SQL Server 2012, however this should work in 2008 +
-- Make sure there is a primary key for the spatial index
CREATE TABLE POSTCODESTEST(ID INT Identity(1,1) PRIMARY KEY, 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')
-- add geography column and index
ALTER TABLE POSTCODESTEST ADD Location Geography
GO
CREATE SPATIAL INDEX PC_SDX ON POSTCODESTEST(Location) USING GEOGRAPHY_GRID
UPDATE POSTCODESTEST SET Location = Geography::Point(Latitude, longitude, 4326) -- check the SRID
-- declare search point
DECLARE @Search Geography = Geography::Point(51.392400,-0.181435,4326)
-- get nearest neighbour
SELECT TOP 1 Postcode, Location.STDistance(@Search) DistanceMetres, Location.STDistance(@Search) / 0.3048 DistanceFeet
FROM POSTCODESTEST
WHERE Location.STDistance(@Search) is not null
ORDER BY Location.STDistance(@Search)
Edit: fixed foot conversion
March 2, 2014 at 3:57 am
EricEyster (2/26/2014)
then the only thing left is @radius
I've now changed @radius type to float and now I get
Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type float.
March 2, 2014 at 4:04 am
Sorry MickyT, I didn't see your post before posting, you're solution works, many thanks
March 2, 2014 at 5:29 am
MickyT, can you explain
UPDATE POSTCODESTEST SET Location = Geography::Point(Latitude, longitude, 4326)
especially 4326, also how to convert your code into a working SP please
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply