1

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

Improve this question
4
  • Are you using SQL Server or Oracle? Commented May 14, 2018 at 19:56
  • currently, I am testing it in SQL server. Commented May 14, 2018 at 20:00
  • Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged Commented May 14, 2018 at 20:25
  • Thanks for the tip Marc will look into it Commented May 14, 2018 at 20:30

3 Answers 3

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

OP thought he could use an SQL-89 join to generate sequence numbers. What is the function of the join with row_number?
This assumes that your starting latitude (SLAT) field is always going to go up.
1

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
Improve this answer

4 Comments

Partition by will give the sequence based on the value of the number, but I want to generate a sequence if Start lat-long matches with End lat-long of another row.
Partition should allow you to accomplish this as long as you order correctly
Thanks, Daniel, per your suggestion I modified the query, and I believe I got the result I was looking for, still need to validate the result set, but I am close to the result 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, row_number() over(partition by t1.ROAD_NAME, T1.DIRECTION order by T1.SLAT, T2.ELAT) sequence FROM myTable T1, myTable T2 WHERE T1.ELAT = T2.SLAT AND T1.ELON = T2.SLON AND T1.DIRECTION = T2.DIRECTION
Great glad to hear it!
1

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*/
Improve this answer

1 Comment

Was exactly the query I was looking for

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.