PostgreSQL语句
撤销主键语句
ALTER TABLE public.trajectory_table ALTER COLUMN trj_id DROP NOT NULL;
创建索引--创建GIST索引语句
CREATE INDEX "trj_idx" ON trajectory_table USING gist ("geometry");
创建表-空间
CREATE TABLE polygontable (id SERIAL PRIMARY KEY, name VARCHAR(128), geom GEOMETRY(POLYGON, 26910));
创建表
CREATE TABLE IF NOT EXISTS test (a boolean, b text, d date, t time, ts timestamp);
插入数据
INSERT INTO test VALUES (TRUE, 'true test', '1999-01-08', '04:05:06.789', '1999-01-08 04:05:06');
INSERT INTO test VALUES (FALSE, 'false test', '1999-01-08', '04:06:06.789', '1999-01-08 04:06:06');
增加一列，使用下列语法：
ALTER TABLE table_name ADD column_name datatype
alter table test add num numeric(30);
alter table test add num float;
alter table test add num2 numeric(30,2);
删除列，使用下列语法：
ALTER TABLE table_name DROP COLUMN column_name
alter table test drop column num;
添加扩展
create extension hstore;
create extension postgis;
create extension pgrouting;
删除表
DROP TABLE public.bjwayscar_vertices_pgr;
DROP TABLE public.bjwayscar;
DROP TABLE public.bjpointsofinterestcar;
DROP TABLE public.configuration;
DROP TABLE public.osm_nodes;
DROP TABLE public.osm_ways;
DROP TABLE public.osm_relations;
删除一条记录
delete from bj_ways_car where bj_ways_car.gid = 58779;
删除表及相关依赖关系
DROP TABLE public.bjwayscar_vertices_pgr CASCADE;
DROP TABLE public.bjwayscar CASCADE;
DROP TABLE public.bjpointsofinterestcar CASCADE;
DROP TABLE public.configuration CASCADE;
DROP TABLE public.osm_nodes CASCADE;
DROP TABLE public.osm_ways CASCADE;
DROP TABLE public.osm_relations CASCADE;
查询全表
SELECT * FROM test;
按条件查询
SELECT * FROM test WHERE a;
SELECT * FROM test WHERE a = TRUE;
SELECT a,b FROM test WHERE a;
SELECT a as flg, b as cha FROM test WHERE a;
返回查询
select timestamp '2004-10-19 10:23:54';
查询系统当前完整时间
select now();
select current_timestamp;
查询系统当前日期
select current_date;
查询系统当前时间
select current_time;
时间计算-2年后
select now() + interval '2 years';
select now() + interval '2 year';
select now() + interval '2 y';
select now() + interval '2 Y';
select now() + interval '2Y';
时间计算-1月后
select now() + interval '1 month';
时间计算-3周前
select now() - interval '3 week';
时间计算-10分钟后
select now() + '10 min';
计算两个时间差
select age(now(), timestamp '1989-02-05');
select age(timestamp '2007-09-15');
时间字段的截取 --- EXTRACT函数EXTRACT(field FROM source)，其中 field 表示取的时间对象，source 表示取的日期来源，类型为 timestamp、time 或 interval。
取年份
select extract(year from now());
取月份
select extract(month from now());
取日
select extract(day from timestamp '2013-04-13');
select extract(DAY FROM interval '40 days 1 minute');
查看今天是这一年中的第几天
select extract(doy from now());
查看现在距1970-01-01 00:00:00 UTC 的秒数
select extract(epoch from now());
epoch 值转换回时间戳(epoch值也就是utc值，转换为时间戳)
select timestamp with time zone 'epoch' + 1369755555 * interval '1 second';
时间戳转成epoch值
select extract(epoch from now());
具体时间戳转换为utc(即epoch)
select extract(epoch from timestamp without time zone '1970-01-01 01:00:00');
select extract(epoch from to_timestamp('2019-03-26 14:37:26','yyyy-MM-DD hh24:mi:ss')); -----> 1553582246
epoch 值转换回时间戳(epoch值也就是utc值，转换为时间戳)
select timestamp with time zone 'epoch' + 1553582246 * interval '1 second'; -----> 2019-03-26 14:37:26
字符串到时间戳
select to_timestamp('2019-03-26 14:37:26','yyyy-MM-DD hh24:mi:ss');
select to_timestamp('20190326143726','yyyyMMDDhh24miss');
python datetime 字符串 转 时间戳
from datetime import datetime
datetime.strptime('20190326143726', '%Y%m%d%H%M%S').strftime('%Y-%m-%d %H:%M:%S')
根据具体时间进行计算，一个小时的utc
select extract(epoch from interval '+1 hours');
select extract(epoch from interval '-1 hours');
字符串到时间戳和日期
select to_date('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss');
select to_timestamp('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss');
对日期进行比较，输出boolean
select current_timestamp <= to_date('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss')
select current_timestamp <= to_timestamp('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss') flag;
时间戳根据具体格式转换为字符串
SELECT to_char((TIMESTAMP WITH TIME ZONE'epoch' + 1447898857 * INTERVAL '1 second' ),'yyyy-MM');
utc转timestamp
epoch(utc) ---> timestamp
select timestamp with time zone 'epoch' + 1553582246 * interval '1 second';
select timestamp with time zone 'epoch' + 1553582256 * interval '1 second';
select timestamp with time zone 'epoch' + 1553582266 * interval '1 second';
select timestamp with time zone 'epoch' + 1553582276 * interval '1 second';
2019-03-26 14:37:26
2019-03-26 14:37:36
2019-03-26 14:37:46
2019-03-26 14:37:56
timestamp ---> epoch(utc)
select extract(epoch from to_timestamp('2019-03-26 14:37:26','yyyy-MM-DD hh24:mi:ss'));
select extract(epoch from to_timestamp('2019-03-26 14:38:26','yyyy-MM-DD hh24:mi:ss'));
select extract(epoch from to_timestamp('2019-03-26 14:39:26','yyyy-MM-DD hh24:mi:ss'));
select extract(epoch from to_timestamp('2019-03-26 14:40:26','yyyy-MM-DD hh24:mi:ss'));
1553582246
1553582306
1553582366
1553582426
select extract(epoch from to_timestamp('2019-03-26 14:37:26.57','yyyy-MM-DD hh24:mi:ss'));
1553582246
空间查询较慢
SELECT geom FROM node_table WHERE ST_Distance(geom, 'SRID=0;POINT(116.3211279 39.984223)') < 100;
SELECT geom FROM node_table WHERE ST_Distance(geom, 'POINT(116.3211279 39.984223)') < 100; ----和上一句等价
SELECT geom FROM node_table WHERE ST_Distance(geom, 'SRID=4236;POINT(116.3211279 39.984223)') < 100;----由于存数据时没有指定坐标系，所以该命会报错
空间查询更有效
SELECT geom FROM node_table WHERE ST_DWithin(geom, 'SRID=0;POINT(116.3211279 39.984223)', 100);
SELECT geom FROM node_table WHERE ST_DWithin(geom, 'POINT(116.3211279 39.984223)', 100);
所有道路的总长度是多少（以公里为单位）？默认是没有空间坐标系
SELECT sum(ST_Length(osm_geom))/1000 AS km_roads from road_table;
所有道路的总长度是多少（以公里为单位）？先设置空间坐标系，然后利用投影坐标系转换
select UpdateGeometrySRID('road_table', 'osm_geom', 4326);
SELECT sum(ST_Length(ST_Transform(osm_geom,2436)))/1000 AS km_roads from road_table;
空间查询更有效
SELECT geom as result_out FROM node_table WHERE ST_DWithin(geom, 'POINT(116.3211279 39.984223)', 0.002);
操作查询结果
SELECT ST_DWithin(geom, 'SRID=0;POINT(116.3211279 39.984223)', 0.0015) as result_bool FROM node_table WHERE ST_DWithin(geom, 'SRID=0;POINT(116.3211279 39.984223)', 0.002);
多条件查询—空间查询
SELECT geom as result_bool FROM node_table WHERE ST_DWithin(geom, 'SRID=0;POINT(116.3211279 39.984223)', 0.002) and ST_DWithin(geom, ' SRID=0;POINT(116.3211279 39.984223)', 0.0015);
多条件查询--时间范围查询
SELECT geom FROM node_table where tstamp < '2021-03-15 21:45:46' and '2021-03-15 21:45:43' < tstamp;
多条件查询—时空查询
select
ST_DWithin(time_result.geom, 'SRID=0;POINT(116.3211279 39.984223)', 0.002) as result_bool
from (
select
node_id, name, geom
from node_table
WHERE node_table.tstamp < '2021-03-15 21:45:46' and '2021-03-15 21:45:43' < node_table.tstamp
) as time_result;
多条件查询—时空查询--最佳 给定经纬度+度数阈值（存储默时没社坐标系，即没有SRID）
select
node_id, name, geom
from node_table
WHERE node_table.tstamp < '2021-03-15 21:45:46' and '2021-03-15 21:45:43' < node_table.tstamp and ST_DWithin(node_table.geom, 'SRID=0;POINT(116.3211279 39.984223)', 0.0002)
多条件查询—时空查询--最佳 给定经纬度+距离阈值
select UpdateGeometrySRID('node_table', 'geom', 4326);
select
node_id, name, geom
from node_table
WHERE node_table.tstamp < '2021-03-15 21:45:46' and '2021-03-15 21:45:43' < node_table.tstamp
and ST_DWithin(ST_Transform(node_table.geom,2436), ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(116.3211279 39.984223)'),2436), 265.5);
用到了坐标转换ST_Transform
ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(116.3211279 39.984223)'),2436)
ST_Transform(geom,2436)
空间查询--给定经纬度+距离阈值
select
node_id, name, geom
from node_table
WHERE ST_DWithin(ST_Transform(node_table.geom,2436), ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(116.3211279 39.984223)'),2436), 365.5);
坐标系SRID1为：2249（投影坐标系）；坐标系SRID2为：4326（地理坐标系）。
st_srid()使用
select
node_id, name, st_srid(geom)
from node_table
WHERE ST_DWithin(ST_Transform(node_table.geom,2436), ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(116.3211279 39.984223)'),2436), 365.5);
st_srid()使用
SELECT ST_SRID(ST_Transform(ST_SetSRID(ST_Point(116.3211279, 39.984223), 4326), 3785)) As srid;
ST_SetSRID使用
SELECT ST_Transform(ST_SetSRID(ST_Point(116.3211279, 39.984223), 4326), 3785) As spere_merc;
SELECT ST_Transform(ST_SetSRID(ST_Point(116.3211279, 39.984223), 4326), 2436) As spere_merc;
创建表为
CREATE TABLE samplecol
(vessel_hash serial NOT NULL,
status character varying(50),
station character varying(50),
speed character varying(10),
latitude numeric(12,8),
longitude numeric(12,8),
course character varying(50),
heading character varying(50),
timestamp character varying(50),
the_geom geometry,
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) =
'POINT'::text OR the_geom IS NULL),
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326));
创建gist索引
CREATE INDEX samplecol_the_geom_gist ON samplecol USING gist (the_geom);
空间查询，找到多边形内的所有点
SELECT vessel_hash,ST_X(the_geom) AS long, ST_Y(the_geom) AS lat from samplecol where ST_Contains(ST_GeomFromEWKT('SRID=4326; POLYGON((17.2045898 37.3002753,17.2045898 37.2827946,17.0947266 35.5143431, 19.8413086 35.6215819,19.8193359 37.2827946,17.2045898 37.3002753))'),the_geom);
表samplecol形式如下：
vessel_hash | status | station | speed | latitude | longitude | course | heading | timestamp | the_geom
103079215239 | 99 | 841 | 55 | 36.14622100 | -5.44244810 | 6 | 511 | 2016-07-28T05:55:31.000Z | 0101000020E610000098B55E1D11C51tyyanjuy
103079215239 | 99 | 841 | 45 | 36.14238000 | -5.44235280 | 355 | 511 | 2016-07-28T05:52:32.000Z | 0101000020E6100000162DE521F8C41rrtyjuu
数据库表-更新srid
语法：SELECT UpdateGeometrySRID(table_name, geom_col, new_srid)
-- 更新和查询表信息的字段srid数据
select st_srid(the_geom) from spj4_table;
select UpdateGeometrySRID('spj4_table', 'the_geom', 4326);
坐标转换 ST_Transform 函数
根据前后两个坐标系的数学关系进行坐标转换，参考系变化导致坐标数值变化，但空间位置不变。原本SRID=SRID1，点的坐标为(1, 1)；转换到SRID2坐标系下，新坐标为(0, 0)，在原坐标系中的位置没有变化。
SELECT ST_AsText(
ST_Transform(
ST_GeomFromText(
'POLYGON((743238 2967416,743238 2967450,
743265 2967450,743265.625 2967416,743238 2967416))', 2249),
4326) ) As wgs_geom;
坐标系SRID1为：2249（投影坐标系）； 坐标系SRID2为：4326（地理坐标系）。
使用UUID
安装 uuid_generate_v4() 扩展函数
create extension "uuid-ossp";
执行uuid扩展函数
select uuid_generate_v4();
空间参考查询网址： https://spatialreference.org/和http://epsg.io/
查询与北京、中国有关的坐标系
select * from spatial_ref_sys where srtext ~* 'beijing';
select * from spatial_ref_sys where srtext ~* 'china';
SELECT ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416))',2249),4326)) As wgs_geom;
SELECT ST_AsEWKT(ST_Transform(ST_GeomFromEWKT('SRID=2249;CIRCULARSTRING(743238 2967416 1,743238 2967450 2,743265 2967450 3,743265.625 2967416 3,743238 2967416 4)'),4326));
地理坐标系4326--投影坐标系3857/2436
EPSG:4326 (WGS84) 地理坐标系，WGS84 是目前最流行的地理坐标系统。在国际上，每个坐标系统都会被分配一个 EPSG 代码，EPSG:4326 就是 WGS84 的代码。GPS是基于WGS84的，所以通常我们得到的坐标数据都是WGS84的。一般存储数据时，仍然按WGS84存储。
EPSG:3857 (Pseudo-Mercator) 伪墨卡托投影，也被称为球体墨卡托，Web Mercator。它是基于墨卡托投影的，把 WGS84坐标系投影到正方形上。
EPSG:2436 (北京范围的投影坐标系) 北京的东西范围：Beijing: 115.7°E - 117.4°E; 北京的投影坐标系：EPSG:2436; Beijing 1954 / 3-degree Gauss-Kruger CM 117E (Google it); WGS84 Bounds: 115.5000, 22.6600, 118.5000, 49.8800; Projected Bounds: 345818.6654, 2507707.7737, 654181.3346, 5528676.1748; Scope: Large scale topographic mapping, cadastral and engineering survey.; Last Revised: July 22, 2006; Area: China - 115.5°E to 118.5°E.
查询地址：https://spatialreference.org/和http://epsg.io/
ST_GeomFromText('POINT(39.984686999999994 116.318417)',4326)
select Timestamp '2004-10-19 10:23:54';
空间关系查询
1.相等
1.ST_Equals(geometryA，geometryB) : 如果两个要素空间相等返回true
2.相交
2.ST_Intersects(geometryA，geometryB) : 如果两个图形有相同的部分，它们的内边界或内部相交返回true
3.ST_Crosses(geometryA，geometryB) : 如果两个要素空间相交（X）返回true
4.ST_Overlaps(geometryA，geometryB) : 如果两个要素空间相同维度返回true
3.相离
5.ST_Disjoint(geometryA，geometryB) : 如果两个要素空间没有重合部分返回true
4.相触
6.ST_Touches(geometryA，geometryB) : 如果两个要素空间有接触的部分但不相交返回true
4.包含
7.ST_Within(geometryA，geometryB) : 如果第一个集合完全在第二个集合内部返回true
8.ST_Contains(geometryA，geometryB) : 如果第一个集合不包含第二个集合返回true
Geometric Types---几何类型
Name Storage Size Description Representation
point 16 bytes Point on a plane (x,y)
line 32 bytes Infinite line {A,B,C}
lseg 32 bytes Finite line segment ((x1,y1),(x2,y2))
box 32 bytes Rectangular box ((x1,y1),(x2,y2))
path 16+16n bytes Closed path (similar to polygon) ((x1,y1),...)
path 16+16n bytes Open path [(x1,y1),...]
polygon 40+16n bytes Polygon (similar to closed path) ((x1,y1),...)
circle 24 bytes Circle <(x,y),r> (center point and radius)
PostGIS不仅支持geometry,geography也支持geohash
geohash vs PostGIS
功能 Mysql spatial extension PostGIS
空间索引 仅MyISAM支持R树索引，InnoDB不支持 GIST树索引（R树的变种）
支持的空间类型 仅二维数据 二维、三维以及曲线
空间操作函数 有限的空间函数 基本实现OGC标准定义的空间操作函数
空间投影 不支持 支持多种常用投影坐标系
事务支持 不支持 PostGIS提供了一系列的长事务支持，可以有效支持复杂的空间分析功能
加载速度 MySQL > PostGIS (事务) (作者可能没有进行优化，性能不会更差) -
空间索引的创建速度 MySQL < PostGIS (diff split algo) -
查询效率 MySQL PostGIS（不同性质查询结果不一样） -
GIS系统使用 使用较少 使用较多，例如openstreetmap的数据库后台就是Postgresql+Postgis
ST_GeoHash
PostGIS虽然不推荐使用geohash，但是它内置了转换函数，可以将geometry转换为geohash
ST_GeoHash — Return a GeoHash representation of the geometry.
语法：text ST_GeoHash(geometry geom, integer maxchars=full_precision_of_point);
SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(-126,48),4326));
SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(-126,48),4326),5);
从GeoHash返回几何对象
ST_GeomFromGeoHash
geometry ST_GeomFromGeoHash(text geohash, integer precision=full_precision_of_geohash);-----> Return a geometry from a GeoHash string. The geometry will be a polygon representing the GeoHash bounds.
SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0'));
SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 4));
SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10));
ST_PointFromGeoHash
语法：point ST_PointFromGeoHash(text geohash, integer precision=full_precision_of_geohash);
SELECT ST_AsText(ST_PointFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10));
ST_Box2dFromGeoHash
语法：box2d ST_Box2dFromGeoHash(text geohash, integer precision=full_precision_of_geohash);
SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10);
row_to_json用法
嵌套查询
SELECT
row_to_json(fc)
FROM (
SELECT
'FeatureCollection' AS type
, array_to_json(array_agg(f)) AS features
FROM (
SELECT
'feature' AS type
, ST_AsGeoJSON(geom)::json as geometry --geom表中的空间字段
, (
SELECT
row_to_json(t)
FROM (
SELECT
id, user, content
) AS t
) AS properties
FROM test_table
) AS f
) AS fc
row_to_json用法
select row_to_json(node_table) from node_table;
select row_to_json(row(node_id, name)) from node_table;
select row_to_json(t) from (select node_id, name from node_table) t;
select array_to_json(array_agg(row_to_json(t)))
from (select node_id, name from node_table) t
嵌套查询
select row_to_json(t)
from (
select text_str, pronunciation,
(
select array_to_json(array_agg(row_to_json(d)))
from (
select part_of_speech, body
from definitions_table
where word_id=words_table.id
order by position asc
) d
) as definitions
from words_table
where text_str = 'autumn'
嵌套查询结果：
{
"text_str": "autumn",
"pronunciation": "autumn",
"definitions": [
{
"part_of_speech": "noun",
"body": "skilder wearifully uninfolded..."
},
{
"part_of_speech": "verb",
"body": "intrafissural fernbird kittly..."
},
{
"part_of_speech": "adverb",
"body": "infrugal lansquenet impolarizable..."
}
]
}
按时间查询并转换成json格式
select
row_to_json(fc)
from (
select node_id, name, geom
from node_table WHERE node_table.tstamp < '2021-03-15 21:45:46' and '2021-03-15 21:45:43' < node_table.tstamp
) as fc;
几何方位角(度)计算
WITH rand AS (
SELECT s, random()*2*PI() AS rad1, random()*2*PI() AS rad2
FROM generate_series(1,2,2) AS s
)
, points AS (
SELECT s, rad1, rad2, ST_MakePoint(cos1+s,sin1+s) as p1, ST_MakePoint(s,s) AS p2, ST_MakePoint(cos2+s,sin2+s) as p3
FROM rand, cos(rad1) cos1, sin(rad1) sin1, cos(rad2) cos2, sin(rad2) sin2
)
SELECT s, ST_AsText(ST_SnapToGrid(ST_MakeLine(ARRAY[p1,p2,p3]),0.001)) AS line,
degrees(ST_Angle(p1,p2,p3)) as computed_angle,
round(degrees(2*PI()-rad2 -2*PI()+rad1+2*PI()))::int%360 AS reference,
round(degrees(2*PI()-rad2 -2*PI()+rad1+2*PI()))::int%360 AS reference
FROM points;
几何方位角(度)计算
WITH points AS (SELECT ST_MakePoint(0,1) as p1, ST_MakePoint(0,0) AS p2, ST_MakePoint(1,1) as p3)
SELECT ST_AsText(ST_SnapToGrid(ST_MakeLine(ARRAY[p1,p2,p3]),0.001)) AS line,
degrees(ST_Angle(p1, p2, p3)) as computed_angle
FROM points;
line | computed_angle |
LINESTRING(0 1,0 0,1 1) | 45.00000000000005 |
ST_Angle几何方位角(度)计算—完整计算
WITH points AS (SELECT ST_Transform(ST_SetSRID(ST_MakePoint(120.3568501, 40.1562545),4326),2436) as p1, ST_Transform(ST_SetSRID(ST_MakePoint(119.3568501, 40.1662545),4326),2436) AS p2, ST_Transform(ST_SetSRID(ST_MakePoint(121.3568501, 40.1762545),4326),2436) as p3)
SELECT ST_SnapToGrid(ST_Transform(ST_MakeLine(ARRAY[p1,p2,p3]), 4326),0.001) AS line,
degrees(ST_Angle(p1, p2, p3)) as computed_angle
FROM points;
语法：
float ST_Angle(geometry point1, geometry point2, geometry point3, geometry point4);
float ST_Angle(geometry line1, geometry line2);
对于3个点，计算P1P2P3顺时针测量的角度
对于4个点，计算P1P2、P3P4顺时针测量的角度
ST_Angle(P1, P2, P3) = ST_Angle(P2, P1, P2, P3)
如果输入是2条线，则获取线的第一点和最后一点作为4个点。
ST_Angle(line1, line2) = ST_Angle(line1.P1, line1.P2, line2.P1, line2.P2)
WITH points AS (
SELECT ST_MakePoint(3, 0) AS p1,
ST_MakePoint(1, 0) AS p2,
ST_MakePoint(3, 2) AS p3)
SELECT degrees(ST_Angle(p1, p2, p3)) AS computed_angle
FROM points; --返回315.0
WITH points AS (
SELECT ST_MakePoint(3, 0) AS p1,
ST_MakePoint(1, 0) AS p2,
ST_MakePoint(3, 2) AS p3)
SELECT degrees(ST_Angle(p2, p1, p2, p3)) AS computed_angle
FROM points; --返回315.0
WITH lines AS (
WITH points AS (
SELECT ST_MakePoint(1, 0) AS p1,
ST_MakePoint(3, 0) AS p2,
ST_MakePoint(0, 0) AS p3,
ST_MakePoint(2, 2) AS p4)
SELECT ST_MakeLine(ARRAY[p1, p2]) AS line1, ST_MakeLine(ARRAY[p3, p4]) AS line2
FROM points)
SELECT degrees(ST_Angle(line1, line2)) AS computed_angle, ST_MakeLine(line1, line2) AS geom_shape
FROM lines; --返回315.0
WITH lines AS (
WITH points AS (
SELECT ST_MakePoint(1, 0) AS p1,
ST_MakePoint(3, 0) AS p2,
ST_MakePoint(0, 0) AS p3,
ST_MakePoint(2, 2) AS p4)
SELECT ST_MakeLine(ARRAY[p2, p1]) AS line1, ST_MakeLine(ARRAY[p3, p4]) AS line2
FROM points)
SELECT degrees(ST_Angle(line1, line2)) AS computed_angle, ST_MakeLine(line1, line2) AS geom_shape
FROM lines; --返回135.0
ST_Angle几何方位角(度)计算—完整计算
WITH lines AS (
WITH points AS (
SELECT ST_Transform(ST_SetSRID(ST_MakePoint(120.3568501, 40.1562545),4326),2436) AS p1,
ST_Transform(ST_SetSRID(ST_MakePoint(119.3568501, 40.1662545),4326),2436) AS p2,
ST_Transform(ST_SetSRID(ST_MakePoint(121.3568501, 40.1762545),4326),2436) AS p3)
SELECT ST_Transform(ST_MakeLine(ARRAY[p1,p2]), 4326) AS line1, ST_Transform(ST_MakeLine(ARRAY[p2,p3]), 4326) AS line2
FROM points)
SELECT degrees(ST_Angle(line1, line2)) AS computed_angle, ST_MakeLine(line1, line2) AS geom_shape
FROM lines;
ST_Azimuth 以弧度为单位返回从pointA的垂直方向到pointB的顺时针角度
float ST_Azimuth(geometry pointA, geometry pointB);
float ST_Azimuth(geography pointA, geography pointB);
SELECT degrees(ST_Azimuth(ST_MakePoint(120.3568501, 40.1562545), ST_MakePoint(120.3568501, 41.1562545))) AS computed_angle;-返回0.0(平面坐标)
SELECT degrees(ST_Azimuth(ST_Transform(ST_SetSRID(ST_MakePoint(120.3568501, 40.1562545),4326),2436), ST_Transform(ST_SetSRID(ST_MakePoint(120.3568501, 41.1562545),4326),2436))) AS computed_angle;--返回357.812(SRID=2436平面坐标)
SELECT degrees(ST_Azimuth(ST_Transform(ST_SetSRID(ST_MakePoint(120.3568501, 40.1562545),4326),3857), ST_Transform(ST_SetSRID(ST_MakePoint(120.3568501, 41.1562545),4326),3857))) AS computed_angle;--返回0.0(SRID=3857平面坐标)
SELECT degrees(ST_Azimuth(ST_MakePoint(120.3568501, 40.1562545)::geography, ST_MakePoint(120.3568501, 41.1562545)::geography)) AS computed_angle;--返回 180.0(SRID=4326地理坐标)
SELECT degrees(ST_Azimuth(ST_SetSRID(ST_MakePoint(120.3568501, 40.1562545)::geography,4326), ST_SetSRID(ST_MakePoint(120.3568501, 41.1562545)::geography,4326))) AS computed_angle;--返回=180.0(SRID=4326地理坐标)
OSM相关配置
network_type_all = {'auto','bike','walk','railway','aeroway'}
osm_highway_type_dict = {'motorway': 'motorway',
'motorway_link': 'motorway',
'trunk': 'trunk',
'trunk_link': 'trunk',
'primary': 'primary',
'primary_link': 'primary',
'secondary': 'secondary',
'secondary_link': 'secondary',
'tertiary': 'tertiary',
'tertiary_link': 'tertiary',
'residential': 'residential',
'residential_link': 'residential',
'service': 'service',
'services': 'service',
'cycleway':'cycleway',
'footway': 'footway',
'pedestrian': 'footway',
'steps': 'footway',
'track': 'track',
'unclassified': 'unclassified'}
link_type_no_dict = {'motorway':1,
'trunk':2,
'primary':3,
'secondary':4,
'tertiary':5,
'residential':6,
'service':7,
'cycleway':8,
'footway':9,
'track':10,
'unclassified':11,
'connector':20,
'railway':30,
'aeroway':31}
default_lanes_dict = {'motorway': 4,
'trunk': 3,
'primary': 3,
'secondary': 2,
'tertiary': 2,
'residential': 1,
'service': 1,
'cycleway':1,
'footway':1,
'track':1,
'unclassified': 1,
'connector': 2}
default_speed_dict = {'motorway': 59,
'trunk': 39,
'primary': 39,
'secondary': 39,
'tertiary': 29,
'residential': 29,
'service': 29,
'cycleway':9,
'footway':4,
'track':29,
'unclassified': 29,
'connector':59}
default_oneway_flag_dict = {'motorway': True,
'trunk':True,
'primary':True,
'secondary':False,
'tertiary':False,
'residential':False,
'service':False,
'cycleway':True,
'footway':True,
'track': True,
'unclassified':False,
'connector':False,
'railway':True,
'aeroway':True}
pgrouting规划
--创建city_routing数据库后，执行 安装相关扩展
create extension postgis;
create extension pgrouting;
--查询版本
select postgis_full_version(), pgr_version();
--为shenzhen_roads表添加四个字段
ALTER TABLE shenzhen_roads
ADD COLUMN source INTEGER,
ADD COLUMN target INTEGER,
ADD COLUMN cost DOUBLE PRECISION,
ADD COLUMN reverse_cost DOUBLE PRECISION;
--正向路径的成本
UPDATE shenzhen_roads
SET cost = ST_Length(geom), reverse_cost = -1
WHERE oneway = 'F';
--反向路径的成本
UPDATE shenzhen_roads
SET reverse_cost = ST_Length(geom), cost = -1
WHERE oneway = 'T';
--双向路径的成本
UPDATE shenzhen_roads
SET cost = ST_Length(geom), reverse_cost = ST_Length(geom)
WHERE oneway = 'B';
-- 建立路网拓扑 -- 拓扑路径创建完成后，数据库中会自动多出一张表shenzhen_roads_vertices_pgr
SELECT pgr_createTopology(
'shenzhen_roads',
0.001,
'geom',
'gid',
'source',
'target'
);
-- 查询
select * from public.shenzhen_roads_vertices_pgr srvp;
-- 查询
select * from public.shenzhen_roads sr;
--单条行人路线（一到一）
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id,
source, target,
cost, reverse_cost
FROM shenzhen_roads',
10564, 12089,
directed := FALSE
);
-- 查询
SELECT gid AS id,
source, target,
cost, reverse_cost
FROM shenzhen_roads;
--多个行人前往同一个目的地（多到一）
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id,
source, target,
cost, reverse_cost
FROM shenzhen_roads',
ARRAY[10564, 13019], 12089,
directed := FALSE
);
--许多行人从同一地点离开（一到多）
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id,
source, target,
cost/1.3 AS cost, reverse_cost/1.3 AS reverse_cost
FROM shenzhen_roads',
12089, ARRAY[10564, 13019],
directed := FALSE
);
--多个行人到不同的目的地（多到多）
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id,
source, target,
cost/1.3/60 AS cost, reverse_cost/1.3/60 AS reverse_cost
FROM shenzhen_roads',
ARRAY[12089, 13019], ARRAY[10564, 7304],
directed := FALSE
);
--多个行人到不同的目的地，返回总代价
SELECT * FROM pgr_dijkstraCost(
'SELECT gid AS id,
source, target,
cost/1.3/60 AS cost, reverse_cost/1.3/60 AS reverse_cost
FROM shenzhen_roads',
ARRAY[12089, 13019], ARRAY[10564, 7304],
directed := FALSE
);
--多个行人前往不同的目的地，将每个行人所花费的步行时间求和
SELECT start_vid, SUM(agg_cost)
FROM pgr_dijkstraCost(
'SELECT gid AS id,
source, target,
cost/1.3/60 AS cost, reverse_cost/1.3/60 AS reverse_cost
FROM shenzhen_roads',
ARRAY[12089, 13019], ARRAY[10564, 7304],
directed := FALSE
)
GROUP BY start_vid
ORDER BY start_vid;
--以下查询显示单向路段的数量
SELECT count(*)
FROM shenzhen_roads
WHERE cost < 0;
--以下查询显示单向路段的数量
SELECT count(*)
FROM shenzhen_roads
WHERE reverse_cost < 0;
--行车路径-出发
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id,
source, target,
cost, reverse_cost
FROM shenzhen_roads
',
50013, 19688,
directed := true
);
--行车路径-返回
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id,
source, target,
cost, reverse_cost
FROM shenzhen_roads
',
19688, 50013,
directed := true
);
--OSM的路网数据中具有包含道路类型信息的fclass列，基于该列查询道路的类型信息：
SELECT DISTINCT fclass FROM shenzhen_roads ORDER BY fclass;
--新添加一个penalty列用于保存每条路径的优先级信息（penalty值越小，优先级越高）
ALTER TABLE shenzhen_roads ADD COLUMN penalty DOUBLE PRECISION;
--把每条路径的penalty的值都设为1.0，表示所有路径都没有优先级之分（penalty值都为1.0）
UPDATE shenzhen_roads SET penalty = 1.0;--可能会失效
UPDATE shenzhen_roads SET penalty = 1.0 where gid > 0;
--查询 penalty 优先级
select shenzhen_roads.penalty FROM shenzhen_roads;
--没有优先级的行车路径
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id,
source, target,
cost * penalty AS cost,
reverse_cost
FROM shenzhen_roads
',
50013, 19688,
directed := true
);
-- 修改路径的优先级
UPDATE shenzhen_roads SET penalty = -1.0
WHERE fclass IN ('steps','footway','pedestrian', 'cycleway', 'track', 'track_grade2');
UPDATE shenzhen_roads SET penalty = 5.0
WHERE fclass IN ('residential');
UPDATE shenzhen_roads SET penalty = 0.8
WHERE fclass IN ('tertiary', 'tertiary_link', 'motorway', 'motorway_link', 'living_street');
UPDATE shenzhen_roads SET penalty = 0.5
WHERE fclass IN ('secondary', 'secondary_link');
UPDATE shenzhen_roads SET penalty = 0.3
WHERE fclass IN ('primary', 'primary_link', 'trunk', 'trunk_link');
--查询
select shenzhen_roads.penalty FROM shenzhen_roads;
--具有优先级的行车路径
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id,
source, target,
cost * penalty AS cost,
reverse_cost
FROM shenzhen_roads
',
50013, 19688,
directed := true
);
-- 允许被车辆行驶的路径 创建一个数据库视图
CREATE VIEW vehicle_net AS
SELECT gid, source, target,
cost / 1.3 / 60 AS cost,
reverse_cost / 1.3 / 60 AS reverse_cost, -- 假设行人每秒走1.3m
geom
FROM shenzhen_roads
WHERE fclass NOT IN ('steps', 'cycleway', 'footway', 'track_grade2', 'track');
-- 验证
SELECT count(*) FROM shenzhen_roads;
SELECT count(*) FROM vehicle_net;
--创建一个保存该区域路段的数据库视图
CREATE VIEW little_net AS
SELECT *
FROM vehicle_net
WHERE vehicle_net.geom && ST_MakeEnvelope(12673569, 2573130, 12680410, 2578570, 3857);
-- 验证
SELECT COUNT(*) FROM little_net;
SELECT * FROM little_net;
-- 结果包含道路名信息
SELECT dijkstra.*, ways.name FROM pgr_dijkstra(
'SELECT gid AS id, * FROM little_net',
2169, 4092
) AS dijkstra LEFT JOIN shenzhen_roads AS ways
ON (edge = gid)
ORDER BY seq;
-- 路径的几何信息（WKT格式）
SELECT dijkstra.*, ways.name, ST_AsText(ways.geom) FROM pgr_dijkstra(
'SELECT gid AS id, * FROM little_net',
2169, 4092
) AS dijkstra LEFT JOIN shenzhen_roads AS ways
ON (edge = gid)
ORDER BY seq;
-- 路径的几何信息（二进制格式）
WITH
dijkstra AS(
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id, * FROM little_net',
2169, 4092
)
)
SELECT dijkstra.*, ways.name, ways.geom AS route_geom
FROM dijkstra LEFT JOIN shenzhen_roads AS ways
ON (edge = gid)
ORDER BY seq;
--使用几何信息计算道路方位角
WITH
dijkstra AS(
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id, * FROM little_net',
2169, 4092
)
),
get_geom AS (
SELECT dijkstra.*, ways.name, ways.geom AS route_geom
FROM dijkstra LEFT JOIN shenzhen_roads AS ways ON (edge = gid)
ORDER BY seq)
SELECT seq, name, cost,
-- calculating the azimuth
degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth,
ST_AsText(route_geom),
route_geom
FROM get_geom
ORDER BY seq;
-- 几何信息的方向性
WITH
dijkstra AS(
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id, * FROM little_net',
2169, 4092
)
),
get_geom AS (
SELECT dijkstra.*, ways.name,
-- 将反向路段的几何信息逆序排列
CASE
WHEN dijkstra.node = ways.source THEN ways.geom
ELSE ST_Reverse(ways.geom)
END AS route_geom
FROM dijkstra LEFT JOIN shenzhen_roads AS ways ON (edge = gid)
ORDER BY seq)
SELECT seq, name, cost,
-- 计算方位角
degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth,
ST_AsText(route_geom),
route_geom
FROM get_geom
ORDER BY seq;
-- 创建函数（存储过程）
CREATE OR REPLACE FUNCTION wrk_dijkstra(
IN edges_subset regclass, -- 视图作为参数
IN source BIGINT,
IN target BIGINT,
OUT seq INTEGER,
OUT gid BIGINT,
OUT name TEXT,
OUT cost FLOAT,
OUT azimuth FLOAT,
OUT route_readable TEXT,
OUT route_geom geometry
) RETURNS SETOF record AS
$BODY$
WITH
dijkstra AS (
SELECT * FROM pgr_dijkstra(
-- 使用参数化的视图
'SELECT gid AS id, * FROM ' || $1,
$2, $3)
),
get_geom AS (
SELECT dijkstra.*, ways.name,
CASE
WHEN dijkstra.node = ways.source THEN geom
ELSE ST_Reverse(geom)
END AS route_geom
FROM dijkstra JOIN shenzhen_roads AS ways ON (edge = gid)
ORDER BY seq)
SELECT
seq,
edge,
name,
cost,
degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth,
ST_AsText(route_geom),
route_geom
FROM get_geom
ORDER BY seq;
$BODY$
LANGUAGE 'sql';
-- 使用过程（函数）
SELECT * FROM wrk_dijkstra('vehicle_net', 2169, 4092);
-- 顶点的数量 --
-- 和shenzhen_roads表相联系的顶点的数量
SELECT COUNT(*) FROM shenzhen_roads_vertices_pgr;
-- 和vehicle_net视图相联系的顶点的数量
SELECT COUNT(*) FROM shenzhen_roads_vertices_pgr
WHERE id IN (
SELECT source FROM vehicle_net
UNION
SELECT target FROM vehicle_net
);
-- 和little_net视图相联系的顶点的数量
SELECT COUNT(*) FROM shenzhen_roads_vertices_pgr
WHERE id IN (
SELECT source FROM little_net
UNION
SELECT target FROM little_net
);
-- 最近顶点 --
-- 基于全部顶点计算
SELECT id
FROM shenzhen_roads_vertices_pgr
ORDER BY the_geom <-> ST_SetSRID(ST_Point(12677354.9,2578172.3), 3857)
LIMIT 1;
-- 基于vehicle_net视图计算
WITH
vertices AS (
SELECT * FROM shenzhen_roads_vertices_pgr
WHERE id IN (
SELECT source FROM vehicle_net
UNION
SELECT target FROM vehicle_net)
)
SELECT id FROM vertices
ORDER BY the_geom <-> ST_SetSRID(ST_Point(12677354.9,2578172.3), 3857) LIMIT 1;
-- 基于little_net视图计算
WITH
vertices AS (
SELECT * FROM shenzhen_roads_vertices_pgr
WHERE id IN (
SELECT source FROM little_net
UNION
SELECT target FROM little_net)
)
SELECT id FROM vertices
ORDER BY the_geom <-> ST_SetSRID(ST_Point(12677354.9,2578172.3), 3857) LIMIT 1;
--创建函数wrk_fromAtoB
CREATE OR REPLACE FUNCTION wrk_fromAtoB(
IN edges_subset regclass,
IN x1 NUMERIC, IN y1 numeric,
IN x2 NUMERIC, IN y2 NUMERIC,
OUT seq INTEGER,
OUT gid BIGINT,
OUT name TEXT,
OUT costs FLOAT,
OUT azimuth FLOAT,
OUT geom GEOMETRY
)
RETURNS SETOF record AS
$BODY$
DECLARE
final_query TEXT;
BEGIN
final_query :=
FORMAT($$
WITH
vertices AS (
SELECT * FROM shenzhen_roads_vertices_pgr
WHERE id IN (
SELECT source FROM %1$I
UNION
SELECT target FROM %1$I
)
),
dijkstra AS (
SELECT *
FROM wrk_dijkstra(
'%1$I',
-- source
(SELECT id FROM vertices
ORDER BY the_geom <-> ST_SetSRID(ST_Point(%2$s, %3$s), 3857) LIMIT 1),
-- target
(SELECT id FROM vertices
ORDER BY the_geom <-> ST_SetSRID(ST_Point(%4$s, %5$s), 3857) LIMIT 1)
)
)
SELECT
seq,
dijkstra.gid,
dijkstra.name,
dijkstra.cost / 1000.0 AS costs,
azimuth,
route_geom AS geom
FROM dijkstra
JOIN shenzhen_roads ways USING(gid);$$,
edges_subset, x1,y1,x2,y2
);
RAISE notice '%', final_query; -- 执行该函数时显示函数的逻辑代码信息
RETURN QUERY EXECUTE final_query;
END;
$BODY$
LANGUAGE 'plpgsql';
--使用函数wrk_fromAtoB
SELECT * FROM wrk_fromAtoB(
'vehicle_net',
12677354.9,2578172.3,
12677441.2, 2577908.3
);
--使用函数wrk_fromAtoB
SELECT * FROM wrk_fromAtoB(
'little_net',
12677354.9,2578172.3,
12677441.2, 2577908.3
);
--使用函数wrk_fromAtoB
SELECT * FROM wrk_fromAtoB(
'shenzhen_roads',
12677354.9,2578172.3,
12677441.2, 2577908.3
);
-- 基于geoserver 发布wfs
-- 配置新的SQL视图 填写视图名称为"shenzhen"，并填写如下SQL语句
SELECT ST_MakeLine(route.geom) FROM (
SELECT geom FROM wrk_fromAtoB('vehicle_net', %x1%, %y1%, %x2%, %y2%) ORDER BY seq
) AS route;
osm2pgrouting使用
0. 提前新建好数据库 beijing 并安装以下3个扩展
create extension hstore;
create extension postgis;
create extension pgrouting;
1. 准备好osmosis工具和osm2pgrouting工具
2. cmd 一定是管理员
cd "Program Files (x86)osmosisin"
C:Program Files (x86)osmosisin>osmosis --read-pbf "D:dataeijing.osm.pbf" --write-xml beijing.osm
3. osm2pgrouting 只接受.osm 不接受.osm.pbf二进制文件
cd D:data
osm2pgrouting --file D:/data/beijing.osm --conf S:/PostgreSQL/10/bin/mapconfig_for_cars.xml --schema public --addnodes --prefix bj_ --suffix _car --clean -d beijing -U postgres -h localhost -p 5432 -W jiangshan
or
osm2pgrouting --file D:/data/beijing.osm --conf S:/PostgreSQL/10/bin/mapconfig_for_cars.xml --schema public --prefix bj_ --suffix _car --addnodes --attributes --tags --clean -d beijing -U postgres -h localhost -p 5432 -W jiangshan
正常应该输出以下：
Creating tables...
TABLE: public.bj_ways_car created ... OK.
TABLE: public.bj_pointsofinterest_car created ... OK.
TABLE: public.configuration created ... OK.
TABLE: public.osm_nodes created ... OK.
TABLE: public.osm_ways created ... OK.
TABLE: public.osm_relations created ... OK.
Opening configuration file: S:/PostgreSQL/10/bin/mapconfig_for_cars.xml
Parsing configuration
Exporting configuration ...
- Done
Parsing data
Current osm_nodes: 1600000
Final osm_nodes: 1613675
Current osm_ways: 200000
Final osm_ways: 210604
End Of file
Finish Parsing data
Adding auxiliary tables to database...
Export Ways ...
Processing 210604 ways:
[****| ] (9%) Total processed: 20000 Vertices inserted: 32536 Split ways inserted 32166
[*********| ] (18%) Total processed: 40000 Vertices inserted: 20369 Split ways inserted 27530
[**************| ] (28%) Total processed: 60000 Vertices inserted: 15299 Split ways inserted 22009
[******************| ] (37%) Total processed: 80000 Vertices inserted: 10957 Split ways inserted 15036
[***********************| ] (47%) Total processed: 100000 Vertices inserted: 10081 Split ways inserted 14253
[****************************| ] (56%) Total processed: 120000 Vertices inserted: 6480 Split ways inserted 9379
[*********************************| ] (66%) Total processed: 140000 Vertices inserted: 6910 Split ways inserted 10421
[*************************************| ] (75%) Total processed: 160000 Vertices inserted: 6187 Split ways inserted 8965
[******************************************| ] (85%) Total processed: 180000 Vertices inserted: 6729 Split ways inserted 11042
[***********************************************| ] (94%) Total processed: 200000 Vertices inserted: 13488 Split ways inserted 20426
[**************************************************|] (100%) Total processed: 210604 Vertices inserted: 3274 Split ways inserted 5000
Creating indexes ...
Processing Points of Interest ...
Adding functions for processing Points of Interest ...
To process pointsOfInterest table:
osm2pgr_pois_update(radius default 200, within default 50)
- Using areas of (radius)mts on POIS
- Using edges that are at least (within) mts of each POI
POIS that do not have a closest edge is considered as too far
#########################
size of streets: 210604
Execution started at: Fri Mar 19 12:31:46 2021
Execution ended at: Fri Mar 19 12:36:31 2021
Elapsed time: 285.297 Seconds.
User CPU time: -> 285.299 seconds
#########################
# 删除命令
# --删除表及相关依赖关系 -- 需要时再进行操作
DROP TABLE public.bjwayscar_vertices_pgr CASCADE;
DROP TABLE public.bjwayscar CASCADE;
DROP TABLE public.bjpointsofinterestcar CASCADE;
DROP TABLE public.configuration CASCADE;
DROP TABLE public.osm_nodes CASCADE;
DROP TABLE public.osm_ways CASCADE;
DROP TABLE public.osm_relations CASCADE;
osm2pgrouting新建WAYS_TABLE，更新length_m, cost_s, reverse_cost_s的策略
# length_m, cost_s, reverse_cost_s计算规则 bj_ways_car=WAYS_TABLE
UPDATE WAYS_TABLE SET length_m = ST_length(geography(ST_Transform(the_geom, 4326))),
cost_s = CASE
WHEN one_way = -1 THEN -ST_length(geography(ST_Transform(the_geom, 4326))) / (maxspeed_forward::float * 5.0 / 18.0)
ELSE ST_length(geography(ST_Transform(the_geom, 4326))) / (maxspeed_backward::float * 5.0 / 18.0)
END,
reverse_cost_s = CASE
WHEN one_way = 1 THEN -ST_length(geography(ST_Transform(the_geom, 4326))) / (maxspeed_backward::float * 5.0 / 18.0)
ELSE ST_length(geography(ST_Transform(the_geom, 4326))) / (maxspeed_backward::float * 5.0 / 18.0)
END
WHERE length_m IS NULL AND maxspeed_backward !=0 AND maxspeed_forward != 0;
length_m 单位为米, cost_s 单位为秒, reverse_cost_s 单位为秒！