I was reading the page on partitioning, and the very last line says* "**Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions." *I'm already up to ~400 tables in this partitioning scheme just for Arkansas and Iowa... Is this a good idea? Would there be a better way to do the entire continental US?
On Tue, Jul 30, 2013 at 11:39 AM, Bborie Park <dustym...@gmail.com> wrote: > The quick and dirty approach is to have a query before that query that > transforms the WKT. > > Something like "SELECT ST_AsEWKT(ST_Transform(...))" > > -bborie > > > On Tue, Jul 30, 2013 at 9:35 AM, Jayson Gallardo > <jaysontra...@gmail.com>wrote: > >> I suppose I could do that in my script. How should I go about that? My >> process is as follows: >> >> - User selects area of interest on a map (openlayers) >> - User clicks submit, and python script is called with the WKT passed >> as an argument >> - Python script queries the database, which then outputs the raster >> - Raster is processed through a library >> - Processed raster is displayed as an overlay on the map >> >> >> >> On Tue, Jul 30, 2013 at 11:31 AM, Bborie Park <dustym...@gmail.com>wrote: >> >>> 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 >>> >>> >> >> _______________________________________________ >> 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