Skip to content

Export Query Results

Jump to bottom
Drew Dara-Abrams edited this page Nov 1, 2017 · 13 revisions

Analyst UI can run simple queries from a web browser. To do more complex work with Open Traffic data, you may want to export your query results to a data file for further processing using desktop software, including Microsoft Excel, Esri ArcGIS, QGIS, RStudio, and equivalents.

File Formats

Analyst UI can export to two files formats:

  • GeoJSON: a map data format, which can be read into GIS software
  • CSV: a tabular data format, which can be read into spreadsheet software

GeoJSON Contents

Both route and region queries can be exported to a GeoJSON file, for sharing and use in GIS software.

The GeoJSON file includes a FeatureCollection, with attached properties to describe the route or region query. Here is an example of a GeoJSON export for a route query, with annotations:

{
  "type": "FeatureCollection",
  "features": [
    ... // Each segment is listed here. They are removed from this example to save space.
  ], 
  "properties": {
    "baselineTime": 1048, // Route time given speed limits; in seconds.
    "trafficRouteTime": 1920, // Route time given measured traffic; in seconds.
    "analysisMode": "ROUTE", // The type of analysis being exported: route or region.
    "analysisName": "Route A Weekday Morning Rush", // If a name is set in the UI, it's listed here. Useful to save notes.
    "date": {
      "year": 2017, // Year of data included in this query.
      "week": 33, // Week of that year included in this query; starts with 1 as the first week of the year.
      "days": [ // Days of that week selected.
        "monday",
        "tuesday",
        "wednesday",
        "thursday",
        "friday"
      ],
      "hours": [ // Hours of those days selected; in 24-hour time.
        6,
        7,
        8,
        9
      ]
    }
  }
}

The geometry for each segment in the query will be included as a GeoJSON Feature, enabling GIS software to draw a map of the roadways. Each Feature has properties that include the segment's OSMLR ID and mean speeds at selected days/hours. Here is an example of one of the segment Features that is included in the above route query:

    {
      "type": "Feature",
      "geometry": { // Geometry that can be used to draw this segment on a map using GIS software.
        "type": "LineString",
        "coordinates": [
          [
            121.025955,
            14.618477
          ],
          [
            121.026504,
            14.617617
          ]
        ]
      },
      "properties": {
        "id": {
          "ends_segment": false,
          "begin_percent": 0,
          "end_percent": 1,
          "segment_id": 156833717097,
          "starts_segment": false
        },
        "osmlr_id": 156833717097, // The ID for this OSMLR segment. Can be used to match with other data.
        "speed": 25.933333333333334, // Mean speed on this segment across all selected days/hours; in KPH.
        "speedByHour": [ // Mean speed on this segment at each selected days/hours.
          {
            "dayOfWeek": 1, // The day of week this particular mean speed represents; 1 = Monday
            "hourOfDay": 7, // The particular hour of that day with this mean speed; 24-hour time.
            "speedThisHour": 24 // Mean speed during this particular hour; KPH.
          },
          {
            "dayOfWeek": 1,
            "hourOfDay": 8,
            "speedThisHour": 19
          },
          ... // More entries removed to save space in this example.
        ]
      }
    },

CSV Contents

Both route and region queries can be exported to a CSV file, for sharing and use in spreadsheet and statistical analysis software. CSV files do not include segment geometries for drawing on maps; rather, the entries in the CSV file include OSMLR IDs for each segment. Those IDs will match the IDs used in GeoJSON exports, so it's possible to work with both GeoJSON and CSV exports together.

CSV is a text format that represents a table. Each row is on its own line, and each column is separated by a comma character. It can easily be imported into Excel or other spreadsheet software. If necessary, specify , as the delimiter character and \n ("new line") as the row break character.

Here is an example of a CSV export of a route query:

row_type baseline_route_time traffic_route_time osmlr_id average_speed_on_monday_at_hour_7 average_speed_on_monday_at_hour_8
route_summary 1048 1839 -- -- --
segment -- 36344274850 -- --
segment -- 978452062114 -- --
segment -- 156833717097 28 32

When exporting a route query as CSV, there is an initial row with row_type = route_summary. This includes baseline_route_time, which is the route time given speed limits; in seconds, and traffic_route_time, which is route time given measured traffic; in seconds.

Each row with row_type = segment describes a roadway segment along the route or within the region. osmlr_id provides the OSMLR ID for the segment, for cross-referencing across CSV and GeoJSON exports or with another source of data associated with OSMLR segments. Remaining columns give average speeds (in KPH) at the query's selected days/hours. For example, average_speed_on_monday_at_hour_7 gives the average speed on Monday at 7 a.m. Hours are in 24-hour notation.

Clone this wiki locally