Блог

У меня есть линейный (сегментированный дорожный) набор данных, где каждая дорога имеет уникальный код, но эта дорога может быть сегментирована. Я хочу добавить уникальный код (местоположение) и порядковый номер (на основе начальной цепочки сегмента) в новое поле (segment_id).

Мне это нужно в SQL-версии QGIS, которая разрешает только эти команды-https://sqlite.org/lang.html?

В приведенном ниже примере есть поле, которое я хочу получить в последнем столбце (Segment_ID)

 fid RoadMntnc   Location    Segments    Start_Chainage  Segment_ID
640 Albatross_Cl    3   1   0   3.1
606 Allamanda_St    4   1   0   4.1
620 Barrbal_Dr  25  5   0   25.1
624 Barrbal_Dr  25  5   50  25.2
628 Barrbal_Dr  25  5   130 25.3
1092    Barrbal_Dr  25  5   180 25.4
1093    Barrbal_Dr  25  5   250 25.5
600 Bayil_Dr    27  2   120 27.2
601 Bayil_Dr    27  2   0   27.1
 

Нам нужно будет сгруппировать по местоположению, а затем для каждой группы получить список по возрастанию Start_Chainage, а затем вставить 1-x в значение местоположения, чтобы получить местоположение.Сегмент#

Возможно ли это исключительно в SQL или мне нужно использовать Python?

==== ОБНОВЛЕННЫЙ пример кода, основанный на предложениях QuestionGuyBob

 select ROW_NUMBER () OVER (
    PARTITION BY Location
    ORDER BY Start_Chainage
    ) RowNum, Location, RoadMntnc, Segments,
 CAST(Location as VARCHAR(30))  '.' Cast(RowNum as VARCHAR(30))  AS Segment_ID
    from test_simple_roads
 

Выдает ошибку

 Query preparation error on PRAGMA table_info(_tview): no such column: RowNum
 

Если я изменю номер строки на другое поле, оно не объединится, а добавит два целых числа

Если я изменю его на concat, он будет работать, но я все равно не смогу использовать RowNum или Row_Number, так как получу ту же ошибку (такого столбца нет).

 concat(Location, '.',RowNum)  AS Segment_ID
 

Похоже, вам захочется использовать оконную функцию ROW_NUMBER (). Я просмотрел документацию, и она это подтверждает.

https://www.sqlite.org/windowfunctions.html#built_in_window_functions

Что вы, скорее всего, захотите сделать, так это «ROW_NUMBER() НАД (РАЗДЕЛ ПО ПОРЯДКУ РАСПОЛОЖЕНИЯ ПО FID) КАК RN» в подзапросе. Затем приведите RN к varchar и соедините его с местоположением. Что-то вроде «ПРИВЕДЕНИЕ(местоположение как VARCHAR(30)) ‘.’ ПРИВЕДЕНИЕ(RN КАК VARCHAR(30)) КАК Segment_ID

     SELECT
    *
    ,CAST(Location as VARCHAR(30))  '.' Cast(RowNum as VARCHAR(30))  AS Segment_ID
FROM
    (
    SELECT 
    ROW_NUMBER () OVER (
    PARTITION BY Location
    ORDER BY Start_Chainage
    ) RowNum, Location, RoadMntnc, Segments
 
    from test_simple_roads) AS TEST