[postgis-users] How to handle tiled rasters (ST_Value, grid_pt=>coord, coord=>grid_pt etc)?

[Paragon Corporation]

Stefan,


> The main characteristics to me have been, 1. if a coverage overlaps or
not, and 2. if it is a tiled image. The fact (1) of non-overlapping can
significantly speed up queries and the second would help to "reconstruct"
the original file. Both consequently
 > could to be stored in metadata.
I don't know enough about how the processing works to know if keeping track
of overlapping vs. non-overlapping would speed up queries.  Logically I can
see how it would help outputting the data into images, but I don't see how
it would 
Speed up queries. Well at least not the analytical queries
I'm thinking of where I would be selecting records with geometric intersect
queries or by attribute relationships.  I probably wouldn't be keeping
overlapping rasters in the same table unless I was relating each
specifically to some specific feature like the thermal pictures I took of
the racks in my network lab.

> In other words, if it's a tiled image, would'nt it be good practice to
store that in a single table (without any other images)?
> I  say this because I realize that it's possible to load many raster filed
into the same raster table even with different blocksize_x and blocksize_y
(in which case I wonder what get's stored in raster_columns).
The concept of an image file when you start talking about raster in the
database is amorphous and menaningless to me. 

For example I've got gigabytes of Massachusetts aerials I'd like to bring
into my database.  For convenience they have these distributed as even sized
tiles of say 5 mb in size each.  Once I bring it in, do I care how they had
it stored.
NOT REALLY except to reference back.  Most likely when I output it, I won't
even be outputting it in the same sizes they chose to.  Mine would be based
on some query where the user says -- give me the imagery for my building
block.

If I really cared, I would use the raster2pgsql switch that includes the
originating file name as a column in my raster table.
Then I could reconstitute with a command line something like

gdal_translate -of GTiff   PG:"host='localhost' port='5432'
dbname='mygisdb' user='postgres' password='whatever' schema='ch13'
table='vietelev' where="filename='relief.jpg'"

My syntax I haven't tested but you get the basic idea.

I most likely will chop it up much smaller than the 5 MB size because 5 MB
is too big for me to do the analysisi I want to do with it.  E.g. figuring
out how much grassy land is in Boston or extruding building footprints etc.
There choice of
How they decided to define what an image file is is pretty irrelevant to me
and I think to most people.  I could be wrong.

Will I keep all the files in the same table or separate tables?  I'd keep it
in the same table but bring them all in with the same block size etc which
the raster_columns does keep track of.  In fact I may bring them in at
different times.

An even crazier idea -- I may load different years in the same table -- but
I'd probably keep the block size the same if I were to keep them in the same
table.


> As you assumed below, my other example 'relief_jpg' is a single raster
(originally only 158K). How can I speed up the following query? It's a query
on one row and it takes indefinitely long:
> SELECT ST_Value(ST_Reclass(rast, 1, '1:2'), 1, 1) FROM relief_jpg;

Are you using the ST_Reclass currently in the scripts folder of raster?  I
think that is currently much slower than in needs to be.  Its especially
slow if you have your rast as a single record, because each change requires
a memcopy.
You would really need to chop it up.

> The subclause uses "... FROM relief_jpg) dummy" whereas in the where
clause there's "WHERE r_table_name = 'relief_jpg';".

> I simply thought this is an indication that there's some more information
that could be stored in the metadata, like the fact that the 240 tiles form
a single raster coverage and are non-overlapping.
> But I probably still have to think about all this.
I think there is still discussion going on about what and what shouldn't be
in raster_columns so I suspect the number of columns in that table will grow
before PostGIS 2.0 is out.  Good to think about these things now :)

Hope that helps,
Regina
http://www.postgis.us