2

I am doing a project where I find the banks in Ireland using PostGIS and pgAdmin I have marked the points that I want to find

I want to run a query that finds all the buffer spaces that do not intersect with any nearby buffers. I am using St_intersect after applying the buffer but that does not seem to work.

How do I find the isolated points in the picture above?

3
  • 2
    Welcome to Geographic Information Systems! Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Your questions should as much as possible describe not just what you want to do, but precisely what you have tried and where you are stuck trying that. Please check out our short tour for more about how the site works. Please add the SQL you are using and a description of what isn't working
    – Ian Turton
    Commented Dec 27, 2022 at 15:25
  • How many buffers are there?
    – Bera
    Commented Dec 27, 2022 at 18:13
  • For proximity searches better use ST_DWithin.
    – geozelot
    Commented Dec 30, 2022 at 16:36

2 Answers 2

3

You can use ST_ClusterDBSCAN:

with cte as (
select id, ST_ClusterDBSCAN(geom, eps := 0, minpoints := 2) over () AS cluster_id, geom
from public.buffers100k)
select id, geom from cte where cluster_id is null

< 1 s. for 100 000 buffers: enter image description here

1

You need to test the intersection on itself using a left join. Then you can do a group by using the geometry and finally set a filter only on those that have a count(1) equal to one. According to your data you need to replace the name of the initial points table in the with clause and you need to rename all occurrences of the geometry column.

with sub as(
    select
        *,
        st_buffer(geometry, 15000) as geometry
    from 
        points
) 
select
    *
from sub a
left join sub b
on st_intersects(a.geometry, b.geometry)
group by a.geometry
having count(1) = 1

Update If you have more than the geometry column this will work (we need to rejoin with the first sub-query). But maybe there is a faster solution.

with sub as(
    select
        st_buffer(geometry, 15000) as geometry
    from 
        points
)
select
    *
from (
    select 
        *
    from (
        select
            a.geometry,
            count(1) as count
        from sub a
        left join sub b
        on st_intersects(a.geometry, b.geometry)
        group by a.geometry
    ) sub2
    where sub2.count = 1
) sub3
left join sub
on st_equals(sub.geometry, sub3.geometry)

enter image description here

1
  • 1
    This can be restated as "Find polygons which are not polygons which intersect another one", which can be solved by using the anti-join pattern. Use a NOT EXISTS query as described here.
    – dr_jts
    Commented Dec 29, 2022 at 4:55

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