2、网络建构与分析

在http://overpass-turbo.eu/ 中下载北京或任意城市的OSM数据，将其中的roads数据导入到PostgreSQL中去。注意要将multilinestring转换为linestring。
注意：如果使用DB Manager导入数据，要勾选上“Create single-part geometries instead of multi-part”。

2.1 道路拓扑网络建构

• 向bjraods中添加必要字段

ALTER TABLE shroads ADD COLUMN source integer;
ALTER TABLE shroads ADD COLUMN target integer;
CREATE INDEX shroads_source_idx ON shroads (source);
CREATE INDEX shroads_target_idx ON shroads (target);

• 修改geometry字段名称

ALTER TABLE shroads RENAME COLUMN geom TO the_geom;

• 切分道路网，生成shroads_noded

SELECT pgr_nodeNetwork('shroads', 0.00001);

• 构建道路网络shroads_noded_vertices_pgr

SELECT pgr_createTopology('shroads_noded', 0.00001);

• 删除无效数据

delete from shroads_noded where source is NULL  and target is NULL ;

• 向shroads_noded添加字段并更新数据

ALTER TABLE shroads_noded
 ADD COLUMN name VARCHAR,
 ADD COLUMN type VARCHAR;

UPDATE shroads_noded AS new
 SET name=old.name 
FROM shroads as old
WHERE new.old_id=old.id;

• 计算道路网距离（权重）

ALTER TABLE shroads_noded ADD distance FLOAT8;
UPDATE shroads_noded SET distance = ST_Length(ST_Transform(the_geom, 4326)::geography) / 1000;

2.2 最短路径分析

• Dijkstra最短路径分析

SELECT * FROM pgr_dijkstra('SELECT id,source,target,distance as cost FROM shroads_noded',26,59,false);

以上将生成一组节点和路径标，但没有geometry。

下面是从3927——4036——6698的查询：

SELECT * FROM pgr_dijkstra('SELECT id,source,target,distance as cost FROM shroads_noded',ARRAY[3927, 4036],6698,false);

以上是将pgr_dijkstra查询进行包裹后产生geometry的效果

SELECT
     min(r.seq) AS seq,
     e.old_id AS id,
     e.name,
     e.source,
     e.target,
     sum(e.distance) AS distance,
     ST_Collect(e.the_geom) AS geom 
FROM 
    pgr_dijkstra('SELECT id,source,target,distance AS cost FROM shroads_noded', 26, 59,false) AS r, shroads_noded AS e 
WHERE 
    r.edge=e.id GROUP BY e.old_id,e.name,e.source,e.target;

2.3 从经纬度获取道路网中最近点

• 从经纬度坐标寻找道路网中最近的节点

SELECT
  v.id,
  v.the_geom,
  string_agg(distinct(e.name),',') AS name
FROM
  shroads_noded_vertices_pgr AS v,
  shroads_noded AS e
WHERE
  v.id = (SELECT id FROM shroads_noded_vertices_pgr ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint(121.3984,31.2003), 4326) LIMIT 1)
  AND (e.source = v.id OR e.target = v.id)
GROUP BY v.id, v.the_geom

2.4 服务区域计算

DrivingDistance及行驶距离范围，是计算道路网中可达性的一种通用方式。

以下SQL是计算276号点周围1.12范围的点：

1.12这个值是任意的，一般我们是计算n分钟路程来设置一个值，以确定服务点。

SELECT 
   node, agg_cost, the_geom
FROM 
   pgr_drivingDistance(
    'SELECT id, source, target, distance as cost from shroads_noded',
    276, 
    1.12, 
    false
) as isokron 
inner join shroads_noded_vertices_pgr 
   on (isokron.node = shroads_noded_vertices_pgr.id)

以下是将pgr_drivingDistance的生成点构成多边形的SQL语句：

SELECT 1 As id, ST_SetSRID(pgr_pointsAsPolygon(
    $$SELECT di.node::int4 AS id, ST_X(v.the_geom)::float8 AS x, ST_Y(v.the_geom)::float8 As y
    FROM pgr_drivingDistance(''SELECT id , source, target,distance AS cost FROM public.shroads_noded'',276 , 1.52, false) AS di 
    INNER JOIN public.shroads_noded_vertices_pgr AS v ON di.node = v.id$$
), 4326) As geom;