路径规划应用 pgRouting 实践与开放地图导入 - Openstreetmap PBF

背景

pgRouting Project

pgRouting extends the PostGIS / PostgreSQL geospatial database to provide geospatial routing functionality.

Advantages of the database routing approach are:

Data and attributes can be modified by many clients, like QGIS and uDig through JDBC, ODBC, or directly using Pl/pgSQL. The clients can either be PCs or mobile devices.

Data changes can be reflected instantaneously through the routing engine. There is no need for precalculation.

The “cost” parameter can be dynamically calculated through SQL and its value can come from multiple fields or tables.

Core Features

pgRouting provides functions for:

All Pairs Shortest Path, Johnson’s Algorithm [1]

All Pairs Shortest Path, Floyd-Warshall Algorithm [1]

Shortest Path A*

Bi-directional Dijkstra Shortest Path [1]

Bi-directional A* Shortest Path [1]

Shortest Path Dijkstra

Driving Distance

K-Shortest Path, Multiple Alternative Paths [1]

K-Dijkstra, One to Many Shortest Path [1]

Traveling Sales Person

Turn Restriction Shortest Path (TRSP) [1]

Shortest Path Shooting Star [2]

Learn more about using pgRouting in your application by reading the documentation.

pgRouting is Open Source

pgRouting is available under the GPLv2 license and is supported by a growing community of individuals, businesses and organizations.

PostgreSQL还有一个FDW插件osm_fdw，可以在数据库读取PBF文件的内容当成表来使用。

How to use  
  
Create extension at first:  
  
CREATE EXTENSION osm_fdw;  
  
To access foreign data, you need to create a foreign server object:  
  
CREATE SERVER osm_fdw_server FOREIGN DATA WRAPPER osm_fdw;  
  
Next you should create foreign table. There are two ways: CREATE FOREIGN TABLE query and create_osm_table function. I strongly recommend to use second method. The function create_osm_table(text, text, text) is provided together with the extension. It requires 3 parameters: a name of the table, a name of the foreign server object and a path to *.osm.pbf file.  
  
SELECT create_osm_table('table_name', 'osm_fdw_server', '/path_to_file/file.osm.pbf');  
  
FDW start to read the file with every query. I would recommend to create materialized view to fast data access.  
  
CREATE MATERIALIZED VIEW osm_data AS SELECT * FROM osm_foreign_table WITH DATA;  
  
Table structure  
  
Table should have this structure:  
  
CREATE FOREIGN TABLE table_name (  
    id bigint,  
    type text,  
    lat double precision,  
    lon double precision,  
    tags jsonb,  
    refs bigint[],  
    members jsonb,  
  
    version int,  
    modified timestamp,  
    changeset bigint,  
    user_id int,  
    username text,  
    visible boolean  
)  
SERVER osm_fdw_server  
OPTIONS (  
    filename '/path_to_file/file.osm.pbf'  
);  
Fields can have other names, but position and types must be as in this example.  
  
Column types  
  
The FDW can read 3 openstreetmap types: (NODE), (WAY) and (RELATION).  
  
* `id` - OSM object id  
* `type` - type of the object (Values: NODE, WAY, RELATION)  
* `lat` - latitude (not empty only for NODE)  
* `lon` - longitude (not empty only for NODE type)  
* `tags` - json object with OSM tags (json for postgres 9.3 and jsonb for 9.4)  
* `refs` - array on node ids (not empty only for WAY)  
* `members` - array of objects with relation members (not empty only for RELATION; json for postgres 9.3 and jsonb for 9.4)  
* `version` - OSM version  
* `modified` - OSM last change date  
* `changeset` - OSM changeset  
* `user_id` - id of the OSM user  
* `username` - name of the OSM user  
* `visible` - shows if object is visible  

参考

https://github.com/vpikulik/postgres_osm_pbf_fdw/blob/master/doc/osm_fdw.md

https://github.com/pgRouting

https://www.opentreemap.org/

http://pgrouting.org/

digoal’s 大量PostgreSQL文章入口