Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

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.comwrote:

 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.comwrote:

 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.comwrote:

 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 

Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

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.comwrote:

 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.comwrote:

 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.comwrote:

 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.comwrote:

 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.comwrote:

 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 

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

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.comwrote:

 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.comwrote:

 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.comwrote:

 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.comwrote:

 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.comwrote:

 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 

Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

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.92592592593::numeric(16,10)),
  CONSTRAINT enforce_scaley_rast CHECK (st_scaley(rast)::numeric(16,10) =
(-0.92592592593)::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.comwrote:

 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.comwrote:

 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.comwrote:

 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.comwrote:

 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.comwrote:

 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.comwrote:

 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, 

Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

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.comwrote:

 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.92592592593::numeric(16,10)),
   CONSTRAINT enforce_scaley_rast CHECK (st_scaley(rast)::numeric(16,10) =
 (-0.92592592593)::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.comwrote:

 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.comwrote:

 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.comwrote:

 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.comwrote:

 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:
 

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

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.comwrote:

 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.comwrote:

 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.92592592593::numeric(16,10)),
   CONSTRAINT enforce_scaley_rast CHECK (st_scaley(rast)::numeric(16,10)
 = (-0.92592592593)::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.comwrote:

 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.comwrote:

 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.comwrote:

 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.


 

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

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 jaysontra...@gmail.comwrote:

 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.comwrote:

 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.comwrote:

 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.92592592593::numeric(16,10)),
   CONSTRAINT enforce_scaley_rast CHECK
 (st_scaley(rast)::numeric(16,10) = (-0.92592592593)::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.comwrote:

 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.comwrote:

 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 
 

Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

What if I create subparents for each state, and set an extent constraint on
each subparent? Would that help? Or would the query still check the
constraint for each child of each subparent?


On Tue, Jul 30, 2013 at 11:54 AM, Bborie Park dustym...@gmail.com wrote:

 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 
 jaysontra...@gmail.comwrote:

 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.comwrote:

 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.comwrote:

 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.92592592593::numeric(16,10)),
   CONSTRAINT enforce_scaley_rast CHECK
 (st_scaley(rast)::numeric(16,10) = (-0.92592592593)::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.comwrote:

 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.comwrote:

 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 

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

That should work if you're querying against the subparent instead of the
parent. You'll need to test though...

-bborie


On Tue, Jul 30, 2013 at 9:59 AM, Jayson Gallardo jaysontra...@gmail.comwrote:

 What if I create subparents for each state, and set an extent constraint
 on each subparent? Would that help? Or would the query still check the
 constraint for each child of each subparent?


 On Tue, Jul 30, 2013 at 11:54 AM, Bborie Park dustym...@gmail.com wrote:

 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 
 jaysontra...@gmail.comwrote:

 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.comwrote:

 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.comwrote:

 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.92592592593::numeric(16,10)),
   CONSTRAINT enforce_scaley_rast CHECK
 (st_scaley(rast)::numeric(16,10) = 
 (-0.92592592593)::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.comwrote:

 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 

Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

Thanks for the help again. There will be extensive testing before we go
into production. Right now, we're just trying to get a prototype up and
running. I might need to look into querying a web service for clipped NED
data...


On Tue, Jul 30, 2013 at 12:01 PM, Bborie Park dustym...@gmail.com wrote:

 That should work if you're querying against the subparent instead of the
 parent. You'll need to test though...

 -bborie


 On Tue, Jul 30, 2013 at 9:59 AM, Jayson Gallardo 
 jaysontra...@gmail.comwrote:

 What if I create subparents for each state, and set an extent constraint
 on each subparent? Would that help? Or would the query still check the
 constraint for each child of each subparent?


 On Tue, Jul 30, 2013 at 11:54 AM, Bborie Park dustym...@gmail.comwrote:

 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 jaysontra...@gmail.com
  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 dustym...@gmail.comwrote:

 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.comwrote:

 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.92592592593::numeric(16,10)),
   CONSTRAINT enforce_scaley_rast CHECK
 (st_scaley(rast)::numeric(16,10) = 
 (-0.92592592593)::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.comwrote:

 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 

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

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.comwrote:

 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

Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

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.comwrote:

 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

Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

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.comwrote:

 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.comwrote:

 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

Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

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.comwrote:

 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.comwrote:

 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 

Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

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.comwrote:

 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.comwrote:

 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.comwrote:

 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),
 

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

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.comwrote:

 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.comwrote:

 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.comwrote:

 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 

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

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.comwrote:

 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.comwrote:

 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.comwrote:

 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.comwrote:

 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 

Re: [postgis-users] Querying Multiple Rasters

[Jayson Gallardo]

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.comwrote:

 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.comwrote:

 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