Centos7安装Greenplum 6.13.0和PostGIS

朱雀 2023-01-03 11:27 344阅读 0赞

PostGIS版本和Greenplum版本配套关系：  
PostGIS 2.0.5 for Greenplum 4.3.x  
PostGIS 2.1.5 for Greenplum 5.11.3  
PostGIS 2.1.5 for Greenplum 5.13.0?  
PostGIS 2.1.5 for Greenplum 5.16.0  
PostGIS 2.1.5 for Greenplum 5.18.0  
PostGIS 2.5.4 for Greenplum 6.13.0  
......  
详细配套关系，可以通过下面的下载地址获取对应版本的配套PostGIS

Greenplum下载地址（选择需要的版本）：  
https://network.pivotal.io/products/pivotal-gpdb\#/releases/797473  
对应PostGIS下载：  
https://network.pivotal.io/products/pivotal-gpdb\#/releases/797473/file\_groups/3133

也可以通过github下载（选择需要的版本）：  
https://github.com/greenplum-db/gpdb/releases

注意：PostGIS必须选择Greenplum版本对应的PostGIS版本，否则可能会安装失败

1.Centos7环境准备和初始化配置  
待补充

2.Greenplum 6.13.0安装  
待补充

3.PostGIS插件安装

1.安装PostGIS插件  
\#su - gpadmin  
$ gppkg -i postgis-2.1.5+pivotal.2-gp5-rhel7-x86\_64.gppkg   
......  
20210107:21:42:31:029753 gppkg:kafka-single:gpadmin-\[INFO\]:-postgis-2.1.5+pivotal.2-gp5-rhel7-x86\_64.gppkg successfully installed.  
提示插件安装成功

注意：如果安装时提示“MASTER\_DATA\_DIRECTORY”未配置的错误，请检查gpadmin用户环境变量设置是否正确  
vi /home/gpadmin/.bash\_profile  
vi /home/gpadmin/bashrc

末尾是否追加了下面内容：  
source /usr/local/greenplum-db-5.13.0/greenplum\_path.sh  
export MASTER\_DATA\_DIRECTORY=/data1/gpdata/master/gpseg-1

2.执行postgis sql安装脚本  
在对应的数据库执行postgis install sql  
语法：  
psql -h \[ip\] -p \[port\] -d \[db\_name\] -f /usr/local/greenplum-db-5.13.0/share/postgresql/contrib/postgis-2.1/install/postgis.sql  
psql -h \[ip\] -p \[port\] -d \[db\_name\] -f /usr/local/greenplum-db-5.13.0/share/postgresql/contrib/postgis-2.1/install/spatial\_ref\_sys.sql

执行举例：  
psql -h 127.0.0.1 -p 5432 -d postgres -f /usr/local/greenplum-db-5.13.0/share/postgresql/contrib/postgis-2.1/install/postgis.sql  
psql -h 127.0.0.1 -p 5432 -d postgres -f /usr/local/greenplum-db-5.13.0/share/postgresql/contrib/postgis-2.1/install/spatial\_ref\_sys.sql

sql安装完成之后，可以在相应的数据库中看到如下表 spatial\_ref\_sys 和另外2张视图(geography\_columns, geometry\_columns)， spatial\_ref\_sys 存储着合法的空间坐标系统：  
SELECT srid,auth\_name,proj4text FROM spatial\_ref\_sys limit 5;

3824    EPSG    +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no\_defs   
3889    EPSG    +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no\_defs   
4001    EPSG    +proj=longlat +ellps=airy +no\_defs   
4003    EPSG    +proj=longlat +ellps=aust\_SA +no\_defs   
4005    EPSG    +proj=longlat +a=6377492.018 +b=6356173.508712696 +no\_defs

创建测试表  
CREATE TABLE geometry\_test01 ( id int4, name varchar(255) );  
SELECT AddGeometryColumn ('geometry\_test01', 'geom', 4326, 'POINT', 2);  
SELECT \* from geometry\_test01;

CREATE TABLE geometry\_test02 ( id int4 primary key, name varchar(255), geom geometry );  
SELECT \* from geometry\_test02;

插入测试数据  
INSERT INTO geometry\_test01 (id, name, geom) VALUES (1, 'point',     ST\_GeomFromText('POINT(300 600.3417619204)',4326));  
INSERT INTO geometry\_test01 (id, name, geom) VALUES (2, 'point',     ST\_GeomFromText('POINT(-100.233 500.3417619204)',4326));  
SELECT \* from geometry\_test01;

INSERT INTO geometry\_test02 (id, name, geom) VALUES (1, 'point',     ST\_GeomFromText('POINT(300 600.3417619204)',4326));  
INSERT INTO geometry\_test02 (id, name, geom) VALUES (2, 'point',     ST\_GeomFromText('POINT(-100.233 500.3417619204)',4326));  
INSERT INTO geometry\_test02 (id, name, geom) VALUES (3, '3dline',    'LINESTRING(1 1 1,5 5 5,7 7 5)');  
INSERT INTO geometry\_test02 (id, name, geom) VALUES (4, '3dsquare',  'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))');  
SELECT \* from geometry\_test02;