码峰博客 – 码而思

分享积累从此时此刻开始

Linux安装PostgreSQL和PostGIS

postgresql + postgis 可以方便存储三维空间坐标信息，以及使用空间函数计算三维数据。

Pgsql安装

进入PostgreSQL官网，选择要安装的版本，即可生成安装命令

小编这里安装的是 postgresql 12，系统是 centos 7 , 依次运行如下命令即可完成安装

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:
sudo yum install -y postgresql12-server

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12

进入Psql控制台

su - postgres
psql

配置PostgreSQL

修改管理员密码

用 vavicat 等工具连接时，需要输入用户名密码，管理员默认用户名为 postgres

# 修改 postgres 密码
postgres=# \password postgres
Enter new password: 输入密码回车即可

开放端口

阿里云服务器 安全组放开 5432 端口，宝塔面板 安全 选项 放开 5432 端口

配置远程访问

修改 postgresql.conf 文件
默认的监听本地访问 localhost，改为 * ，监听所有对数据库的访问IP

运行如下命令，查看 postgresql.conf 文件的位置
find / -name postgresql.conf
得到如下地址
/var/lib/pgsql/12/data/postgresql.conf

运行如下命令查看 postgresql.conf
vi /var/lib/pgsql/12/data/postgresql.conf

输入 /listen_addresses 
回车，查找 listen_addresses 所在行
按 i 键，进入编辑模式
将 listen_addresses = 'localhost'  改为 listen_addresses = '*' 
按 ESC 退出编辑模式
输入 :wq  
回车，退出文件

修改 pg_hba.conf 文件

设置ip为 0.0.0.0/0 , 允许所有主机通过用户名验证的方式访问数据库

运行如下命令，查看 pg_hba.conf 文件的位置
find / -name pg_hba.conf
得到如下地址
/var/lib/pgsql/12/data/pg_hba.conf

运行如下命令查看 pg_hba.conf
vi /var/lib/pgsql/12/data/pg_hba.conf

将 IPv4 对应的信息修改为如下所示
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

完成后重启 postgresql

systemctl restart postgresql-12

navicat 连接数据库，点击左下角的 测试链接，提示 “连接成功” 表示配置成功

安装GIS扩展

Pgsql 安装完成后并不能直接安装 postgis , 否则会提示

ERROR: could not open extension control file“/www/server/pgsql/share/extension/pg_trgm.control”

需要先在系统上安装PostGiS , 再在 Pgsql 上安装 PostGis

yum安装PostGIS扩展

由于PostGIS从2.0之后，要安装很多依赖组件，为了便于依赖组件的安装，先安装epel (Extra Packages for Enterprise Linux)

yum install epel-release

执行yum list postgis*，查看可用的postgis的包，选择和postgresql-12对应的最新版postgis33_12.x86_64

yum list postgis*

执行yum install postgis33_12.x86_64，安装postgis组件

yum install postgis33_12.x86_64

Pgsql安装PostGIS扩展

进入Psql控制台，安装 gis 扩展

# 安装 postgis 扩展
create extension postgis;
# 验证栅格类数据需要的raster扩展
create extension postgis_raster;
# 如果安装带有sfcgal，验证下三维sfcgal扩展
create extension postgis_sfcgal;


# 查看 postgis 版本
select postgis_version();

验证 PostGIS 扩展是否可用

# 验证 PostGIS 扩展
select ST_SetSRID(ST_Point(-108,30.741),4326),ST_GeomFromText('POINT(-106.51 29.741)',4326);

Psql常用命令

postgres=# \help // 获取SQL命令的帮助,同 \h
postgres=# \quit // 退出, 同 \q

postgres=#\l // 显示所有数据库
postgres=# \c [database_name] // 切换到指定数据库,相当于use
postgres=# \dt // 显示当前数据库中的所有表
postgres=# \d [table_name] // 显示当前数据库的指定表的表结构

postgres=# \du // 显示所有用户
postgres=# drop User 用户名 // 删除用户
postgres=# \password dlf // 重新设置用户dlf的密码,然后需要 \q退出后才生效

c:>psql exampledb < user.sql // 将user.sql文件导入到exampled数据库中
postgres=# \i aaa.sql // 将aaa.sql导入(到当前数据库)
postgres=# \h select // 精细显示SQL命令中的select命令的使用方法
postgres=# \conninfo // 显示当前数据库和连接信息
postgres=# \e // 进入记事本sql脚本编辑状态(输入批命令后关闭将自动在命令行中执行)
postgres=# \di // 查看索引(要建立关联)
postgres=# \prompt [文本] 名称 // 提示用户设定内部变数
postgres=# \encoding [字元编码名称] // 显示或设定用户端字元编码*可以将存储过程写在文本文件中aaa.sql,然后在psql状态下:
postgres=# \df // 查看所有存储过程（函数）
postgres=# \df+ name // 查看某一存储过程
postgres=# select version(); // 获取版本信息
postgres=# select usename from pg_user; // 获取系统用户信息

数据库

--*创建数据库：
create database [数据库名];

--*删除数据库：
drop database [数据库名];

schema

-- 选择 schema
set search_path to test_schema;

-- 删除 schema
DROP schema test_2;

--查看所有schema
SELECT * FROM information_schema.schemata;

查看表

--获取当前db中所有的表信息
select * from pg_tables;

--用户自定义的表，如果未经特殊处理，默认都是放在名为public的schema下
 select tablename from pg_tables where schemaname='public';
 
--列出某个数据库下的某个schema下面所有的表
select * from pg_tables where schemaname = 'test_schema';

--*显示表内容:
select * from student;
--计算数值
SELECT city, (temp_lo+temp_hi)/2 AS temp_avg, date FROM weather;
--选择查看
SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
--排序
SELECT * FROM weather ORDER BY city;
SELECT * FROM weather ORDER BY city, temp_lo;
--返回唯一值
SELECT DISTINCT city FROM weather ORDER BY city;

操作表

--*创建表：
-- create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;);
CREATE TABLE cities (
name       varchar(80),
location   point
);

--添加列;*在已有的表里添加字段：
-- alter table [表名] add column [字段名] [类型];
ALTER TABLE weather ADD country varchar(80);

--删除列
ALTER TABLE weather DROP country;
--*删除表中的字段：
alter table [表名] drop column [字段名];

--*在表中插入数据：
-- insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);

--更新行
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';

--删除行
DELETE FROM weather WHERE city = 'Hayward';

--*重命名一个表：
alter table [表名A] rename to [表名B];

*重命名一个字段： 
alter table [表名] rename column [字段名A] to [字段名B];

*给一个字段设置缺省值： 
alter table [表名] alter column [字段名] set default [新的默认值];

*去除缺省值： 
alter table [表名] alter column [字段名] drop default;
 
*修改表中的某行某列的数据：
update [表名] set [目标字段名]=[目标值] where [该行特征];

删除表

--清空表
DELETE FROM tablename;

--*删除一个表：
drop table [表名]; 

--*删除表中某行数据：
delete from [表名] where [该行特征];
delete from [表名];    // 删空整个表

批量设置srid

找到库中所有的包含 geometry 类型的字段，修改为支持 三维几何的类型

DO $$
DECLARE
    rec RECORD;
    alter_sql TEXT;
BEGIN
    FOR rec IN 
        SELECT 
            gc.f_table_schema, 
            gc.f_table_name, 
            gc.f_geometry_column,
            gc.srid
        FROM 
            geometry_columns gc
    LOOP
        -- 构建 ALTER TABLE 语句
        alter_sql := 'ALTER TABLE ' || quote_ident(rec.f_table_schema) || '.' || quote_ident(rec.f_table_name) ||
                     ' ALTER COLUMN ' || quote_ident(rec.f_geometry_column) ||
                     ' TYPE geometry(GeometryZ, ' || rec.srid || ') USING ST_Force3D(' || quote_ident(rec.f_geometry_column) || ');';
        
        -- 打印或执行 SQL 语句
        RAISE NOTICE '%', alter_sql; -- 仅显示生成的SQL语句
        -- EXECUTE alter_sql; -- 如果你想直接执行，请取消此行注释
    END LOOP;
END $$;

默认 Geometry 为二维几何类型，关与 三维几何类型的设置


# 创建三维几何类型
CREATE TABLE my_table (
    id serial PRIMARY KEY,
    geom geometry(GeometryZ, 4326) -- 假设你使用的SRID是4326，并且需要PointZ类型
);

# 修改字段为三维几何
ALTER TABLE public.boundary 
ALTER COLUMN geom 
TYPE geometry(GeometryZ, 4326) 
USING ST_Force3D(geom, 4326);

# 检查几何字段是否支持XYZ
SELECT f_geometry_column, type, srid 
FROM geometry_columns 
WHERE f_table_name = 'my_table';
注：查看返回的type列，若其值类似于POINTZ, LINESTRINGZ, POLYGONZ, 或者更一般化的GEOMETRYZ等，则表明该字段支持三维坐标。

其它sql


ST_AsGeoJSON(geom)
geom 转 geojson  格式

st_astext(geom)
转geo text格式，LINESTRING Z (114.01877137268475 22.53888901103222 .....)

ST_Force2D(geom::geometry)
强制三维转二维


使用表的属性 更复杂的 JSON 对象
SELECT json_build_object(
    'type', 'Feature',
    'geometry', ST_AsGeoJSON(geom)::json,
    'properties', json_build_object(
        'id', boundary_id,
        'task_id', task_id 
        -- 可以添加更多属性
    )
) AS feature
FROM boundary;

使用表的所有内容，属性 构建 FeatureCollection 对象
SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(
        json_build_object(
            'type', 'Feature',
            'geometry', ST_AsGeoJSON(geom)::json,
            'properties', json_build_object(
                'id', boundary_id,
								'task_id', task_id 
            )
        )
    )
) AS feature_collection
FROM boundary;

注

1.本文参考 Linux安装PostgreSQL和PostGIS 和 postgreSQL 操作 教程 ，感谢作者的分享

2.navicat 工具可 点击这里下载 Navicat15安装包.zip

发表回复

目录
← Index