2

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?

enter image description here

EDIT

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)?

1

Your data is probably being silently cast to geometry when you do that since ST_MakeEnvelope returns a geometry rathan than a geography and so you are doing a planar check rather than a geodetic check. Try instead:

location && ST_MakeEnvelope(65,-77,120,-75)::geography
  • I tried your suggestion but the result was exactly the same. I'm pretty sure, the data is treated as geography. If it would be treated as geometry (lon=x-axis, lat=y-axis), the bounding box would follow the parallels and meridians. – Ludwig Oct 18 '15 at 13:08
  • This brought me to the idea to go the other way around and cast the geography column to geometry. Indeed, this produced the desired result but the select needed very long. I guess casting the column to another type made the query planner not use the index any more. Any ideas solving this question within "geography" functions? – Ludwig Oct 18 '15 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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