Search code examples
sqlpostgresqlgispostgis

Why ST_Equals for same geometries returns false?

Why ST_Equals for same objects (one from geometry column and another from geojson extracted from the same geometry column) returns false?

Ex.:

select st_equals(geom, st_setsrid(st_geomfromgeojson(st_asgeojson(geom)), 2180)) from city

Definition of column geom is:

ALTER TABLE city ADD COLUMN geom geometry(MultiPolygon,2180);

EDIT:

Query with st_asgeojson(geom, 999999999) also not working

select st_equals(geom, st_setsrid(st_geomfromgeojson(st_asgeojson(geom, 999999999)), 2180)) from city

EDIT2:

Reproducible example:

SELECT st_equals(geom, st_setsrid(st_geomfromgeojson(st_asgeojson(geom, 999999999)), 2180))
FROM
  (SELECT st_geomfromewkb(decode('AQYAACCECAAABAAAAAEDAAAAAQAAAAsAAAAPqGkZxtsfQR+Tq/icNydBNwTIEZsUIEF/uKr0AfIm
QTsvQxUcvB9Bdw1O9C7IJkEVnpoEBD0fQcFK5/MexSZBwMHT9KXEHkFNt8zxIdwmQezy8+91nx5B
JW6L+tkeJ0G68RXtlYkeQdnY3QSabSdB7MjG/EsBH0FuiKD6qpQnQczdMBNOrB9BK783BvmeJ0GO
URITcx4gQbSMcQaieSdBD6hpGcbbH0Efk6v4nDcnQQEDAAAAAQAAAAUAAABERDkK5GcfQdNUQuem
ZCZBkJbI/psQH0FUMIjfsikmQQDtYP3jBR9BVr0n5jpcJkHS02H+iw0fQQL74OyGjyZBREQ5CuRn
H0HTVELnpmQmQQEDAAAAAQAAAAUAAAAkHKMesHYgQTy3RfkU7iZBi9UrHiJzIEG7SVjr0oMmQR/r
kBTaKSBBH3xW6YJ0JkHnHCARmg8gQbVkpO76nCZBJByjHrB2IEE8t0X5FO4mQQEDAAAAAQAAAAUA
AAA8b3IhIIwgQcCpIQq1bidBINh3JxC6IEFwnmYBGSwnQYN91h44eCBB6PT+/2AhJ0HeIjUWYDYg
QQi4hQfNWidBPG9yISCMIEHAqSEKtW4nQQ==', 'base64')) geom) a
Solution

  • st_asGeoJson reduces coordinates decimals to 9 digits by default. Should the source geometry have more digits, the transformed geometry is therefore different.

    Also geometries are saved using double precision, with 15 significant digits. If your data (input) has less, the remaining digits are still set but can be considered as noise... however this noise is exported/imported back, and can be slightly different than the input.

    The simplest to overcome this is to snap the coordinates to a grid, whose size is your input precision. You should do the snap on the source geometry (when adding the data to your DB), and when reading the geojson

    st_equals(ST_SnaptoGrid(geom,5),ST_SnaptoGrid(st_setsrid(st_geomfromgeojson(st_asgeojson(ST_SnaptoGrid(geom,5))), 2180),5))