# Is there a PostGIS function for checking the validity of a lon/lat pair, given a coordinate system?

I have data with lon lat pairs that I'm converting to geography types. Some of the pairs, however, are corrupt -- in my case, very negative values for longitude.

Is there a helper function in postgis that I can use to exclude such pairs? I'd like to exclude rows whose lon lat values aren't valid given the range of my coordinate system.

I'd like to do something like:

``````select *
from data_with_lonlats
where POINT_IS_VALID(ST_POINT(longitude, latitude), 4326)
``````

where `4326` is my coordinate system, which has range `[-180 -90, 180 90]`.

• As you know the crs range why not something "(longitude BETWEEN -180 AND 180) AND (latitude BETWEEN -90 AND 90)"
– J.R
Aug 22, 2018 at 15:14
• You would use your lat & lon as the rule and exclude lat & lon that weren't true to the rule. So, Select * from table where lat between # and # AND Lon between # and # Aug 22, 2018 at 15:38
• I could! haha. i'm wondering if there's something built-in. It seems like there is... when i try to make a geography for the point, postgres warns that it's coercing the value to fit in the range. is that process using a helper function to check if it needs to coerce the value? if so, can i use it? Aug 22, 2018 at 17:43
• i'd also be interested in something that does this for polygons... and the `between` logic isn't useful there Aug 22, 2018 at 17:44
• – CL.
Aug 23, 2018 at 7:24

## 1 Answer

Here's a function to do this:

``````CREATE OR REPLACE FUNCTION is_valid_geography(geometry) RETURNS boolean AS
'SELECT ST_XMin(\$1) >= -180 AND ST_XMax(\$1) <= 180 AND
ST_YMin(\$1) >= -90 AND ST_YMax(\$1) <= 90;'
LANGUAGE sql IMMUTABLE STRICT;
``````

It should work on any type of geometry.

• thanks - accepting as correct, unless/until someone identifies a built in function Aug 23, 2018 at 15:46