[postgis-users] Querying Multiple Rasters

Tue Jul 23 13:10:22 PDT 2013

Oh, okay. Yeah you're right about it taking time. I wrote a python script
to generate the raster2pgsql call with the appropriate table name, so I can
just let it run while I do other things. I really appreciate your help on
this. I googled your name and I see you're a pretty busy person, so I'm
glad you're taking the time to answer my questions.

On Tue, Jul 23, 2013 at 3:05 PM, Bborie Park <dustymugs at gmail.com> wrote:

> 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
>>
>>
>
> _______________________________________________
> 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/367cdf1a/attachment.html>