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

[Paul Ramsey]

On Thu, Oct 11, 2012 at 12:12 AM, Nicolas Ribot <nicolas.ribot at gmail.com> wrote:
> 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.

The st_distance code for geography has been sped up, but only for 2.1,
not in the 2.0 series.

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

Not if your table is using the geography type. Geography doesn't care
about datelines or poles, it laughs in the faces of cartesian
singularities.

> 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
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users