# ST_Equals Postgis problems

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?

• Can you post an example of geom1 and geom2? – Jakub Kania Mar 28 '13 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 Mar 28 '13 at 5:00
• As mentioned in second code snippet geometries are exactly the same when converted with ST_AsText – Tomislav Muic Mar 28 '13 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 Mar 28 '13 at 10:19
• both columns are 31275 – Tomislav Muic Mar 28 '13 at 10:27

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.

• 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