Overlapping address ranges - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Overlapping address ranges

Author

Topic

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-11 : 17:32:44

I am struggling with a query to find overlapping address ranges in a db. I want to find cases where a from/to range overlaps with a from/to of a different segment id, but same roadnameid. Can anyone tell me where the statement below goes awry? Note that my fields are nvarchar. I'm getting all the records multiplied, not just the overlapping records.

Thanks,

Jim

SELECT s1.RDNAMELEFT,s1.ROADNAMEID,s2.ROADNAMEID
FROM GISLOADER.Streets AS s1
INNER JOIN GISLOADER.Streets AS s2 ON s1.ROADNAMEID=s2.ROADNAMEID
WHERE
s1.ROADNAMEID = s2.ROADNAMEID AND
(
s1.LEFTFROM BETWEEN s2.LEFTFROM AND s2.LEFTTO OR
s1.LEFTTO BETWEEN s2.LEFTFROM AND s2.LEFTTO OR
s2.LEFTFROM BETWEEN s1.LEFTFROM AND s1.LEFTTO OR
s2.LEFTTO BETWEEN s1.LEFTFROM AND s1.LEFTTO
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 17:49:39

[code]SELECT *
FROM GISLOADER.Streets AS s1
INNER JOIN GISLOADER.Streets AS s2 ON s2.ROADNAMEID = s1.ROADNAMEID
WHERE s1.LEFTFROM <= s2.LEFTTO
AND s1.LEFTTO >= s2.LEFTFROM[/code]

E 12°55'05.63"
N 56°04'39.26"

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-12 : 09:55:37

Peso,

Thanks much for your reply. When I ran it though, I got 1,336,662 rows. I think we're missing a parameter or two.

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 10:04:31

You will get double amount of overlapping records, since record 1 overlap record 2, both records will be displayed with each other as overlapping part.
Do you have an IDENTITY column or some other primary key column present in the table?

SELECT		*
FROM		GISLOADER.Streets AS s1
INNER JOIN	GISLOADER.Streets AS s2 ON s2.ROADNAMEID = s1.ROADNAMEID
WHERE		s1.LEFTFROM <= s2.LEFTTO
		AND s1.LEFTTO >= s2.LEFTFROM
		AND s1.pkCol < s2.pkCol

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 10:10:43

[code]DECLARE @Sample TABLE
(
pkCol INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
nameRoad VARCHAR(20),
rngFrom INT,
rngTo INT
)

INSERT @Sample
SELECT 'Peso', 1, 18 UNION ALL
SELECT 'Peso', 19, 25 UNION ALL
SELECT 'Sweden', 1, 2147483647 UNION ALL
SELECT 'SQLTeam', 100, 199 UNION ALL
SELECT 'SQLTeam', 150, 175

-- Without pkCol check, original query
SELECT *
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.nameRoad = s1.nameRoad
WHERE s1.rngFrom <= s2.rngTo
AND s1.rngTo >= s2.rngFrom

-- As you can see, the road is overlapping self
-- and other roads and presented twice

-- With pkCol check, new query
SELECT *
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.nameRoad = s1.nameRoad
WHERE s1.rngFrom <= s2.rngTo
AND s1.rngTo >= s2.rngFrom
AND s1.pkCol < s2.pkCol[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 10:12:26

Original query

pkCol	nameRoad	rngFrom	rngTo		pkCol	nameRoad	rngFrom	rngTo
1	Peso		1	18		1	Peso		1	18
2	Peso		19	25		2	Peso		19	25
3	Sweden		1	2147483647	3	Sweden		1	2147483647
4	SQLTeam		100	199		4	SQLTeam		100	199
5	SQLTeam		150	175		4	SQLTeam		100	199
4	SQLTeam		100	199		5	SQLTeam		150	175
5	SQLTeam		150	175		5	SQLTeam		150	175

And with the new query (including pkcol check)

pkCol	nameRoad	rngFrom	rngTo	pkCol	nameRoad	rngFrom	rngTo
4	SQLTeam		100	199	5	SQLTeam		150	175

E 12°55'05.63"
N 56°04'39.26"

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-12 : 10:32:04

The query below brought me down to 748479 records (on a 32090 record table).

SELECT *
FROM GISLOADER.Streets AS s1
INNER JOIN GISLOADER.Streets AS s2 ON s2.ROADNAMEID = s1.ROADNAMEID
WHERE s1.LEFTFROM <= s2.LEFTTO
AND s1.LEFTTO >= s1.LEFTFROM
AND s1.OBJECTID_1 < s2.OBJECTID_1

OBJECTID_1 is uniqueID. Recall that FROM/TO are nvarchar. Could that be it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 10:40:01

No.

SELECT *
FROM GISLOADER.Streets AS s1
INNER JOIN GISLOADER.Streets AS s2 ON s2.ROADNAMEID = s1.ROADNAMEID
WHERE s1.LEFTFROM <= s2.LEFTTO
AND s1.LEFTTO >= s2.LEFTFROM
AND s1.OBJECTID_1 < s2.OBJECTID_1

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 11:01:53

How many records do you get now?

E 12°55'05.63"
N 56°04'39.26"

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-12 : 11:14:16

I got 174573 records.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 11:27:49

You get 174,573 overlapping records from a 32,090 record table with this?

SELECT		*
FROM		GISLOADER.Streets AS s1
INNER JOIN	GISLOADER.Streets AS s2 ON s2.ROADNAMEID = s1.ROADNAMEID
WHERE		s1.LEFTFROM <= s2.LEFTTO
		AND s1.LEFTTO >= s2.LEFTFROM
		AND s1.OBJECTID_1 < s2.OBJECTID_1

Oh man, you do have a lot of overlapping records...

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 11:28:54

If you inspect some of the records, is it true they overlap?

E 12°55'05.63"
N 56°04'39.26"

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-12 : 11:40:45

First of all we need to get rid of those that have no LeftFrom/LeftTo. I checked one that had a from/to and it did not appear to have overlapping segments with the same ROADNAMEID.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 12:39:14

Great! And good luck!

E 12°55'05.63"
N 56°04'39.26"

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-12 : 14:30:26

I know the 174,573 overlapping is not correct. Something is awry in the query logic. Thanks anyways for your efforts!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 15:04:09

Post some 20 records or so as sample data.
Choose well, both records you know will not overlap, and records that will overlap.

E 12°55'05.63"
N 56°04'39.26"

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-12 : 15:41:16

Here goes:

"OID_","OBJECTID_1","LEFTPAR","MCDRIGHT","LEFTPRE","LEFTFROM","LEFTTO","RIGHTPRE","RIGHTFROM","RIGHTTO","ROADNAMEID","SEGMENTID","PREDIRLEFT","PREDIRRIGH","RDNAMELEFT","RDNAMERIGH","RDTYPELEFT","RDTYPERIGH","SUFFDIRLEF","SUFFDIRRIG","RDNAMEIDLE","RDNAMEIDRI","GlobalID"
,24677.000000,"O","BV"," ","12301","12399"," ","12300","12398",1153.000000,24677.000000,"W","W","FAIRMOUNT","FAIRMOUNT","AVE","AVE"," "," ",1153.000000,1153.000000,"{AA4D36CF-97DF-4990-97B7-7DF66283F682}"
,24678.000000,"O","BV"," ","12401","12499"," ","12400","12498",1153.000000,24678.000000,"W","W","FAIRMOUNT","FAIRMOUNT","AVE","AVE"," "," ",1153.000000,1153.000000,"{B3FF59A1-77D6-43E3-BE39-7E15158442E2}"
,85351.000000,"O","BV"," ","157411","157413"," ","157414","157416",1172.000000,25407.000000,"W","W","COLFAX","COLFAX","PL","PL"," "," ",1172.000000,1172.000000,"{FDC39AB7-7A55-44FF-9085-92824D5EC746}"
,85352.000000,"O","MICO"," ","157417","157419"," ","157420","157422",1148.000000,25422.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{D9B2D2D2-799E-4CE2-95EC-ECD96C189365}"
,85353.000000,"O","MICO"," ","157423","157425"," ","157426","157428",1148.000000,31909.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{13E18142-9BB8-4CC9-B267-5E21BDE4013B}"
,85354.000000,"O","BV"," ","157429","157431"," ","157432","157434",1156.000000,25266.000000,"W","W","LANCASTER","LANCASTER","AVE","AVE"," "," ",1156.000000,1156.000000,"{198A857F-4043-4B14-933C-86EA4BEB83F1}"
,85355.000000,"O","MICO"," ","157435","157437"," ","157438","157440",1148.000000,31909.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{EDA4394E-49B2-4F42-9999-4765E2AA6752}"
,85356.000000,"O","MICO"," ","157441","157443"," ","157444","157446",1148.000000,31911.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{E792C1A4-6F87-4D55-9DF6-ACA3802C8859}"
,85357.000000,"O","BV"," ","157447","157449"," ","157450","157452",1152.000000,25246.000000,"W","W","EGGERT","EGGERT","PL","PL"," "," ",1152.000000,1152.000000,"{D2983D36-8BE1-4B83-A738-9CFEAE7DDCC1}"
,85358.000000,"O","MICO"," ","157453","157455"," ","157456","157458",1148.000000,31911.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{8AFDD61A-CAEC-45D7-9FDF-5630543C3471}"
,85359.000000,"O","MICO"," ","157459","157461"," ","157462","157464",1148.000000,31913.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{03B7AD43-CA48-4441-9863-29B7FEE05DF8}"
,85360.000000,"O","BV"," ","157465","157467"," ","157468","157470",7666.000000,25413.000000,"W","W","VILLARD","VILLARD","AVE","AVE"," "," ",7666.000000,7666.000000,"{024385AB-75F2-473F-B9C5-BF979AA8A5C0}"
,85361.000000,"O","MICO"," ","157471","157473"," ","157474","157476",1148.000000,25482.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{006C33F0-A33A-44A7-AC02-1374AC68EE5E}"
,85362.000000,"O","MICO"," ","157477","157479"," ","157480","157482",1148.000000,31913.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{53FDC6AE-5059-4E74-9EBF-6D8299BFDE63}"
,85363.000000,"O","BV"," ","157483","157485"," ","157486","157488",1163.000000,25425.000000,"W","W","CUSTER","CUSTER","AVE","AVE"," "," ",1163.000000,1163.000000,"{8B2D8956-20A9-4DEB-82B6-7512FFBDD2A0}"
,85364.000000,"O","MICO"," ","157489","157491"," ","157492","157494",1148.000000,25482.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{5734A0F3-E815-4C30-AD84-C3C73974DDE7}"
,85365.000000,"O","MICO"," ","157495","157497"," ","157498","157500",1148.000000,31917.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{6C29C9D3-AA64-4ED0-A4BF-5B7CCC11416B}"
,85366.000000,"O","BV"," ","157501","157503"," ","157504","157506",1161.000000,25304.000000,"W","W","SILVER SPRING","SILVER SPRING","DR","DR"," "," ",1161.000000,1161.000000,"{FC116CF6-C71B-4718-A174-6AB3303BE50F}"
,85367.000000,"O","MICO"," ","157507","157509"," ","157510","157512",1148.000000,31917.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{BC0CCAFD-DA71-47DB-99F0-BAD2704CB879}"
,85368.000000,"O","MICO"," ","157513","157515"," ","157516","157518",1148.000000,31919.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{3BE8B4CF-65E5-456D-92BF-CC907938407E}"
,85369.000000," ","BV"," ","157519","157521"," ","157522","157524",1161.000000,25416.000000,"W","W","SILVER SPRING","SILVER SPRING","DR","DR"," "," ",1161.000000,1161.000000,"{69064ECB-F58A-408E-BFF6-1726376D0B0D}"
,85370.000000,"O","MICO"," ","157525","157527"," ","157528","157530",1148.000000,31919.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{6733E27D-49B2-4C25-B6AD-A3DC2423D6F6}"
,85371.000000,"O","MICO"," ","157531","157533"," ","157534","157536",1148.000000,31920.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{B33E6CCE-1EB3-49DD-9343-2EACBF66283E}"
,85372.000000,"O","MICO"," ","157537","157539"," ","157540","157542",1148.000000,25054.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{479837B9-98B7-411C-AFDE-9F1F2116F3D3}"
,85373.000000,"O","MICO"," ","157543","157545"," ","157546","157548",1148.000000,25055.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{A207C6D7-655C-4EFE-9105-3A9DAB3DEDB7}"
,85374.000000,"O","BV"," ","157549","157551"," ","157552","157554",1149.000000,25216.000000,"W","W","COURTLAND","COURTLAND","AVE","AVE"," "," ",1149.000000,1149.000000,"{DD4151D8-896D-4368-9558-F1F04C96D25A}"
,85375.000000,"O","MICO"," ","157555","157557"," ","157558","157560",1148.000000,25056.000000,"N","N","124TH","124TH","ST","ST"," "," ",1148.000000,8304.000000,"{9180FD3B-2A48-43A6-8CE6-BAFEF0629588}"

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-12 : 17:10:14

I think I got it. It was finding all the nulls and 0's. Below gives me 5036 records that appear to be overlapping. Thanks again so much for your help. This is great.

SELECT *
FROM GISLOADER.Streets AS s1
INNER JOIN GISLOADER.Streets AS s2 ON s2.ROADNAMEID = s1.ROADNAMEID
WHERE s1.LEFTTO IS NOT NULL AND
s1.LEFTTO <> '' AND
s1.ROADNAMEID <> 0 AND
s1.ROADNAMEID IS NOT NULL AND
s1.OBJECTID_1 != s2.OBJECTID_1 AND
s1.LEFTFROM <= s2.LEFTTO AND
s1.LEFTTO >= s2.LEFTFROM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 17:31:25

WHERE s1.LEFTTO IS NOT NULL AND
s1.LEFTTO <> ''

One of these two is redundant. When you check for <> '' then automatically all NULLs are omitted.
And a string cannot be < ''.

SELECT          *
FROM            GISLOADER.Streets AS s1
INNER JOIN      GISLOADER.Streets AS s2 ON s2.RoadNameID = s1.RoadNameID
                AND s2.LeftTo > ''
                AND s2.LeftFrom > ''
                AND s2.RoadNameID <> 0
                AND s2.ObjectID_1 > s1.ObjectID_1
                AND s2.LeftTo >= s1.LeftFrom 
                AND s2.LeftFrom <= s1.LeftTo 
WHERE           s1.LeftTo > ''
                AND s1.LeftFrom > ''
                AND s1.RoadNameID <> 0

E 12°55'05.63"
N 56°04'39.26"

jlandwehr
Starting Member

23 Posts

Posted - 2009-02-13 : 09:59:03

Peso,
I see your point. Furthermore, your version of the statement cut our my duplicated records, so I'm down to exactly 2518 records (from 5036). This is much closer to what I expected. Thanks again so much. I learned much through these iterations.
Best Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources