<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 class="gmail_quote">On Jul 23, 2013 1:14 PM, "Bborie Park" <<a href="mailto:dustymugs@gmail.com">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">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>