Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nearest match, is this possible ?


Nearest match, is this possible ?

Author
Message
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38956
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

--
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!

mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
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:Tongueoint(Latitude, longitude, 4326) -- check the SRID

-- declare search point
DECLARE @Search Geography = Geography:Tongueoint(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
mick burden
mick burden
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 431
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.
mick burden
mick burden
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 431
Sorry MickyT, I didn't see your post before posting, you're solution works, many thanks
mick burden
mick burden
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 431
MickyT, can you explain

UPDATE POSTCODESTEST SET Location = Geography:Tongueoint(Latitude, longitude, 4326)

especially 4326, also how to convert your code into a working SP please
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
mick burden (3/2/2014)
MickyT, can you explain

UPDATE POSTCODESTEST SET Location = Geography:Tongueoint(Latitude, longitude, 4326)

especially 4326, also how to convert your code into a working SP please


The 4326 reference is the SRID. This stands for Spatial Reference Identifier. The Geography data type requires that a SRID is specified, so that it can determine distances and areas accurately.

I picked 4326 (WGS84) as this is the most common of the projections and what most(all) GPS units work on.

To get a list of valid SRIDs for a geography
select * from sys.spatial_reference_systems where spatial_reference_id = 4326



When doing spatial operations, your Geographies must have the same SRID.

As far as a procedure to do a nearest neighbour and assuming you have created a Geography column, this should do the trick
CREATE PROCEDURE findNearestPostcode(@Lat Float, @Long Float)
AS
BEGIN
-- Create the search point
DECLARE @Search Geography = Geography:Tongueoint(@Lat,@Long,4326)

-- Select the nearest postcode
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)
END;
GO


mick burden
mick burden
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 431
That's great mickyT, thank you and everyone else for your help in this as well as your patience
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search