[postgis-users] raster loading and ST_Value performance (summary)
Phil Hurvitz
phurvitz at uw.edu
Wed Sep 19 14:40:59 PDT 2012
Thanks to "Francois Hugues" <hugues.francois at irstea.fr> and Pierre
Racine <Pierre.Racine at sbf.ulaval.ca> for their excellent suggestions.
> I have two questions:
>
> Q1: What effect does different tile size have on storage and performance?
>
> Q2: We will need to be getting raster values from a large number of
> point tables over a large number of raster tables. Is there any
> method to speed up the queries?
Hugues suggested:
> Q1 : Tiling will help you if you make an index on your raster table (using GIST and st_convexhull). I don't see any index in what you described.
The "-I" flag in raster2pgsql created the indexes, so all my test
rasters had indexes built at load time.
> Q2 : Tiling +Index will speed up your query. You should also reduce time using st_intersects(rast,the_geom) in the where clause.
Using the ST_Intersects sped things up greatly, from >4 s to 30 ms for
one point. Wow!
SELECT rid, ST_Value(rast, the_geom, false) FROM slope, (SELECT the_geom
FROM twins_withn_wa LIMIT 1) AS foo WHERE ST_Intersects(rast, the_geom);
rid | st_value
-----+-----------------
589 | 8.1965799331665
(1 row)
Time: 30.720 ms
Pierre suggested:
>> Q1: What effect does different tile size have on storage and performance?
>
> Biggest tiles = longer to load in memory and less efficient indexing
>
> I suggest you experiment with much smaller tiles: 100x100 and 10x10
Smaller tiles also helped speed things up. The rasters are shown with
names corresponding to the tile size (1000, 100, and 10). Getting ~9000
point values with the 1000x1000 raster took about 42 s (pretty fast with
Hugues' ST_Intersects suggestion), but for the 10x10 tile, the values
were returned in about 1 s. Wow wow!
SELECT rid, ST_Value(rast, the_geom, true) FROM slope1000,
(SELECT the_geom FROM twins_withn_wa) AS foo
WHERE ST_Intersects(rast, the_geom);
rid | st_value
------+----------------------
11 | 3.69029211997986
11 | 1.24964261054993
...
Time: 42094.863 ms
SELECT rid, ST_Value(rast, the_geom, true) FROM slope100,
(SELECT the_geom FROM twins_withn_wa) AS foo
WHERE ST_Intersects(rast, the_geom);
rid | st_value
--------+----------------------
58365 | 8.1965799331665
36233 |
...
Time: 1606.054 ms
SELECT rid, ST_Value(rast, the_geom, true) FROM slope10,
(SELECT the_geom FROM twins_withn_wa) AS foo
WHERE ST_Intersects(rast, the_geom);
rid | st_value
----------+----------------------
5843074 | 8.1965799331665
3619526 |
...
Time: 1099.805 ms
>> Q2: We will need to be getting raster values from a large number of
>> point tables over a large number of raster tables. Is there any method
>> to speed up the queries?
>
> If your rasters are all of the same size and cover the same footprint I would suggest to make them different bands of the same raster. So for the same point you will search for the right tile only once.
I will keep that suggestion for future use. We will have that situation
for some of our data. In any case, if I had to wait only 1 s for each
query, that would be sufficient without needing to band the raster data.
-P.
**************************************************************
Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
Urban Form Lab | 1107 NE 45th Street, Suite 535 | Box 354802
University of Washington, Seattle, Washington 98195-4802, USA
phurvitz at u.washington.edu | http://gis.washington.edu/phurvitz
"What is essential is invisible to the eye." -de Saint-Exupéry
**************************************************************
On 2012-09-18 6:11 PM, Phil Hurvitz wrote:
> Hello PostGIS raster experts,
>
> I have loaded a raster representing slope for an area of Washington
> State (USA). The source was a large Imagine (img/ige) file (about 4.3
> GB, 31935 x 34649 pixels at 10 m pixel size), which I copied to the db
> using
>
> raster2pgsql -I -C -e -Y -F -d -s 2926 -t 1000x1000 slope_ps.img
> gis.slope | psql dbname
>
> I used the tiling of 1000x1000 after numerous attempts to load the
> raster without tiling, and using several different file types (TIFF,
> ArcInfo grid, ArcInfo ASCII), each of which caused a Segmentation
> Error/Core dump.
>
> Now that I have a working raster, I can get a single point value using
> this query:
>
> SELECT rid,
> ST_Value(rast, the_geom, false) FROM slope,
> (SELECT the_geom FROM twins_withn_wa LIMIT 1) AS foo;
>
> rid | st_value
> -----+-----------------
> ... |
> 589 | 8.1965799331665
> ... |
> (1120 rows)
> Time: 4768.648 ms
>
> 4 s seems like a long time to get one value from a raster. Here is the
> output of EXPLAIN ANALYZE, which seems to show that the nested loop is
> taking most of the time:
>
> QUERY PLAN
> ----------------------------------------------------------------------
> Nested Loop (cost=0.00..312.67 rows=1120 width=164) (actual
> time=7.604..4736.852 rows=1120 loops=1)
> -> Limit (cost=0.00..0.26 rows=1 width=128) (actual
> time=0.005..0.005 rows=1 loops=1)
> -> Seq Scan on twins_withn_wa (cost=0.00..3043.02 rows=11702
> width=128) (actual time=0.004..0.004 rows=1 loops=1)
> -> Seq Scan on slope (cost=0.00..21.20 rows=1120 width=36) (actual
> time=0.004..0.890 rows=1120 loops=1)
> Total runtime: 4737.278 ms
> (5 rows)
>
>
> I am running on a fairly beefy RHEL 6.3 machine with 16 G RAM (8 GB
> allocated to PostgreSQL), PostGIS 2.0.1 on PostgreSQL 9.1.1:
>
> select postgis_full_version();
> postgis_full_version
> -----------------------------------------------------------------------
> POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.7.1, 23
> September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.6"
> RASTER
> (1 row)
>
> select version();
> version
> --------------------------------------------------------------------------
> PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit
> (1 row)
>
> I have two questions:
>
> Q1: What effect does different tile size have on storage and performance?
>
> Q2: We will need to be getting raster values from a large number of
> point tables over a large number of raster tables. Is there any method
> to speed up the queries?
>
> Any pointers will be greatly appreciated!
>
More information about the postgis-users
mailing list