[postgis-users] Spatial query help

[Stephen Crawford]

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)