Jake Jake - 5 months ago 23
SQL Question

SQL: Change SQL table with start and end XY

I'm trying to manipulate an SQL table from the database Vertica.

Each row of the table has a start and end XY coordinates (which draws a subpath). Each subpaths when drawn in a map joins together to form a path (denoted by the unique route name). That is why the EndX, End Y is the same as the StartX, StartY for each route.

As the mapping software can only read only X & Y columns only, hence I need to squeeze them into just two columns.

To spare the details (think I did not explain well enough), I have a sample illustration below, on how the table looks like, and what it should become.

enter image description here

Provided a better raw data below for anyone who can help out to do a sample SQL statement. Thank you in advance!

Original Table

road a,1,1.2323,23.1231,1.3242,23.54354
road a,2,1.3242,23.5435,1.3342,24.23533
road b,1,1.4312,25.3432,1.4454,25.2322
road b,2,1.4454,25.2322,1.4634,25.3424
road b,3,1.4634,25.3424,1.5213,26.2132

Final Table

road a,1,1.2323,23.1231
road a,2,1.3242,23.5435
road a,3,1.3342,24.23533
road b,1,1.4312,25.3432
road b,2,1.4454,25.2322
road b,3,1.4634,25.3424
road b,4,1.5213,26.2132

UPDATE: the final output will be used for drawing a route path in Tableau~ Each route is defined by the XY, and the line will follow the Sequence number.


Appreciate that your problem appears to distill down to adding the n + 1 ordered pair for each route. One approach is then to simply UNION together every starting point with a second query which identifies the end point.

SELECT RouteName,
FROM yourTable
    SELECT t1.RouteName,
           t1.Sequence + 1,
    FROM yourTable t1
    LEFT JOIN yourTable t2
        ON t1.EndX = t2.StartX AND
           t1.EndY = t2.StartY AND
           t1.RouteName = t2.RouteName
    WHERE t2.RouteName IS NULL

I didn't order the result set, but the tricky part here is coming up with the query, and hopefully this has you covered.