[postgis-users] selected aligned tiles in pgraster between several large raster tables.

[Graeme B. Bell]

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, postgis-users-request at lists.osgeo.org 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