[postgis-users] select only points inside a polygon

[Stephen Woodbridge]

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);

-Steve
  http://imaptools.com/

> 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