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