1

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?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
dailysse
  • 25
  • 1
  • 3

3 Answers3

5

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Just in case that it is not used for something with so small distances as OSM buildings it is better use geography not geometry. For geometry and long distance it can give strange results when something is directly on axis North South and other building on West-East. Degrees from both axis are not comparable in meter distances for most cases. – Grzegorz Grabek Oct 15 '18 at 14:22
  • But if it is from OSM it should be in 3857 so it should be not a problem in that case. – Grzegorz Grabek Oct 15 '18 at 14:26
0

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
JGH
  • 15,928
  • 4
  • 31
  • 48
0

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.

Grzegorz Grabek
  • 960
  • 7
  • 16