Thread: find overlapping address ranges
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/
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
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.
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/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.