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 <[email protected]> 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 > <[email protected]>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 <[email protected]> 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 < >>> [email protected]> 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 <[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 >>>> >>>> >>> >>> _______________________________________________ >>> 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
