安装postgis
安装postgresql-11
yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
yum install -y postgresql11.x86_64 postgresql11-libs.x86_64 postgresql11-server.x86_64 postgresql11-contrib.x86_64 postgresql11-devel.x86_64
初始化数据库
mkdir -p /data/pgdata
chmod 700 /data/pgdata
chown postgres:postgres /data/pgdata
vi /usr/lib/systemd/system/postgresql-11.service
把Environment=PGDATA=/var/lib/pgsql/11/data/修改为Environment=PGDATA=/data/pgdata/
/usr/pgsql-11/bin/postgresql-11-setup initdb #初始化
# centos7 的开机启动
systemctl start postgresql-11 #启动postgresql服务
systemctl enable postgresql-11 #设置开机启动
# centos6 的开机启动
service postgresql-11 start #启动postgresql服务
chkconfig postgresql-11 on #设置开机启动
安装PostGIS
yum install -y wget
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum install -y postgis25_11.x86_64 postgis25_11-utils.x86_64
针对数据库启用PostGIS插件
# 添加空间插件
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
安装之后，public下会新增一个表spatial_ref_sys。
存储经纬度
点POINT类型的数据结构为POINT(0 0)，正好可以用作存储经纬度。
两个重要的坐标体系
4326 GCS_WGS_1984 World Geodetic System (WGS)
26986 美国马萨诸塞州地方坐标系（区域坐标系） 投影坐标, 平面坐标
添加点字段
SELECT AddGeometryColumn ('t_point', 'geom_point', 4326, 'POINT', 2);
SELECT AddGeometryColumn ('t_point', 'geom_point_26986', 26986, 'POINT', 2);
ALTER TABLE t_point ADD COLUMN geom_p_alter geometry(POINT,4326);
添加空间索引
CREATE INDEX idx_t_point
ON t_point
USING gist(geom_point);
广州 23.124999, 113.250478
清远 23.678114, 113.055371
韶关 24.815976, 113.614100
郴州 25.773645, 113.022818
株洲 27.817057, 113.110819
岳阳 29.338286, 113.129162
咸宁 29.843173, 114.324432
黄石 30.186407, 115.040655
武汉 30.538355, 114.298878
INSERT INTO t_point (id,name,geom_point ) VALUES (1,'广州',ST_GeomFromText('POINT(113.250478 23.124999)', 4326));
INSERT INTO t_point (id,name,geom_point ) VALUES (2,'清远',ST_GeomFromText('POINT(113.055371 23.678114)', 4326));
INSERT INTO t_point (id,name,geom_point ) VALUES (3,'韶关',ST_GeomFromText('POINT(113.614100 24.815976)', 4326));
INSERT INTO t_point (id,name,geom_point ) VALUES (4,'郴州',ST_GeomFromText('POINT(113.022818 25.773645)', 4326));
INSERT INTO t_point (id,name,geom_point ) VALUES (5,'株洲',ST_GeomFromText('POINT(113.110819 27.817057)', 4326));
INSERT INTO t_point (id,name,geom_point ) VALUES (6,'岳阳',ST_GeomFromText('POINT(113.129162 29.338286)', 4326));
INSERT INTO t_point (id,name,geom_point ) VALUES (7,'咸宁',ST_GeomFromText('POINT(114.324432 29.843173)', 4326));
INSERT INTO t_point (id,name,geom_point ) VALUES (8,'黄石',ST_GeomFromText('POINT(115.040655 30.186407)', 4326));
INSERT INTO t_point (id,name,geom_point ) VALUES (9,'武汉',ST_GeomFromText('POINT(114.298878 30.538355)', 4326));
SELECT ST_AsText(ST_Transform(ST_Transform(st_geometryfromtext('POINT(113.250478 23.124999)',4326),26986),4326));
update t_point set geom_point_26986=ST_Transform(geom_point,26986),geom_p_alter=geom_point;
