Wednesday, 7 August 2019

postgis - Bounding box along parallels and meridians


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




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...