Quick start-PolarDB(PolarDB)-阿里云帮助中心

This topic helps you quickly understand the basic usage of the Ganos MapMatching engine, including creating the extension, creating road network data, building a topological network graph, and performing trajectory matching.

Basic concepts

Road network：A digital information set that describes the structure and characteristics of a road traffic network. It includes information such as road locations, types, grades, connection relationships, geometric shapes, and traffic rules.
Node：In road network data, a node usually represents an intersection or endpoint of roads. It is a point that connects edges in a road network.
Edge：In the context of road network data, an edge is an abstract concept that connects two nodes in a road network. An edge mainly represents the connection between two locations, without involving the specific physical characteristics of roads. In an undirected graph, edges have no direction. In a directed graph, edges can represent the travel direction. An edge usually carries attribute information, such as length and passability, but its core role is to build the structural framework of the road network.
Segment：A segment is a part of a road with actual physical attributes and geographic location information. It is a continuous section of road in the road network, which may start from one intersection and end at the next intersection, or be a section with the same characteristics, such as lane count and speed limit. Segment data is more detailed than edge data and usually includes road surface material, width, gradient, lane configuration, traffic sign information, right-of-way (such as whether turning or going straight is allowed), and so on. Segments are the basic units for applications such as navigation, traffic analysis, and road maintenance planning.

Case reference

Create the extension

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

CREATE EXTENSION ganos_mapmatching CASCADE;
CREATE EXTENSION ganos_fdw CASCADE;

Note

When you create the ganos_mapmatching extension, the ganos_geometry, ganos_spatialref, and ganos_trajectory extensions are installed at the same time. If you encounter an error such as ERROR: invalid extension name: "ganos_trajectory", contact us for assistance.

To avoid permission issues, we recommend that you install the extensions in the public schema.

CREATE EXTENSION ganos_mapmatching WITH SCHEMA PUBLIC CASCADE;
CREATE EXTENSION ganos_fdw WITH SCHEMA PUBLIC CASCADE;

Example 1: Implement road matching by using your own road network data.

Import the road network data.

CREATE TABLE network (fid bigint,   -- Unique identifier of the road segment
                         source bigint,  -- Identifier of the start node of the road segment
                         target bigint,  -- Identifier of the end node of the road segment
                         cost double precision, -- Road weight value (currently unused)
                         geom public.geometry(LineString,4326) -- Geometry object 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)'));

Build a map matching workspace.
```
SELECT st_createworkspace('mm_ws_test1', 'select fid, source, target, geom from network');
```
The road network structure generated in the workspace is as follows:

Create the trajectory data to be matched.

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)'));

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;

The following result is returned:

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

Example 2: Implement road matching by using OSM data.

Import the OSM data.

-- Import OSM road network data from OSS. Currently, only source files stored in OSS can be imported.
-- accesskeyid and accesskeysecret are the AccessKey pair used to access OSS
-- directory specifies the folder. You can leave it empty if the file is in the root directory.
SELECT st_regforeigntables('OSS://{accesskeyid}:{accesskeysecret}@{endpoint}/{bucket}/{directory}/macau-latest.osm.pbf' , 'osmserver', 'OSMNT');
SELECT st_setcplconfig('OSS_ENDPOINT', '{endpoint}');

-- Extract 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 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;

Construct the road network data.

-- Update the start and end node IDs 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;

Build a map matching workspace.

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

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));

The following result is returned:

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)