[postgis-users] Querying Multiple Rasters

Bborie Park dustymugs at gmail.com
Tue Jul 30 09:31:16 PDT 2013 

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 <jaysontrades at 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.000092592592593::numeric(16,10)),
>   CONSTRAINT enforce_scaley_rast CHECK (st_scaley(rast)::numeric(16,10) =
> (-0.000092592592593)::numeric(16,10)),
>   CONSTRAINT enforce_srid_rast CHECK (st_srid(rast) = 4269),
>   CONSTRAINT enforce_width_rast CHECK (st_width(rast) = 100)
>
> and my python script:
>
> wkt = sys.argv[1]  # Polygon shape in WKT format
> raster_type = 'GTiff'
> table_name = 'dem_elevation'
> map_srs = 900913
> table_srs = 4269
> sql_text = 'SELECT ST_AsGDALRaster(ST_CLIP(ST_Union(rast),
> ST_GeomFromText(\'%s\',%i)),\'%s\') FROM "%s" WHERE ST_Intersects(rast,
> ST_Transform(ST_GeomFromText(\'%s\',%i),%i))' % (wkt, map_srs,
> raster_type, table_name, wkt, map_srs, table_srs)
>
>
>
> On Tue, Jul 30, 2013 at 11:12 AM, Bborie Park <dustymugs at 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 <jaysontrades at 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 <jaysontrades at 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 <dustymugs at 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 <
>>>>> jaysontrades at 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 <dustymugs at 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 <
>>>>>>> jaysontrades at 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 <dustymugs at 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 <
>>>>>>>>> jaysontrades at 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 <dustymugs at 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 <
>>>>>>>>>>> jaysontrades at 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" <dustymugs at 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 <
>>>>>>>>>>>>> jaysontrades at 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 <
>>>>>>>>>>>>>> dustymugs at 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 <
>>>>>>>>>>>>>>> jaysontrades at 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 at lists.osgeo.org
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> _______________________________________________
>>>>>>>>>>>>>>> postgis-users mailing list
>>>>>>>>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> _______________________________________________
>>>>>>>>>>>>>> postgis-users mailing list
>>>>>>>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> _______________________________________________
>>>>>>>>>>>>> postgis-users mailing list
>>>>>>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>> _______________________________________________
>>>>>>>>>>>> postgis-users mailing list
>>>>>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> _______________________________________________
>>>>>>>>>>> postgis-users mailing list
>>>>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> _______________________________________________
>>>>>>>>>> postgis-users mailing list
>>>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> _______________________________________________
>>>>>>>>> postgis-users mailing list
>>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> postgis-users mailing list
>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> postgis-users mailing list
>>>>>>> postgis-users at lists.osgeo.org
>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>
>>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> postgis-users mailing list
>>>>>> postgis-users at lists.osgeo.org
>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>
>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at lists.osgeo.org
>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>
>>>>>
>>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130730/197fa4cb/attachment.html>

More information about the postgis-users mailing list