Are you able to transform the wkt before passing it to the sql? Partitioning only works on constant values, not values that need processing, e.g. ST_Transform(ST_GeomFromText(\'%s\',%i),%i)).
-bborie On Tue, Jul 30, 2013 at 9:25 AM, Jayson Gallardo <jaysontra...@gmail.com>wrote: > Here's the constraints: > CONSTRAINT dem_elevation_n33w092_pkey PRIMARY KEY (rid ), > CONSTRAINT enforce_height_rast CHECK (st_height(rast) = 100), > CONSTRAINT enforce_max_extent_rast CHECK > (st_coveredby(st_convexhull(rast), '*...truncated...*'::geometry)), > CONSTRAINT enforce_num_bands_rast CHECK (st_numbands(rast) = 1), > CONSTRAINT enforce_out_db_rast CHECK (_raster_constraint_out_db(rast) = > '{f}'::boolean[]), > CONSTRAINT enforce_pixel_types_rast CHECK > (_raster_constraint_pixel_types(rast) = '{32BF}'::text[]), > CONSTRAINT enforce_same_alignment_rast CHECK (st_samealignment(rast, '* > ...truncated...*'::raster)), > CONSTRAINT enforce_scalex_rast CHECK (st_scalex(rast)::numeric(16,10) = > 0.000092592592593::numeric(16,10)), > CONSTRAINT enforce_scaley_rast CHECK (st_scaley(rast)::numeric(16,10) = > (-0.000092592592593)::numeric(16,10)), > CONSTRAINT enforce_srid_rast CHECK (st_srid(rast) = 4269), > CONSTRAINT enforce_width_rast CHECK (st_width(rast) = 100) > > and my python script: > > wkt = sys.argv[1] # Polygon shape in WKT format > raster_type = 'GTiff' > table_name = 'dem_elevation' > map_srs = 900913 > table_srs = 4269 > sql_text = 'SELECT ST_AsGDALRaster(ST_CLIP(ST_Union(rast), > ST_GeomFromText(\'%s\',%i)),\'%s\') FROM "%s" WHERE ST_Intersects(rast, > ST_Transform(ST_GeomFromText(\'%s\',%i),%i))' % (wkt, map_srs, > raster_type, table_name, wkt, map_srs, table_srs) > > > > On Tue, Jul 30, 2013 at 11:12 AM, Bborie Park <dustym...@gmail.com> wrote: > >> Jayson, >> >> Can you share one of the queries? Also, what check constraints are you >> using? >> >> -bborie >> >> >> On Tue, Jul 30, 2013 at 7:49 AM, Jayson Gallardo >> <jaysontra...@gmail.com>wrote: >> >>> So, I used Explain on my SELECT statement, and whether >>> constraint_exclusion is on or off, it seems to spit out the same number of >>> rows in the query plan. Is there something I need to do for my table >>> constraints so that it doesn't do a check on every table I have loaded? >>> >>> >>> On Tue, Jul 30, 2013 at 9:20 AM, Jayson Gallardo <jaysontra...@gmail.com >>> > wrote: >>> >>>> Quick follow up question to my situation... I recently loaded 3m >>>> resolution NED for Iowa. I have them loaded to one table per source tile, >>>> and have them inheriting from the parent table that the Arkansas NED is >>>> inheriting from. Ever since, however, my database seems to be running >>>> pretty slow. I've run a full vacuum on the data, and there are constraints >>>> on each table. >>>> >>>> How can I be sure that when I query the parent database that it's not >>>> querying every single table? >>>> >>>> >>>> On Tue, Jul 23, 2013 at 3:14 PM, Bborie Park <dustym...@gmail.com>wrote: >>>> >>>>> I'm just glad to help. Feel free to post your experience, feedback, >>>>> issues and/or wishes on the mailing-list. >>>>> >>>>> -bborie >>>>> >>>>> >>>>> On Tue, Jul 23, 2013 at 1:10 PM, Jayson Gallardo < >>>>> jaysontra...@gmail.com> wrote: >>>>> >>>>>> 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 <dustym...@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 < >>>>>>> jaysontra...@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 >>>>>>>> <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 >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>>> 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