Pgrouting-based Path Planning

Recently contacted pgrouting. Record the learning results.

Using pgrouting for path planning can only import line data, and mutiline will fail. Note This when importing data.

1. Create a database

There are two methods:

1. Create a pgadmin visualization tool

Directly create a database using the data path postgis template database or the postgis template database as the template, so that the created database directly supports space query and space analysis.

2. Create a database using the command line

Create a database

Createdb-u Postgres Routing

Allow the database to support postgis and pgrouting functions and basic tables

Psql-u Postgres-D routing-F postgis. SQL

Psql-u Postgres-D routing-fspatial_ref_sys. SQL

Psql-u Postgres-D routing-frouting_core. SQL

Psql-u Postgres-D routing-frouting_core_wrappers. SQL

Psql-u Postgres-D routing-frouting_topology. SQL

In Windows, you can paste the corresponding SQL file and run the Code directly in the corresponding table.

 

2. Import SHP data to a spatial database

Two methods:

1. visualization tools

(A) Open the postgis Tool

Postgisshapefile import/export manager under the postgis installation directory

A dialog box is displayed.

(B) Set database connection

Click View connection details to set the database connection.

(C) Add SHP data

Add the SHP file to be imported

Click Add file. The file selection dialog box is displayed.

Select the SHP file to be imported. Note that the path must be in English. Otherwise, the import will fail.

(D) encoding settings

Click options. The device encoding format is GBK. Select generate simple geometries instead of multi geometries.

The SHP data imported here must be single-line, otherwise the path calculation cannot be completed.

(E) view imported data

After successfully importing data, you can view the imported data table in PostgreSQL.

2. import data through command lines

First, use the shp2psql.exe program to replace the SHP file with an SQL script,

Format: shp2pgsql path \ SHP data file name new data table name> path \ SQL file name. SQL

Shp2pgsql-s 4326 beijingmodified public. beijingmodified> beijingmodified. SQL

 

SQL file obtained after execution

Psql-u Postgres-D routing-fbeijingmodified. SQL

 

3. Create a network topology

// Add the start point id

Alter table public. Beijing add columnsource integer;

// Add end point id

Alter table public. Beijing add columntarget integer;

// Add the road weight

Alter table public. Beijing add columnlength double precision;

 

// Create a topology layout for the sampledata table, that is, assign values to the source and target fields.

Select pgr_createtopology (public. Beijing, 0.00001, 'geom ', 'gid ');

 

// Create an index for the source and target fields

Create index source_idx onbeijingmodified ("Source ");

Create index target_idx onbeijingmodified ("target ");

 

// Assign a value for length

Update beijingmodified set length = st_length (Geom );

// Or assign values with the length of an existing field. The following shape_length is an existing Length attribute in SHP.

Update beijingmodified set length = shape_length;

 

// Add the reverse_cost field to the beijingmodified table and assign values with the length value

Alter table beijingmodified add columnreverse_cost double precision;

Update beijingmodified set reverse_cost = length;

 

4. Try to query

Query using pgr_dijkstra Algorithm

Select seq, id1 as node, Id2 as edge, costfrom pgr_dijkstra ('

Select GID as ID,

Source: integer,

Target: integer,

Length: Double precision as cost

From beijingmodified ',

30, 60, false, false );

 

// Place the query result in the new table

Select seq, id1 as node, Id2 as edge, cost, Geom into pair stra_res from pgr_dijkstra ('

Select GID as ID,

Source: integer,

Target: integer,

Length: Double precision as cost

From beijingmodified ',

30, 60, false, false) as di

Join beijingmodified PT

On Di. Id2 = pt. GID;

/*

Pgr_dijkstra is defined as pgr_costresult []
Pgr_dijkstra (textsql, integer source, integer target, Boolean directed, Boolean has_rcost );

Whether directed limits the direction. Has _ rcost has unknown function. The returned value is pgr_costresult.

What is pgr_costresult's explanation in workshop?

A set of records to describe a path resultwith cost attribute

A set of records with consumption attributes used to describe the Path results.

The definition is as follows:

Create type pgr_costresult

(

SEQ integer, -- rowsequence

Id1 integer, -- node ID

Id2 integer, -- edge ID (-1 for the last row)

Cost float8 -- cost totraverse from id1 using Id2

);

*/

Export the table as SHP, and then define the Coordinate System In ArcMap to open. The preceding result is shown in.

Pgrouting-based Path Planning