[postgis-users] how to speed up a query of polygons which contain a point?

[Kevin Neufeld]

Actually, ST_Contains already contains an index call internal in the method.

SELECT p.prosrc as "Source code"
FROM pg_catalog.pg_proc p
WHERE p.proname ~ '^(st_contains)$';
                Source code
-----------------------------------------
  SELECT $1 && $2 AND _ST_Contains($1,$2)
(1 row)

Just make sure you have a GIST index created on your polygonal table 
(and you've ANALYZEd your table).  You can verify if your query is using 
the index by prefixing your sql query with EXPLAIN.  Note, however, that 
just because you may have a spatial index on your polygon table, there's 
no guarantee that PostgreSQL will necessarily use it.  The query planner 
does it's best to determine what indexes should be used and when.  For 
example, if every entry in your polygon table intersects the your token 
POINT listed below, it doesn't make any sense to use the index.  In 
fact, using the index in such case will make your query significantly 
slower.

Cheers,
Kevin

On 9/20/2010 5:00 AM, Li Li wrote:
> hi all,
>      I use this condition in sql to get the polygons that contains this
> point, is there any sql which utilize space index to speed up it?
>      I use "where  ST_Contains(geom,ST_GeomFromText('POINT(116.355387
> 39.993801)',4326));" now. Thank you.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users