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

[Paragon Corporation]

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: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Graeme B. Bell
Sent: Tuesday, June 18, 2013 4:13 AM
To: <postgis-users at lists.osgeo.org>
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, 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

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users