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 9:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
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

--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 #1545465
Posted Wednesday, February 26, 2014 11:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 1,004, Visits: 3,028
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
Post #1545525
Posted Sunday, March 2, 2014 3:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:23 AM
Points: 287, Visits: 405
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.
Post #1546692
Posted Sunday, March 2, 2014 4:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:23 AM
Points: 287, Visits: 405
Sorry MickyT, I didn't see your post before posting, you're solution works, many thanks
Post #1546693
Posted Sunday, March 2, 2014 5:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:23 AM
Points: 287, Visits: 405
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
Post #1546696
Posted Sunday, March 2, 2014 11:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 1,004, Visits: 3,028
mick burden (3/2/2014)
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


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::Point(@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

Post #1546712
Posted Monday, March 3, 2014 11:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:23 AM
Points: 287, Visits: 405
That's great mickyT, thank you and everyone else for your help in this as well as your patience
Post #1547041
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse