Preventing invalid values in PostGIS geography

Question

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.

Answer 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.