入门教程

准备工作

在使用空间数据库之前，我们需要了解用户及其权限、数据库的字符集和表空间等数据库基础内容。如果您已具备相关基础，可以略过本节。

创建用户

在安装 openGauss/PostgreSQL 数据库时，会自动创建一个初始用户，初始用户具有系统的最高权限，能够执行所有的操作。这里我们通过命令行登录到数据库，并创建一个新的系统管理员用户。

# 登录到数据库
--openGauss
gsql -d postgres
--PostgreSQL
psql -d postgres

# 创建新用户 yukontest
--openGauss
CREATE USER yukontest WITH SYSADMIN password "Bigdata@123";
--PostgreSQL
CREATE USER yukontest WITH SUPERUSER password 'Bigdata@123' ;

如果新创建的用户不带有 SYSADMIN / SUPERUSER 属性，很多操作都会没有权限。关于用户和权限的相关问题可以参考 openGauss 文档 、PostgreSQL ;

创建表空间

通过使用表空间，管理员可以控制一个数据库安装的磁盘布局。这样有以下优点：

  • 如果初始化数据库所在的分区或者卷空间已满，又不能逻辑上扩展更多空间，可以在不同的分区上创建和使用表空间

  • 表空间允许管理员根据数据库对象的使用模式安排数据位置，从而提高性能

    • 一个频繁使用的索引可以放在性能稳定且运算速度较快的磁盘上，比如一种固态设备

    • 一个存储归档的数据，很少使用的或者对性能要求不高的表可以存储在一个运算速度较慢的磁盘上

  • 管理员通过表空间可以设置占用的磁盘空间。用以在和其他数据共用分区的时候，防止表空间占用相同分区上的其他空间

这里我们使用刚才创建的 yukontest 用户来创建一个表空间

# 登录到数据库(openGauss为例)
gsql -d postgres -U yukontest -W Bigdata@123

# 创建表空间 yukonspace
CREATE TABLESPACE yukonspace LOCATION '/home/omm/data';

注意：目录 /home/omm/data 必须已经存在且具有可访问权限。

创建数据库

创建一个新的数据库。缺省情况下新的数据库将通过复制标准系统数据库 template0 来创建。且仅支持使用 template0 来创建

Note

  1. 只有拥有 CREATEDB 权限的用户才可以创建新数据库，系统管理员默认拥有此权限。

  2. 不能在事务块中执行创建数据库语句。

  3. 在创建数据库过程中，出现类似 Permission denied 的错误提示，可能是由于文件系统上数据目录的权限不足。出现类似 No space left on device 的错误提示，可能是由于磁盘满引起的。

如果您在数据库中存储中文字符，推荐选择 GBK 编码，当然您也可以选择 UTF8 编码。

这里我们创建一个 yukontutorial 数据库来进行后面的学习。

# 使用 yukontest 用户连接到 postgres 数据库(openGauss为例)
gsql -d postgres -U yukontest -W Bigdata@123

# 创建数据库 yukontutorial ，并指定数据库编码为 'UTF8' 表空间为上面创建好的 yukonspace
CREATE DATABASE yukontutorial ENCODING='UTF8' TABLESPACE=yukonspace;

有关更多创建数据库的操作可以参考 openGauss 的文档

创建扩展

安装一个扩展,Yukon目前共提供 postgis、postgis_raster、postgis_sfcgal、yukon_geomodel、yukon_geogridcoder五个扩展，postgis为基础扩展，被其他扩展依赖，因此安装其他扩展之前请先安装基础扩展;

语法

PostgreSQL

CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ] [ SCHEMA schema_name ]
    [ VERSION version ]
    [ CASCADE ]

openGauss/GaussDB

CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ] [ SCHEMA schema_name ]
             [ VERSION version ]
             [ FROM old_version ]

参数说明

IF NOT EXISTS

如果系统已经存在一个同名的扩展，不会报错。这种情况下会给出一个提示。请注意该参数不保证系统存在的扩展和现在脚本创建的扩展相同。

extension_name

将被安装扩展的名字。

schema_name

扩展的实例被安装在该模式下，扩展的内容可以被重新安装。指定的模式必须已经存在，如果没有指定，扩展的控制文件也不指定一个模式，这样将使用默认模式。

Note

注意： 扩展不认为它在任何模式里面：扩展在一个数据库范围内的名字是不受限制的，但是一个扩展的实例是属于一个模式的。

version

安装扩展的版本，可以写为一个标识符或者字符串.默认的版本在扩展的控制文件中指定。

CASCADE

自动安装此扩展所依赖但尚未安装的任何扩展。它们的依赖项同样以递归方式自动安装。该子句（如果给定）适用于以这种方式安装的所有扩展。语句的其他选项不适用于自动安装的扩展;特别是，始终选择其默认版本

old_version

当你想升级安装“old style” 模块中没有的内容时,你必须指定FROM old_version。这个选项使CREATE EXTENSION 运行一个安装脚本将新的内容安装到扩展中，而不是创建一个新的实体.注意SCHEMA指定了包括这些已存在实体的模式。

示例

--在当前数据库安装postgis扩展
CREATE EXTENSION postgis;

--在当前数据库安装postgis扩展到指定模式下
CREATE EXTENSION postgis with schema schema_name;

Note

一般情况下创建扩展不需要指定SCHEMA，openGauss/GaussDB非初始化用户扩展时不支持public，使用 [ WITH ] [ SCHEMA schema_name ] 参数添加扩展，如果需要扩展到public下，必须使用数据库初始化用户进行扩展，解决权限有关问题； GaussDB有yukon_geogridcoder、yukon_geomodel、yukon_vector_pyramid三个试用模块，创建试用模块的扩展需要启用enable_extension参数，可尝试在数据库中执行”set enable_extension=on”。

使用矢量数据

创建 postgis 扩展

在命令行使用 yukontest 用户连接到 yukontutorial 数据库：

# 连接到数据库(openGauss为例)
gsql -d yukontutorial -U yukontest -W Bigdata@123

--创建 postgis 扩展
CREATE EXTENSION postgis;

显示如下信息，则表示创建成功：

CREATE EXTENSION

写入数据

新建表格 global_points，并写入点数据：

-- 创建带geometry列的表
CREATE TABLE global_points (id SERIAL PRIMARY KEY,name VARCHAR(64),location geometry(POINT,3857));

-- 写入10个点对象
INSERT INTO global_points (name, location) VALUES ('Town1', ST_GeomFromEWKT('SRID=3857;POINT (12057531.405152922 4096788.3009192282)') );
INSERT INTO global_points (name, location) VALUES ('Town2', ST_GeomFromEWKT('SRID=3857;POINT (12057879.323089447 4096794.06898925)') );
INSERT INTO global_points (name, location) VALUES ('Town3', ST_GeomFromEWKT('SRID=3857;POINT (12057241.150712628 4096835.404659481)') );
INSERT INTO global_points (name, location) VALUES ('Town4', ST_GeomFromEWKT('SRID=3857;POINT (12057396.37461059 4096885.372392862)') );
INSERT INTO global_points (name, location) VALUES ('Town5', ST_GeomFromEWKT('SRID=3857;POINT (12056940.710538926 4096886.9123589676)') );
INSERT INTO global_points (name, location) VALUES ('Town6', ST_GeomFromEWKT('SRID=3857;POINT (12058128.24460281 4096938.23117457)') );
INSERT INTO global_points (name, location) VALUES ('Town7', ST_GeomFromEWKT('SRID=3857;POINT (12058378.134595742 4097081.4360874156)') );
INSERT INTO global_points (name, location) VALUES ('Town8', ST_GeomFromEWKT('SRID=3857;POINT (12058636.607321415 4097235.1257181372)') );
INSERT INTO global_points (name, location) VALUES ('Town9', ST_GeomFromEWKT('SRID=3857;POINT (12058917.488660585 4097395.7746241256)') );
INSERT INTO global_points (name, location) VALUES ('Town10', ST_GeomFromEWKT('SRID=3857;POINT (12059180.102471316 4097548.7255362184)') );

空间查询

查询距离指定点500米范围内的对象：

SELECT * FROM global_points WHERE ST_DWithin(location, ST_GeomFromEWKT('SRID=3857;POINT (12058378 4097081)'), 500);

输出结果：

 id | name  |                      location
----+-------+----------------------------------------------------
  6 | Town6 | 0101000020110F000046C9D307C2FF6641D920971DD5414F41
  7 | Town7 | 0101000020110F0000BB9B4E44E1FF664162B6D1B71C424F41
  8 | Town8 | 0101000020110F0000522D6F93010067412C88179069424F41
(3 rows)

栅格数据

创建 postgis_raster 扩展

在命令行使用 yukontest 用户连接到 yukontutorial 数据库：

# 连接到数据库(openGauss为例)
gsql -d yukontutorial -U yukontest -W Bigdata@123

--创建 postgis_raster 扩展
CREATE EXTENSION postgis_raster;

显示如下信息，则表示创建成功：

CREATE EXTENSION

创建成功后，退出 gsql 控制台。

导入数据

用 raster2pgsql 工具导入范例数据 hillshade.tif 数据(openGauss为例)：

raster2pgsql -s 0 /SampleData/hillshade.tif -t 256x256 | gsql -d yukontutorial

导入成功后，再次登录数据库，刷新元数据信息：

# 连接到数据库
gsql -d yukontutorial -U yukontest -W Bigdata@123
# 刷新元数据信息
select AddRasterConstraints('hillshade', 'rast');
# 查看信息
select * from raster_columns where r_table_name='hillshade';

使用栅格数据

提取指定像素的值：

select ST_Value(rast, 1, 10, 10, true) from hillshade ;