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(at)arc(dot)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(at)postgresql(dot)org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Martijn van Oosterhout <kleptog(at)svana(dot)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.