postgreSQL+postGIS+NodeJS 路径查询:从一无所知到略知一二
目录
一、软件准备
1.下载
首先,在官网下载即可:
1) PostgreSQL,我用的windows 版 12.4:https://www.enterprisedb.com/downloads/postgres-postgresql-downloads;
2) PostGIS,记得下配套的版本,pg12:http://download.osgeo.org/postgis/windows/;
2.安装
1) PostgreSQL,按照默认配置安装即可,端口默认5432,自定义用户名/密码;
安装成功后打开pgAdmin 4。
2) PostGIS
选择 Create spatial database,会初始化一个空间数据库:
选择安装目录,这里选择之前安装的PostgreSQL12 (务必)的目录:
安装成功后打开 PostGIS PostGIS Bundle 3 for PostgreSQL x64 12 Shapefile and DBF Loader Exporter
二、创建数据库,数据准备
1.创建空间库
1) sql 添加空间扩展:
切换刚才创建的数据库下,运行下面的命令即可:
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
2) 手动添加(二选一即可):
2.导入数据
1) 打开postgis,连接数据库mapdata
add File ,选择shape 文件;
3.对数据进行路网拓扑数据计算处理
执行成功后,执行成功后会生产一个 vertices_pgr 的表,里面包含路网相交点的空间数据
alter table nanjingroad add column source int;
alter table nanjingroad add column target int;
create index road_source_idx on road("source");
create index road_target_idx on road("target");
ALTER TABLE nanjingroad ADD COLUMN length double precision;
update road set length =st_length(geom);
ALTER TABLE public.nanjingroad ADD column rev_length numeric;
update public.nanjing_road set rev_length=length;
SELECT pgr_createTopology('road',0.00001, 'geom', 'gid');
转换坐标系(如有报错)
UnhandledPromiseRejectionWarning: error: ST_Distance: Operation on mixed SRID geometries (MultiLineString, 0) != (Point, 4326)
sql 执行以下代码:
select UpdateGeometrySRID('public','nanjingroad', 'geom', 4326);
4.路径计算测试
例如:source:47461,target:42345
SELECT * FROM pgr_dijkstra('SELECT gid as id,source,target,length::float as cost,rev_length::float as reverse_cost FROM public.nanjingroad',47461,42345,true);
三、NodeJS 后端代码实现
1.nodeJS 链接 postgreSQL
1)创建连接池
var pgsql = require('pg');
var pgconnection =new pgsql.Pool({
host: 'localhost',
port: 5432,
user: 'postgres',
password: 'postgres',
database: 'mapdata'
});
2. 后端实现
2)关键代码
/**
* 求当前点最近的路段
* @param {*} x
* @param {*} y
*/
function getRoadByPoint(x, y) {
return new Promise((resolve, reject) => {
pgConnection.connect(function (err, connection) {
if (err) {
reject(err)
} else {
connection.query(`SELECT * FROM public.nanjingroad ORDER BY ST_Distance(geom,ST_GeometryFromText('POINT(${x} ${y})',4326)) LIMIT 1`, (err, rows) => {
if (err) {
reject(err)
} else {
resolve(rows)
}
// 结束会话
connection.release()
})
}
})
})
}
/**
* 使用dijkstra 算法求最短路径
* @param {*} source
* @param {*} target
*/
function getDijkstraPath(source, target) {
return new Promise((resolve, reject) => {
pgConnection.connect(function (err, connection) {
if (err) {
reject(err)
} else {
let sql = `SELECT * from public.nanjingroad where gid in (SELECT edge FROM pgr_dijkstra('SELECT gid as id,source,target,length::float as cost,rev_length::float reverse_cost FROM public.nanjingroad',${source},${target},true))`;
connection.query(sql, (err, rows) => {
if (err) {
reject(err)
} else {
resolve(rows)
}
// 结束会话
connection.release()
})
}
})
})
}
五、效果展示
1.查询效果
2. 效率测试
数据量:9w+
单次查询用时:500-600ms 左右
本文地址:https://blog.csdn.net/u012123612/article/details/108573859
上一篇: mysql alter add一列、drop一列、change列名
下一篇: 还说快给她揉揉