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 <[email protected]> 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 > <[email protected]>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" <[email protected]> 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 < >>> [email protected]> 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 <[email protected]>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 < >>>>> [email protected]> 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 >>>>>> [email protected] >>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>> >>>>>> >>>>> >>>>> _______________________________________________ >>>>> postgis-users mailing list >>>>> [email protected] >>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>> >>>>> >>>> >>>> _______________________________________________ >>>> postgis-users mailing list >>>> [email protected] >>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>> >>>> >>> >>> _______________________________________________ >>> postgis-users mailing list >>> [email protected] >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>> >>> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
