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-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Bborie Park
Sent: Tuesday, May 22, 2012 5:35 PM
To: postgis-users@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@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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
