&& operator doesn't work on Geography

[torlan]

I have the table world_capitals, containing all world capitals in POINT. Query "select * from world_capitals where geog && ST_GeographyFromText('POLYGON((-180 -90,180 -90,180 90,-180 90,-180 -90))')" returns all rows, but query "select * from world_capitals where geog && ST_GeographyFromText('POLYGON((-179 -85,179 -85,179 85,-179 85,-179 -85))')" returns no rows.

[pramsey]

Your second bounding box is a narrow rectangle over the international dateline, there are no world capitals in that box.

Your problem is a specific example of the general issue around describing bounded areas and arcs on a sphere. Since lines are segments of great circles, and coordinate pair has two interpretations, a short one and a long one. As a design decision in PostGIS geography, we decided to always choose the short one.

Microsoft made similar choices in their geography, but also added an "everything" geography to get around the problem of defining a world-covering region. It looks like your version of a world-covering entity did in fact return the right results, but that is not be design: I never tested that case, we just got lucky.

To create a region that covers large swathes of the globe, build it from arcs that are less than 180 degrees apart. That way each arc will be interpreted as the smaller possibility and the region as a whole will bound the area you want.