CODEWITHSUNDEEP
postgresqlpostgisdistance
dailysse
dailysse

Reputation: 25

How to find the shortest distance from the point to the polygon?

Actually the question in the title.

There is a table (osm_buildings) in which the addresses of buildings and their polygons are located. And there is a point, and you need to find the nearest polygons to this point.

Finding the distances between points is very simple and predictable, but how to correctly and most importantly quickly find the distance from the point to the polygon?

Upvotes: 1

Views: 3541

Answers (3)

Grzegorz Grabek
Grzegorz Grabek

Reputation: 980

When you want to return result just for one point at once then answer of Laurenz Albe is perfect. But if you want to return results for more than one point at once I assume you stored buildings in some geometry/geography type field, not as text.

select t2.*, a.*
  from target t2,
  lateral (select o.*
             from osm_buildings o, target t
            where t2.id=t.id
            order by st_distance(o.geom::geography, t.geom::geography) limit 1) a

Also if your data set is big and you accept that from some points there is now close polygon in some acceptable range (for example 1 km) you can add st_dwithin(o.geom,t.geom, your_max_distance) in where clauses in the lateral subquery. If you want to return more then one "closest polygon" just increase the limit.

Upvotes: 0

JGH
JGH

Reputation: 17906

You can use ST_DISTANCE between a point and a polygon, it will return the shortest distance.

SELECT ST_Distance(
        'SRID=4326;POINT(-70 42)'::geometry,
        'SRID=4326;POLYGON((-72 42, -73 42, -73 43, -72 43, -72 42))'::geometry
    );

--> 2

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247950

The distance operator <-> works well between points and polygons.

You can query like this:

SELECT b.*
FROM osm_buildings AS b
ORDER BY b.polygon <-> 'POINT(3.14 2.78)'::geometry
LIMIT 10;

This will get the 10 buildings closest to that point.

That query can use an index on the polygon column.

Upvotes: 5

Related Questions