[postgis-users] points in polygon

[Bruce Rindahl]

I don't this is even that complicated.
Try
SELECT  point.id
FROM    point, polygon
WHERE   (polygon.fips = '0644000')
       AND point.the_geom && polygon.the_geom
       AND intersects(polygon.the_geom, point.the_geom);


The first clause checks for only LA county.
The next clause checks if the bounding box of the point is within the
bounding box of the polygon.  (very fast if the GIST indexes are set)
Finally anything that matches the first two is checked for an actual
intersection.

Bruce Rindahl

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Matthew Perry
Sent: Friday, March 31, 2006 12:03 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] points in polygon

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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users