[postgis-users] points in polygon

[Young Kim]

Thanks for your prompt response.

I have tried :
SELECT  point.id
FROM    point, polygon
WHERE   (polygon.fips = '0644000')
      and point.the_geom && SetSRID('BOX3D(-118.668411254883
33.7045364379883,-117.753349304199 34.3367500305176)'::box3d,-1)
	  AND (contains(polygon.the_geom, point.the_geom));

got ~146000ms.

could not get "makebox2d" to work. syntax errors. i'm very new to postgis.

thanks.


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