I have just stumbled upon what seems to be a very curious behaviour by PostGIS.
The XYZ point -117.0, 32.0, 0 in WGS84 (SRID 4326) is legal:
items=> SELECT st_y(st_geomfromtext('POINT Z (-117.0 32.0 0)', 4326)); st_y ------ 32 (1 row) Now consider the XYZ point 32.0, -117.0, 0. It should be illegal under WGS84 (SRID 4326), because earth latitudes should be within `[-90, 90], but PostGIS accepts it:
items=> SELECT st_y(st_geomfromtext('POINT Z (32.0 -117.0 0)', 4326)); st_y ------ -117 (1 row) PostGIS will even calculate the distance between the two illegal points, giving a minor notice:
items=> SELECT st_distance_sphere( (st_geomfromtext('POINT Z (32.0 -117.0 0)', 4326)), (st_geomfromtext('POINT Z (-117.0 32.0 0)', 4326))); NOTICE: Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY CONTEXT: SQL function "st_distance_sphere" statement 1 st_distance_sphere -------------------- 15938523.6399401 (1 row) This caused a very nasty bug with an external library which sanely refused to accept a -117.0 latitude.
Can I block PostgreSQL from accepting coordinates violating WGS84 into the DB?
أكثر...
The XYZ point -117.0, 32.0, 0 in WGS84 (SRID 4326) is legal:
items=> SELECT st_y(st_geomfromtext('POINT Z (-117.0 32.0 0)', 4326)); st_y ------ 32 (1 row) Now consider the XYZ point 32.0, -117.0, 0. It should be illegal under WGS84 (SRID 4326), because earth latitudes should be within `[-90, 90], but PostGIS accepts it:
items=> SELECT st_y(st_geomfromtext('POINT Z (32.0 -117.0 0)', 4326)); st_y ------ -117 (1 row) PostGIS will even calculate the distance between the two illegal points, giving a minor notice:
items=> SELECT st_distance_sphere( (st_geomfromtext('POINT Z (32.0 -117.0 0)', 4326)), (st_geomfromtext('POINT Z (-117.0 32.0 0)', 4326))); NOTICE: Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY CONTEXT: SQL function "st_distance_sphere" statement 1 st_distance_sphere -------------------- 15938523.6399401 (1 row) This caused a very nasty bug with an external library which sanely refused to accept a -117.0 latitude.
Can I block PostgreSQL from accepting coordinates violating WGS84 into the DB?
أكثر...