[postgis-users] optimize query

[Gold, Jack L (US SSA)]

Thanks to everyone for the very timely responses.

Bborie,

	As usual, you rock!   First, we did not have our table indexed, but I slapped around that developer a bit and it won't happen again.  We're dealing with about 800Mb with .dt1 DTED tiles.  Once we indexed and ran something similar to this query you sent, we were able to achieve lookup times < ~.200s as you stated.  Unfortunately, we collectively got lost trying to interpret your query. :)  The string substitution and CROSS JOIN confused us a bit.  Do you think you could break down why this works?

--Jack

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bborie Park
Sent: Tuesday, May 22, 2012 5:35 PM
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] optimize query

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