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));
(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));
(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
(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?

  • Since you clearly know how to perform such a check in a trigger, it appears your question really is, "Can I perform horizon validation at zero cost?" To which the answer is, "No". – Vince May 31 '15 at 14:24
  • @Vince thanks. I do know how to validate my input, but I thought that PostgreSQL might have a configuration flag that would reject illegal output for me. It is worth mentioning that I only discovered one flaw, and there might be quite a few more. – Adam Matan May 31 '15 at 14:35
  • There are an infinite number of flaws in data capture, but only a small number that could be automated, and none at no cost. If doing valudation checks reduces input speed from 5000 features/sec to 200 f/s, folks will likely notice. – Vince May 31 '15 at 15:11
  • You should note that for some values of 4326 your first coordinates are illegal and the second ones are valid. If in doubt don't use 4326 – Ian Turton May 31 '15 at 17:11
  • @Vince The same way I expect floats and integers to be, well, floats and integers, I expect WGS84 Coordinates to be WGS84 Coordinates. Allowing latitude of 117 is the same as allowing division by zero because it saves validation time. – Adam Matan May 31 '15 at 19:30

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.