Postgresql+postgis地图开发说明

实习期间需要部署路网数据库查询服务，windows和ubuntu下的安装部署大同小异，现以postgresql 10+postgis 2.5.0为例

1. 安装postgresql+postgis部署地图数据查询服务

ubuntu: sudo apt install libpq-dev postgresql-server-dev-10 postgresql-server-dev-all

windows:参考博客csdn blog

2. 下载openstreetmap地图osm数据

• 下载国家地图数据，在www.openstreetmap.org，无法具体到省份城市

• 下载具体省份城市地图下载可参考省市地图

• 更小区域地图下载，可使用www.openstreetmap.org限定经纬度直接导出

3. 创建数据库，并支持postgis和pgrouting

• 方式一，命令行

createdb -U postgres XXX（数据库名称，后续都用XXX代替）

进入数据库\c XXX,输入以下语句使数据库支持空间分析和空间查询

1
2
3
4
5
6

CREATE EXTENSION postgis; 
CREATE EXTENSION pgrouting;
CREATE EXTENSION postgis_topology; 
CREATE EXTENSION fuzzystrmatch; 
CREATE EXTENSION postgis_tiger_geocoder; 
CREATE EXTENSION address_standardizer;

• 方式二：在pgAdmin中打开，直接以postgis数据库为模版创建数据库，这样创建的数据库直接支持空间查询和空间分析。

4. 将osm数据导入数据库blog

4.1 安装OSM数据导入工具osm2pgsql

(Ubuntu操作)：

sudo apt install osm2pgsql

(windows操作)：

• osm2pgsql下载地址：http://customdebug.com/osm/osm2pgsql.zip

• default.style文件:https://github.com/openstreetmap/osm2pgsql/blob/master/default.style

• osm2pgsql下载好之后解压，解压完成之后，将下载的default.style放入到.\osm2pgsql\x64文件夹下

4.2 进入数据库

windows: cmd输入psql -U postgres -d XXX进入数据库

ubuntu: psql -U user_name -d database_name -h localhost

4.3 为了避免后面出现要输入密码或者出现Error: Connection to database failed: fe_sendauth: no password supplied错误，将文件pg_hba.conf（文件路径：...\PostgreSQL\10\data）里面的md5，下图所示的两个md5改为trust

4.4 (windwos)加载postgis对象和函数定义（postgis.sql）

• 在数据库安装位置bin文件夹下打开dos窗口，输入命令psql -U postgres -d osm -f “此处为postgis.sql文件的路径”。

• postgis.sql文件在...PostgreSQL\10\share\contrib\postgis-2.5文件夹下

4.5 (windows)加载EPSG坐标系统定义（spatial_ref_sys.sql）

• 在dos窗口下输入命令psql -U postgres -d osm -f 此处为postgis.sql文件的路径

• postgis.sql文件在...PostgreSQL\10\share\contrib\postgis-2.4文件夹下

4.6 导入OSM数据

• ubuntu

  osm2pgsql -d XXX –U username -P portID -C 12000 -S /home/china-latest.osm.pbf -H localhost -W

• windows

  在下载的osm2pgsql的.\osm2pgsql\x64文件夹下打开dos窗口，输入命令osm2pgsql -d XXX –U username -P portID -C 12000 -S "C:\develop\postsql-gis\osm2pgsql\x64\default.style" china-latest.osm.pbf

4.7 验证数据是否成功

• (linux) \d发现多了几张table和view说明导入成功

• (windows) 从菜单栏的postgressql打开pgAdmin，发现XXX库中多了4张表，说明导入成功

5. 创建路网拓扑图和查询数据表

5.1 创建数据表，选择指定列，并重新排序gid

• 选择指定列：CREATE TABLE XXX AS (SELECT osm_id,name,ref,highway,oneway,bridge,way FROM planet_osm_roads);

• 添加起点id：ALTER TABLE XXX ADD COLUMN source integer;

• 添加终点id：ALTER TABLE XXX ADD COLUMN target integer;

• 添加道路权重值: ALTER TABLE XXX ADD COLUMN length double precision;

• 创建序列：

1
2
3
4
5
6

CREATE SEQUENCE test_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

• 更新自增ID：

1
2
3

alter table XXX add column gid int;
alter table XXX alter column gid set default nextval('test_id_seq');
update XXX set gid=nextval('test_id_seq');

• 修改数据表中列名way为geom: alter table XXX rename way to geom

5.2 创建路网拓扑 SELECT pgr_createTopology('XXX',0.00001, 'geom', 'gid');

5.3 为length赋值 update XXX set length =st_length(geom);

6. 使用QGIS加载数据表和在线地图

6.1 QGIS连接postgressql

• 连接PostGIS数据源——数据库管理器——导入数据——加载数据

• 输入数据库名称 端口 用户名和密码

• 双击数据表即可显示图层

6.2 安装在线地图插件显示地图图层

• 打开QGIS，在插件下搜QGIS cloud插件，选择 add background layer 即可添加google map，bing map，openstreet map 作为底图