[postgis-users] Querying Multiple Rasters

[Bborie Park]

Can you describe your elevation dataset? Is it USGS NED? At which
resolution (10 meter, 3 meter?)?

As for table partitioning...

http://www.postgresql.org/docs/9.0/static/ddl-partitioning.html

You'll probably partition spatially, though an easy solution is to have a
table for each input raster file.

-bborie





On Tue, Jul 23, 2013 at 11:05 AM, Jayson Gallardo <jaysontrades at gmail.com>wrote:

> Thanks for responding. Could you outline how I would go about doing a
> partitioned table structure? My only concern with tile size is processing
> time. Most of my queries will involve areas of less than 1 mi^2, and I
> would clip the data into that shape. I just don't know where to start!
> There's not too many resources online/print dealing with postgis rasters in
> detail.
>
>
> On Tue, Jul 23, 2013 at 12:57 PM, Bborie Park <dustymugs at gmail.com> wrote:
>
>> You may not need to drop all the constraints when adding additional data
>> to the table. You most likely will need to drop is the maximum extent
>> constraint. Assuming the input rasters have the same scale, skew and SRID
>> as that found in the table, you don't need to drop those corresponding
>> constraints.
>>
>> If you're going to do the continental US at a fine resolution (e.g. 1
>> meter), you do NOT want to put all the rasters in one table. You'll want to
>> use a partitioned table structure and should consider a bigger tile size
>> (depending on your hardware).
>>
>> -bborie
>>
>>
>> On Tue, Jul 23, 2013 at 10:43 AM, Jayson Gallardo <jaysontrades at gmail.com
>> > wrote:
>>
>>> I've looked and looked, but I have not been able to find an answer to my
>>> question. I have downloaded elevation data for the state of Arkansas (in
>>> the form of multiple tiles), and used raster2pgsql to upload it into a
>>> single table:
>>>
>>> raster2pgsql -I -C -e -F -t 50x50 -l 2,4 n*/grdn* public.dem_elevation |
>>> psql -U postgres -d testdb -h localhost -p 5432
>>>
>>> I did this because I didn't know how to pull the data if they were in
>>> separate tables. Now, however I would like to add elevation data for other
>>> areas. I tried to just add it to the current table, but that required
>>> dropping the constraints which for such a huge amount of data seems to take
>>> a long time (I let it run for 24+ hours and it didn't finish). So, my
>>> question is, if I load all my rasters as individual tables, how could I run
>>> something similar to this query on them all (from a python script):
>>>
>>> SELECT ST_AsGDALRaster(ST_CLIP(ST_Union(rast),
>>> ST_GeomFromText(WKT,900913)),'GTiff') FROM "dem_elevation" WHERE
>>> ST_Intersects(rast, ST_Transform(ST_GeomFromText(WKT,900913),4269))
>>>
>>> My goal, if it's not obvious, is to clip elevation data and export it to
>>> a GTiff format and perform some operations on that raster data. Eventually,
>>> I would like to put the whole continental US elevation data into my
>>> database, so I need to be able to do so, while still being able to query
>>> them based on an area of interest the user selects from a map. I started
>>> working with PostGIS and Mapserver last month, so please forgive my
>>> ignorance on such topics. Thanks in advance
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130723/38768951/attachment.html>