Reputation: 109
I want to create a network by matching the end of one line with the start of another line in SQL and create a sequence based on matching points.
For example, I have data as below:
ID Route Direction SLAT SLON ELAT ELON
----------------------------------------------------------------------
1 RT-3 E 40.1 -74.1 40.2 -74.2
2 RT-3 E 40.2 -74.2 40.3 -74.3
3 RT-3 E 40.6 -74.6 40.7 -74.7
4 RT-3 E 40.3 -74.3 40.4 -74.4
5 RT-3 E 40.5 -74.5 40.6 -74.6
6 RT-3 E 40.4 -74.4 40.5 -74.5
7 RT-9 E 40.11 -74.11 40.21 -74.21
8 RT-9 E 40.31 -74.31 40.41 -74.41
9 RT-9 E 40.61 -74.61 40.71 -74.71
10 RT-9 E 40.51 -74.51 40.61 -74.61
11 RT-9 E 40.41 -74.41 40.51 -74.51
12 RT-9 E 40.21 -74.21 40.31 -74.31
And the required result is
ID Route Direction SLAT SLON ELAT ELON Seq
-------------------------------------------------------------------------------
1 RT-3 E 40.1 -74.1 40.2 -74.2 1
2 RT-3 E 40.3 -74.3 40.4 -74.4 3
3 RT-3 E 40.2 -74.2 40.3 -74.3 2
4 RT-3 E 40.6 -74.6 40.7 -74.7 6
5 RT-3 E 40.5 -74.5 40.6 -74.6 5
6 RT-3 E 40.4 -74.4 40.5 -74.5 4
7 RT-9 E 40.11 -74.11 40.21 -74.21 1
8 RT-9 E 40.21 -74.21 40.31 -74.31 2
9 RT-9 E 40.61 -74.61 40.71 -74.71 6
10 RT-9 E 40.51 -74.51 40.61 -74.61 5
11 RT-9 E 40.41 -74.41 40.51 -74.51 4
12 RT-9 E 40.31 -74.31 40.41 -74.41 3
I came up with this query but not sure how to get the sequence
SELECT
T1.ROAD_NAME, T1.DIRECTION, T1.SLAT, T1.SLON, T1.ELAT, T1.ELON,
T2.SLAT AS SLAT_2, T2.SLON AS SLON_2, T2.ELAT AS ELAT_2, T2.ELON AS ELON_2
FROM
myTable T1, myTable T2
WHERE
T1.ELAT = T2.SLAT
AND T1.ELON = T2.SLON
AND T1.DIRECTION = T2.DIRECTION
Thanks in advance.
Brijesh
Upvotes: 1
Views: 915
Reputation: 1789
You could use a nested CTE like a hierarchy like this:
WITH cte1
AS (
/*this first query gets the starting points*/
SELECT m1.*
,1 rlevel
FROM [myTable] m1
LEFT JOIN [myTable] m2 ON m1.slat = m2.elat
AND m1.slon = m2.elon
AND m1.ROUTE = m2.ROUTE
WHERE m2.id IS NULL
UNION ALL
/*this query returns the chain of rows following the starting rows
as well as subsequent rows in the chain*/
SELECT m.*
,rlevel + 1 rlevel
FROM mytable m
INNER JOIN cte1 c1 ON m.slat = c1.elat
AND m.slon = c1.elon
AND m.ROUTE = C1.ROUTE
)
SELECT *
FROM cte1
ORDER BY id
/*you can order by rlevel to see the chaining of rows*/
Upvotes: 0
Reputation: 2696
Use partition and order by
SELECT T1.ROAD_NAME, T1.DIRECTION, T1.SLAT, T1.SLON, T1.ELAT, T1.ELON
row_number() over(partition by t1.ROAD_NAME order by T1.SLAT) sequence
FROM myTable t1
Upvotes: 1
Reputation: 238296
Point along a line always progress in one direction, so you could sort by slat, slon
. This would even work for vertical or horizontal lines:
SELECT ROAD_NAME, DIRECTION, SLAT, SLON, ELAT, ELON,
row_number() over (partition by road_name, direction order by slat, slon) seq
FROM myTable
Upvotes: 1