Re: find overlapping address ranges

On Mon, Sep 16, 2002 at 09:45:46PM +0200, Tino Wildenhain wrote:
> Hi Alex,
>
> if I understand your problem correctly, a so called self-join
> is what you need here.
>
> This looks like this:
>
> select c1.gid, c1.street, ... from cityplus c1, cityplus c2
> where SOME_OVERLAPPING_CONDITION(c1..., c2...)

Probably something like:

a.streetname = b.streetname
and a.numhigh > b.numlow
and a.numlow < b.numhigh

> The trick is to join your table with it self like with another
> table and use the common syntax to compare your rows.
> Note you probably get your results twice, if your
> overlap-condition is commutable, e.g. overlap(c1,c2) is
> the same as overlap(c2,c1). In this case, DISTINCT is your friend.

Or you can make the operation non-commutable by using:

a.addressid < b.addressid

This will halve the number of comparison required.

> --On Montag, 16. September 2002 13:21 -0600 Alex Rice <alex_rice@arc.to>
> wrote:
>
> > This is more of a SQL question than a pgsql question. I know this should
> > not be hard, I just can't wrap my mind around it. Thanks...
> >
> > So I have a table with street names and address ranges. Some of the
> > address ranges overlap for the same street ranges, and I need to write a
> > report on those rows. In other words, I want to do something similar to
> > this:
> >
> > SELECT gid, street, fromleft, toleft, fromright, toright
> > FROm cityplus WHERE
> > HAS_OVERLAPPING_ADDRESS_RANGE_FOR_SAME_STREETNAME()
> > ORDER BY street
> >
> > Does this require a subselect? Stored procedure? Neither? Can I use a
> > pgsql line geometric type to check for overlap? This data is in Postgis
> > as well, so I have those methods at my disposal as well.
> >
> >
> >                Table "cityplus"
> >     Column   |       Type        | Modifiers
> > ------------+-------------------+-----------
> >   gid        | integer           |
> >   street     | character varying |
> >   fromleft   | integer           |
> >   toleft     | integer           |
> >   fromright  | integer           |
> >   toright    | integer           |
> >   fnode_     | integer           |
> >   tnode_     | integer           |
> >   lpoly_     | integer           |
> >   rpoly_     | integer           |
> >   length     | double precision  |
> >   netcurr_   | integer           |
> >   netcurr_id | integer           |
> >   l_low      | integer           |
> >   l_high     | integer           |
> >   r_low      | integer           |
> >   r_high     | integer           |
> >   str        | character varying |
> >   dgn        | character varying |
> >   q          | character varying |
> >   stanno     | character varying |
> >   code       | integer           |
> >   the_geom   | geometry          |
> > Indexes: cityplus_addnum_index,
> >           cityplus_geom_index,
> >           cityplus_gid_index,
> >           cityplus_oid_index
> > Check constraints: "$1" (srid(the_geom) = -1)
> >                     "$2" ((geometrytype(the_geom) =
> > 'MULTILINESTRING'::text) OR ( the_geom IS NULL))
> >
> >
> > Alex Rice
> > Mindlube Software
> > http://mindlube.com/
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.