On Thu, Oct 11, 2012 at 12:12 AM, Nicolas Ribot <nicolas.ri...@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 <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 _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
