[postgis-users] Finding polygons outside other polygons

[Nicklas Avén]

Hallo Martin

I guess I just try to answer a part of your question. I don't know what your exception comes from.

If I understand things right your approach won't work because what you are doing is joining your houses with every city exept the one it eventually is intersecting. You have do get it from the other side.

Get all intersections in a subquery and then ask for all houses not in that subquery. 
If you have a column gid with unique values in houses table you could do something like:

select count(*) from my_houses where gid not in
(Select a.gid from my_houses as a , my_cities_multi as f
 where   ST_Intersects( a.the_geom  , f.the_geom ) 

The indexes is important here. Both the spatial indexes and an index on the gid column.

Hope that helps
Nicklas




2010-03-08 Martin Weinelt  wrote:

Hello to all,
>
>in a current project, I am trying to do a bit more serious stuff with PostGIS, 
>apart from using it as as a backend for web mapping. 
>
>I could use some tips or guidelines for the follwing - not very complicated - 
>task.
>
>I have two tables, one holding some 3000 polygons describing city limits, 
>another with some 100000 polygons describing individual houses. 
>
>What I would like to have is another table with all houses outside any city 
>limits.
>
>The following function seem to be candidates for this
>  ST_Disjoint
>  ST_Covers (not)
>  ST_Within (not)
>  ST_Intersects (not)
>  ST_Distance
>
>First thing I did was to put the city polygons into one MultiPolygon (table 
>with one row, 'my_cities_multi' ). Then , for the sake of testing this, I 
>ran :
>
>select count(a.the_geom) from my_houses as a , my_cities_multi as f
>  where   ST_Disjoint( a.the_geom  , f.the_geom ) 
>
>This took some 30 minutes on a 3 GHz, two core machine and ended with:
>
>"NOTICE: IllegalArgumentException: Points of LinearRing do not form a closed 
>linestring"
>
>I've checked with ST_IsClosed on both tables, no complains.
>
>I guess my task is a rather common application. But I am not that SQL-savvy. 
>May be some one can point me to a best practice with this, also in terms of 
>performance.
>
>Thanks,
>
>Martin 
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100308/91f4bd79/attachment.html>