Re: find overlapping address ranges

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.