Checking PostGIS point geometry?

Question

I originated some points with incorrect geometries (ex: 1432.33 , 900.2 for EPSG4326) because lat/lon in data source where also incorrect.

How can I detect in the_geom these incorrect ones?

I think ST_IsValid is not useful, as the ST_Geometry is well formed, even if its data inside is not.

Answer 1

Prevent these by either using a geography type, which forbid these coordinates, or add a check constraint for the geometry column (e.g., geom on my_table):

ALTER TABLE my_table
  ADD CONSTRAINT valid_long_lat CHECK (
    ST_XMin(geom) >= -180 AND ST_XMax(geom) <= 180 AND
    ST_YMin(geom) >= -90 AND ST_YMax(geom) <= 90);

Answer 2

You could query the Point Geometry's X & Y coordinates by using the ST_X & ST_Y.

Your query could be something like:

SELECT * from table 
    where (ST_X(the_geom)>180) OR (ST_X(the_geom)<-180) OR (ST_Y(the_geom)>90)
    OR (ST_Y(the_geom)<-90)

Answer 3

Consider postgis_latlon, an open-source utility suite for PostGIS that I've written to solve similar problems.

latlon_is_valid(geom)

Returns false if any of the points in the geometry has a lat not in [-90, 90] or lon not in [-180, 180].

The package is still in Beta, looking forward for any comments.