[postgis-users] select only points inside a polygon

Stephen Woodbridge woodbri at swoodbridge.com
Sat May 8 12:34:27 PDT 2010

Nicola Zandonà wrote:
> Hi All, i'm a newbie with postgis and probably this is an easy question.
> I have 2 tables, one with a point geometry attribute called tableA, and 
> the other one with a polygon and multi-polygon geometry attribute, 
> called tableB.
> I need to retrieve only those points inside a particular geometry called 
> 1 in the second table, so i try something like:
> SELECT point
> FROM tableA a, tableB b
> WHERE ST_Contains(b.the_geom, a.the_geom)
> AND b.id = '1';

This is a similar construct to what you have. You should have gist 
indexes on at least the tableA.

SELECT point
FROM tableA a, tableB b
WHERE b.id =1
AND b.the_geom && a.the_geom
AND ST_distance(b.the_geom, a.the_geom)=0.0;

You can also try using this where clause if you are using version 1.3+ 
and it should be faster:

WHERE b.id =1
AND ST_DWithin(b.the_geom, a.the_geom, 0.0);


> but no result is returned.
> Is there something wrong with that query? Maybe the use of ST_Contains?
> Any help will be greatly appreciated!
> Tnx in advance.
> Nick.
> _______________________________________________
> 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