find overlapping address ranges

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:

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/

On Monday, September 16, 2002, at 08:18 PM, Martijn van Oosterhout
wrote:

On Mon, Sep 16, 2002 at 09:45:46PM +0200, Tino Wildenhain wrote:

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

Tino and Martijn, thanks for the suggestions. I'm on the right track
now. Couple more questions I would like to venture if I may...

1) In this table, fromleft toleft fromright toright are inconsistently
used: the "to" address are sometimes higher than the "from" address,
and the lefts may be odd or even and vice-versa. So I need to calculate
the numhigh and numlow before doing the comparison shown above. Can
this be done in SQL? I think it requires subquery?

2) If you could recommend a book on SQL, which one would you recommend?

Thanks,

Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alrice@swcp.com
alex_rice@arc.to

On Tue, Sep 17, 2002 at 09:38:49AM -0600, Alex Rice wrote:

On Monday, September 16, 2002, at 08:18 PM, Martijn van Oosterhout
wrote:

On Mon, Sep 16, 2002 at 09:45:46PM +0200, Tino Wildenhain wrote:

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

Tino and Martijn, thanks for the suggestions. I'm on the right track
now. Couple more questions I would like to venture if I may...

1) In this table, fromleft toleft fromright toright are inconsistently
used: the "to" address are sometimes higher than the "from" address,
and the lefts may be odd or even and vice-versa. So I need to calculate
the numhigh and numlow before doing the comparison shown above. Can
this be done in SQL? I think it requires subquery?

Maybe int4larger and int4smaller are what you are looking for?

2) If you could recommend a book on SQL, which one would you recommend?

No idea, sorry.

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/