Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 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)
share|improve this answer
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
add comment

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);
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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