之前写过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/sbin
export LD_LIBRARY_PATH=$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 -I$CODE_BASE/contrib/postgis/ -I$BINARYLIBS/dependency/____/cjson/comm/include/ -I$BINARYLIBS/dependency/____/openssl/comm/include/ -I$BINARYLIBS/dependency/____/kerberos/comm/include/ -I$BINARYLIBS/dependency/____/libobs/comm/include/" CPPFLAGS="-I$CODE_BASE/contrib/postgis/ -I$BINARYLIBS/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;

postgis 2.4.x更新日志

PostGIS 2.4.10dev
2022/04/24

• Bug Fixes and Enhancements *

• #4871, TopoGeometry::geometry cast returns NULL for empty
  TopoGeometry objects (Sandro Santilli)
• #4757, Handle line collapse due to snap-to-existing node (Sandro Santilli)
• #4769, Fix segfault in st_addband (Raúl Marín)
• #4727, Fix geocentric bounding box computation for rare case (Paul Ramsey)
• #4326, Fix CircularPolygon area calculation (Paul Ramsey)
• #4968, Update autogen.sh to handle autoconf 2.71 issues (Raúl Marín, Regina Obe)
• #4971, Cunit fixes for newer CUnit (Raúl Marín, Regina Obe)
• #5140, regress cleanly against GEOS 3.7-3.8, GDAL 3.4,
  fix address_standardizer install
  and allow to install via pkg-config (Regina Obe)

PostGIS 2.4.9
2020/08/14

• Bug Fixes and Enhancements *

• #4709, Fix crash in adding edge to corrupted topology, (Sandro Santilli)
• Handle non-closed edge rings by human readable error (Sandro Santilli)
• #4706, Fix crash in ST_ChangeEdgeGeom on corrupted topology (Sandro Santilli)
• #4480, Geography Distance inconsistent with Intersects (Paul Ramsey)
• #4481, Improve libprotobuf detection for old systems (Paul Ramsey)
• #4475, Avoid reading into empty ptarra (Paul Ramsey)
• #4492, Fix ST_Simplify ignoring the value of the 3rd parameter (Raúl Marín)
• #4494, Fix ST_Simplify output having an outdated bbox (Raúl Marín)
• #4493, Fix ST_RemoveRepeatedPoints output having an outdated bbox (Raúl Marín)
• #4495, Fix ST_SnapToGrid output having an outdated bbox (Raúl Marín)
• #4506, Remove tolerance in point-in-ring stabline tests (Paul Ramsey)
• #4518, Backport system views to make upgrade to PostGIS3 cleaner (Paul Ramsey)
• #4519, Fix getSRIDbySRS crash (Raúl Marín)
• #4534, Fix leak in lwcurvepoly_from_wkb_state (Raúl Marín)
• #4536, Fix leak in lwcollection_from_wkb_state (Raúl Marín)
• #4537, Fix leak in WKT collection parser (Raúl Marín)
• #4546, Fix ST_ConcaveHull under FDW (Raúl Marín)
• #4547, Fix AddRasterConstraints handling of empty tables (Sandro Santilli)
• #4549, Fix schema qualification of internal types (Raúl Marín)
• #4546, Fix PLPGSQL functions missing the schema qualification (Raúl Marín)
• #4588, Fix update when st_union(geometry) doesn’t exist (Raúl Marín)
• #4621, Prevent stack overflow when parsing WKB (Raúl Marín)
• #4646, Fix gserialized_cmp incorrect comparison (dkvash)
• #4652, Fix several memory related bugs in ST_GeomFromGML (Raúl Marín)
• #4661, Fix access to spatial_ref_sys with a non default schema (Raúl Marín)
• #4699, crash on null input to ST_Union(raster, otherarg) (Jaime Casanova, 2ndQuadrant)
• #4691, Fix segfault during gist index creation with empty geometries (Raúl Marín)

PostGIS 2.4.8
2019/08/11

• Bug Fixes and Enhancements *

• #4361, Fix postgis_type_name with (GEOMETRYM,3) (Matt Bretl)
• #4326, Fix circular arc distance calculation (Paul Ramsey)
• #4388, AddRasterConstraints: Ignore NULLs when generating constraints (Raúl Marín)
• #4327, Avoid pfree’ing the result of getenv (Raúl Marín)
• #4406, Throw on invalid characters when decoding geohash (Raúl Marín)
• #4440, Internal type lookups fail over FDW (Paul Ramsey)
• #4445, Fix bug in lwgeom_le (Raúl Marín)
• #4466, Fix undefined behaviour in _postgis_gserialized_stats (Raúl Marín)
• #4209, Handle NULL geometry values in pgsql2shp (Paul Ramsey)
• #4419, Use protobuf version to enable/disable mvt/geobuf (Paul Ramsey)
• #4437, Handle POINT EMPTY in shape loader/dumper (Paul Ramsey)
• #4461, ST_AsTWKB doesn’t always remove duplicate points (Nicklas Avén)
• #4470, ST_GeomFromGeoJSON crash on empty rings (Darafei Praliaskouski)
• #4420, update path does not exists for address_standardizer extension (Regina Obe)

PostGIS 2.4.7
2019/03/11

• Bug Fixes and Enhancements *

• #4139, Make mixed-dimension ND index build tree correctly.
  WARNING: REINDEX your ND index on tables that have records with different M/Z
  dimensions for &&& operator to work predictably.
  (Darafei Praliaskouski, Arthur Lesuisse, Andrew Gierth, Raúl Marín)
• #4273, Tighter parsing of WKT (Paul Ramsey)
• #4267, Enable Proj 6 deprecated APIs (Darafei Praliaskouski, Raúl Marín)
• #4290, More robust distance calculations in geography (Paul Ramsey)
• #4292, ST_AsMVT: parse JSON numeric values with decimals as doubles (Raúl Marín)
• #4289, ST_AsMVTGeom: Transform coordinates space before clipping (Raúl Marín)
• #4275, Avoid passing a NULL pointer to GEOSisEmpty (Raúl Marín)
• #4296, Use server_version_num instead of parsing version() (Raúl Marín)
• #4298, Geodetic tolerance issue in 32-bit (Paul Ramsey)
• #4283, Avoid final point duplicates for circle stroking (Paul Ramsey)
• #4313, #4307, PostgreSQL 12 compatibility (Laurenz Albe, Raúl Marín)
• #4290, Schema qualify geometry casts in raster functions (Regina Obe)
• #4086, Constraint violation loading tiger_data (zcta5 geometry type) (Regina Obe)

PostGIS 2.4.6
2018/11/24

• Bug Fixes and Enhancements *

• #4160, Use qualified names in topology extension install (Raúl Marín)
• #4181, ST_AsMVTGeom: Avoid type changes due to validation (Raúl Marín)
• #4183, ST_AsMVTGeom: Drop invalid geometries after simplification (Raúl Marín)
• #4189, Fix undefined behaviour in SADFWrite (Raúl Marín)
• #4191, Fix undefined behaviour in ptarray_clone_deep (Raúl Marín)
• #4020, Fix leftovers in topology upgrade from 2.1 (Sandro Santilli)
• #4206, Replace deprecated use of getrelid with rt_fetch
  because getrelid is long deprecated and removed in pg12dev (Laurenz Albe)
• #3457, Fix raster envelope shortcut in ST_Clip (Sai-bot)
• #4215, Use floating point compare in ST_DumpAsPolygons (Darafei Praliaskouski)
• #4059, #4177, Remove use of variable length arrays (Paul Ramsey)
• #4216, Return to slicing for bbox access in gserialized (Paul Ramsey)
• #4136, Proper repeated point removal on small polygons (Paul Ramsey)
• #4223, ST_DistanceTree error for near parallel boxes (Paul Ramsey)
• #4326, Allocate enough memory in gidx_to_string (Raúl Marín)
• #4190, Avoid undefined behaviour in gserialized_estimate (Raúl Marín)
• #4247, Avoid undefined behaviour in next_float functions (Raúl Marín)
• #4249, Fix undefined behaviour in raster intersection (Raúl Marín)
• #4246, Fix undefined behaviour in ST_3DDistance (Raúl Marín)
• #4244, Avoid unaligned memory access in BOX2D_out (Raúl Marín)

PostGIS 2.4.5
2018/09/12

• Bug Fixes and Enhancements

• #4031, Survive to big MaxError tolerances passed to ST_CurveToLine
  (Sandro Santilli)
• #4058, Fix infinite loop in linearization of a big radius small arc
  (Sandro Santilli)
• #4071, ST_ClusterKMeans crash on NULL/EMPTY fixed (Darafei Praliaskouski)
• #4079, ensure St_AsMVTGeom outputs CW oriented polygons (Paul Ramsey)
• #4070, use standard interruption error code on GEOS interruptions
  (Paul Ramsey)
• #3980, delay freeing input until processing complete (lucasvr)
• #4090, PG 11 support (Paul Ramsey, Raúl Marín)
• #4077, Serialization failure for particular empty geometry cases (Paul Ramsey)
• #3997, fix bug in lwgeom_median and avoid division by zero (Raúl Marín)
• #4093, Inconsistent results from qsort callback (yugr)
• #4081, Geography DWithin() issues for certain cases (Paul Ramsey)
• #4105, Parallel build of tarball (Bas Couwenberg)
• #4163, MVT: Fix resource leak when the first geometry is NULL (Raúl Marín)

PostGIS 2.4.4
2018/04/08

• Bug fixes *

• #3055, [raster] ST_Clip() on a raster without band crashes the server
  (Regina Obe)
• #3942, geojson: Do not include private header for json-c >= 0.13
  (Björn Esser)
• #3952, ST_Transform fails in parallel mode (Paul Ramsey)
• #3978, Fix KNN when upgrading from 2.1 or older (Sandro Santilli)
• #4003, lwpoly_construct_circle: Avoid division by zero (Raúl Marín Rodríguez)
• #4004, Avoid memory exhaustion when building a btree index (Edmund Horner)
• #4016, proj 5.0.0 support (Raúl Marín Rodríguez)
• #4017, lwgeom lexer memory corruption (Peter E)
• #4020, Casting from box3d to geometry now returns correctly connected
  PolyhedralSurface (Matthias Bay)
• #4025, #4032 Incorrect answers for temporally “almost overlapping” ranges
  (Paul Ramsey, Darafei Praliaskouski)
• #4052, schema qualify several functions in geography (Regina Obe)
• #4055, ST_ClusterIntersecting drops SRID (Daniel Baston)
• #4067, Guard lookup of namespace in internal call case

• Enhancements *

• #3946, Compile support for PgSQL 11 (Paul Ramsey)
• #3992, Use PKG_PROG_PKG_CONFIG macro from pkg.m4 to detect pkg-config
  (Bas Couwenberg)
• #4044, Upgrade support for PgSQL 11 (Regina Obe)

PostGIS 2.4.3
2018/01/17

• Bug fixes *

• #3713, Support encodings that happen to output a ‘’ character
• #3827, Set configure default to not do interrupt testing,
  was causing false negatives for many people (Regina Obe)
  revised to be standards compliant in #3988 (Greg Troxel)
• #3930, Minimum bounding circle issues on 32-bit platforms
• #3965, ST_ClusterKMeans used to lose some clusters on initialization
  (Darafei Praliaskouski)
• #3956, Brin opclass object does not upgrade properly (Sandro Santilli)
• #3982, ST_AsEncodedPolyline supports LINESTRING EMPTY and MULTIPOINT EMPTY
  (Darafei Praliaskouski)
• #3975, ST_Transform runs query on spatial_ref_sys without schema
  qualification. Was causing restore issues. (Paul Ramsey)

• Enhancements *

• #3944, Update to EPSG register v9.2 (Even Rouault)

PostGIS 2.4.2
2017/11/15

• Bug fixes *

• #3917, Fix zcta5 load
• #3667, Fix for bug in geography ST_Segmentize
• #3926, Add missing 2.2.6 and 2.3.4 upgrade paths (Muhammad Usama)