[postgis-users] Finding polygons outside other polygons

[Martin Weinelt]

Dear Nicklas,

your solution works like charme, thanks indeed. 

The ST_IsClosed was not enough for excluding broken geometries from the input 
tables.  I put an ST_IsValid-test into the WHERE-clause: 

insert into houses_offlimits 
   (select * from houses where gid not in 
       (Select a.gid from houses as a , my_cities as f
       where  ST_IsValid(a.the_geom) AND ST_Intersects( a.the_geom , 
f.the_geom ) ) );

Taking care of the broken ones is another question.

Cheers, Martin

On Monday 08 March 2010 12:44:47 Martin Weinelt wrote:
> 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