Render traffic conditions using platform capabilities

Technical components

• DB: This topic uses RDS (MySQL) as an example. The specific DB depends on the database where the data tables are stored in your project.

  In Cloud Traffic Control Center > Data > DataService Studio > Data Tables, search for the table name to confirm the database where the table is stored.

• GIS Server: TMS, WFS

• Map: GAS (Mapbox 1.x)

Data

The following data tables are involved in rendering real-time traffic conditions:

• Basic road segment information table: dwd_tfc_bas_rdnet_ioiseg_info

• Real-time traffic conditions table: dws_tfc_state_ioi_nd_lastmultispeed_rt

To perform historical playback, the following data tables are also required:

• 7. 30-day historical playback data: adm_tfc_state_ioi_tpdoe_speedindex_d

• Monthly historical playback: adm_tfc_state_ioi_tpdoe_speedindex_m

• Same-day historical playback: adm_tfc_state_ioi_tp_multispeed_rt

The following steps use the dwd_tfc_bas_rdnet_ioiseg_info table as an example to show how to retrieve table data:

1. In DataService Studio > Data Tables, search for the table name.

2. Click the table name to go to its details page. On this page, you can view details such as the table's fields, data lineage, and rules.

Services

• Base map

  Projects usually purchase base map engine products from vendors such as Tumeng or Qianxun. You can contact your project manager to obtain the service. This topic uses an open source map engine as an example to explain how to use it for traffic condition rendering.

  • Service format: WMTS

  • Service source: https://map.geoq.cn/ArcGIS/rest/services/ChinaOnlineStreetGray/MapServer/tile/{z}/{y}/{x} (Replace {z}, {y}, and {x} with your actual parameters. z represents the level, and x and y represent the row and column.)

• Basic road segment information table

  • Service format: TMS (vector tiles)

  • Service source: dwd_tfc_bas_rdnet_ioiseg_info

• Real-time traffic conditions

  • Service format: TMS (vector tiles)

  • Service source: dws_tfc_state_ioi_nd_lastmultispeed_rt JOIN dwd_tfc_bas_rdnet_ioiseg_info

Rendering

The frontend rendering engine is GAS, which is developed based on Mapbox 1.x. In traffic condition scenarios, GAS can be treated as Mapbox. It primarily uses the layer capabilities of Mapbox. For more information, see the official Mapbox documentation.

Step 1: Prepare the data source

Add the geometry spatial field to the dwd_tfc_bas_rdnet_ioiseg_info basic road segment information table. The code is as follows:

1. Add a new field: ALTER TABLE `dwd_310000`.`dwd_tfc_bas_rdnet_ioiseg_info` ADD COLUMN `geometry` LINESTRING NULL COMMENT 'Longitude and latitude string in geometry format'

2. Transform and insert the spatial field: UPDATE `dwd_310000`.`dwd_tfc_bas_rdnet_ioiseg_info` SET `geometry` = GeomFromText(CONCAT('LINESTRING(', REPLACE(REPLACE(lnglat_seq, ',', ' '), ';', ','), ')'), 4326)

3. Set the field to NOT NULL: ALTER TABLE `dwd_310000`.`dwd_tfc_bas_rdnet_ioiseg_info` modify COLUMN `geometry` LINESTRING NOT NULL

4. Create an index: CREATE SPATIAL INDEX i_geometry ON `dwd_310000`.`dwd_tfc_bas_rdnet_ioiseg_info`(geometry);

Step 2: Insert the data sources into GeoServer

• Query the underlying road network structure

  SELECT t.road_level,t.geometry AS the_geom
  FROM  dwd_310000.dwd_tfc_bas_rdnet_ioiseg_info  t

• Query real-time traffic conditions

  SELECT
      t.road_level,
      p.jam_state_drawing,
      t.geometry AS the_geom 
  FROM
      dws_310000.dws_tfc_state_ioi_nd_lastmultispeed_rt p
  RIGHT JOIN dwd_310000.dwd_tfc_bas_rdnet_ioiseg_info t 
  ON p.ioi_id = t.start_ioi_id 
  WHERE
      p.dt = curdate() 
  AND p.jam_state_drawing IN ( 2, 3, 4 ) 
  AND p.tp = '1mi'