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

[Nicolas Ribot]

Hi Maxime,

Yes indeed, the new geography type take advantage of a very fast Gist
index,often leading to queries faster than their geometry equivalent
(see Paul Ramsey's blog about this new type).
St_distance might also benefit from this new code.

Concerning polygons crossind dateline, yes you will have to cut them
in 2 parts (trigger could be a good option for that)

Finally, yes, this list is the right place to discuss about best
practices and DB optimization.

Nicolas

On 10 October 2012 22:27, Maxime Poulin <mpoulin at expedia.com> wrote:
> 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.
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>