query question

[G. van Es]

Hi All,

Maybe a newbie question, sorry for that.

We want to know wich objects of a certain type touches objects (multipolygon) of another type. Let me explain my silly statement.

The table T looks simplified like:

type | the_geom (multipolygon)
==============================
13   | ....
13   | ....
11   | ....
14   | ....
11   | ....
15   | ....
15   | ....

I want to have a list of wich geometry of a certain type is touching or crossing another type.

Can someone point me in the right direction?

Thanks a lot,

Ge



     
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Maxime van Noppen]

G. van Es wrote:
> I want to have a list of wich geometry of a certain type is touching or crossing another type.
>
> Can someone point me in the right direction?

Hi,

If I understand your problem you want to find for each geometry in your
table which geometries intersect with it ?

This can be done with the ST_Intersects function and an self-join.
Something like :

SELECT t1.type, t2.type FROM T t1, T t2 WHERE t1.type != t2.type AND
ST_Intersects(t1.the_geom, t2.the_geom);

This will generate an output like :

t1.type | t2.type
-----------------
13      | 12
13      | 1
13      | 4
12      | 3
12      | 8
...

--
Maxime
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[G. van Es]

Thanks Maxime,

That worked!  

However now I see another problem.

Some polygons are touching in a way they shouldn't. This is a problem of our drawing department but since there are a few thousand of those objects it would be nice to query with a certain tolerance. Say, the object should overlap for at least 80%.

Well, that is too much rocket science for me at the moment.
If someone has any clue if this is possible please drop me a line.

Thanks,
Ge

--- On Wed, 11/4/09, Maxime van Noppen <maxime@...> wrote:

> From: Maxime van Noppen <maxime@...>
> Subject: Re: [postgis-users] query question
> To: "PostGIS Users Discussion" <postgis-users@...>
> Date: Wednesday, November 4, 2009, 3:56 AM
> G. van Es wrote:
> > I want to have a list of wich geometry of a certain
> type is touching or crossing another type.
> >
> > Can someone point me in the right direction?
>
> Hi,
>
> If I understand your problem you want to find for each
> geometry in your
> table which geometries intersect with it ?
>
> This can be done with the ST_Intersects function and an
> self-join.
> Something like :
>
> SELECT t1.type, t2.type FROM T t1, T t2 WHERE t1.type !=
> t2.type AND
> ST_Intersects(t1.the_geom, t2.the_geom);
>
> This will generate an output like :
>
> t1.type | t2.type
> -----------------
> 13      | 12
> 13      | 1
> 13      | 4
> 12      | 3
> 12      | 8
> ...
>
> --
> Maxime
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

     
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Nicolas Ribot]

2009/11/5 G. van Es <gves2000@...>:
> Thanks Maxime,
>
> That worked!
>
> However now I see another problem.
>
> Some polygons are touching in a way they shouldn't. This is a problem of our drawing department but since there are a few thousand of those objects it would be nice to query with a certain tolerance. Say, the object should overlap for at least 80%.
>

Hello,

You could compute the actual intersection (st_intersection) area and
compare its area to the initial polygons area

Nicolas

> Well, that is too much rocket science for me at the moment.
> If someone has any clue if this is possible please drop me a line.
>
> Thanks,
> Ge
>
> --- On Wed, 11/4/09, Maxime van Noppen <maxime@...> wrote:
>
>> From: Maxime van Noppen <maxime@...>
>> Subject: Re: [postgis-users] query question
>> To: "PostGIS Users Discussion" <postgis-users@...>
>> Date: Wednesday, November 4, 2009, 3:56 AM
>> G. van Es wrote:
>> > I want to have a list of wich geometry of a certain
>> type is touching or crossing another type.
>> >
>> > Can someone point me in the right direction?
>>
>> Hi,
>>
>> If I understand your problem you want to find for each
>> geometry in your
>> table which geometries intersect with it ?
>>
>> This can be done with the ST_Intersects function and an
>> self-join.
>> Something like :
>>
>> SELECT t1.type, t2.type FROM T t1, T t2 WHERE t1.type !=
>> t2.type AND
>> ST_Intersects(t1.the_geom, t2.the_geom);
>>
>> This will generate an output like :
>>
>> t1.type | t2.type
>> -----------------
>> 13      | 12
>> 13      | 1
>> 13      | 4
>> 12      | 3
>> 12      | 8
>> ...
>>
>> --
>> Maxime
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@...
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[G. van Es]

Good call!
With the following partial statement we get promising results.

round(st_area(st_intersection(r.the_geom,s.the_geom)) / st_area(r.the_geom) * 100) as Percentage


Thanks,
Ge

--- On Thu, 11/5/09, Nicolas Ribot <nicky666@...> wrote:

> From: Nicolas Ribot <nicky666@...>
> Subject: Re: [postgis-users] query question
> To: "PostGIS Users Discussion" <postgis-users@...>
> Date: Thursday, November 5, 2009, 12:45 AM
> 2009/11/5 G. van Es <gves2000@...>:
> > Thanks Maxime,
> >
> > That worked!
> >
> > However now I see another problem.
> >
> > Some polygons are touching in a way they shouldn't.
> This is a problem of our drawing department but since there
> are a few thousand of those objects it would be nice to
> query with a certain tolerance. Say, the object should
> overlap for at least 80%.
> >
>
> Hello,
>
> You could compute the actual intersection (st_intersection)
> area and
> compare its area to the initial polygons area
>
> Nicolas
>
> > Well, that is too much rocket science for me at the
> moment.
> > If someone has any clue if this is possible please
> drop me a line.
> >
> > Thanks,
> > Ge
> >
> > --- On Wed, 11/4/09, Maxime van Noppen <maxime@...>
> wrote:
> >
> >> From: Maxime van Noppen <maxime@...>
> >> Subject: Re: [postgis-users] query question
> >> To: "PostGIS Users Discussion" <postgis-users@...>
> >> Date: Wednesday, November 4, 2009, 3:56 AM
> >> G. van Es wrote:
> >> > I want to have a list of wich geometry of a
> certain
> >> type is touching or crossing another type.
> >> >
> >> > Can someone point me in the right direction?
> >>
> >> Hi,
> >>
> >> If I understand your problem you want to find for
> each
> >> geometry in your
> >> table which geometries intersect with it ?
> >>
> >> This can be done with the ST_Intersects function
> and an
> >> self-join.
> >> Something like :
> >>
> >> SELECT t1.type, t2.type FROM T t1, T t2 WHERE
> t1.type !=
> >> t2.type AND
> >> ST_Intersects(t1.the_geom, t2.the_geom);
> >>
> >> This will generate an output like :
> >>
> >> t1.type | t2.type
> >> -----------------
> >> 13      | 12
> >> 13      | 1
> >> 13      | 4
> >> 12      | 3
> >> 12      | 8
> >> ...
> >>
> >> --
> >> Maxime
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users@...
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@...
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

     
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users