<div dir="ltr">Thanks for your input. I'll try to take a look at your suggestions. I would like to say that I have done some tuning to postgres. Our idea for downloading continental US ned data may be the wrong way to go anyways. Also, forgive me if I seem ignorant to some aspects; I'm a non-traditional college student working for USDA on campus as a programmer, and I'm learning everything as I go. I hope to get to the point where I am on level footing with you all.</div>
<div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Jul 31, 2013 at 2:51 AM, Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Sorry for the double post.<br>One last thing :<br>about your query:<br>you do a ST_clip ( st_union )<br>You could try a ST_union(st_clip) .<br>If you have a lot of big rasters it may be faster (??).<br><br>Cheers,<br>Rémi-C<div class="HOEnZb">
<div class="h5"><br>
<br><div class="gmail_quote">2013/7/31 Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hey<br>Sorry to bother you with technical details, but your query being slow may come from hardware, OS, postgres, postgis, bad partitionning, bad query, bad index,...<br>So can you give more details please?<br><br>What are your versions (OS,postgres, postgis, gdal).<br>
What is the on disk size of a child table (see statistics)<br>How many rows are there in a child table?<br>I'm guessing you have an index about envelope of each row.<br><br>What says a EXPLAIN ANALYZE of your query ?<br>
<br><br>Quick check list :<br><br>Hardware<br><div style="margin-left:40px">[_] Have you two separate hard disk (one for sys, one for data) / Is your disk the bottleneck?<br></div>OS<br><div style="margin-left:40px">[_] What is your system doing when computing a query (look for process, memory usage, disk usage)<br>
</div><div style="margin-left:40px">[_] Is you OS caching working?<br>[_] if linux, have you tun your kernel max memory?<br></div><br>Postgres<br><div style="margin-left:40px">[_] Have you tuned postgresql.conf conf (very restrictive by default : MUST DO).<br>
</div>Postgis<br><div style="margin-left:40px">[_] ...no idea yet...<br></div>Partitionning<br><div style="margin-left:40px">[_] Are your constraints on child table sufficently restrictive to avoid querrying a lots of child table<br>
[_] Are your constraints used by query planner (explain analyze) . <br><div style="margin-left:40px">If you want to be sure, you can manually explicit it for test purpose : adding to WHERE clause "AND st_coveredby(st_convexhull(rast), '<i style="font-weight:bold">...truncated...</i>'::geometry)) = TRUE</div>
</div>Query<br><div style="margin-left:40px">[_] I'm guessing you test your query in pgadmin before using python : you could start from minimal query (find rasters in a clipping zone directly querying a child table), then make it grow (find rasters in father table,then compute new raster from previous result, etc), watching how the processing time grows in the process.<br>
<div style="margin-left:40px">Also you should know the computational price of doing <span><font face="courier new, monospace">ST_AsGDALRaster(ST_CLIP(ST_Union(rast), that is separate the time to find "rast" intersecting your clipping zone, and the time to compute a union and the ST_AsGdalRaster function.<br>
</font></span></div></div><div style="margin-left:40px"><br>[_] As suggested, and if you want to avoid sending another query first to compute our clipping box, you could use CTE (IE a "WITH" statement), or subquery. This way you avoid computing it twice :<br>
<div style="margin-left:40px"><span><font face="courier new, monospace">WITH my_clipping_geom AS (</font></span><br><span></span></div><div style="margin-left:80px">
<font face="courier new, monospace">SELECT </font><span><span style="font-family:'courier new',monospace"> ST_Transform(ST_GeomFromText(</span></span><span><span style="font-family:'courier new',monospace"><span><span style="font-family:'courier new',monospace"> wkt</span></span>,</span></span><span><span style="font-family:'courier new',monospace"><span><span style="font-family:'courier new',monospace"> map_srs</span></span>,</span></span><span><span style="font-family:'courier new',monospace"><span><span style="font-family:'courier new',monospace">table_srs</span></span>))</span></span><span><span style="font-family:'courier new',monospace"> </span></span>AS geom<br>
LIMIT 1<br><span></span></div><div style="margin-left:40px"><span><font face="courier new, monospace">)</font></span><br><span><font face="courier new, monospace">SELECT ST_AsGDALRaster(ST_CLIP(ST_Union(rast), </font></span><span><span style="font-family:'courier new',monospace">my_clipping_geom.geom</span></span><span><font face="courier new, monospace"> ,</font></span><span><font face="courier new, monospace"><span><span style="font-family:'courier new',monospace"> raster_type</span></span>) </font></span><br>
<span><span style="font-family:'courier new',monospace">FROM </span></span><span><span style="font-family:'courier new',monospace"><span><span style="font-family:'courier new',monospace">table_name</span></span></span><span style="font-family:'courier new',monospace">, my_clipping_geom </span></span><br>
<span><span style="font-family:'courier new',monospace">WHERE ST_Intersects(rast,my_clipping_geom.geom)</span></span><br><br></div></div>Index <br><div style="margin-left:40px">
[_] You use ST_Intersects, therefore you must have an index to speed it up.<br><div style="margin-left:40px">I'm guessing you have one on bounding box of each raster (each line), is it used (explain analyze : index scan)<br>
<br></div></div>Don't lose hope, as the whole system can be severly speeded up by tuning and small change may have big effects.<br><br>Cheers,<br>Rémi-C<div><div><br><div style="margin-left:40px">
<br></div><br><div class="gmail_quote">
2013/7/30 Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">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...</div>
<div><div>
<div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 30, 2013 at 12:01 PM, Bborie Park <span dir="ltr"><<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">That should work if you're querying against the subparent instead of the parent. You'll need to test though...<span><font color="#888888"><div>
<br></div><div>-bborie</div></font></span></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">
On Tue, Jul 30, 2013 at 9:59 AM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">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?</div><div>
<div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Tue, Jul 30, 2013 at 11:54 AM, Bborie Park <span dir="ltr"><<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">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...<div><br></div><div>1. All NED files are stored as out-db rasters</div>
<div>2. Each table is for one state, though in some situations you may want more than one table per state (e.g. Texas, California).</div><div><br></div><div>That should help you keep the # of partitions to a minimum and reduce the size of each partition.</div>
<span><font color="#888888">
<div><br></div><div>-bborie</div><div><br></div></font></span></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 30, 2013 at 9:42 AM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">I was reading the page on partitioning, and the very last line says<i> "</i><span style="line-height:18.234375px;font-size:12px;font-family:verdana,sans-serif"><i>Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions." </i>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?</span></div>
<div><div>
<div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 30, 2013 at 11:39 AM, Bborie Park <span dir="ltr"><<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">The quick and dirty approach is to have a query before that query that transforms the WKT.<div><br></div>
<div>Something like "SELECT ST_AsEWKT(ST_Transform(...))"</div><span><font color="#888888"><div><br></div><div>-bborie</div>
</font></span></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 30, 2013 at 9:35 AM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">I suppose I could do that in my script. How should I go about that? My process is as follows: <div><div>
<ul><li>User selects area of interest on a map (openlayers)<br></li><li>User clicks submit, and python script is called with the WKT passed as an argument</li>
<li>Python script queries the database, which then outputs the raster</li><li>Raster is processed through a library</li><li>Processed raster is displayed as an overlay on the map</li></ul></div></div></div><div>
<div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Tue, Jul 30, 2013 at 11:31 AM, Bborie Park <span dir="ltr"><<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr"><div>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)).</div>
<span><font color="#888888">
<div><br></div><div>-bborie</div></font></span></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 30, 2013 at 9:25 AM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Here's the constraints:<div><div> CONSTRAINT dem_elevation_n33w092_pkey PRIMARY KEY (rid ),</div><div>
CONSTRAINT enforce_height_rast CHECK (st_height(rast) = 100),</div><div> CONSTRAINT enforce_max_extent_rast CHECK (st_coveredby(st_convexhull(rast), '<i style="font-weight:bold">...truncated...</i>'::geometry)),</div>
<div> CONSTRAINT enforce_num_bands_rast CHECK (st_numbands(rast) = 1),</div><div> CONSTRAINT enforce_out_db_rast CHECK (_raster_constraint_out_db(rast) = '{f}'::boolean[]),</div><div> CONSTRAINT enforce_pixel_types_rast CHECK (_raster_constraint_pixel_types(rast) = '{32BF}'::text[]),</div>
<div> CONSTRAINT enforce_same_alignment_rast CHECK (st_samealignment(rast, '<b><i>...truncated...</i></b>'::raster)),</div><div> CONSTRAINT enforce_scalex_rast CHECK (st_scalex(rast)::numeric(16,10) = 0.000092592592593::numeric(16,10)),</div>
<div> CONSTRAINT enforce_scaley_rast CHECK (st_scaley(rast)::numeric(16,10) = (-0.000092592592593)::numeric(16,10)),</div><div> CONSTRAINT enforce_srid_rast CHECK (st_srid(rast) = 4269),</div><div> CONSTRAINT enforce_width_rast CHECK (st_width(rast) = 100)</div>
</div><div><br></div><div>and my python script:</div><blockquote style="margin:0 0 0 40px;border:none;padding:0px"><div><div><span style="background-color:rgb(103,78,167)"><font color="#eeeeee" face="courier new, monospace">wkt = sys.argv[</font><font color="#ff0000" face="courier new, monospace">1</font><font color="#eeeeee" face="courier new, monospace">] </font><font color="#6fa8dc" face="courier new, monospace"># Polygon shape in WKT format</font></span></div>
</div><div><div><span style="background-color:rgb(103,78,167)"><font color="#eeeeee" face="courier new, monospace">raster_type = </font><font color="#00ff00" face="courier new, monospace">'GTiff'</font></span></div>
</div><div><div><span style="background-color:rgb(103,78,167)"><font color="#eeeeee" face="courier new, monospace">table_name = </font><font color="#00ff00" face="courier new, monospace">'dem_elevation'</font></span></div>
</div><div><div><span style="background-color:rgb(103,78,167)"><font color="#eeeeee" face="courier new, monospace">map_srs = </font><font color="#ff0000" face="courier new, monospace">900913</font></span></div></div><div>
<div><span style="background-color:rgb(103,78,167)"><font color="#eeeeee" face="courier new, monospace">table_srs = </font><font color="#ff0000" face="courier new, monospace">4269</font></span></div></div><div><span style="background-color:rgb(103,78,167)"><font face="courier new, monospace"><font color="#eeeeee">sql_text = </font><font color="#00ff00">'SELECT ST_AsGDALRaster(ST_CLIP(ST_Union(rast), ST_GeomFromText(\'%s\',%i)),\'%s\') </font></font><span style="font-family:'courier new',monospace"><font color="#00ff00">FROM "%s"</font></span><span style="font-family:'courier new',monospace"><font color="#00ff00"> WHERE ST_Intersects(rast, ST_Transform(ST_GeomFromText(\'%s\',%i),%i))'</font><font color="#eeeeee"> </font></span><span style="color:rgb(238,238,238);font-family:'courier new',monospace">% (wkt, map_srs, raster_type, table_name, wkt, map_srs, table_srs)</span></span></div>
</blockquote></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 30, 2013 at 11:12 AM, Bborie Park <span dir="ltr"><<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Jayson,<div><br></div><div>Can you share one of the queries? Also, what check constraints are you using?</div>
<span><font color="#888888"><div><br></div><div>-bborie</div></font></span></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">
On Tue, Jul 30, 2013 at 7:49 AM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">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?</div>
<div><div>
<div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 30, 2013 at 9:20 AM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">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. <div>
<br></div><div>How can I be sure that when I query the parent database that it's not querying every single table?</div></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">
On Tue, Jul 23, 2013 at 3:14 PM, Bborie Park <span dir="ltr"><<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">I'm just glad to help. Feel free to post your experience, feedback, issues and/or wishes on the mailing-list.<span><font color="#888888"><div>
<br></div><div>-bborie</div></font></span></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">
On Tue, Jul 23, 2013 at 1:10 PM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">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.</div>
<div><div>
<div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 23, 2013 at 3:05 PM, Bborie Park <span dir="ltr"><<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">No. I'm suggesting it later as it does take time and separates operations. Get everything imported first and then add constraints.<div>
<br></div><div>Having said that, you can do it all at once if so desired... just preference depending on volume of import data.</div><span><font color="#888888">
<div><br></div><div>-bborie</div></font></span></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 23, 2013 at 1:02 PM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Okay, is there a specific reason why? As your link states: "<code style="color:rgb(46,46,46);font-size:13px">raster2pgsql</code><span style="color:rgb(46,46,46);font-family:'Lucida Grande',Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13px"> loader uses this function to register raster tables". Are you saying I should specify constraints that will be similar across all tables?</span></div>
<div><div>
<div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 23, 2013 at 2:53 PM, Bborie Park <span dir="ltr"><<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">I'd suggest adding constraints after the fact through SQL instead of letting raster2pgsql do it.<div>
<br></div><div><a href="http://www.postgis.net/docs/manual-2.0/RT_AddRasterConstraints.html" target="_blank">http://www.postgis.net/docs/manual-2.0/RT_AddRasterConstraints.html</a><span><font color="#888888"><br>
</font></span></div><span><font color="#888888"><div><br></div><div>-bborie</div></font></span></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 23, 2013 at 12:51 PM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">So based on the link you provided, and what else I've gathered, I first create a parent table:<div>
<div>
CREATE TABLE dem_elevation</div><div>(</div><div> rid integer NOT NULL PRIMARY KEY</div><div> rast raster,</div>
<div><br></div><div>);<br></div></div><div> 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:</div>
<div>ALTER TABLE dem_elevation_n36w091 INHERIT dem_elevation;</div><div><br></div><div>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?</div>
</div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 23, 2013 at 2:33 PM, Bborie Park <span dir="ltr"><<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">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.<span><font color="#888888"><div>
<br></div><div>-bborie</div></font></span></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 23, 2013 at 11:56 AM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><p dir="ltr">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. </p>
<div><div>
<div class="gmail_quote">On Jul 23, 2013 1:14 PM, "Bborie Park" <<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>> wrote:<br type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">Can you describe your elevation dataset? Is it USGS NED? At which resolution (10 meter, 3 meter?)?<div><br></div><div>As for table partitioning...</div><div><br></div><div><a href="http://www.postgresql.org/docs/9.0/static/ddl-partitioning.html" target="_blank">http://www.postgresql.org/docs/9.0/static/ddl-partitioning.html</a></div>
<div><br></div><div>You'll probably partition spatially, though an easy solution is to have a table for each input raster file.</div><div><br></div><div>-bborie</div><div><div><br><div><br></div><div><br></div></div>
</div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 23, 2013 at 11:05 AM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">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.</div>
<div><div>
<div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 23, 2013 at 12:57 PM, Bborie Park <span dir="ltr"><<a href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">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.<div>
<br></div><div>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).</div>
<div><br></div><div>-bborie</div></div><div class="gmail_extra"><br><br><div class="gmail_quote"><div><div>On Tue, Jul 23, 2013 at 10:43 AM, Jayson Gallardo <span dir="ltr"><<a href="mailto:jaysontrades@gmail.com" target="_blank">jaysontrades@gmail.com</a>></span> wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div dir="ltr"><span style="font-family:arial,sans-serif;font-size:13px">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:</span><div style="font-family:arial,sans-serif;font-size:13px">
<br><div>raster2pgsql -I -C -e -F -t 50x50 -l 2,4 n*/grdn* public.dem_elevation | psql -U postgres -d testdb -h localhost -p 5432<div><br></div><div>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):</div>
</div></div><div style="font-family:arial,sans-serif;font-size:13px"><br></div><div style="font-family:arial,sans-serif;font-size:13px">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))</div>
<div style="font-family:arial,sans-serif;font-size:13px"><br></div><div style="font-family:arial,sans-serif;font-size:13px">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</div>
</div>
<br></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>
</div></div></blockquote></div><br>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>