[postgis-users] Finding polygons outside other polygons

[Martin Weinelt]

Hi Nicklas,

thanks a lot, especially for the tip with the subquery. Of course I started up 
you code and , again:

> NOTICE:  IllegalArgumentException: Points of LinearRing do not form a closed 
linestring
> ERROR:  POSTGIS2GEOS conversion failed

i will try and check again, if I have any invalid geomatries in the tables.

Cheers, 

Martin

On Monday 08 March 2010 09:55:23 Nicklas Avén wrote:
> 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



-- 
 --- Martin Weinelt
 --- kk+w - digital cartography
 --- Kiel, Germany
 --- Tel: +49.431.5791165
 --- http://kkw.planiglobe.com