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