Removing overlapping features by percentage in PostGIS

Question

I have geometry records of type MultiLineStringZM and I am trying to INSERT new records that overlap some of the records currently in the table. If they were type MultiPolygon I could use ST_Area() as in the following

SELECT
    a.id
FROM
    a, b
WHERE
    ST_Area(ST_Intersection(a.geom, b.geom)) > 0.5 * ST_Area(a.geom);

to identify the polygons that overlap by a majority. However ST_Area() is not a compatible function for MultiLineStringZM, and the above query does not return any results.

ST_Covers(), ST_Within() & ST_Intersects() will either not return any results or return too many depending.

How can I identify & delete all records overlapped by a 'majority' or some specified fraction for this geometry type?

Answer 1

You could try this, which finds the intersection and calculates the total length of the intersection, then compares that to the line length. Change the tolerance as needed (I'm assuming you want less than 50% overlap):

select id from 
  (select 
    a.id,
    st_length(st_union(st_intersection(a.geom,b.geom))) ilen,
    st_length(geom) len
  from
    a,
    (select st_union(geom) geom from b) b
    group by a.id
  ) foo 
where ilen/len < 0.5;

If you also need to account for an intersection tolerance (if lines are less than 1 m apart, is that still an intersection?), you can use buffers and st_area to determine overlap %. I'm assuming your srid is lat/lon. If it's projected, you don't need the geography casting.

select id from 
  (select 
    a.id, 
    st_area(st_union(st_intersection(a.geom,b.geom))) iarea,
    st_area(geom) area
  from 
    (select id, st_buffer(geom::geography,1)::geometry geom from a) a,
    (select st_union(st_buffer(geom::geography,1)::geometry) geom from b) b
    group by a.id
  ) foo 
where iarea/area < 0.5;