[postgis-users] Spoke too soon

[Darrel Maddy]

I would be grateful if someone could help me with the syntax for filenames when using the WHERE clause in gdal_translate

I have 
gdal_translate -of GTiff "PG:host=localhost port=5432  dbname=testcuda user=postgres password=H$%£"! schema=public table=rast100 where='filename="Heights_11_10.tif"' mode=2" Height10.tif

I tried various forms of the 'filename=' clause (and simplifying the filename to H10.tif) but without success (the form given on the FAQ does not work in windows either). What does work is:

gdal_translate -of GTiff "PG:host=localhost port=5432  dbname=testcuda user=postgres password= H$%£"!  schema=public table=rast100 where='rid>someno AND rid<someno' mode=2" Height10.tif

Obviously I can work with the latter but it requires me to know the start and end rid (or do a query to find it). Ideally I  would like to be able to just download the image via the filename.

Thanks in anticipation of your help.

Darrel





-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Darrel Maddy
Sent: 05 February 2012 10:11
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Rv: Postgis for Postgres 64 bits

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.maddy at 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-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users