[postgis-users] points in polygon

Young Kim youngkkim at gmail.com
Fri Mar 31 09:52:40 PST 2006

hello all,

looking to select points in a polygon FAST. Seems like a simple
problem, but could not find good doc anywhere.

Thanks in advance.

points - 8663 points in greater los angeles area.
          - has "gist" index on "the_geom"
polygon - city of los angeles
            - has "gist" index on "the_geom"
            - has "btree" index on "fips"
also ran "vaccum analyze"

looking to select all points in city of los angeles.
out of 8663, 3248 points are contained in city of los angeles polygon.
it takes ~163000 ms. that's roughly 3 minutes.
How can I make this faster?

QUERY (x,y values are extent of polygon):
SELECT   point.id
FROM      point, polygon
WHERE   (x >= -118.668411254883)
       AND (y >= 33.7045364379883)
       AND (x <= -117.753349304199)
       AND (y <= 34.3367500305176)
       AND (polygon.fips = '0644000')
       AND point.the_geom && polygon.the_geom
       AND (contains(polygon.the_geom, point.the_geom))

 Aggregate  (cost=9.08..9.09 rows=1 width=4) (actual
time=162893.411..162893.411 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..9.05 rows=9 width=4) (actual
time=4137.275..162885.507 rows=3248 loops=1)
         Join Filter: contains("outer".the_geom, "inner".the_geom)
         ->  Index Scan using idx_polygon_fips on polygon 
(cost=0.00..3.01 rows=1 width=9414) (actual time=0.013..0.017 rows=1
               Index Cond: ((fips)::text = '0644000'::text)
         ->  Index Scan using idx_point_the_geom on point 
(cost=0.00..6.02 rows=1 width=25) (actual time=62.512..17889.149
rows=7050 loops=1)
               Index Cond: (point.the_geom && "outer".the_geom)
               Filter: ((point.x >= -118.668411254883) AND (point.y >=
33.7045364379883) AND (point.x <= -117.753349304199) AND (point.y <=
34.3367500305176) AND (point.the_geom && "outer".the_geom))
 Total runtime: 162893.483 ms

More information about the postgis-users mailing list