2

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?

7
  • 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, 2015 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, 2015 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, 2015 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, 2015 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, 2015 at 19:30

1 Answer 1

0

ok, if i understood this right you want to prevent posttgres to acepting coordinates out of the -90,90 range.

i think you can use a trigger in order to do that

CREATE OR REPLACE FUNCTION validate_coordinates()
RETURNS TRIGGER AS $$
BEGIN
    IF (NEW.coordinates[0] < -90 OR NEW.coordinates[0] > 90 OR NEW.coordinates[1] < -180 OR NEW.coordinates[1] > 180) THEN
        RAISE EXCEPTION 'Coordinates are not valid or whatever you want to show';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

then you can create your trigger before inserting the points using something like this.

CREATE TRIGGER your_trigger
BEFORE INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION validate_coordinates();

i hope it helps bro

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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