2

I have a situation where I need to find out if a geometry being imported into a postGIS 2.1 database matches one drawn previously (i.e we want to know if the imported geometry is less up-to-date than the existing one).

I thought I could use st_equals for this, but it isn't working as expected.

This is the code I am using to find if the new geometry exists:

SELECT id FROM "GeomArchive" WHERE ST_Equals(geom, ST_GeometryFromText(wkt,4326));

however, it always returns false even when I pass in a geometry extracted from the "GeomArchive" table.

I saw this question, but the solution didn't help.

I find "=" works, which seems to make it unlikely that the problem is with rounding, since I would have expected a false result with this as well.

I'm not sure what "=" compares. Something I read in the pg documentation seemed to indicate that it actually only compares bounding boxes.

Why is st_equals not working in this situation?

Community
  • 1
drunkenwagoner
  • 714
  • 3
  • 7

1 Answers1

3

I guess your problem is the precision of coordinates.

using "=" operator only compares bounding boxes and they are stored as float4 while the coordinates is float8.

When you extract, I guess you mean using ST_AsText, then you will likely loose some precision. Translating a floating point value to 10 based representation is not always possible.

So try to snap the geom to a grid and then compare like this:

SELECT id FROM "GeomArchive" 
WHERE ST_Equals(st_snaptogrid(geom,0.00001), 
st_snaptogrid(ST_GeometryFromText(wkt,4326),0.00001));
Nicklas Avén
  • 13,241
  • 1
  • 39
  • 48