Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
2  
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
1  
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

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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