RFC: Transportation structured columns

[brad-richardson]

As members have started consuming the transportation data, the segment road column has emerged as a consistent pain point. road is currently a string column containing structured JSON. Although JSON has provided flexibility as we develop the schema, we think there might be a better way.

We would love to get your feedback and field your questions on these proposed changes! See enumerated options we're considering at the end.

Goals

1. Transportation data is easier to understand and consume
2. Address JSON querying difficulties
3. Consistent feature structure, aligned with other Overture themes
4. Remove complexities of nested JSON field validation
5. Ensure backward compatibility and reduce schema churn for v1.0.0
6. Increased confidence when merging multiple data sources, potentially with extended schemas

Proposed Changes

• Structure all fields with strict types
• Split road into separate, flattened columns

Structured Fields

Currently, the segment road column is a string used to store a JSON object. Here is the list of subproperties for this JSON:

1. restrictions.speed_limits
2. restrictions.access
3. restrictions.prohibited_transitions
4. surface
5. flags
6. level
7. width
8. lanes

Storing this column as JSON introduces consumption challenges, including queries becoming harder to write and understand. Here’s an example query to answer "is access allowed for a given travel mode?":

JSON_ARRAY_CONTAINS(
  JSON_EXTRACT(
    TRY(JSON_PARSE(road),
    '$.restrictions.access[?(@.access_type==''allowed'')].when.mode[*]'
  ),
  'bicycle'
)

If we have structured columns, the query could instead look like:

ANY_MATCH(
  restrictions.access,
  access ->
   access.access_type = 'allowed'
   AND ANY_MATCH(access.when.mode, mode -> mode = 'bicycle')
)

An additional challenge with JSON vs structured fields is that JSON must be parsed in its entirety and held in memory when used for filtering or joining. This will be less performant than having a strict column structure, which could be further improved by flattening the structure a bit, leading to our next topic…

Property Flattening

Some subproperties of the road are complex and deeply nested on their own. Here is what road.restrictions.speed_limits (already three layers of nesting) might look like in a create table statement:

CREATE EXTERNAL TABLE overture_release (
...
speed_limits array<struct<
 min_speed: int,
 max_speed: int,
 between: array<double>,
 when: struct<
   during: string,
   heading: string,
   mode: array<string>,
   recognized: array<string>,
   vehicle: struct<
     axle_count:struct<
       is_more_than: struct<value: int, unit: string>,
       is_at_least: struct<value: int, unit: string>,
       is_equal_to: struct<value: int, unit: string>,
       is_at_most: struct<value: int, unit: string>,
       is_less_than: struct<value: int, unit: string>
     >,
     height:struct<
       is_more_than: struct<value: float, unit: string>,
       is_at_least: struct<value: float, unit: string>,
       is_equal_to: struct<value: float, unit: string>,
       is_at_most: struct<value: float, unit: string>,
       is_less_than: struct<value: float, unit: string>
     >,
     length: struct<
       is_more_than: struct<value: float, unit: string>,
       is_at_least: struct<value: float, unit: string>,
       is_equal_to: struct<value: float, unit: string>,
       is_at_most: struct<value: float, unit: string>,
       is_less_than: struct<value: float, unit: string>
     >,
     weight: struct<
       is_more_than: struct<value: float, unit: string>,
       is_at_least: struct<value: float, unit: string>,
       is_equal_to: struct<value: float, unit: string>,
       is_at_most: struct<value: float, unit: string>,
       is_less_than: struct<value: float, unit: string>
     >,
     width: struct<
       is_more_than: struct<value: float, unit: string>,
       is_at_least: struct<value: float, unit: string>,
       is_equal_to: struct<value: float, unit: string>,
       is_at_most: struct<value: float, unit: string>,
       is_less_than: struct<value: float, unit: string>
     >
   >
 >
>>
...

We can remove a couple layers by flattening vehicle:

speed_limits array<struct<
 min_speed: int,
 max_speed: int,
 between: array<double>,
 when: struct<
   during: string,
   heading: string,
   mode: array<string>,
   recognized: array<string>,
   vehicle: array<struct<
     dimension: string, # axle_count, height, length, width, weight
     comparison: string, # greater_than, greater_than_equal, equal, less_than_equal, less_than
     value: string,
     unit: string
  >>
 >
>>

We could further flatten by removing the when structure, at some expense to readability (speed limit when heading backward vs speed limit heading backward):

speed_limits array<struct<
 min_speed: int,
 max_speed: int,
 between: array<double>,
 during: string,
 heading: string,
 mode: array<string>,
 recognized: array<string>,
 vehicle: array<struct<
   dimension: string, # axle_count, height, length, width, weight
   comparison: string, # greater_than, greater_than_equal, equal, less_than_equal, less_than
   value: string,
   unit: string
 >>
>>

Options

Here is a list of options we're considering. The transportation task force is leaning towards 3) or 4), and we would like to take action on this soon, possibly for the June release.

1. Structure road property in-place
2. Split road subproperties as top-level properties with existing schema
3. Split road subproperties as top-level properties with partially flattened schema
4. Split road subproperties as top-level properties with aggressively flattened schema

[brad-richardson]

#203 is the current PR to rework segment with structured columns

[PaudelXA]

Thanks Transportation team for looking into it.

As there will still be some Un-flattened schema, an example snippet of the code that flattens would be useful as a reference to adapt it to the needs.

[brad-richardson]

We plan to provide some usage examples soon

[brad-richardson]

Closing as completed - see #203