PostgreSQL与PostGIS的基础入门


PostgreSQL版本:9.6.12

PostGIS版本:2.4.6

一、概述

1.1 PostgreSQL概述

PostgreSQL是一个功能强大的对象关系型数据库管理系统(ORDBMS)。用于安全地存储数据,支持最佳做法,并允许在处理请求时检索它们。

PostgreSQL的特点如下:

PostgreSQL支持SQL的许多功能,例如复杂SQL查询、SQL子选择、外键、触发器、视图、事务、多进程并发控制、流式复制、热备等。

支持更多的数据类型,除了支持SQL基本的数据类型以外,还支持uuid,monetary, enumerated,geometric,binary,network address,bit string,text search,xml,json,array,composite和range数据类型,以及一些内部对象标识和日志位置类型。

可以安装多个扩展以向PostgreSQL添加附加功能,比如支持PostGIS扩展。

继承表,分区表就是依赖于继承实现的。

搜索索引:全文搜索索引足以应对简单场景;丰富的索引类型,支持函数索引,条件索引。

支持OLAP:citus分布式插件,ANSI SQL兼容,窗口函数,CTE,CUBE等高级分析功能,任意语言写UDF。

1.2 PostGIS概述

PostGIS作为新一代空间数据存储标准模型,将空间地理信息数据结构规范为关系型数据库可以承载的sp模式(simple features),这样,使得之前门槛颇高的gis空间数据存储模式变得通俗易懂、简单明了。

最重要的只要接触过SQL语言,就可以利用PostGIS的SQL语法便捷的操纵装载着空间信息的数据框(数据表),这些二维表除了被设定了一个特殊的空间地理信息字段(带有空间投影信息、经纬度信息等)之外,与主流数据管理系统所定义的各种字段并无两样。

PostGIS安装不仅依赖于PostgreSQL,还依赖于很多插件:

GEOS几何对象库

GDAL栅格功能

LibXML2

LIBJSON

PostGIS的特点如下:

PostGIS支持所有的空间数据类型,这些类型包括:点(POINT)、线(LINESTRING)、面(POLYGON)、多点 (MULTIPOINT)、多线(MULTILINESTRING)、多面(MULTIPOLYGON)和几何集合 (GEOMETRYCOLLECTION)等。PostGIS支持所有的对象表达方法,比如WKT和WKB。

PostGIS支持所有的数据存取和构造方法,如GeomFromText()、AsBinary(),以及GeometryN()等。

PostGIS提供简单的空间分析函数(如Area和Length)同时也提供其他一些具有复杂分析功能的函数,比如Distance。

PostGIS提供了对于元数据的支持,如GEOMETRY_COLUMNS和SPATIAL_REF_SYS。同时,PostGIS也提供了相应的支持函数,如AddGeometryColumn和DropGeometryColumn。

PostGIS提供了一系列的二元谓词(如Contains、Within、Overlaps和Touches)用于检测空间对象之间的空间关系,同时返回布尔值来表征对象之间符合这个关系。

PostGIS提供了空间操作符(如Union和Difference)用于空间数据操作。

数据库坐标变换

球体长度运算

三维的几何类型

空间聚集函数

栅格数据类型

二、安装

如何安装PostgreSQL + PostGIS请参考:《如何安装PostgreSQL + PostGIS请点击》。

PostgreSQL与PostGIS版本的依赖关系可点击:http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS

三、基本使用

3.1 PostgreSQL

3.1.1 psql登陆

# 切换用户

su postgres

# 执行psql命令

psql

默认连接postgres数据库,会出现“postgres=#”的字符串,执行效果如下图所示:

3.1.2 创建数据库

创建testdb数据库:

CREATE DATABASE testdb;

3.1.3 复制数据库

创建demo数据库,内容与testdb数据库一致:

CREATE DATABASE demo TEMPLATE=testdb;

3.1.4 删除数据库

删除demo数据库:

drop database demo;

3.1.5 查看数据库列表

执行\l来查看数据库列表:

\l

执行效果如下图所示:

3.1.6 连接数据库

连接数据库有两种方式:

psql模式内连接

假如连接testdb数据库,执行以下代码:

postgres=# \c testdb

执行效果如下图所示:

psql模式外连接

postgres=# \q # 退出psql模式

bash-4.2$ psql -d testdb

执行效果如下图所示:

3.1.7 创建表

创建一个表,用来存储城市的最高及最低温度。代码如下所示:

CREATE TABLE location_city (

name varchar(80),

location point

);

3.1.8 列出数据表

在testdb数据库下,执行\d命令列出数据表:

\d

执行效果如下:

3.1.9 查看表结构

使用\d [tableName]命令来查看表结构,比如查看location_city表:

\d location_city;

执行效果如下图所示:

3.1.10 批量插入数据

批量插入数据有两种常用的方式:

多values方式

INSERT INTO location_city VALUES (‘San Francisco’, ‘(-194.0, 53.0)’), (‘New York’, ‘(-184.0, 43.0)’), (‘北京’, ‘(-94.0, 133.0)’), (‘Los Angeles’, ‘(-297.0, 63.0)’), (‘Chicago’, ‘(-94.0, 283.0)’);

copy方式

首先,在/tmp创建一个文件location_city.csv,插入如下数据,其中第一行的内容为空:

“Denver”,”(123,34)”

“Fort Worth”,”(-23,21)”

“上海”,”(45,66)”

执行如下代码:

COPY location_city FROM ‘/tmp/location_city.csv’ delimiter ‘,’ csv header;

执行结果如下图所示:

copy扩展

copy location_city to ‘/tmp/location_city_out.csv’ delimiter ‘,’ csv header encoding ‘GBK’;

打开该文件后效果:

通过window的excel查看,正常。

用linux的vim命令查看,中文乱码。解决方案:

解决linux下查看文件乱码问题:

vim ~/.vimrc

set encoding=utf-8 fileencodings=ucs-bom,utf-8,cp936

查看文件编码:

# 在Vim中可以直接查看文件编码

:set fileencoding

3.1.11 查询数据

查询location_city表中name与position字段的所有数据:

SELECT * FROM location_city;

3.1.12 更新数据

使用UPDATE … SET …命令来更新location_city表的数据:

UPDATE location_city SET location = ‘(52,53)’ WHERE name = ‘Fort Worth’;

3.1.13 删除数据

删除指定数据,比如删除name为“San Francisco”的数据:

DELETE FROM location_city WHERE name = ‘San Francisco’;

清空cities表数据:

DELETE FROM location_city;

# 或者

TRUNCATE location_city;

3.2 PostGIS

PostgreSQL数据库安装PostGIS扩展,数据库将可以进行空间数据管理、数量测量与几何拓扑分析。

3.2.1 在testdb数据库下安装PostGIS扩展

安装PostGIS扩展:

CREATE EXTENSION postgis;

验证PostGIS扩展是否安装成功:

SELECT postgis_full_version();

执行效果如下图所示:

还可以安装其它的一些扩展:

– Enable Topology

CREATE EXTENSION postgis_topology;

– Enable PostGIS Advanced 3D– and other geoprocessing algorithms

– sfcgal not available with all distributions

CREATE EXTENSION postgis_sfcgal;

– fuzzy matching needed for Tiger

CREATE EXTENSION fuzzystrmatch;

– rule based standardizer

CREATE EXTENSION address_standardizer;

– example rule data set

CREATE EXTENSION address_standardizer_data_us;

– Enable US Tiger Geocoder

CREATE EXTENSION postgis_tiger_geocoder;

可使用\dx命令查看已安装的扩展。

3.2.2 创建空间数据表

先建立一个常规的表存储

CREATE TABLE cities(id smallint,name varchar(50));

添加一个空间列,用于存储城市的位置。

习惯上这个列叫做 “the_geom”。它记录了数据的类型(点、线、面)、有几维(这里是二维)以及空间坐标系统。这里使用 EPSG:4326 坐标系统:

SELECT AddGeometryColumn (‘cities’, ‘the_geom’, 4326, ‘POINT’, 2);

3.2.3 插入数据到空间表

批量插入三条数据:

INSERT INTO cities(id, the_geom, name) VALUES (1,ST_GeomFromText(‘POINT(-0.1257 51.508)’,4326),‘London, England’), (2,ST_GeomFromText(‘POINT(-81.233 42.983)’,4326),‘London, Ontario’), (3,ST_GeomFromText(‘POINT(27.91162491 -33.01529)’,4326),‘East London,SA’);

3.2.4 简单查询

标准的PostgreSQL语句都可以用于PostGIS,这里我们查询cities表数据:

SELECT * FROM cities;

执行效果如下图所示:

这里的坐标是无法阅读的 16 进制格式。要以WKT文本显示,使用ST_AsText(the_geom)或ST_AsEwkt(the_geom)函数。也可以使用ST_X(the_geom)和ST_Y(the_geom)显示一个维度的坐标:

SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;

执行效果如下图所示:

3.2.5 空间查询

以米为单位并假设地球是完美椭球,上面三个城市相互的距离是多少?

执行以下代码计算距离:

SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;

执行效果如下图所示:

四、总结

本文首先说明了PostgreSQL与PostGIS的基本概念,又罗列了两者的yum安装教程及版本兼容关系,最后讲解了一下PostgreSQL的简单使用及PostGIS的空间查询的简单示例。

关于PostgreSQL的一些官方学习资料如下,请参考:

https://www.postgresql.org/files/documentation/pdf/9.6/postgresql-9.6-A4.pdf

https://wiki.postgresql.org/wiki/9.1第二章

https://wiki.postgresql.org/wiki/Main_Page

易百教程:https://www.yiibai.com/postgresql/postgresql-datatypes.html

中文手册:http://www.postgres.cn/docs/9.6/index.html

Postgres中文社区:http://www.postgres.cn/v2/home

关于PostGIS的官方学习资料如下,请参考:

英文官方资料:http://www.postgis.net/stuff/postgis-2.4.pdf

中文社区资料:http://www.postgres.cn/docs/PostGis-2.2.0dev_Manual.pdf

  大连哪家医院做包皮手术好 http://xz.lnbohaink.com/

  大连正规的男科医院 http://mobile.bhnkyy.net/

  专业包皮医院 http://mobile.62671288.net/

转载自:https://blog.csdn.net/qq_42894764/article/details/88970142

You may also like...