[postgis-users] determining intersection count

[Arnaud Lesauvage]

Bart van den Eijnden (OSGIS) wrote:
> Hi list,
> 
> I want to determine with how many geometries in a table a certain 
> geometry (X) intersects. It works fine when I use:
> 
> SELECT count(*) FROM bestemmingsplannen.bouwvlakken AS b, 
> bestemmingsplannen.axel_v AS bp WHERE b.geometrie && bp.geometrie AND 
> intersects(b.geometrie, bp.geometrie) AND b.bouwvlak_id = 10 AND NOT 
> bp.naam = 'Axel';
> 
> so I select geometry X using bouwvlak_id=10.
> 
> Now I want to use GeometryFromText, but the count I get back is totally 
> different (12 versus 2). What am I doing wrong?
> 
> SELECT count(*) FROM bestemmingsplannen.bouwvlakken AS b, 
> bestemmingsplannen.axel_v AS bp WHERE [...]

I believe it is because you forgot to include 'WHERE b.bouwvlak_id 
= 10' in the last query.
If you don't mention that, all rows of b times the rows of bp 
matching your criteria will be included in the count.


Regards
--
Arnaud