Bbox search on geography can miss polygons

[aaime]

The && operator seems to miss polygons in the result set even if the bbox queried is smaller than a quadrant (no arcs longer than 180°).

To reproduce:

• get this world borders dataset: ​http://demo.geo-solutions.it/share/world.tar.bz2
• import into postgis using: shp2pgsql -G -I -s 4326 world.shp world
• execute this query:

SELECT name FROM "public"."world" WHERE "geog" && ST_GeogFromText('POLYGON ((-33.7060546875 40.25390625, -33.7060546875 66.97265625, 32.5634765625 66.97265625, 32.5634765625 40.25390625, -33.7060546875 40.25390625))') order by name;

The result should contain both "SPAIN" and "ANDORRA", but it does not. Just tweaking a little the bbox makes Spain appear, but Andorra is still missing:

SELECT name FROM "public"."world" WHERE "geog" && ST_GeogFromText('POLYGON ((-33.4423828125 38.671875, -33.4423828125 65.390625, 32.8271484375 65.390625, 32.8271484375 38.671875, -33.4423828125 38.671875))') order by name;

System information:

• Ubuntu 10.04 64bit
• Postgis 1.5.2 or trunk at revision 7111, both exhibit similar behavior
• queries are run from psql

[pramsey]

Does this clarify the issue?

​http://www.gcmap.com/mapui?P=W33.7060546875+N40.25390625-W33.7060546875+N66.97265625-E32.5634765625+N66.97265625-E32.5634765625+N40.25390625-W33.7060546875+N40.25390625&MS=wls&DU=mi

[aaime]

I see it. At the same time it seems to make geography data support unusable for WMS purposes. Should I cast everything to geometry when doing the typical wms bbox query? And in that case, wouldn't that result in indexing not being used?

[aaime]

Another option could be, I guess, to densify the geometry used for querying, but it would still be just an approximation...

[pramsey]

Well... "I want to use geography, but I don't want geography semantics" seems like a hard mandate to fulfill in general. If you really don't want geography semantics, use geometry instead, right? Densification seems the obvious first route to fixing particular rectangle issues, in much the same way one would have to densify a correct great circle line to get geography semantics inside geometry. As a more general proposition, your post is the second in as many days with the same issue ("square" boxes not being treated as people expect, probably as the result of query rectangles being drawn on projected maps) so if there's a general utility function that makes sense we should provide it.

[aaime]

Eh well, I guess that people want to use geographic for the right reasons (like dwithin filter support in meter) but at the same time they need to use it for WMS mapping as well. I am actually looking at this one following up a GeoServer bug report, I'm not using it myself: ​http://jira.codehaus.org/browse/GEOS-4400