Graeme, Can you also try your test dropping the ::geometry cast? ~= is natively supported for raster I believe so no need to apply cast again though it probably ends up being the same but would be curious to see if performance is a bit better
Thanks, Regina -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Graeme B. Bell Sent: Tuesday, June 18, 2013 4:13 AM To: <[email protected]> Subject: Re: [postgis-users] selected aligned tiles in pgraster between several large raster tables. bborie, That's extremely interesting! That's exactly the type of operator I was hoping to hear about when I wrote my original post. I expect it will run a little slower than the current approach, because bbox equality and bbox index lookups are never going to be as fast as an integer primary key. To check this, I ran a quick test on a smaller dataset (100x100 meter resolution) replacing "on a.rid=b.rid", "on a.rid=c.rid", and so on, with "on A.rast::geometry ~= B.rast::geometry". I'm using postgis 2.0.3. Runtime with ".rid = " was 28 seconds Runtime with ".rast::geometry ~=" was 339 seconds. At a first glance it seems to be slightly more than 12 times slower to use the BBOX comparison rather than the rid. The returned rows were identical. There's also the little question of 'floating point instability'. Hopefully it's not a problem, but it might be important to test that raster2pgsql predictably outputs exactly the same bounding box on all the millions of records in each case. It "almost certainly does", of course, which is similar to the issue with RID numbering. Thought provoking. Thanks! Graeme. On Jun 17, 2013, at 9:00 PM, [email protected] wrote: > Graeme, > > robe2 and I were discussing this thread and we were wondering if using > the ~= operator would work for your problem. > > http://www.postgis.net/docs/manual-2.0/ST_Geometry_Same.html > > -bborie _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
