I have a PostGIS table with a column
location geography(Point,4326). To select data from a specific region I use
location && ST_MakeEnvelope(65,-77,120,-75).
This may work well for most applications, but my data is in the Antarctic region and there it is not exactly doing what I want.
What the intersection does is creating a rectangle with corners according the boundaries as you can see in the image attached in red. What I want is a selection of all points between latitude -75° and -77° and longitude 65° and 120° (the black polygon).
Is there any possibility in PostGIS to do such a query where the bounding box isn't a rectangle but follows the parallels and meridians?
Having a closer look at the documentation of "ST_MakeEnvelope" and trying it myself seems like
ST_AsText(ST_MakeEnvelope(65, -77, 120, -75, 4326)) simply translates to
"POLYGON((65 -77,65 -75,120 -75,120 -77,65 -77))". The corners of the polygon are logically connected by the shortest line on the sphere/spheroid which is a great circle/geodesic. This is exactly the behaviour I would expect for any test with a polygon, but it's not what I expect from a test for lonmin, latmin, lonmax, latmax.
It seems like ST_MakeEnvelope is not the correct function for such a query, especially close to the geographic poles.
Is there any other possibility without converting the column to geometry (which probably prevents the usage of the index on this column and lasts way too long)?