Hey Jack, My query looks the way it does as I find it to be a cleaner instead of having the point geometry embedded in the main query.
Your query is perfectly acceptable and probably runs just as fast as mine. -bborie On 05/22/2012 03:05 PM, Gold, Jack L (US SSA) wrote: > 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 > -- 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
