ST_Equals Postgis problems

Question

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?

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, Mar 28 '13 at 05:00
As mentioned in second code snippet geometries are exactly the same when converted with ST_AsText — Tomislav Muic, Mar 28 '13 at 08: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, Mar 28 '13 at 10:19

score 5 · Accepted Answer · answered Mar 28 '13 at 13:52

5

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_equals(
      ST_SnapToGrid(c.simple_geom, 0.01),
      ST_SnapToGrid(h.simple_geom, 0.01)
   );

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

answered Mar 28 '13 at 13:52

Paul Ramsey

19,865
1
47
57

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. – Tomislav Muic Mar 28 '13 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. – Tomislav Muic Mar 28 '13 at 18:14

ST_Equals Postgis problems

1 Answers1

Linked