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/

Search Discussions

  • Tino Wildenhain at Sep 16, 2002 at 7:41 pm
    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 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
  • Martijn van Oosterhout at Sep 17, 2002 at 2:18 am

    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 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.
  • Alex Rice at Sep 17, 2002 at 3:38 pm

    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
  • Martijn van Oosterhout at Sep 17, 2002 at 11:24 pm

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedSep 16, '02 at 7:21p
activeSep 17, '02 at 11:24p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase