[postgis-users] points in polygon
David Bitner
osgis.lists at gmail.com
Fri Mar 31 10:07:47 PST 2006
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
>
More information about the postgis-users
mailing list