安装
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-1.noarch.rpm
yum install epel-release
yum install postgresql10
yum install postgresql10-server
yum install postgis24_10
配置
/usr/pgsql-10/bin/postgresql-10-setup initdb
vim /var/lib/pgsql/10/data/pg_hba.conf
vim /var/lib/pgsql/10/data/postgresql.conf
list_addresses ='*';
port=5432;
max_connections=1000;
max_worker_processes=8;
max_parallel_workers_per_gather=8;
数据库操作
创建数据库
postgres:create database gv_place encoding = "UTF-8";
查询表GFGX_Y_DMK_DMSJ的关键字,属性,是否为空
postgres:SELECT col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull FROM pg_class as c,pg_attribute as a where c.relname = 'GFGX_Y_DMK_DMSJ' and a.attrelid = c.oid and a.attnum>0
导入tar数据
shell:pg_restore -h 192.168.4.32 -p 5432 -U postgres -d gv_place -v /home/zhangjx/placeName0118.tar
导出tar数据
数据导出到sql
pg_dump -h localhost -U postgres gv_geodata -t image_meta_tmp > image_meta_tmp.sql
从sql导出
psql -h 192.168.4.32 -U postgres -d postgres -f image_meta_tmp.sql
st_astext函数
SELECT *,st_astext("WZ") FROM "GFGX_Y_DMK_DMSJ" WHERE "DMMC" = '万山群岛' ORDER BY "DLBM" LIMIT 10
ST_AsGeoJSON - 将几何体作为GeoJSON元素返回。
查看postgis版本信息
select postgis_full_version();
删除数据库
REVOKE CONNECT ON DATABASE gv_place FROM public;
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='gv_place' AND pid<>pg_backend_pid();
drop database gv_place;