[postgis-users] Points-in-polygon performance

[Paul Ramsey]

pggis 1.3+

select t.*
from region_polygons r, geom_table t
where
   st_contains(r.rpg_geom,t.geom_fld)
and
  rpg_name = 'GVRD_OLD'

pgis < 1.3

select t.*
from region_polygons r, geom_table t
where
   r.rpg_geom && t.geom_fld
and
   contains(r.rpg_geom,t.geom_fld)
and
  rpg_name = 'GVRD_OLD'


On 20-Dec-07, at 1:48 PM, Robin Chauhan wrote:

> I have a region_polygons table where region_polygons.rpg_geom is a  
> MULTIPOLYGON.
> I want to select rows in the geom_table where geom_table.geom_fld  
> is within region_polygons.rpg_geom
>
> The following idiom works for me, but is waaaay slow (whereas I  
> found other kinds of spatial queries are pretty zippy):
>
> SELECT * from geom_table where EXISTS
>                 (select rpg_id from region_polygons where
>                 geom_fld && rpg_geom and intersects(geom_fld,  
> rpg_geom) and rpg_name='GVRD_OLD')
>
> Where should I start?
>
> a) Simplify the multipolygon?
> b) Joins instead of EXISTS?
> c) Would putting the literal WK format for the polygon right into  
> the SQL query help?
> d) ... or something else?
>
> -- 
> Robin R Chauhan
> Pathway Intelligence Inc
> robin at pathwayintelligence.com
> 604-886-3964
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users