SHORTEST DISTANCE

%3CLINGO-SUB%20id%3D%22lingo-sub-1217593%22%20slang%3D%22en-US%22%3ESHORTEST%20DISTANCE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1217593%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3EHi%20all%2C%20I%20am%20looking%20to%20create%20a%20function%20that%20i%20can%20then%20use%20on%20two%20tables.%20The%20first%20table%20with%20UPRN%20(unique%20premise%20ref.)%2C%20Latitude%20and%20Longitude%20coordinates%2C%20geolocation%20and%20type.%20The%20second%20table%20is%20identical%20but%20of%20a%20different%20type.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20to%20create%20a%20function%20that%20then%20lists%2C%20for%20each%20UPRN%20in%20table%201%2C%20the%20nearest%20UPRN%20(in%20order%20of%20distance)%20from%20table%202.%20I%20have%20looked%20at%20using%20the%20Geography%20type%20but%20am%20unsure%20how%20to%20build%20it%20into%20a%20function%20that%20i%20can%20then%20use%20on%20table%201.%20Can%20anyone%20please%20help%3F%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all, I am looking to create a function that i can then use on two tables. The first table with UPRN (unique premise ref.), Latitude and Longitude coordinates, geolocation and type. The second table is identical but of a different type.

 

I am looking to create a function that then lists, for each UPRN in table 1, the nearest UPRN (in order of distance) from table 2. I have looked at using the Geography type but am unsure how to build it into a function that i can then use on table 1. Can anyone please help?

1 Reply

@Tippelschniff175 

Do you simply mean straight distance? I think I can use STDistance function.

 

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.STDistance(@h);

 

https://docs.microsoft.com/nl-be/sql/t-sql/spatial-geography/stdistance-geography-data-type?view=sql...