CODEWITHSUNDEEP
sqlsql-server
Brijesh
Brijesh

Reputation: 109

SQL Server create sequence based on matching two columns of different rows

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

Answers (3)

Wyatt Shipman
Wyatt Shipman

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

Daniel Marcus
Daniel Marcus

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

Andomar
Andomar

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

Related Questions