# [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.

BACKGROUND:
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"

PROBLEM:
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
loops=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

```