[postgis-users] determining intersection count

[Bart van den Eijnden (OSGIS)]

Hi Mark,

thanks. That solved it indeed.

Best regards,
Bart

Mark Cave-Ayland wrote:

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


-- 
Bart van den Eijnden
OSGIS, Open Source GIS
http://www.osgis.nl