[proposal] improve postgis support ( latest geos, proj, gdal, sfcgal )

ImreSamu · April 14, 2023, 11:20am

now the SELECT PostGIS_Full_Version();

                        postgis_full_version                                                                
                                           
------------------------------------------------------------------------------------------------------ 

POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="150" 
    GEOS="3.9.0-CAPI-1.16.2" 
    SFCGAL="1.3.8" 
    PROJ="7.2.1" 
    GDAL="GDAL 3.2.2,
    released 2021/03/05" LIBXML="2.9.10" 
    LIBJSON="0.15" LIBPROTOBUF="1.3.3 " 
    WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER
(1 row)

and I think it could be improved.

If most packages are being built in a container, I think it might be worthwhile to use the latest of some of the postgis-related packages.

using the latest GEOS ( 3.11) , PROJ (9.1), GDAL(3.5), SFCGAL(1.4)
see the support matrix: https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS
And based on the postgis 3.3.0 release notes some new version is recomended
“If compiled with recently released GEOS 3.11.0 you can take advantage of new functions added and improvements to some existing functions.”

ImreSamu · April 14, 2023, 11:21am

imho: You can check the postgis/postgis:15-master Dockerfile ;

SFCGAL 1.4 ( with cgal header only )
Geos, GDAL, PROJ build
postgis Test

github.com

postgis/docker-postgis/blob/master/15-master/Dockerfile

#
# NOTE: THIS DOCKERFILE IS GENERATED VIA "make update"! PLEASE DO NOT EDIT IT DIRECTLY.
#

# "experimental" ;  only for testing! Changes expected!
# multi-stage dockerfile;  minimal docker version >= 17.05

ARG DOCKER_CMAKE_BUILD_TYPE=Release
FROM postgres:15-bullseye as builder

LABEL maintainer="PostGIS Project - https://postgis.net"
LABEL org.opencontainers.image.source="https://github.com/postgis/docker-postgis"

WORKDIR /

# apt-get install
RUN set -ex \
    && apt-get update \
    && apt-get install -y --no-install-recommends \
      curl \

This file has been truncated. show original

vadim2404 · April 14, 2023, 12:26pm

At this moment, the vast majority of such libraries came from Debian. However, such changes require compiling them manually. I don’t think that such an update will benefit us somehow.

ImreSamu · April 14, 2023, 10:09pm

at least the current compiled SFCGAL can be upgraded

libcgal-dev can be removed. ;
and modify the code to : SFCGAL 1.4.1 + CGAL 5.5.2 header only

I can also do the Pull Request …

github.com

neondatabase/neon/blob/ebea29841517cd189c800e09e67a85202376dcdc/Dockerfile.compute-node#L66-L72


      
          # SFCGAL > 1.3 requires CGAL > 5.2, Bullseye's libcgal-dev is 5.2
          RUN wget https://gitlab.com/Oslandia/SFCGAL/-/archive/v1.3.10/SFCGAL-v1.3.10.tar.gz -O SFCGAL.tar.gz && \
              echo "4e39b3b2adada6254a7bdba6d297bb28e1a9835a9f879b74f37e2dab70203232 SFCGAL.tar.gz" | sha256sum --check && \
              mkdir sfcgal-src && cd sfcgal-src && tar xvzf ../SFCGAL.tar.gz --strip-components=1 -C . && \
              cmake . && make -j $(getconf _NPROCESSORS_ONLN) && \
              DESTDIR=/sfcgal make install -j $(getconf _NPROCESSORS_ONLN) && \
              make clean && cp -R /sfcgal/* /

I don’t think that such an update will benefit us somehow.

Although I acknowledge that this is not the most critical point in the NEON project, which would require high priority or significant developer resources, it is worth mentioning that the postgis, geos, gdal, and proj libraries are developed at a faster pace than the more conservative Debian. Furthermore, if some of the packages are already compiled, having an up-to-date PostGIS ecosystem could be one of the advantages of the NEON project compared to other Debian-based Postgres cloud services.

disclaimer: I am a GitHub - postgis/docker-postgis: Docker image for PostGIS contributor;

ImreSamu · April 15, 2023, 8:31pm

Alternatives:

) probably a custom docker image option would solve my problem.

~ “allow users to provide their own docker image that includes the extensions they want, or something like that”

2.) The other alternative is adding debian:bookworm based image.
It has a newer Geos (3.11 )

so I can select

Postgres:15 - bullseye ( → this is the current image )
Postgres:15 - bookworm - testing

vadim2404 · April 15, 2023, 8:40pm

Feel free to contribute

yes, we have such ideas

I’d prefer to stay on the current base image.

ImreSamu · April 27, 2023, 7:06pm

Probably an upgrade to Debian 12 will solve most of the problems.

Debian 12.0 “Bookworm” ( Planned For Release On 10 )