Pprecipitation environment setup and OSM. PBF data processing

Word of warning: Don’t bother to use the Docker environment directly

docker pull pgrouting/pgrouting
Copy the code

1. Install required software

PostgreSQL(Version :11.12 Windows) download address
PostGis(version: PG11/3.1.0 Windows) download address

2. Create the database and load the extension

Using the PGrouting feature requires the following two extension features,PostGis: Pacrylate has been included in PG11/3.1.0 Windows

Use navicat or postgresQL pgAdmin to execute the following SQL statement

CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
Copy the code

To check whether the plug-in has loaded successfully, execute the following SQL to obtain postGIS and Pacrylate versions

SELECT postgis_full_version(),pgr_version();
Copy the code

3. Prepare data

Downloaded by Geofabrik in. Osm. PBF format

Currently, the vector data of China -latest.osm. PBF is used

Other data sources that can split OpenstreetMap by city
Download the source data to include all layers, if necessary to extract the data

Osmium-tool Github is required

After pulling the source code, execute the following statement after compiling in Linux according to the official document

./osmium tags-filter /home/cq/gis/pbf/china-latest.osm.pbf  w/highway=motorway w/highway=trunk w/highway=primary w/highway=motorway_link w/highway=trunk_link w/highway=primary_link -o china-osm2pgrouting.osm.pbf
Copy the code

Road network data with tags for motorway, Trunk,primary,motorway_link, Trunk_link and primary_link were extracted successfully

Description of highway Tag, address

Osm. PBF data transferred to OSM (PMT. PBF format data is not supported)

Osmconvert currently uses the Windows support large file version download address

Convert command
```
osmconvert640.8.8p.exe --out-osm china-osm2pgrouting.osm.pbf>china-pgrouting.osm
Copy the code
```

Install osm2pgrouting making

After pulling the source code, run the following statement to import data after compiling it successfully in Linux according to the official document

Osm2pacrylate -F./build/ China-pacrylate. Osm -h 192.168.2.177 -P 5432 -d china_pacrylate -U Postgres -w root -c ./mapconfig.xml --clean
#Mapconfig.xml is in the osm2pgoring directory
#-f OsM file path
#-h postgresql IP
#-p postgresql port
#-d PostgresQL database name
#-u PostgresQL user name
#- W posgtresql password
#-c osm2pacrylate configuration file path
#--clean Deletes the original data in the database
Copy the code

4. Common queries

Osm data uses the SRID=4326 coordinate system

Query the nearest vertex position of any coordinates

SELECT * FROM ways_vertices_pgr
WHERE
	ST_DWithin (
		the_geom,
		'SRID=4326; POINT (116.432583 39.910729) '.1000
	)
ORDER BY
	ST_Distance (
		the_geom,
		'SRID=4326; POINT (116.432583 39.910729) '
	)
LIMIT 1;
Copy the code

Calculate the result between two vertices using Dijkstra algorithm

select * from pgr_dijkstra (
		'SELECT gid AS id,source, target,cost, reverse_cost FROM ways'.153567.927741,
		directed := FALSEDirected graph computations are often unavailable due to incomplete road network dataCopy the code

Dijkstra’s calculation results are presented in the form of GeoJson

SELECT
	ST_AsGeoJSON (ST_UNION(b.the_geom)) AS geojson
FROM
 select * from pgr_dijkstra (
		'SELECT gid AS id,source, target,cost, reverse_cost FROM ways'.153567.927741,
		directed := FALSE
	) A,
	ways b
WHERE
	A .edge = b.gid;
Copy the code

Use the A* algorithm

SELECT * FROM pgr_astar(
    'SELECT gid as id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM ways'.153567.927741,	
	directed := FALSE);
Copy the code

Pprecipitation environment setup and OSM. PBF data processing