SELECT * FROM centroids WHERE centroid = ST_GeomFromText('POINT (21.6198205 41.9418615)', 4326);
Record have centroid geometry equal to POINT (21.619820510769063 41.94186153740355)

Running on PostgreSQL with PostGIS 2.3 db shows me this record.
Running on PostgreSQL with PostGIS 2.4 db doesn't show me anything.

When I adjust query to exact geometry as in the record, so it is
SELECT * FROM centroids WHERE centroid = ST_GeomFromText('POINT (21.619820510769063 41.94186153740355)', 4326); it shows me record on both databases.
I couldn't find anything about how exactly does ST_GeomFromText work from technical side.
Is it correct behaviour?

  • 3
    I think you may be framing this problem incorrectly. Point equivalence is unrelated to ST_GeomFromText use. It fact, the WKT parser is masking the fundamental issue of whether 1cm of precision is sufficient to define a point, or whether nanometer precision is required. Keep in mind that some floating-point vales are impossible to represent as text, so doing such comparisons is inherently risky. – Vince May 2 '19 at 12:27
  • 3
    You should do such comparisons by adding postgis.net/docs/manual-2.0/ST_SnapToGrid.html to the chain for accepting some tolerance. – user30184 May 2 '19 at 12:37
  • 2
    Also the definition of equality has changed between 2.3 and 2.4. This article is worth reading. – JGH May 2 '19 at 12:49
  • For my task, 1cm of precision is enough. I already have in database points with higher precision. My idea now is to snap or round existing geometries, and update database writer to write new geometries with centrimetre precision. Current geometries I can snap using ST_SnapToGrid, but I've noticed that some of them are snapped incorrectly, such as POINT (21.619820510769063 41.94186153740355) to POINT (21.6198205 41.941861499999995). Don't know if I should create a different question for that issue. I understand, that my problem was related to newer (2.4) PostGIS version. – kujaw May 6 '19 at 10:04

Your Answer

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

Browse other questions tagged or ask your own question.