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 <dustym...@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 > <jaysontra...@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 <dustym...@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 < >>> jaysontra...@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" <dustym...@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 < >>>>> jaysontra...@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 <dustym...@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 < >>>>>>> jaysontra...@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@lists.osgeo.org >>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>>> postgis-users mailing list >>>>>>> postgis-users@lists.osgeo.org >>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>>> >>>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> postgis-users mailing list >>>>>> postgis-users@lists.osgeo.org >>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>> >>>>>> >>>>> >>>>> _______________________________________________ >>>>> postgis-users mailing list >>>>> postgis-users@lists.osgeo.org >>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>> >>>>> >>>> _______________________________________________ >>>> postgis-users mailing list >>>> postgis-users@lists.osgeo.org >>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>> >>>> >>> >>> _______________________________________________ >>> postgis-users mailing list >>> postgis-users@lists.osgeo.org >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>> >>> >> >> _______________________________________________ >> postgis-users mailing list >> postgis-users@lists.osgeo.org >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users