Organize the use of PostgreSQL's extended functionality PostGIS and pgrouting

PostGIS installation using PostgreSQL in the bin directory under Stackbuiler

PostGIS

1. Build the Table statement:

create table NODES (ID SERIAL not null, geometry geography (POINTZ, 4326) null); The field geometry represents a point in three-dimensional space, and change POINTZ to POINT for two-dimensional points
create table EDGES (ID SERIAL not null, geometry geography (LINESTRINGZ, 4326) null); field geometry represents the line in three-dimensional space. Similarly, for two-dimensional, change LINESTRINGZ to LINESTRING
2. Insert statement:

insert into nodes (geometry) values (ST_GeographyFromText (‘SRID = 4326; POINT (-110 30 40)’));
insert into edges (geometry) values (ST_GeographyFromText (‘SRID = 4326; LINESTRING (-110 30 40,11 22 33)’));
3. Modify the field type:

alter table public.nodes alter column geometry set data type geography (PointZ, 4326);
4. Query statement:

select ST_AsText (geometry) from nodes;
select ST_AsText (geometry) from edges;
About pgrouting:

1. Installation under Windows:

Download the PGRouting corresponding to the local PostgreSQL version on the official website, my version of PostgreSQL 9.2, this version can use PGRouting corresponding version is 2.0.

After downloading PGRouing, you can see that there are 3 folders (bin, lib, share) and 5 files. There may be changes in the future. Copy these three folders to the PostgreSQL installation directory and merge them with the folder of the same name .

2. Let the database support pgrouting:

CREATE EXTENSION pgrouting;

3.pgrouting needs to add some basic fields to the edges

Columns ‘source’, ‘target’ must be of type int4, ‘cost’ must be of type float8 (other field names can also be used, please pay attention when using built-in function query)

4. Query the shortest path between two nodes:



select * from pgr_dijkstra (‘select id as id, source :: integer, target :: integer, length :: double precision as cost from edges’, 30,60, false, false);
 

Sort out the extended functions of postgresql and the use of postgis and pgrouting