原作者：彭冲
之前写过openGauss支持oracle_fdw和mysql_fdw插件的文章：《opengauss1.0.1支持oracle_fdw和mysql_fdw》
本文介绍openGauss如何支持postgis扩展
本文参考链接：https://gitee.com/opengauss/openGauss-server/tree/master/third_party/dependency/postgis
系统环境
Centos7.6 x86_64
openGauss源码编译
openGauss源码编译的过程请参考文章：《抢鲜体验2：openGauss从源码到主备》
编译postgis(2.4.2)
《安装依赖》：http://postgis.net/docs/manual-2.4/postgis_installation.html#install_requirements
设置环境变量
su - omm
export GAUSSHOME=/opt/og
export PATH=GAUSSHOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbinexportLDLIBRARYPATH=GAUSSHOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin
export LD_LIBRARY_PATH=GAUSSHOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbinexportLDLIBRARYPATH=GAUSSHOME/lib:/usr/local/lib:/usr/local/lib64:/usr/lib64
export toolhome=GAUSSHOME/pggis_tools
下载postgis及相关依赖包
cd /opt/openGauss-server/third_party/dependency/postgis
wget https://opengauss.obs.cn-south-1.myhuaweicloud.com/dependency/postgis-xc-master-2020-09-17.tar.gz
tar zxvf postgis-xc-master-2020-09-17.tar.gz
mv postgis-xc-master postgis-xc
先编译安装postgis依赖包
cd postgis-xc/proj-4.9.2
chmod +x ./configure
./configure --prefix=GAUSSHOME/pggis_tools/proj
make && make install
ldd $GAUSSHOME/pggis_tools/proj/lib/libproj.so
cd …/geos-3.6.2
chmod +x ./configure
./configure --prefix=$GAUSSHOME/pggis_tools/geos
make && make install
ldd $GAUSSHOME/pggis_tools/geos/lib/libgeos.so
cd …/json-c-json-c-0.12.1-20160607
chmod +x ./configure
./configure --prefix=$GAUSSHOME/pggis_tools/json
make && make install
cd …/libxml2-2.7.1
chmod +x ./configure
./configure --prefix=$GAUSSHOME/pggis_tools/libxml2
make && make install
ldd /opt/og/pggis_tools/libxml2/lib/libxml2.so
cd …/gdal-1.11.0
chmod +x ./configure
./configure --prefix=$GAUSSHOME/pggis_tools/gdal
make && make install
ldd /opt/og/pggis_tools/gdal/lib/libgdal.so
应用patch包，否则编译postgis会报错
cd $CODE_BASE
cd /opt/openGauss-server/
patch -p1 < third_party/dependency/postgis/postgis.patch
缺失两个文件及需要固定的路径的处理
mkdir -p /home/carrot/data/openGauss-server/third_party/buildtools/gcc/res/lib64
mkdir -p /home/carrot/data/openGauss-server/third_party/buildtools/gcc/res/lib64
chmod 755 /home/carrot/data/openGauss-server/third_party/buildtools/gcc/res/lib64
cd /opt/openGauss-server/
cp ./binarylibs/buildtools/centos7.6_x86_64/gcc8.2/gcc/lib64/libstdc++.la /home/carrot/data/openGauss-server/third_party/buildtools/gcc/res/lib64
cp ./binarylibs/buildtools/centos7.6_x86_64/gcc8.2/gcc/lib64/libstdc++.so /home/carrot/data/openGauss-server/third_party/buildtools/gcc/res/lib64
如果不加上面的处理make可能出现下面的错误（后续更新的版本可能没这个bug了）
/bin/grep: /home/carrot/data/openGauss-server/third_party/buildtools/gcc/res/lib/…/lib64/libstdc++.la: No such file or directory
/bin/sed: can’t read /home/carrot/data/openGauss-server/third_party/buildtools/gcc/res/lib/…/lib64/libstdc++.la: No such file or directory
libtool: error: ‘/home/carrot/data/openGauss-server/third_party/buildtools/gcc/res/lib/…/lib64/libstdc++.la’ is not a valid libtool archive
make[1]: *** [liblwgeom.la] Error 1
make: *** [all] Error 1
编译postgis
cd /opt/openGauss-server/third_party/dependency/postgis/postgis-xc/postgis-2.4.2
./configure --prefix=toolhome/pggis2.4.2 \
--with-pgconfig=GAUSSHOME/bin/pg_config
–with-projdir=toolhome/proj \
--with-geosconfig=toolhome/geos/bin/geos-config
–with-jsondir=toolhome/json \
--with-xml2config=toolhome/libxml2/bin/xml2-config
–with-raster
–with-gdalconfig=toolhome/gdal/bin/gdal-config \
--with-topology \
--without-address-standardizer \
CFLAGS="-fPIC -O2 -fpermissive -DPGXC -pthread -D_THREAD_SAFE -D__STDC_FORMAT_MACROS -DMEMORY_CONTEXT_CHECKING -w -ICODE_BASE/contrib/postgis/ -IBINARYLIBS/dependency/____/cjson/comm/include/ -IBINARYLIBS/dependency//openssl/comm/include/ -IBINARYLIBS/dependency/____/kerberos/comm/include/ -IBINARYLIBS/dependency//libobs/comm/include/" CPPFLAGS="-ICODEBASE/contrib/postgis/−ICODE_BASE/contrib/postgis/ -ICODEBASE/contrib/postgis/−IBINARYLIBS/dependency//cjson/comm/include -I$BINARYLIBS/dependency//libobs/comm/include/ -fpermissive -w -DMEMORY_CONTEXT_CHECKING -D__STDC_FORMAT_MACROS" CC=g++ -q
make && make install
拷贝动态库文件及扩展文件到openGauss相应位置
cp $toolhome/json/lib/libjson-c.so.2 $GAUSSHOME/lib/libjson-c.so.2
cp $toolhome/geos/lib/libgeos_c.so.1 $GAUSSHOME/lib/libgeos_c.so.1
cp $toolhome/proj/lib/libproj.so.9 $GAUSSHOME/lib/libproj.so.9
cp $toolhome/geos/lib/libgeos-3.6.2.so $GAUSSHOME/lib/libgeos-3.6.2.so
cp $toolhome/pggis2.4.2/lib/liblwgeom-2.4.so.0 $GAUSSHOME/lib/liblwgeom-2.4.so.0
cp $PGGIS_DIR/postgis-xc/postgis-2.4.2/postgis.control $GAUSSHOME/share/postgresql/extension/
cp $PGGIS_DIR/postgis-xc/postgis-2.4.2/postgis–2.4.2.sql $GAUSSHOME/share/postgresql/extension/
gsql连接创建扩展并测试
create extension postgis;
create extension postgis_topology;
create extension fuzzystrmatch;
create extension postgis_tiger_geocoder;
SELECT na.address,na.streetname,na.streettypeabbrev,na.zip FROM normalize_address(‘1 Devonshire Place,Boston, MA 02109’) as na;
mogdb
opengauss
