I have three points:
- Point A
- Point SW
- Point NE
I want to know if Point A resides within the bounds determined by points SW (for south-west coordinates) and NE (for north-east coordinates).
I've so far tried both ST_Intersects
and &&
to determine this, which works fine for small regions, but fails whenever the bounding area determined by SW and NE points covers more than half the globe. In this case it seems to instead use a different, smaller, bounding area using the same four points.
As an example, if I'd want to cover everything from the SW point of South Africa to the NE point of Ireland it would instead catch points where the the NW point is in Ireland and the the SE point is in Madagascar. That is, China would not considered to be within the bounds.
Here are some SQL queries to illustrate this:
select
ST_GeographyFromText('SRID=4326;POINT(0 0)') && ST_GeographyFromText('SRID=4326;POLYGON((180 -80, -80 -80, -80 80, 180 80, 180 -80))') as expectTrue_1,
ST_Intersects(ST_GeographyFromText('SRID=4326;POINT(0 0)'), ST_GeographyFromText('SRID=4326;POLYGON((180 -80, -80 -80, -80 80, 180 80, 180 -80))')) as expectTrue_2,
ST_GeographyFromText('SRID=4326;POINT(0 0)') && ST_GeographyFromText('SRID=4326;POLYGON((180 -80, 180 80, -80 80, -80 -80, 180 -80))') as expectTrue_3,
ST_Intersects(ST_GeographyFromText('SRID=4326;POINT(0 0)'), ST_GeographyFromText('SRID=4326;POLYGON((180 -80, 180 80, -80 80, -80 -80, 180 -80))')) as expectTrue_5,
ST_GeographyFromText('SRID=4326;POINT(-90 0)') && ST_GeographyFromText('SRID=4326;POLYGON((180 -80, -80 -80, -80 80, 180 80, 180 -80))') as expectFalse_1,
ST_GeographyFromText('SRID=4326;POINT(0 0)') && ST_GeographyFromText('SRID=4326;POLYGON((1 -1, -1 -1, -1 1, 1 1, 1 -1))') as expectTrue_5;
I'd want for this to return t t t t f t
but it returns f f f f t t
.
I've also tried ST_Intersects(ST_MakeBox2D())
as per the following SO post:
https://stackoverflow.com/questions/24212355/query-by-coordinates-takes-too-long-options-to-optimize/24218861?iemail=1&noredirect=1#24218861
I'm using PostGIS 2.1.3 with the Geography
data type.
What functions or approaches should I use to have PostGIS treat my more-than-half-globe-spanning polygons as more-than-half-globe-spanning ones instead of 'optimizing' them to be less?
I understand I could just split the bounds in, say, two parts, and check for each, but there must be a cleaner solution.