6

If I use pgAdmin to insert a point with an invalid latitude, say 91°, I receive the following notice before the query completes successfully:

NOTICE: Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY

If I use a PDO statement in PHP there doesn't seem to be a way to get at that notice. Even if there were, I don't want to find out that the coercion happened after the fact—I want to prevent the record from being inserted in the first place.

Is there any way to have the insertion of invalid geography points fail?

My first thought was to use a check constraint, but the coercion of invalid values happens before the constraint conditions are evaluated. This means that ST_Y always returns the coerced value in the range [-90, 90], and the constraint is never violated.

  • Which version of postgis are you running? My version (2.2) actually calculates back from 90 (92 becomes 88 etc.). This would make (a bit of) sense if the longitude would be swapped by 180 deg, (going over the pole) but it doesn't. I'm curious what the postgis people have to say about this. Is it intentional? – tilt Feb 20 '16 at 13:31
  • To give a more useful comment: you might add values to a lat and long column first, do the constraint check there and add a trigger that creates a geography upon a successful insert (bit of a d-tour, but would work). – tilt Feb 20 '16 at 13:37
  • I have 2.2 running on Windows, and 2.1 under linux. Both of them have the same behaviour you describe, where the point is reflected off the pole instead of correctly passing to the other side of the globe. My hope was that I wouldn't have to store the same information in two different columns -- not exactly good database design -- but it's the only solution I've thought of so far, too. – Michael Underwood Feb 20 '16 at 20:54
  • Ticket over here: trac.osgeo.org/postgis/ticket/3468 – tilt Feb 24 '16 at 16:38
1

If efficiency isn't a huge concern you could first make a GEOMETRY (which won't perform any bounds checking). Then compute its bounds. If the bounds are good, cast it to GEOGRAPHY. If not, raise an exception.

You could encapsulate that logic in a function like this:

CREATE OR REPLACE FUNCTION CastToGeographyWithBoundsCheck(geom GEOMETRY)
        RETURNS GEOGRAPHY AS $$
DECLARE
    bbox BOX2D;
BEGIN
    bbox := Box2D(geom);
    IF ST_XMin(bbox) < -180 THEN
        RAISE EXCEPTION 'Invalid coordinates (longitude < -180)';
    END IF;
    IF ST_XMax(bbox) > 180 THEN
        RAISE EXCEPTION 'Invalid coordinates (longitude > 180)';
    END IF;
    IF ST_YMin(bbox) < -90 THEN
        RAISE EXCEPTION 'Invalid coordinates (latitude < -90)';
    END IF;
    IF ST_YMax(bbox) > 90 THEN
        RAISE EXCEPTION 'Invalid coordinates (latitude > 90)';
    END IF;
    RETURN geom::GEOGRAPHY;
END;
$$ LANGUAGE plpgsql;

Then your INSERT statement could look like this:

INSERT INTO some_table ( ..., my_geography_column )
VALUES ( ..., CastToGeographyWithBoundsCheck(ST_Point( ... )) )

Just replace ST_Point( ... ) with whatever GEOMETRY constructor is appropriate for your application.

  • This is one way to accomplish what I'm after, but in my case efficiency is a pretty major concern, so I ran a few tests. Remarkably, using this function sometimes performs better than simply including ST_Point(...)::geography in the INSERT statement. I don't really have an explanation for that... The ratio of the wall time using this function to that without it ranged between 0.75 and 1.31 over several tests of 100, 1000, or 10 000 inserts each. The average ratio was 1.00, meaning you can perhaps assume that on average there is no performance hit at all. – Michael Underwood Jul 22 '16 at 23:22

Your Answer

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

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