[postgis-users] determining intersection count

[Mark Cave-Ayland]

On Fri, 2006-07-28 at 15:29 +0200, 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 
> GeometryFromText('MULTIPOLYGON(((51603.74237044 
> 365820.98748955555,51607.223759734 365821.15731342667,51606.459552328 
> 365818.18539568223,51603.402722704 365818.8646911667,51603.74237044 
> 365820.98748955555)))', 28992) && bp.geometrie AND 
> intersects(GeometryFromText('MULTIPOLYGON(((51603.74237044 
> 365820.98748955555,51607.223759734 365821.15731342667,51606.459552328 
> 365818.18539568223,51603.402722704 365818.8646911667,51603.74237044 
> 365820.98748955555)))', 28992), bp.geometrie) AND NOT bp.naam = 'Axel';
> 
> I verified I am using the right geometry by using the following query:
> 
> select AsText(geometrie) from bestemmingsplannen.bouwvlakken b where 
> b.bouwvlak_id = 10;
> 
> the result is:
> astext
> "MULTIPOLYGON(((51603.74237044 365820.987489556,51607.223759734 
> 365821.157313427,51606.459552328 365818.185395682,51603.402722704 
> 365818.864691167,51603.74237044 365820.987489556)))"
> 
> Thanks in advance.
> 
> Best regards,
> Bart


Hi Bart,

My guess is that PostgreSQL is calculating a cross product result since
you still mention the bestemmingsplannen.bouwvlakken table in your
second query, yet there is no longer any constraint binding it to the
bestemmingsplannen.axel_v in your second query. This is a "feature" of
SQL which causes the planner to executed a nested loop between the two
mentioned tables if the constraint is omitted. The solution should be to
remove the reference to bestemmingsplannen.bouwvlakken from the query,
e.g.


SELECT count(*) FROM  
bestemmingsplannen.axel_v AS bp WHERE 
GeometryFromText('MULTIPOLYGON(((51603.74237044 
365820.98748955555,51607.223759734 365821.15731342667,51606.459552328 
365818.18539568223,51603.402722704 365818.8646911667,51603.74237044 
365820.98748955555)))', 28992) && bp.geometrie AND 
intersects(GeometryFromText('MULTIPOLYGON(((51603.74237044 
365820.98748955555,51607.223759734 365821.15731342667,51606.459552328 
365818.18539568223,51603.402722704 365818.8646911667,51603.74237044 
365820.98748955555)))', 28992), bp.geometrie) AND NOT bp.naam = 'Axel';



Kind regards,

Mark.