SHORTEST DISTANCE

[Tippelschniff175]

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?

[Tippelschniff175]

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?

[#3]

@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...