linux_postgresql安装rpm

pgdg-centos10-10-2.noarch.rpm下载

百度云下载地址   https://pan.baidu.com/s/1t5A5F7EspecqpdXOGr1Usw
官网下载地址  https://yum.postgresql.org/

卸载postgresql

rpm -qa | grep postgres    检查PostgreSQL 是否已经安装  如果已经安装了可以通过rpm -ev  xxx 删除
rpm -qal | grep postgres   检查PostgreSQL 安装位置     手动删除文件夹目录

创建postgres的linux的用户组

以后操作都使用该账户操作 
useradd postgres
passwd postgres
#两次输入密码并确认 这里密码都为postgres

安装postgresql

yum localinstall pgdg-centos10-10-2.noarch.rpm  
yum list | grep postgresql    #能够列表所有关于postgresql列表  这里选中postgresql10-server.x86_64
yum install -y postgresql10-server.x86_64

初始化数据库

/usr/pgsql-10/bin/postgresql-10-setup initdb

postsql安装完成的物理目录的位置

/usr/pgsql-10/bin/       #postgresql
/var/lib/pgsql/10/data   #postgresql存放数据的地方

设置开机启动数据库

sudo systemctl enable postgresql-10.service   #设置数据库的开机启动

启动/重启数据库

sudo systemctl start postgresql-10   #启动数据库
/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile start   #启动数据库手工的方式
systemctl restart postgresql-10        #重启数据库
/usr/pgsql-10/bin/pg_ctl restart -D /var/lib/pgsql/10/data #手工重启数据库

修改postgres用户的密码  

su - postgres
psql
ALTER USER postgres WITH PASSWORD 'postgres';

修改远程配制文件

vi /var/lib/pgsql/10/data/pg_hba.conf
host     all             all             0.0.0.0/0               md5     #修改成这样
   METHOD这列要修改成md5
vi /var/lib/pgsql/10/data/postgresql.conf
     修改listen_addresses  及port  这两项

放开防火墙

默认的端口为5432
vi /etc/sysconfig/iptables
-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT
service iptables restart  #重启

postgis的安装

yum list | grep postgis  查询一下
yum install postgis24_10.x86_64   #安装postgis

扩展test数据库的gis

假如已经创建了test数据库
\c test
CREATE EXTENSION postgis;    #开户了gis

CREATE TABLE mylocation ( 
  id SERIAL PRIMARY KEY,
  geom GEOMETRY(Point, 4326),
  name VARCHAR(128),
  x double precision,
  y double precision
); 
 
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.0001 0)', 4326),'zhangsan',0.0001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.001 0)', 4326),'zhangsan',0.001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.001 0)', 4326),'zhangsan',0.001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.1 0)', 4326),'zhangsan',0.1,0
);
 
 
 
SELECT id, name,geom,x,y,   ST_DistanceSphere(
                      geom,
                      ST_GeometryFromText('POINT(0 0)')) distance
FROM mylocation
WHERE ST_DWithin(
  geom, 
  ST_GeomFromText('POINT(0 0)', 4326),
  0.001
)ORDER BY distance asc;;

查询单位为米 

SELECT id, name,geom,x,y,   ST_DistanceSphere(
                      geom,
                      ST_GeometryFromText('POINT(0 0)')) distance
FROM mylocation
WHERE ST_DWithin(
  geom::geography, 
  ST_GeomFromText('POINT(0 0)', 4326)::geography,
  1000
) ORDER BY distance asc;