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