6

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?

  • Are the lines perfectly covering each others (where they overlap), or is there a tolerance to consider? – JGH May 31 '17 at 2:14
  • 1
    if like you said st_area is not compatible with MultiLineStringZM then try using the ST_Force_2D to convert the geometries to 2D – ziggy May 31 '17 at 3:28
  • and then convert to 4D? St_Area(ST_Force2D(geom))? Is any information lost by doing so? – Don P May 31 '17 at 6:57
  • @JGH they do not most of the time, but do directly overlap the lines > 40-50% – Don P May 31 '17 at 7:01
  • 2
    Did you try ST_Length? Only works when the coordinates of the lines are perfectly aligned, as @JGH mentioned. – tilt May 31 '17 at 9:19

Your Answer

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

Browse other questions tagged or ask your own question.