[postgis-users] optimize query

[Bborie Park]

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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu