|
|
After showing basic facts about road network of Hatsfield city in
Part 1, we want to ask some questions.
First: What is the longest street in this piece ?
Calculate street length:
SELECT roadname, SUM(link_len) AS total_len
FROM roads
GROUP BY roadname
ORDER BY SUM(link_len) DESC;
|
Results:
| roadname | total_len |
| Mortington Avenue | 498 |
| High Amplitude Avenue | 464 |
| Archibald St | 367 |
| ... | ... |
Ok. Mortington Avenue is the longest.
Next question: Are these two streets intersects ("Archibald St" & "High Amplitude Avenue") ?
In order to answer we have to find at least one junction that they share.
Look for the shared junction:
SELECT a.juncno, a.roadname, b.roadname
FROM
(SELECT TJunc AS juncno, roadname
FROM roads
WHERE roadname = 'Archibald St'
UNION
SELECT FJunc AS juncno, roadname
FROM roads
WHERE roadname = 'Archibald St') AS a
INNER JOIN
(SELECT TJunc AS juncno, roadname
FROM roads
WHERE roadname = 'High Amplitude Avenue'
UNION
SELECT FJunc AS juncno, roadname
FROM roads
WHERE roadname = 'High Amplitude Avenue') AS b
ON a.juncno = b.juncno;
|
Results:
| juncno | a.roadname | b.roadname |
| 8 | Archibald St | High Amplitude Avenue |
The answer is: "Archibald St" and "High Amplitude Avenue" intersects in
junction number 8.
sqlexamples.info
|
