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
