[postgis-users] Spatial query help

[Paul Ramsey]

Query looks perfectly look to me. (You might use = instead of LIKE, but 
that won't change your performance in this case.)

Your statistics look odd, you might run "ANALYZE" and then use EXPLAIN 
ANALYZE to generate a query plan with both estimated and real time 
measurements.  But regardless, the plan you got here doesn't look too bad.

Looks like you have hit exactly the same "testing many small things 
within one large thing" performance issue that everyone seems to be 
having these last couple months.

http://geotips.blogspot.com/2007/06/performance-and-contains.html

Our first approximation at a performance boost for that is just getting 
into internal test land now, so hopefully we'll have something suitable 
for bleeding edge users in a month or so.

P.

Stephen Crawford wrote:
> I'm trying to write an efficient spatial query between two table that in
> effect says "give me all the features from table 1 that intsersect the
> selected features from table 2".  The following query works but takes about
> 50 seconds.  Both table have a gist index.  The "blm_lands" table is fairly
> high-resolution, and the selection for "ANCHORAGE" includes many islands. 
> 
> Is the query below the best way to do it?  I have also included the query
> plan.
> 
> Thanks,
> Steve
> 
> Stephen Crawford
> Center for Environmental Informatics
> The Pennsylvania State University
> 
> 
> SELECT DISTINCT(m.mlra_name) AS name, m.mlra_reg_s AS mlra_id FROM mlra AS
> m, blm_lands_best AS o
> 	WHERE m.the_geom && o.the_geom
> 	AND intersects(m.the_geom,o.the_geom)
> 	AND o.district LIKE 'ANCHORAGE';
> 	
> 	
> QUERY PLAN                                            
> ----------------------------------------------------------------------------
> ----------------------
>  Unique  (cost=17.21..17.22 rows=1 width=26)
>    ->  Sort  (cost=17.21..17.21 rows=1 width=26)
>          Sort Key: m.mlra_name, m.mlra_reg_s
>          ->  Nested Loop  (cost=0.00..17.20 rows=1 width=26)
>                Join Filter: intersects("inner".the_geom, "outer".the_geom)
>                ->  Seq Scan on blm_lands_best o  (cost=0.00..7.61 rows=2
> width=77)
>                      Filter: ((district)::text ~~ 'ANCHORAGE'::text)
>                ->  Index Scan using mlra_gist_index on mlra m
> (cost=0.00..4.78 rows=1 width=114)
>                      Index Cond: (m.the_geom && "outer".the_geom)
>                      Filter: (m.the_geom && "outer".the_geom)
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 

   Paul Ramsey
   Refractions Research
   http://www.refractions.net
   pramsey at refractions.net
   Phone: 250-383-3022
   Cell: 250-885-0632