[postgis-users] Querying Multiple Rasters

Tue Jul 23 13:05:54 PDT 2013

No. I'm suggesting it later as it does take time and separates operations.
Get everything imported first and then add constraints.

Having said that, you can do it all at once if so desired... just
preference depending on volume of import data.

-bborie

On Tue, Jul 23, 2013 at 1:02 PM, Jayson Gallardo <jaysontrades at gmail.com>wrote:

> Okay, is there a specific reason why? As your link states: "raster2pgsql loader
> uses this function to register raster tables". Are you saying I should
> specify constraints that will be similar across all tables?
>
>
> On Tue, Jul 23, 2013 at 2:53 PM, Bborie Park <dustymugs at gmail.com> wrote:
>
>> I'd suggest adding constraints after the fact through SQL instead of
>> letting raster2pgsql do it.
>>
>> http://www.postgis.net/docs/manual-2.0/RT_AddRasterConstraints.html
>>
>> -bborie
>>
>>
>> On Tue, Jul 23, 2013 at 12:51 PM, Jayson Gallardo <jaysontrades at gmail.com
>> > wrote:
>>
>>> So based on the link you provided, and what else I've gathered, I first
>>> create a parent table:
>>>  CREATE TABLE dem_elevation
>>> (
>>>   rid integer NOT NULL PRIMARY KEY
>>>   rast raster,
>>>
>>> );
>>>  Then I run raster2pgsql on all the downloaded elevation data, sending
>>> each input tile to its own table, ie. dem_elevation_n36w091. Then alter
>>> table to inherit from parent:
>>> ALTER TABLE dem_elevation_n36w091 INHERIT dem_elevation;
>>>
>>> With raster2pgsql taking care of setting the constraints for each table.
>>> Now, I can just query the parent table dem_elevation to get what I need?
>>>
>>>
>>> On Tue, Jul 23, 2013 at 2:33 PM, Bborie Park <dustymugs at gmail.com>wrote:
>>>
>>>> I use the USGS NED 10 meter for California with one table for each
>>>> input raster. In the partitioned table scheme, data tables inherit from a
>>>> template (parent) table. Queries run on the parent table access the
>>>> inherited tables.
>>>>
>>>> -bborie
>>>>
>>>>
>>>> On Tue, Jul 23, 2013 at 11:56 AM, Jayson Gallardo <
>>>> jaysontrades at gmail.com> wrote:
>>>>
>>>>> Yes, it's usgs ned. And I initially went with one table for each input
>>>>> tile, but I didn't know how to join (or union) them together for my query.
>>>>>  On Jul 23, 2013 1:14 PM, "Bborie Park" <dustymugs at gmail.com> wrote:
>>>>>
>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> 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
>>>>
>>>>
>>>
>>> _______________________________________________
>>> 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/6c1cd2ec/attachment.html>