[postgis-users] Querying Multiple Rasters

Bborie Park dustymugs at gmail.com
Tue Jul 23 12:53:35 PDT 2013


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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130723/44f85b28/attachment.html>


More information about the postgis-users mailing list