4

I have a table with points and another table with polygons (multipolyons to be more accurate). How do I find the points that fall within a specified distance of the polygon's border? Most of the points fall within a polygon but I want a list of points that fall within 100 meters of a polygon edge. If a point is 20 meters from a boundary that is shared by adjacent polygons, I'd expect that point to be listed within 100 meters of two polygons.

The SQL below selects too many points. It select points inside the polygon but outside the interior buffer. It should not select points inside the polygon and greater than 100 meters from the edge.

select * 
from towers t, areas c
where st_dwithin(t.geom, c.geom, 100.00)
order by t.description

How do I modify this query to return points that are within 100 meters of a polygon's boundary, not necessarily all points within the poylgon?

  • 2
    I still don't get what you're trying to achieve. Can you share table definitions and data? And possibly mark the expected ids. – Michal Zimmermann Oct 26 '15 at 17:22
  • You still have duplicates using my second option? This sound plain impossible with a DISTINCT statement?! My code worked fine on a test case (but I'll double check tomorrow). So I agree with Michal at this stage we need more info about your tables. – MarHoff Oct 26 '15 at 17:41
  • Ok after seeing your comment it's definitely the variant you'll have to use. Are you sure you didn't add a mistake when implementing? As far as I tested my code seemed to work... – MarHoff Oct 26 '15 at 17:42
  • @DenaliHardtail then use my earlier code without the EXCEPT part. – Michal Zimmermann Oct 26 '15 at 17:43
  • @MarHoff, no duplicates with that option., it's fine. – DenaliHardtail Oct 26 '15 at 17:43
4

This one was tricky so i decomposed a lot using CTE.

As I understood question he want to find points within 100 meters from any boundary, whether it's inside our outside the polygon. I also provide a variant for case where he want to remove duplicated points (in reach of more e than one polygon).

I'm assuming all along that you table initial tables are called 'mutipolytable' and 'points'.

WITH 
--Dump as single polygon
poly as (SELECT *, (ST_dump(geom)).geom AS geom_poly FROM mutipolytable),
--Dump every rings in polygon
rings as (SELECt *, (ST_DumpRings(geom_poly)).geom as geom_rings from poly),
--Convert every rings to linestrings
boundary as (SELECT *, ST_boundary(geom_rings) as geom_boundary from rings)

--Join points table to boundary using ST_DWithin
SELECT boundary.*, points.* FROM
point JOIN boundary
ON ST_DWithin(points.geom,boundary.geom_boundary,100)

Alternatively should your goal be to select point near boundary without need to keep information about which polygon is in reach just replace the final SELECT statement by this one.

SELECT DISTINCT point.* FROM
point JOIN boundary
ON ST_DWithin(point.the_geom,boundary.geom_boundary,100)
  • 1
    You're right. I think he means points outside and inside a polygons boundary within a distinct distance. I tried your query. Good to see, that it is as fast as my (kind of slight) approach (assuming there are spatial indices). – Stefan Oct 26 '15 at 10:32
  • You can also use ST_ExteriorRing for ST_DumpRings+ST_Boundary. – Stefan Oct 26 '15 at 13:40
  • Was not pretty sure about this, i guess it depend whether he want to select point near potential "holes" inside the polygon. I considered he want them, but to remove them your solution is eventually even simpler. Now would be a nice time for him to provide some feedback, I'm not even sure it is what he was looking for :/ – MarHoff Oct 26 '15 at 14:23
0

EDIT:

After a couple of times reading the question:

This answer works only for points outside of polygons


You can exclude the points within the polygons with the function ST_Contains.

SELECT * 
FROM points a, polygons b
  WHERE ST_DWithin(a.geom, b.geom, 100.00)
    AND NOT ST_Contains(b.geom, a.geom);

With NOT you select only the points that are placed outside the polygons.

This query will be really fast, when you have spatial indices for your tables.

You have a table with multipolygons. With the following query you can group the resulting points for every polygon of the type multipolygon:

WITH single_obj AS (
SELECT
  id,
  name,
  (ST_Dump(geom)).geom
FROM polygons
)
SELECT
  b.id,
  b.name,
  count(a.*),
  ST_Collect(a.geom) AS geom 
FROM points a, single_obj b
  WHERE ST_DWithin(a.geom, b.geom, 100.00)
  AND NOT ST_Contains(b.geom, a.geom)
    GROUP BY b.id,b.name;
-1
SELECT *
FROM towers t
JOIN areas c ON ST_DWithin(t.geom, c.geom, 100)
EXCEPT
SELECT *
FROM towers t
JOIN areas c ON ST_Within(t.geom, c.geom);

This gives you all points that are within 100m metres yet not exactly within the polygon. But I'm not really sure whether this is what you're looking for.

UPDATE:

SELECT *
FROM towers t
JOIN areas c ON ST_DWithin(t.geom, c.geom, 100)

This gives you all the points that are within 100m from any boundary of any polygon. There might be duplicated geometries when the point lies within 100m from more than one polygon. Use DISTINCT to keep only unique geometries.

  • Sorry Michal but even your updated code keep selecting all points that are inside a polygon. DenaliHardtail wanted only point near a border, disregarding wether they are inside or outside. If you work with contiguous polygons that cover all the studied area your query will basically select all point and that is not the goal. – MarHoff Oct 27 '15 at 7:34
  • You're right, I realized it yesterday, but was too lazy to fix it. Will look into it today (hopefully). – Michal Zimmermann Oct 27 '15 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.