4

I need a spatial function to detect given polygon is overlapping with existing polygons in the database.

I tried to use ST_Intersects(). It returns true if polygons touch each other. But In my case touching i okay. I also tried ST_Overlaps(). This one returns false if 2 polygons same or contain one another. which is unacceptable.

Currently I'm using SELECT COUNT(f) > 0 FROM fences f WHERE GeometryType(ST_INTERSECTION(f.region, :region)) = 'POLYGON' But this looks like can't use rtree indexes.

Is there a better way to detect polygons overlapping?

hurelhuyag
  • 143
  • 3
  • Are you using ArcGIS Desktop? Try the Topology Validation if you are using ArcGIS. Perhaps this video may help: https://www.youtube.com/watch?v=hCwR0z9mM7Y – TMK Jan 12 '24 at 09:24
  • @TMK No, I'm not using ArcGIS. I just assumed all SQL databases are the same for GIS functionality. Currently, I'm using MariaDB. I also looked into PostGIS documentation. – hurelhuyag Jan 12 '24 at 09:30
  • You could use Something like {ST_intersect()=true AND ST_touches=false} to get only "true" intersection – J.R Jan 12 '24 at 09:45

1 Answers1

5

If you are looking for overlaps in any dimension (i.e. allowing the ST_Touches(a.geom, b.geom) case) use

ST_Intersects(a.geom, b.geom)

If you you need to disallow border-only overlaps, either use

ST_Intersects(a.geom, b.geom)
AND
NOT ST_Touches(a.geom, b.geom)

if a direct index lookup is not supported by the DB engine, or

a.geom && b.geom
AND
ST_Relate(a.geom, b.geom, '2********')

if you can enforce index lookups explicitly - here for PostGIS via a.geom && b.geom, as ST_Relate itself is not sargable.

In either way, if you only want to check for existence of overlapping fences, run

SELECT
  EXISTS (
    SELECT
      1
    FROM
      fences AS f
    WHERE
      <condition>
  )
;

which is much more scalable than the COUNT(*) > 0 approach - if the COUNT interpretation is not specifically optimized in the DB engine.

geozelot
  • 30,050
  • 4
  • 32
  • 56
  • Thanks for SELECT EXISTS() query. I needed a boolean response because spring-data-jpa can't tell if the query result has a row or not. – hurelhuyag Jan 15 '24 at 02:59
  • ST_INTERSECTS(f.region, :region) AND NOT ST_TOUCHES(f.region, :region) passed my tests. And utilizes rtree indexes. – hurelhuyag Jan 15 '24 at 03:03