PolarDB:Quick start

Terms

• Road network: a comprehensive and organized digital representation of the structure and characteristics of a road network. The key elements of a road network include the locations, types, classifications, connections, geometric shapes, and traffic regulations of the roads.

• Node: a point at which roads intersect or end in a road network. A node is a connecting point between edges.

• Edge: an abstract concept that connects two nodes in a road network. Edges are mainly used to represent the connections between two locations, and do not involve specific road physical characteristics. In an undirected graph, edges have no direction. In a directed graph, edges can represent the travel direction. Edges carry specific attribute information, such as length and passability, but their central role is to build the structural framework of a road network.

• Segment: a section of a road that has physical attributes and geographic location information. A segment is a continuous section of a road in a road network. It may start at one intersection and end at the next intersection, or a section of a road that has the same characteristics, such as the number of lanes and the speed limit. Segments provide a more detailed view compared with edges. The segment data may include specifics such as road surface material, width, slope, lane configuration, traffic signage, and traffic rules (such as whether turns or straight travel are allowed) Segments are the basic units in scenarios such as navigation, transportation analysis, and road maintenance planning.

Examples

Create the ganos_mapmatching extension

To use the MapMatching function, you must create the ganos_mapmatching extension in the database.

CREATE EXTENSION ganos_mapmatching CASCADE;
CREATE EXTENSION ganos_fdw CASCADE;

Example 1: Use your own road network data to perform road matching

1. Import road network data.

   CREATE TABLE network (fid bigint, -- The unique identifier of the segment.
                            source bigint, -- The start node identifier of the road segment.
                            target bigint, -- The end node identifier of the road segment.
                            cost double precision, - The road weight value (not currently used).
                            geom public.geometry(LineString,4326) -- The geometric objects of the road segment.
   );
   
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(1, 1, 2,1, st_geomfromtext('LINESTRING(2 1,2 0)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(2, 2, 1,1, st_geomfromtext('LINESTRING(2 0,2 1)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(3, 3, 1,1, st_geomfromtext('LINESTRING(3 1,2 1)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(4, 4, 3,1, st_geomfromtext('LINESTRING(4 1,3 1)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(5, 1, 5,1, st_geomfromtext('LINESTRING(2 1,2 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(6, 5, 1,1, st_geomfromtext('LINESTRING(2 2,2 1)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(7, 3, 6,1, st_geomfromtext('LINESTRING(3 1,3 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(8, 7, 8,1, st_geomfromtext('LINESTRING(0 2,1 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(9, 8, 7,1, st_geomfromtext('LINESTRING(1 2,0 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(10,5, 8,3, st_geomfromtext('LINESTRING(2 2,1 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(11,8, 5,3, st_geomfromtext('LINESTRING(1 2,2 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(12,6, 5,1, st_geomfromtext('LINESTRING(3 2,2 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(13,5, 6,1, st_geomfromtext('LINESTRING(2 2,3 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(14,6, 9,1, st_geomfromtext('LINESTRING(3 2,4 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(15,9, 6,1, st_geomfromtext('LINESTRING(4 2,3 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(16,10,5,1, st_geomfromtext('LINESTRING(2 3,2 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(17,5, 10,1, st_geomfromtext('LINESTRING(2 2,2 3)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(18,6, 11,1, st_geomfromtext('LINESTRING(3 2,3 3)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(19,10,11,1, st_geomfromtext('LINESTRING(2 3,3 3)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(20,11,12,1, st_geomfromtext('LINESTRING(3 3,4 3)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(21,13,10,1, st_geomfromtext('LINESTRING(2 4,2 3)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(22,10,13,1, st_geomfromtext('LINESTRING(2 3,2 4)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(23,9, 12,1, st_geomfromtext('LINESTRING(4 2,4 3)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(24,12,9,1, st_geomfromtext('LINESTRING(4 3,4 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(25,9, 4,1, st_geomfromtext('LINESTRING(4 2,4 1)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(26,4, 9,1, st_geomfromtext('LINESTRING(4 1,4 2)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(27,14,15,1, st_geomfromtext('LINESTRING(0.5 3.5,2 3.5)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(28,15,14,1, st_geomfromtext('LINESTRING(2 3.5,0.5 3.5)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(29,16,17,1, st_geomfromtext('LINESTRING(3.5 4,3.5 2.3)'));
   INSERT INTO network(fid, source, target, cost, geom)
   VALUES(30,17,16,1, st_geomfromtext('LINESTRING(3.5 2.3,3.5 4)'));

   

2. Build a map matching workspace.

   SELECT st_createworkspace('mm_ws_test1', 'select fid, source, target, geom from network');

   The following road network topology is generated in the workspace:

   

3. Create the trajectory data that you want to match.

   CREATE TABLE traj(id int, geom geometry(linestring, 4326));
   INSERT INTO traj(id, geom)
   VALUES(1,st_geomfromtext('LINESTRING(4.15 1.6,3.47 0.92,2.4 0.92,2.14 1.53,2.14 2.57,2.49 2.98)'));

   

4. Perform road matching.

   SELECT st_astext(st_mapmatching('mm_ws_test1', geom, '{"algorithm":"stmatch","k":4,"r":0.25,"e":0.5}'::text)) AS geom
   FROM traj;

   Sample result:

                        geom                     
   ----------------------------------------------
    LINESTRING(4 1.6,4 1,3 1,2 1,2 2,2 3,2.49 3)
   (1 row)

   

Example 2: Use OpenStreetMap (OSM) data to match roads

1. Import OSM data.

   -- Import OSM road network data from Object Storage Service (OSS). Currently, you can import only source files from OSS.
   -- accesskeyid and accesskeysecret are the AccessKey pair used to access OSS.
   -- directory is the folder. You do not need to specify a root directory.
   SELECT st_regforeigntables('OSS://{accesskeyid}:{accesskeysecret}@{endpoint}/{bucket}/{directory}/macau-latest.osm.pbf' , 'osmserver', 'OSMNT');
   SELECT st_setcplconfig('OSS_ENDPOINT', '{endpoint}');
   
   -- Extract the road network node data.
   CREATE TABLE macau_network_nodes
   (
       id     BIGSERIAL,
       fid    BIGINT,
       eout   INTEGER,
       lon    DOUBLE PRECISION ,
       lat    DOUBLE PRECISION,
       cnt    INTEGER,
       chk    INTEGER,
       ein    INTEGER,
       name   text,
       tags   text,
       point  GEOMETRY(point, 4326)
   );
   
   INSERT INTO macau_network_nodes(fid, lon, lat, name, tags, point)
   SELECT osm_id::bigint, ST_X(geom), ST_Y(geom), name, to_json(other_tags)::jsonb, geom
   FROM points;
   
   -- Extract the road network edge data.
   CREATE TABLE macau_network_ways
   (
       gid               BIGSERIAL,
       fid            BIGINT,
       length            DOUBLE PRECISION,
       length_m          DOUBLE PRECISION,
       p                 TEXT,
       source            BIGINT,
       target            BIGINT,
       source_osm        BIGINT,
       target_osm        BIGINT,
       cost              DOUBLE PRECISION,
       reverse_cost      DOUBLE PRECISION DEFAULT -1,
       cost_s            DOUBLE PRECISION,
       reverse_cost_s    DOUBLE PRECISION DEFAULT -1,
       rule              TEXT,
       one_way           INT,
       oneway            TEXT,
       x1                DOUBLE PRECISION,
       y1                DOUBLE PRECISION,
       x2                DOUBLE PRECISION,
       y2                DOUBLE PRECISION,
       maxspeed_forward  DOUBLE PRECISION,
       maxspeed_backward DOUBLE PRECISION DEFAULT -1,
       priority          DOUBLE PRECISION DEFAULT 1,
       name              TEXT,
       tags              JSONB,
       linestring        GEOMETRY(LINESTRING, 4326)
   );
   
   INSERT INTO macau_network_ways(fid, source_osm, target_osm, name, tags, linestring)
   SELECT osm_id::bigint, source_osm, target_osm, name, to_json(other_tags)::jsonb, geom
   FROM lines;

2. Construct the road network data.

   -- Update the IDs of the start and end nodes of the road network.
   UPDATE macau_network_ways
   SET source = macau_network_nodes.id
   FROM macau_network_nodes
   WHERE macau_network_ways.source_osm = macau_network_nodes.fid;
   
   UPDATE macau_network_ways
   SET target = macau_network_nodes.id
   FROM macau_network_nodes
   WHERE macau_network_ways.target_osm = macau_network_nodes.fid;

   

3. Build a map matching workspace.

   SELECT st_createworkspace('mm_ws_test2', 'select fid, source, target, linestring from macau_network_ways');

4. Perform road matching.

   SELECT st_astext(st_mapmatching('mm_ws_test2', 'LINESTRING(113.5492 22.1881,113.5493 22.1881,113.5504 22.1885,113.5505 22.1886,113.5502 22.189,113.5501 22.1891,113.5491 22.1887,113.5489 22.1887)', '{"algorithm":"stmatch","k":4,"r":0.25,"e":0.5,"tolerance":0.5,"vmax":1.0,"factor":0.5}'::text));

   Sample result:

   LINESTRING(113.5492633 22.1880942,113.5493491 22.1881266,113.5504129 22.1885289,113.5505378 22.1885762,113.5502504 22.1892274,113.5505378 22.1885762,113.5502504 22.1892274,113.5501282 22.1891812,113.5490843 22.1887864,113.5489761 22.1887455)