Jack,
You'll want to check to make sure that you have a spatial index on
dted_elevations. Beyond that, the following suggestions usually help...
1. What is your tile size? Smaller is always faster both in terms of
spatial granularity and data retrieval time. I generally have all my
tiles (regardless of type of raster) sized between 25x25 and 50x50.
2. How large is your table? This would be total # of rows and the total
"on-disk" size. If your table fits in memory, things will be faster
over successive calls.
I just wrote something similar last week where you're driving a pin
through an elevation raster. My SQL template is...
WITH f AS (
SELECT ST_Transform(
ST_SetSRID(
ST_MakePoint(
%s,
%s
)
, 4326)
, 4269) AS geom
)
SELECT
ST_Value(t.tile, f.geom)
FROM ned.%s t
CROSS JOIN f
WHERE ST_Intersects(
t.tile,
f.geom
)
AND ST_Value(t.tile, f.geom) IS NOT NULL
I'm using USGS NED 10m where each source raster is kept in its own table
with each tile being 53x53. I could have used partitioned tables but I
got lazy. On disk, each table is between 300 - 500 MB (including TOAST
and indices). For the query above with a user-defined longitude and
latitude, the resultset is returned in ~0.2 seconds.
-bborie
On 05/22/2012 02:16 PM, Gold, Jack L (US SSA) wrote:
> I'm finding a search query we wrote is running really slow. Any ideas on how
> to optimize this better? Or is this the best I can do?
>
> SELECT ST_Value(rast, ST_Transform(ST_SetSRID(ST_Point(66.12, 37.32),
> 4326),ST_SRID(rast))) FROM dted_elevations WHERE ST_Intersects(rast,
> ST_Transform(ST_SetSRID(ST_Point(66.12, 37.32), 4326), ST_SRID(rast)));
>
> --Jack Gold
>
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
