Spatial SQL: find nearest river/stream to a field

Question

I have a table of polygons (representing fields) with a polygon geometry (fields) and another table with multi-line geometry (rivers). I want to find the top 5 rivers/lines closest to a particular field, say with ID = '660411001'. Each table has a shape column as geometry.

How do I do this using spatial t-sql query?

Appreciate any help.

Answer 1

Check an example:

declare @Fields table (id int, polygons geometry)

insert @Fields values
( 660411001, 'POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))'),
( 660411002, 'POLYGON ((10 10, 130 10, 140 120, 0 150, 10 10))')

declare @Rivers table (id int, lines geometry)

insert @Rivers values
( 1, 'MULTILINESTRING((210 210, 300 330, 450 50))')

select * from @Fields
select * from @Rivers

select top(5) with ties r.*, f.polygons.STDistance(r.lines) as distance
from @Fields f, @Rivers r
where f.id = 660411001
order by f.polygons.STDistance(r.lines)

Answer 2

Hi，@deskcheck1-0579
Welcome to Microsoft T-SQL Q&A Forum!

I don't have much contact with spatial SQL. Here I provide my ideas and reference documents. In order to find the nearest river, the nearest neighbor query should be used. You can find the concept of this in this article. Find the nearest river, my idea for this is:

1. Merge the objects in the collection polygon table and river
2. Measure the distance of each combination
3. Sort the combinations first by object (origin) in the polygon table, then by distance.
4. For each object in the polygon table, choose the first combination that should be the shortest distance
   Maybe this article will help you.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

For this you can use the STDistance (geography Data Type) function.
https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/stdistance-geography-data-type?view=sql-server-ver15