Finding all isolated buffer points using PostGIS [closed]

Question

I am doing a project where I find the banks in Ireland using PostGIS and pgAdmin

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?

Answer 1

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:

Answer 2

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)