[postgis-users] Question about Crosses(geometry, geometry)

[Matt Doughty]

Thanks for the help, everyone. Regina takes all the honours this time though.

Your 1. script worked, but I had to tweak it as I had forgotten to mention that were some line segments that are completely contained by the polygons. Thus I added a Contains() argument and it worked, quite quickly too! On Monday I'll have time to try your other suggestions.

The final script:

SELECT lines.*
FROM lines 
LEFT JOIN polygons 
ON 	((crosses(lines.the_geom,polygons.the_geom)) 
	OR 
	(contains(polygons.the_geom, lines.the_geom)))
WHERE polygons.the_geom is NULL

Many thanks once again,

Matt

 
 
Matt Doughty
 
GEOGRAMA S.L.
Tel.:  +34 945 13 13 72    652 77 14 15
Fax: +34 945 23 03 40 
www.geograma.com
 
 

-----Mensaje original-----
De: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] En nombre de Obe, Regina
Enviado el: viernes, 08 de junio de 2007 12:53
Para: PostGIS Users Discussion
Asunto: RE: [postgis-users] Question about Crosses(geometry, geometry)

There are 3 ways of doing it I can think of.  I'm assuming you are trying to figure out which lines do not cross any of the polygons correct?

I'm not sure which would be faster in your case.  Way 1 is usually the fastest

Way 1
SELECT lines.* 
FROM  lines LEFT JOIN polygons ON crosses(lines.the_geom,polygons.the_geom)
WHERE polygons.the_geom is NULL


Way 2 - I think what David was thinking (here I am assuming gid is primary key of your lines table) - non-correlated subquery
SELECT lines.* 
FROM  lines 
WHERE   lines.gid NOT IN(SELECT l.gid FROM lines l, polygons p WHERE crosses(l.the_geom,polygons.the_geom))

Way 3 - correlated subquery
SELECT lines.* 
FROM  lines 
WHERE   NOT EXISTS(SELECT p.the_geom FROM polygons p WHERE crosses(lines.the_geom,polygons.the_geom))

Hope that hleps,
Regina






-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Matt Doughty
Sent: Friday, June 08, 2007 6:30 AM
To: davidtecher at yahoo.fr; PostGIS Users Discussion
Subject: RE: [postgis-users] Question about Crosses(geometry, geometry)

David,

Thanks for the tip but it didn't work. It returned the following error:

>ERROR: missing FROM-clause entry for table "polygons"

Matt

 
 
Matt Doughty
 
GEOGRAMA S.L.
Tel.:  +34 945 13 13 72    652 77 14 15
Fax: +34 945 23 03 40 
www.geograma.com
 
 

-----Mensaje original-----
De: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] En nombre de TECHER David
Enviado el: viernes, 08 de junio de 2007 12:27
Para: PostGIS Users Discussion
Asunto: Re: [postgis-users] Question about Crosses(geometry, geometry)

Hi Matt

instead of

select lines.* from lines,polygons where not 
crosses(lines.the_geom,polygons.the_geom)

try

select lines.* from lines where not 
crosses(lines.the_geom,polygons.the_geom)

don't use a call to polygons' table

Matt Doughty a écrit :
>
> Hi List,
>
> I've a question about the CROSSES (geometry, geometry) function. I'd 
> like to use it to select lines from a table that do not cross, or 
> intersect, some polygons in a separate table.
>
> The following script lets me select the lines that cross the polygons:
>
> SELECT
>
> lines.*
>
> FROM
>
> lines,
>
> polygons
>
> WHERE
>
> CROSSES(lines.the_geom, polygons.the_geom)
>
> Now I'd like to place a 'NOT' before the function CROSSES (-> NOT 
> CROSSES(lines.the_geom, polygons.the_geom), rather naively it seems, 
> as doing that returns me a table with the same geometry as the 
> original layer, but with 30 times more rows (an increase of 400 to 
> 12,000 records). In other words, it hasn't 'selected' and lines out of 
> the layer, merely repeated the table geometry 30 times.
>
> Can anybody explain where I'm going wrong?
>
> Many thanks in advance,
>
> Matt
>
> Matt Doughty
>
> 	
>
> GEOGRAMA S.L.
>
> Tel.: +34 945 13 13 72 652 77 14 15
>
> Fax: +34 945 23 03 40
>
> www.geograma.com <http://www.geograma.com/>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   


	

	
		
___________________________________________________________________________ 
Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son interface révolutionnaire.
http://fr.mail.yahoo.com
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users