5

My problem is that I have a load of geographic point data (srid 4326) in a table. A user may do a bounding box search - essentially create a polygon as follows:

l_poly geography(Polygon,4326):= 'POLYGON((-120 -89, -120 81, 59.999 81, 59.999 -20, -120 -20))';

... then query using the ST_Intersect predicate

WHERE ST_Intersects(my_tab.point_data, l_poly)

In the above example, I can return a point at lon=0, lat=0. However, once my eastern-most longitude goes above 60 degrees (currently 59.999), then the longitudinal range exceeds 180 degrees, and my polygon wraps the opposite way around the earth. In this case I can not return data at 0 longitude, but I would at e.g. 180 longitude.

It seems as though a geographic polygon cannot be defined that exceeds a longitudinal extent of 180 degrees. Is this true? Can anyone point me in the direction of a solution?

I have tried casting a geography from a geometry defined with enforced Right Handedness, which I thought might define the interior of the polygon to the right hand side of a clockwise drawn polygon. But this doesn't work either.

1

Densify your query polygon. Maybe ST_Segmentize()? Note that the lines between points are naturally going to be great circles, so if your users are drawing boxes on a mercator map, they could get back some results that look very odd to them (boxes you draw with straight lines in mercator will be interpreted as curvey great circles). A "solution" is to ST_Segmentize() sufficiently aggressively that the wee great circle edges coming out have very little curve to them.

  • Oh, you'll have to densify it in geometry space, not geography space, so your densified points will end up being linear interpolations, hope that's what you want. Something like Geography(ST_Segmentize(ST_GeometryFromText('',4326),0.01)) – Paul Ramsey Oct 16 '13 at 23:05
  • Thanks Paul. I think that will work. A geometric box will comprise straight edges in the x and y directions, regardless of the number of segments. Once this is cast to a geography, the lines are all great circles between points on the same lon,lat. – jwovens Oct 17 '13 at 8:37
  • In fact, there is a problem with this approach. ST_Segementize will interpolate points between +178 and -178 by going through +177, +176, ... -177 because the cartesian plane obviously doesn't wrap at +-180. So when this is cast to a geography, it cannot cross the international date line. – jwovens Oct 17 '13 at 12:23
  • Right. I guess I assumed your users were drawing boxes on a bounded cartesian map. – Paul Ramsey Oct 17 '13 at 16:03
0

Can't remember asking this question, but I've stumbled upon it 18 months later and realise I can add an explanation (at least of what I think happens):

PostGIS does not care how you order your points in a geographic (srid 4326) polygon. It is simply an array of points.

My original thoughts were about longitudinal range. In fact, it's not that. It's surface area. Write a simple loop that builds a bbox centred on a point, where the east and west progressively diverged in longitude and the north and south progressively diverged in latitude, for each iteration of the loop. Now compute the area of each iteration...

The area increases to a maximum of half the surface area of the earth, after which is reduces. Visualise this on a sphere rather than a mercator projection. When the polygon is as big as it can be, it divides the earth into 2 hemispheres and it is not unique which hemisphere is being defined. In fact PostGIS throws an error in this case (the exact hemisphere) when trying to compute area. As the x,y s 'diverge' further, in fact they are getting closer on the 'other side of the earth'. PostGIS can only guess which side of the earth you define by your polygon, and so guess the smaller area.

Until PostGIS implement a handedness e.g. a right hand rule, then an array of clockwise points and an array of anti-clockwise points are no different.

As an aside, as I was only bothered by constant lat and lon boxes, I was able to index the st_x and st_y of the point, and construct a bbox query that referenced these coordinates directly rather than using the spatial index.

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.