3

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.

2

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);
  • 1
    or you can create polygon for "correct" area and use it as constraint. If your data is only one one country you can gadm.org shapefiles – simplexio Feb 25 '13 at 9:34
1

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)
  • 1
    slightly more compact: ST_X(the_geom) not between -180 and 180 or ST_Y(the_geom) not between -90 and 90 – Mike T Feb 25 '13 at 9:12
0

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.

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.