I am trying to find overlapping lines using ST_EQUALS on two tables

This query returns 0 rows:

SELECT * FROM geom1 c, geom2 h WHERE st_equals(c.simple_geom, h.simple_geom);

This query runs correctly and returns overlapping lines:

SELECT * FROM geom1 c, geom2 h WHERE st_astext(c.simple_geom)=st_astext(h.simple_geom);

I am not sure why ST_equals fails. Both colums are indexed, I thought it was due to geom being multilinestring, but after converting to simple_geom with just LineString (using St_geometryN(geom,1)) it is still giving unexpected results.

So it is not indexes, and neither MultiLineString column.

What are other possible sources of this behaviour?

  • 2
    Can you post an example of geom1 and geom2? Commented Mar 28, 2013 at 0:15
  • Please define what you mean by "overlapping". A straight line A-B-C-D overlaps straight line B-C-D, but the lines are not equal in a way that ST_equals will catch.
    – L_Holcombe
    Commented Mar 28, 2013 at 5:00
  • 1
    As mentioned in second code snippet geometries are exactly the same when converted with ST_AsText Commented Mar 28, 2013 at 8:42
  • An example would help a lot, but I'm guessing you don't have a spatial reference identifier set (e.g. its 0 or -1) for the geometry.
    – BradHards
    Commented Mar 28, 2013 at 10:19
  • both columns are 31275 Commented Mar 28, 2013 at 10:27

1 Answer 1


You probably have a single-bit difference between the coordinates, small enough it doesn't show up in the text output, but large enough that it causes the ordinates to actually differ.

SELECT * FROM geom1 c, geom2 h WHERE 
      ST_SnapToGrid(c.simple_geom, 0.01),
      ST_SnapToGrid(h.simple_geom, 0.01)

If you force them to be identical, it should work.

  • Ok, I'll try that. It seems that this is most probably the answer since the input data was first hand drawn in Autocad and then imported in PostGIS. Commented Mar 28, 2013 at 14:00
  • Yup, this does the trick. I think I will update geometry columns to be snapped to grid. Sub-centimeter precision in geometry isn't really needed and it just produces such weird errors. Commented Mar 28, 2013 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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