[postgis-users] points in polygon

[David Bitner]

Have you tried creating a bounding box out of the x, y pair you are
using and then using the && operator on that rather than using the
x>,y> statements.

See MakeBox2D in http://postgis.refractions.net/docs/ch06.html

On 3/31/06, Young Kim <youngkkim at gmail.com> wrote:
> 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.
>
>
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>