Re: find overlapping address ranges - Mailing list pgsql-general

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...)

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.

HTH
Tino Wildenhain

--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