Post reply

Nearest match, is this possible ?

  • March 2, 2014 at 11:00 am

    #1693377

    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

  • March 3, 2014 at 11:25 am

    #1693656

    That's great mickyT, thank you and everyone else for your help in this as well as your patience

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply