2

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

7
  • 2
    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 #
    – enolan
    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
  • 1
    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
  • 1
    PostGIS does not know coordinate system bounds.
    – CL.
    Aug 23, 2018 at 7:24

1 Answer 1

4

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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