Apologies, I did not notice that gdal_translate could take a where clause in the connection string. Everything now works as I wanted.
many thanks Darrel On 4 Feb 2012, at 22:03, "Darrel Maddy" <darrel.ma...@newcastle.ac.uk> wrote: > Thanks again for the rapid response. I have been using gdal_translate as you > suggest to reconstitute images, although, as yet, I have only successfully > done this when the tiled target image is in a table of its own. I am probably > being a bit slow but have not yet figured out how to extract one target image > from a table which contains multiple tiled images using only gdal_translate - > ideally I do not want to have to create a separate table for the target image > but rather simply extract the image of interest from a PG table containing > all of the images (hope that makes sense). > > Darrel > > > > -----Original Message----- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon > Corporation > Sent: 04 February 2012 21:44 > To: 'PostGIS Users Discussion' > Subject: Re: [postgis-users] Rv: Postgis for Postgres 64 bits > > > >> "First thing 1000x1000 blocks is too big. If you are going to be >> doing a lot of ST_Value calls, you probably want to chunk your tiles >> to 100x100 or below. >> >> ST_Value currently requires doing a memcopy for each call so the >> bigger your tiles the more memcopying it will have to do. >> >> On top of that the bigger your tiles, the less useful ST_Intersects >> is. Its kind of the same issue with big geometries -- like if you >> stuffed all of africa in one record, and then were searching for a >> city in Africa, the ST_Intersects will not be that useful." >> >> Thanks for this. I did do a few tests. This is what I got: >> >> Tile Size Time >> 2000 350873ms >> 1000 235415 >> 500 288298 >> 300 234895 >> 200 231374 >> >> I guess I was concerned that at 200x200 the images were split across >> 3344 rows as opposed to the 176 of the 1000x1000. I am learning here, >> so I will give 100x100 a go (tomorrow) - I just worry about when I >> need to put the images back together (images will be uploaded as tiles >> and then the originals - model outputs - will be deleted). The >> geometry is composed of >> 100 points (this is for testing only - final configuration has yet to >> be determined) - I figured it would be faster if they were all on the >> same tile, but may be not? >> >> Thanks >> >> Darrel >> > > Bborie probably has more info on this since he's working it and can hopefully > chime in. Right now the ST_Union you'd use in the db is not very efficient > since its using plpgsql. Doing it with the gdal_translate is a lot faster and > you can do basic where conditions with that like where file_name='...'. > > In 2.0 ST_Union will be done mostly in C so should be faster. > > As far as reconstituting, yes and no. If your tiles are smaller and you > don't need the full tile, doing clip operations and union subset that covers > a geometric region will be faster again because of the whole memcopy issue. > > However if you need to export the tile -- reconsitute the original, then you > are right a bigger tile would be better, though you may want to try > gdal_translate as that I think is much more efficient for export. > http://www.postgis.org/documentation/manual-svn/RT_FAQ.html#id2997931 > > A lot fo these performance issues we'll be dealing with in the 2.1 release, > but for 2.0, we are just focussing on functionality and stability. > > Hope that helps, > Regina > > > _______________________________________________ > 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
