3m NED data doesn't exist for the continental US (at least from USGS). But if you were to do so, you could consider a different scheme...
1. All NED files are stored as out-db rasters 2. Each table is for one state, though in some situations you may want more than one table per state (e.g. Texas, California). That should help you keep the # of partitions to a minimum and reduce the size of each partition. -bborie On Tue, Jul 30, 2013 at 9:42 AM, Jayson Gallardo <[email protected]>wrote: > 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 <[email protected]> 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 >> <[email protected]>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 <[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 >>> >>> >> >> _______________________________________________ >> 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
