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?

  • Does geom definitely has 4326 set? If so, could you provide a couple of example geometries that should be equal? – John Powell Jun 16 '15 at 6:24
  • Unfortunately, our main database server is down and we don't have an ETA on it. However, the data is definitely in 4326. – drunkenwagoner Jun 16 '15 at 23:57

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), 
  • You solution is basically the same as that of the link I provided. Unfortunately, using snaptogrid doesn't seem to help. – drunkenwagoner Jun 16 '15 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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