[postgis-users] points in polygon

[Matthew Perry]

Perhaps you can try WITHIN instead of CONTAINS?

"""
Within(geometry A, geometry B)

    Returns 1 (TRUE) if Geometry A is "spatially within" Geometry B.
"""

- matt

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


--
Matt Perry
perrygeo at gmail.com
http://www.perrygeo.net