[postgis-users] Geographies, geometries, international date line

[Maxime Poulin]

Hi everyone.
And so this is my first post to this mailing list, and since I registered, I haven't seen so many messages so I hope the level of this email is up to this list's standards.

Here is the situation.
At Expedia, we are working on upgrading our geo system and that implies migrating to PostGIS.

During my tests, I did the following:

I have two tables for regions, one, say region_geometry, keeps the polygon as a geometry(4326). The second one, say region_geography, keeps the polygon of a region as a Geography(4326).

We also have other tables mapping hotels and points of interests, both storing their location as a geography point(4326).

The test case is to find everything that is within a region. In other words, find all hotels and points of interests that are within a given region's polygon.

The first test uses the region_geometry table, and the query uses ST_Within function (using geometries and the && operator).
The second test uses the region_geography table, and the query uses ST_Intersects function (using geographies).

I was expecting the geometry version to be much faster, but I actually find that performance is somewhat the same. Both queries use the special index, perform the index scan and so on.  So how can this be possible, how can I be sure that geometries are actually faster than geographies ?

The main issue with these two tests is regarding polygons crossing the international date line. So suppose I have a polygon that maps the Fiji islands. That polygon will cross the international date line. The geography method works fine, but the polygon, when used in geometry world, is actually a ribbon around the planet. I absolutely understand the reason, but if I really want to stay in the geometry world, how can I work around this problem ? Do I really have to split my polygon to create 2 polygons not crossing the line ? Do I have to filter my requests, find if the polygon crosses the date line and if so, "revert" to geographic calculations ?

I have somewhat the same questions regarding distance calculations. In geometry world, if I want all the hotels in a 200 meters radius of a given point, I would use something like ST_Distance_spheroid(p1, p2) < 200. In geography, I would use ST_DWithin(p1, p2, 200). My tests shows that STD_Within (geography, geography) is most of the time faster than st_distance(p1, p2) with or without spheroid. Again, in both cases, the spatial indexes are used.

I have many quite precise and edgy questions regarding good practices and performance tuning for PostGIS.  I would like to provide samples and discuss around them, see what I / we can find. Is this list the right place to ask ?

Maxime Poulin.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121010/33106eee/attachment.html>