Hi Sean,
Thanks for answering. I tried to set CPPFLAGS and LDFLAGS, but it doesn't help, CMake is always finding SQLite3 libraries in /usr/lib64 and INCLUDE_DIR in /usr/include.
Could the problem be that my SQLite3 libraries are installed in $HOME/opt/lib and not in $HOME/opt/lib64?

If your SQLite3 library is located in $PREFIX/lib then I think you need to change this line in your GDAL install from -DCMAKE_INSTALL_RPATH=$PREFIX/lib64 to -DCMAKE_INSTALL_RPATH=$PREFIX/lib.

If you are not sure where it is, try locating the path to it with a command like this: find $HOME -name libsqlite3.so

Then try adding these to your script after you set the PREFIX and PATH variables, eg.

mkdir -p ~/opt/src
cd ~/opt/src
export PREFIX=$HOME/opt
export PATH=$PREFIX/bin:$PATH
export C_INCLUDE_PATH=$PREFIX/include:$C_INCLUDE_PATH
export CPLUS_INCLUDE_PATH=$PREFIX/include:$CPLUS_INCLUDE_PATH
export LD_LIBRARY_PATH=$PREFIX/lib:$LD_LIBRARY_PATH
export CPPFLAGS="-I$PREFIX/include $CPPFLAGS"
export LDFLAGS="-L$PREFIX/lib $LDFLAGS"
...

Note: you can also try clearing any CMAKE cache before each build by locating the CMakeCache.txt file in the build directories, and deleting it if you think there are any conflicts due to them.

    I did all you suggested but nothing changed. I delete all files from the build directory before each build.
    libsqlite3.so is in $PREFIX/lib and I changed -DCMAKE_INSTALL_RPATH=$PREFIX/lib64 to -DCMAKE_INSTALL_RPATH=$PREFIX/lib, however, CMakeCache.txt has these two lines:

    //No help, variable specified on the command line.
    CMAKE_INSTALL_RPATH:UNINITIALIZED=/home/zavod/opt/lib

    It was the same with -DCMAKE_INSTALL_RPATH=$PREFIX/lib64 though. However, when -DCMAKE_INSTALL_RPATHis changed to $PREFIX/lib build exits with an error (near the end of the building) saying it could not find GEOS libraries, which is not the problem with $PREFIX/lib64 where build goes all the way to the end without problems.

    So it's got to be something else, it is just beyond my understanding of compiling from the source.

    Note: In CMakeCache fille there are two settings that are very odd because they are contradictory:

    //Path to a library.
    SQLite3_LIBRARY:FILEPATH=/home/zavod/opt/lib/libsqlite3.so
    
    //Path to a library.
    pkgcfg_lib_PC_SQLITE3_sqlite3:FILEPATH=/usr/lib64/libsqlite3.so

      arbormagna

      I delete all files from the build directory before each build.

      Make sure you're either completely deleting the build directory or explicitly removing CMakeCache.txt before re-running CMake.

      You can also try something like this to attempt to bypass attempts CMAKE might be using to persist in using default system paths:

      -DSQLite3_INCLUDE_DIR=$PREFIX/include \
      -DSQLite3_LIBRARY=$PREFIX/lib/libsqlite3.so \

      Or; try appending the cmake command with --verbose to try and get more details as to why it's using the system paths.

      Otherwise, I'm going to try this out myself and get back to you with any findings.

      arbormagna Sorry nick , although CMakeCache.txt is the same as it was in the time of my original post, when I ran ldd libgdal.so libsqlite3.so is referenced to correct location: libsqlite3.so.0 => /home/zavod/opt/lib/libsqlite3.so.0 (0x00007fb719b2f000), while ogrinfo -so --formats doesn't throw any sqlite3 errors or any errors for that matter.
      All I did is adding those lines at the beggining of the script as you suggested:

      export C_INCLUDE_PATH=$PREFIX/include:$C_INCLUDE_PATH
      export CPLUS_INCLUDE_PATH=$PREFIX/include:$CPLUS_INCLUDE_PATH
      export LD_LIBRARY_PATH=$PREFIX/lib:$LD_LIBRARY_PATH
      export CPPFLAGS="-I$PREFIX/include $CPPFLAGS"
      export LDFLAGS="-L$PREFIX/lib $LDFLAGS"

      Script for GDAL building was not changed and looks like this:

      #Download and install gdal 3.6.4
      cd $PREFIX/src
      wget http://download.osgeo.org/gdal/3.6.4/gdal-3.6.4.tar.gz
      tar xvf gdal-3.6.4.tar.gz
      cd gdal-3.6.4
      mkdir -p build
      cd build
      cmake .. -DCMAKE_BUILD_TYPE=Release \
      -DCMAKE_C_COMPILER=$PREFIX/bin/gcc \
      -DCMAKE_CXX_COMPILER=$PREFIX/bin/g++ \
      -DCMAKE_INSTALL_PREFIX=$PREFIX \
      -DSQLite3_INCLUDE_DIR=$PREFIX/include \
      -DSQLite3_LIBRARY=$PREFIX/lib/libsqlite3.so \
      -DSQLITE3EXT_INCLUDE_DIR=$PREFIX/include \
      -DCMAKE_BUILD_WITH_INSTALL_RPATH=ON \
      -DCMAKE_INSTALL_RPATH=$PREFIX/lib64
      cmake --build . -j8
      cmake --build . --target install

      I will try to compile Postgres and Postgis now and let you know if everything goes well.

      P.S. I would really like to know what made it successful this time even though nothing seems to be changed in the CMakeCache.txt file. Forgive me if I am missing something obvious.

      • nick replied to this.

        arbormagna Following the version recommendations you linked earlier. I was able to install PostgreSQL 15 with PostGIS 3.3.3, and minimal dependencies (you'd have to install whatever you need that is missing).

        You don't have to install a newer GCC version and all it's related dependencies, but rather use scl enable devtoolset-11 bash instead, which is installed on most servers. If you get an error when using that, open a support ticket and ask us to install it on your server as it might not be installed.

        Here's the really rough set of steps I organized in my spare time that lumps everything (Yes, it could be cleaner 😅) in a single NGINX proxy app (~/apps/psql15) used for the PostgreSQL 15 install (that was inspired by another post I made in the past for PostgreSQL 12). Just replace all the relevant fields to match your app and dev environment on the server.

        #Download, install, and configure PostgreSQL15
        cd ~/apps/psql15
        wget https://ftp.postgresql.org/pub/source/v15.0/postgresql-15.0.tar.gz
        tar zxf postgresql-15.0.tar.gz 
        export PGHOME=$HOME/apps/psql15/
        export PGDATA=$HOME/apps/psql15/pgdata
        ./postgresql-15.0/configure --prefix=$PGHOME
        make
        make install
        mkdir $PGDATA
        $PGHOME/bin/initdb -D $PGDATA
        sed -i 's/#port = 5432/port = XXXXX/g' $PGDATA/postgresql.conf
        
        #Version check
         ~/apps/psql15/bin/postgres --version
        
        #Immediately setup superuser **DO NOT SKIP**
        ~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile start
        ~/apps/psql15/bin/psql -h localhost -p XXXXX -d postgres
        ALTER USER newdbuser WITH PASSWORD 'mypasswd';
        exit
        sed -i 's/trust/md5/g' $PGDATA/pg_hba.conf
        ~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile restart
        
        #Commands to stop, start, restart, and connect
        ~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata stop
        ~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile start
        ~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile restart
        ~/apps/psql15/bin/psql -h localhost -p XXXXX -d postgres -U newdbuser
        
        # Install CMAKE
        # make some dirs if they don't already exist, etc
        mkdir -p ~/opt ~/bin ~/src && cd ~/src
        export PATH=$HOME/opt/bin:$PATH
        wget https://github.com/Kitware/CMake/archive/refs/tags/v3.27.8.tar.gz
        tar zxf v3.27.8.tar.gz
        cd CMake-3.27.8/
        ./bootstrap --prefix=$HOME/opt
        make
        make install
        cd ~/bin
        ln -s ~/opt/bin/* .
        
        ## Install other POSTGIS dependencies
        
        # Set dev env stuff
        scl enable devtoolset-11 bash
        export PGHOME=$HOME/apps/psql15/
        export PATH="$PGHOME/bin:$PATH"
        export LD_LIBRARY_PATH="$PGHOME/lib:$LD_LIBRARY_PATH"
        
        # Install GEOS
        cd $PGHOME/
        wget https://download.osgeo.org/geos/geos-3.12.0.tar.bz2
        tar xjf geos-3.12.0.tar.bz2
        cd geos-3.12.0
        mkdir build && cd build
        cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++
        make -j9
        make install
        
        #Download and install googletest
        git clone https://github.com/google/googletest.git $PGHOME/googletest/
        cd $PGHOME/googletest/
        mkdir build && cd build
        cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
                 -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
                 -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
                 -DBUILD_SHARED_LIBS=ON
        make -j9
        make install
        
        #Download and install sqlite3
        cd $PGHOME/
        wget https://www.sqlite.org/2023/sqlite-autoconf-3440000.tar.gz
        tar xzf sqlite-autoconf-3440000.tar.gz
        cd sqlite-autoconf-3440000
        ./configure --prefix=$PGHOME
        make -j9
        make install
        
        #Download and install PROJ9
        cd $PGHOME/
        wget https://download.osgeo.org/proj/proj-9.2.1.tar.gz
        tar zxf proj-9.2.1.tar.gz 
        cd proj-9.2.1/
        mkdir build && cd build
        cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
                 -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
                 -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
                 -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
                 -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so
        make -j9
        make install
        
        # Install GDAL
        cd $PGHOME/
        wget https://github.com/OSGeo/gdal/releases/download/v3.7.0/gdal-3.7.0.tar.gz
        tar zxf gdal-3.7.0.tar.gz
        cd gdal-3.7.0/
        mkdir build && cd build
        cmake .. -DCMAKE_BUILD_TYPE=Release \
                 -DCMAKE_INSTALL_PREFIX=$PGHOME \
                 -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
                 -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
                 -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
                 -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so \
                 -DGEOS_DIR=$PGHOME/lib64/cmake/GEOS
        cmake --build .
        cmake --build . --target install
        
        # Install PostGIS
        export LD_LIBRARY_PATH="$PGHOME/lib64:$PGHOME/lib:$LD_LIBRARY_PATH";
        cd $PGHOME/
        wget https://download.osgeo.org/postgis/source/postgis-3.3.3.tar.gz
        tar zxf postgis-3.3.3.tar.gz
        cd postgis-3.3.3/
        export LDFLAGS="-L$PGHOME/lib64"
        export CPPFLAGS="-I$PGHOME/include"
        ./configure --with-pgconfig=$PGHOME/bin/pg_config \
                    --with-geosconfig=$PGHOME/bin/geos-config \
                    --with-projdir=$PGHOME \
                    --without-protobuf \
                    --without-sfcgal
        make -j9
        make install
        
        # Connect to PostgreSQL, create postgis extension
        export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
        ~/apps/psql15/bin/psql -h localhost -p XXXXX -d postgres -U newdbuser
        CREATE EXTENSION postgis;
        
        # Checking PostGIS in psql:
        SELECT PostGIS_Version();
        
                    postgis_version            
        ---------------------------------------
         3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
        (1 row)
        
        postgres=#

        I hope it helps your workflow in getting this working for your Opalstack served projects 🙂

        5 days later

        Dear Nick,

        Thank you for your help, and sorry for late reply. Last several days I was trying to get POstgis installed with the script you provided, but I ran into a few issues.
        First, after installing GDAL, ogrinfowas missing so I 'fixed' it by adding

        -DCMAKE_BUILD_WITH_INSTALL_RPATH=ON \
        -DCMAKE_INSTALL_RPATH=$PGHOME/lib64

        configure options.

        Also, because I need SFCGALand PROTOBUF dependencies, I installed several dependencies related to those, and then ran into problems while configuring POSTGIS(it couldn't find PROJlibraries), which I managed to solve by setting variables:

        export PROJ_CFLAGS="-I$PGHOME/include"
        export PROJ_LIBS="-L$PGHOME/lib -lproj"

        This helped the installation make it through, however, during runtime, when I would try to make postgis extension on the database it would throw an error that it couldn't find proj libraries.

        I didn't want to bother you and wanted to crack this one myself but I am out of options at the moment.

        • nick replied to this.

          arbormagna Hi, can you please specify all the dependencies your project requires? I will have to revise and look at the build later.

            nick Yes off course, I want to deploy Geodjango project with Django 4.2 and I need:

            PostgreSQL 15  with OpenSSL
            PostGIS 3.3
            GEOS 3.10.5
            GDAL 3.6.4
            PROJ 6 - 9
            json-c 0.17
            protobuf-c 1.4.1
            SFCGAL 1.5.0

              arbormagna nick Hi, just an update from my side. Building upon your script I have now a script that brings the process to the end, i.e. I have a working PostGIS extension:

              # Install CMAKE
              # make some dirs if they don't already exist, etc
              mkdir -p $HOME/opt/src $HOME/bin $HOME/src && cd $HOME/opt/src
              export PATH="$HOME/opt/bin:$PATH"
              
              wget https://github.com/Kitware/CMake/archive/refs/tags/v3.27.8.tar.gz
              tar zvxf v3.27.8.tar.gz
              cd CMake-3.27.8/
              ./bootstrap --prefix=$HOME/opt
              make -j9
              make install
              cd ~/bin
              ln -s ~/opt/bin/* .
              
              ## Install other POSTGIS dependencies
              
              # Set dev env stuff
              scl enable devtoolset-11 bash
              export PATH="$HOME/opt/bin:$PATH"
              export PGHOME=$HOME/apps/psql15
              export PGDATA=$HOME/apps/psql15/pgdata
              mkdir -p $HOME/apps/psql15/pgdata
              mkdir -p $PGHOME/src
              export MY_PORT=XXXXX
              export PATH="$PGHOME/bin:$PATH"
              export LD_LIBRARY_PATH="$PGHOME/lib:$LD_LIBRARY_PATH"
              export C_INCLUDE_PATH=$PGHOME/include:$C_INCLUDE_PATH
              export CPLUS_INCLUDE_PATH=$PGHOME/include:$CPLUS_INCLUDE_PATH
              export CPPFLAGS="-I$PGHOME/include $CPPFLAGS"
              export LDFLAGS="-L$PGHOME/lib $LDFLAGS"
              
              # Install GEOS
              cd $PGHOME/src
              wget https://download.osgeo.org/geos/geos-3.10.5.tar.bz2
              tar xvjf geos-3.10.5.tar.bz2
              cd geos-3.10.5
              mkdir build && cd build
              cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
              		 -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
              		 -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++
              make -j9
              make install
              
              #Download and install googletest
              git clone https://github.com/google/googletest.git $PGHOME/src/googletest/
              cd $PGHOME/src/googletest/
              mkdir build && cd build
              cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
                       -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
                       -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
                       -DBUILD_SHARED_LIBS=ON
              make -j9
              make install
              
              #Download and install sqlite3
              cd $PGHOME/src
              wget https://www.sqlite.org/2023/sqlite-autoconf-3440000.tar.gz
              tar xvzf sqlite-autoconf-3440000.tar.gz
              cd sqlite-autoconf-3440000
              ./configure --prefix=$PGHOME
              make -j9
              make install
              
              #Download and install PROJ9
              cd $PGHOME/src
              wget https://download.osgeo.org/proj/proj-9.2.1.tar.gz
              tar zvxf proj-9.2.1.tar.gz
              cd proj-9.2.1/
              mkdir build && cd build
              cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
                       -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
                       -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
                       -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
                       -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so
              make -j9
              make install
              
              #Download and install json-c-0.16
              cd $PGHOME/src
              git clone https://github.com/json-c/json-c.git
              cd json-c
              git checkout remotes/origin/json-c-0.16
              mkdir ../json-c-build
              cd ../json-c-build
              cmake ../json-c -DCMAKE_INSTALL_PREFIX=$PGHOME \
              				-DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
              				-DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++
              make -j9
              make install
              
              # Install GDAL
              cd $PGHOME/src
              wget https://github.com/OSGeo/gdal/releases/download/v3.6.4/gdal-3.6.4.tar.gz
              tar zvxf gdal-3.6.4.tar.gz
              cd gdal-3.6.4/
              mkdir build && cd build
              export LD_LIBRARY_PATH=$PGHOME/lib64:$LD_LIBRARY_PATH
              cmake .. -DCMAKE_BUILD_TYPE=Release \
                       -DCMAKE_INSTALL_PREFIX=$PGHOME \
                       -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
                       -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
                       -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
                       -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so
              cmake --build . -j9
              cmake --build . --target install
              
              # Download and install postgresql 15.4
              cd $PGHOME/src
              wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz
              tar xvf postgresql-15.4.tar.gz
              cd postgresql-15.4
              LDFLAGS=-lstdc++ ./configure CC=/opt/rh/devtoolset-11/root/usr/bin/gcc \
              		--prefix=$PGHOME \
              		--with-pgport=$MY_PORT \
              		--with-ssl=openssl
              make -j9
              make install
              
              # Download and install protobuf
              cd $PGHOME/src
              git clone https://github.com/protocolbuffers/protobuf.git
              cd protobuf
              git checkout remotes/origin/4.0.x
              ./autogen.sh
              ./configure --prefix=$PGHOME
              make -j9
              make install
              
              # Download and install protobuf-c
              cd $PGHOME/src
              wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.4.0/protobuf-c-1.4.0.tar.gz
              tar -xzvf protobuf-c-1.4.0.tar.gz
              cd protobuf-c-1.4.0
              export LD_LIBRARY_PATH=$PGHOME/lib64:$LD_LIBRARY_PATH
              ./configure --prefix=$PGHOME \
              			CC=/opt/rh/devtoolset-11/root/usr/bin/gcc \
                          protobuf_CFLAGS="-I$PGHOME/include" \
                          protobuf_LIBS="-L$PGHOME/lib -lprotobuf"
              make -j9
              make install
              
              #Download and install gmp 6.3.0 (needed for SFCGAL)
              cd $PGHOME/src
              wget https://ftp.gnu.org/gnu/gmp/gmp-6.3.0.tar.xz
              unxz gmp-6.3.0.tar.xz
              tar -xvf gmp-6.3.0.tar
              cd gmp-6.3.0
              ./configure --enable-cxx \
              			--prefix=$PGHOME
              make -j9
              make install
              
              #Download and install mpfr 4.2.1 (needed for SFCGAL)
              cd $PGHOME/src
              wget https://ftp.gnu.org/gnu/mpfr/mpfr-4.2.1.tar.gz
              tar -xvf mpfr-4.2.1.tar.gz
              cd mpfr-4.2.1
              ./configure --prefix=$PGHOME
              make -j9
              make install
              
              # Download and install boost 1.83.0 (needed for SFCGAL)
              cd $PGHOME/src
              wget https://boostorg.jfrog.io/artifactory/main/release/1.83.0/source/boost_1_83_0.tar.gz
              tar -xzvf boost_1_83_0.tar.gz
              cd boost_1_83_0
              export LD_LIBRARY_PATH=$PGHOME/lib64:$LD_LIBRARY_PATH
              ./bootstrap.sh --prefix=$PGHOME
              ./b2 install -j9
              
              # Download and install CGAL-5.6 (needed for SFCGAL)
              cd $PGHOME/src
              wget https://github.com/CGAL/cgal/releases/download/v5.6/CGAL-5.6.tar.xz
              tar -xvf CGAL-5.6.tar.xz
              cd CGAL-5.6
              mkdir -p build
              cd build
              cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME -DCMAKE_BUILD_TYPE="Release"
              make -j9
              make install
              
              # Download and install SFCGAL-1.5.0
              cd $PGHOME/src
              wget https://gitlab.com/SFCGAL/SFCGAL/-/archive/v1.5.0/SFCGAL-v1.5.0.tar.gz
              tar -xvf SFCGAL-v1.5.0.tar.gz
              cd SFCGAL-v1.5.0
              mkdir -p build
              cd build
              export CXX=c++
              cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
              -DCMAKE_BUILD_TYPE="Release"
              make -j9
              make install
              
              # Install PostGIS
              export LD_LIBRARY_PATH="$PGHOME/lib64:$PGHOME/lib:$LD_LIBRARY_PATH";
              cd $PGHOME/src
              wget https://download.osgeo.org/postgis/source/postgis-3.3.3.tar.gz
              tar zvxf postgis-3.3.3.tar.gz
              cd postgis-3.3.3/
              export LDFLAGS="-L$PGHOME/lib64 -L$PGHOME/lib"
              export CPPFLAGS="-I$PGHOME/include"
              ./configure --with-pgconfig=$PGHOME/bin/pg_config --with-gdalconfig=$PGHOME/bin/gdal-config --with-geosconfig=$PGHOME/bin/geos-config --with-projdir=$PGHOME --with-jsondir=$PGHOME
              make -j9
              make install

              However, even though the PostGIS is built with all needed dependencies:

              -------------- Dependencies --------------
                GEOS config:          /home/arbormagna/apps/psql15/bin/geos-config
                GEOS version:         3.10.5
                GDAL config:          /home/arbormagna/apps/psql15/bin/gdal-config
                GDAL version:         3.6.4
                SFCGAL config:        /home/arbormagna/apps/psql15/bin/sfcgal-config
                SFCGAL version:       1.5.0
                PostgreSQL config:    /home/arbormagna/apps/psql15/bin/pg_config
                PostgreSQL version:   PostgreSQL 15.4
                PROJ4 version:        92
                Libxml2 config:       /usr/bin/xml2-config
                Libxml2 version:      2.9.1
                JSON-C support:       yes
                protobuf support:     yes
                protobuf-c version:   1004000
                PCRE support:         Version 1
                Perl:                 /usr/bin/perl
              
               --------------- Extensions ---------------
                PostGIS Raster:                     enabled
                PostGIS Topology:                   enabled
                SFCGAL support:                     enabled
                Address Standardizer support:       enabled

              GDAL and SFCGAL are not available at run time (I figure they are not installed):

                              postgis_full_version
              
              -------------------------------------------------------
              -------------------------------------------------------
              -----------------------------------------------------
               POSTGIS="3.3.3 2355e8e" [EXTENSION] PGSQL="150" GEOS="
              3.10.5-CAPI-1.16.2" PROJ="9.2.1" LIBXML="2.9.1" LIBJSON
              ="0.16" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)"
              (1 row)

              As a side note, if I add an option CC=/opt/rh/devtoolset-11/root/usr/bin/gcc to PostGIS configuration I can not create PostGIS extension at run time (postgis can not find libproj.so). Could this mean that it can be related to compiler? Any insights are highly appreciated.

              • nick replied to this.

                arbormagna Hi there, sorry for any delays - it took me a while to get this tested.

                Couple things I noticed:

                • CGAL INSTALL.md notes that:

                  Since Version 5.0, CGAL is now header-only by default, meaning that you do not need to build and install CGAL.

                  So, camke/make/install won't do anything, you just have to extract it and tell SFCGAL where the headers are when you install it, eg. export CGAL_DIR=/home/user/src/CGAL-5.6

                • Assuming SFCGAL installed successfully, create it's related extension after the postgis one:

                  $PGHOME/bin/psql -h localhost -p XXXXX -d db_name -c "CREATE EXTENSION postgis_sfcgal;"

                  Then the $PGHOME/bin/psql -h localhost -p XXXXX -d db_name -c "SELECT PostGIS_Full_Version();" command should show something like this:

                  -------------------
                   POSTGIS="3.3.3 2355e8e" [EXTENSION] PGSQL="150" GEOS="3.10.5-CAPI-1.16.2" SFCGAL="SFCGAL 1.5.0
                  , CGAL 5.6, BOOST 1.83.0" PROJ="9.2.1" LIBXML="2.9.1" LIBJSON="0.17" LIBPROTOBUF="1.4.0" WAGYU=
                  "0.5.0 (Internal)"
                  (1 row)

                In case you want to review the entire install process, please see below, which is an adapted version of your work and mine.

                # Make NGINX proxy app (eg we'll use psql15 here) and note the port
                
                # Create necessary directories if they don't already exist
                mkdir -p ~/opt ~/bin ~/src ~/apps/psql15/pgdata
                
                # Enable devtoolset and environment variables (need to set again if terminal session ever ends)
                source /opt/rh/devtoolset-11/enable
                export PATH=~/opt/bin:~/apps/psql15/bin:$PATH;
                export PGHOME=~/apps/psql15;
                export PGDATA=~/apps/psql15/pgdata;
                export LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib64:$LD_LIBRARY_PATH;
                export C_INCLUDE_PATH=$PGHOME/include:$C_INCLUDE_PATH;
                export CPLUS_INCLUDE_PATH=$PGHOME/include:$CPLUS_INCLUDE_PATH;
                export CPPFLAGS="-I$PGHOME/include $CPPFLAGS";
                export LDFLAGS="-L$PGHOME/lib64 -L$PGHOME/lib $LDFLAGS"
                
                # Install CMake
                cd ~/src
                wget https://github.com/Kitware/CMake/archive/refs/tags/v3.27.8.tar.gz
                tar zvxf v3.27.8.tar.gz
                cd CMake-3.27.8/
                ./bootstrap --prefix=~/opt
                make -j9
                make install
                
                # Install PostgreSQL 15 with OpenSSL (replace XXXXX with port)
                cd ~/src
                wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz
                tar xvf postgresql-15.4.tar.gz
                cd postgresql-15.4
                LDFLAGS=-lstdc++ ./configure --prefix=$PGHOME --with-openssl
                make -j9
                make install
                $PGHOME/bin/initdb -D $PGDATA
                sed -i 's/#port = 5432/port = XXXXX/g' $PGDATA/postgresql.conf
                
                # Immediately setup superuser password **DO NOT SKIP**
                $PGHOME/bin/pg_ctl -D $PGDATA -l logfile start
                $PGHOME/bin/psql -h localhost -p XXXXX -d postgres
                ALTER USER shell_user WITH PASSWORD 'superuser_pwd';
                exit
                sed -i 's/trust/md5/g' $PGDATA/pg_hba.conf
                $PGHOME/bin/pg_ctl -D $PGDATA -l logfile restart
                
                # Create production DB/user
                $PGHOME/bin/createuser new_user -h localhost -p XXXXX --no-createdb --no-superuser --no-createrole --pwprompt
                $PGHOME/bin/createdb new_db -h localhost -p XXXXX --owner=new_user
                
                #Commands to stop, start, restart, and connect
                ~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata stop
                ~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile start
                ~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile restart
                ~/apps/psql15/bin/psql -h localhost -p XXXXX -d new_db -U new_user
                
                # Install GEOS 3.10.5
                cd ~/src
                wget https://download.osgeo.org/geos/geos-3.10.5.tar.bz2
                tar xvjf geos-3.10.5.tar.bz2
                cd geos-3.10.5
                mkdir build && cd build
                cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME
                make -j9
                make install
                
                # Install sqlite3
                cd ~/src
                wget https://www.sqlite.org/2023/sqlite-autoconf-3440000.tar.gz
                tar xvzf sqlite-autoconf-3440000.tar.gz
                cd sqlite-autoconf-3440000
                ./configure --prefix=$PGHOME
                make -j9
                make install
                
                # Install PROJ9
                cd ~/src
                wget https://download.osgeo.org/proj/proj-9.2.1.tar.gz
                tar zvxf proj-9.2.1.tar.gz
                cd proj-9.2.1/
                mkdir build && cd build
                cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
                         -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
                         -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so
                make -j9
                make install
                
                # Install json-c 0.17
                cd ~/src
                git clone https://github.com/json-c/json-c.git
                cd json-c
                git checkout json-c-0.17
                mkdir build && cd build
                cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME
                make -j9
                make install
                
                # Download and install googletest
                cd ~/src
                git clone https://github.com/google/googletest.git
                cd googletest/
                mkdir build && cd build
                cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
                         -DBUILD_SHARED_LIBS=ON
                make -j9
                make install
                
                # Install GDAL 3.6.4
                cd ~/src
                wget https://github.com/OSGeo/gdal/releases/download/v3.6.4/gdal-3.6.4.tar.gz
                tar zvxf gdal-3.6.4.tar.gz
                cd gdal-3.6.4/
                mkdir build && cd build
                cmake .. -DCMAKE_BUILD_TYPE=Release \
                         -DCMAKE_INSTALL_PREFIX=$PGHOME \
                         -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
                         -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so
                make -j9
                make install
                
                # Download and install protobuf
                cd ~/src
                git clone https://github.com/protocolbuffers/protobuf.git
                cd protobuf
                git checkout remotes/origin/4.0.x
                ./autogen.sh
                ./configure --prefix=$PGHOME
                make -j9
                make install
                
                # Download and install protobuf-c
                cd ~/src
                wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.4.0/protobuf-c-1.4.0.tar.gz
                tar -xzvf protobuf-c-1.4.0.tar.gz
                cd protobuf-c-1.4.0
                ./configure --prefix=$PGHOME \
                            protobuf_CFLAGS="-I$PGHOME/include" \
                            protobuf_LIBS="-L$PGHOME/lib -lprotobuf"
                make -j9
                make install
                
                # Download and install gmp 6.3.0 (needed for SFCGAL)
                cd ~/src
                wget https://ftp.gnu.org/gnu/gmp/gmp-6.3.0.tar.xz
                tar xf gmp-6.3.0.tar.xz
                cd gmp-6.3.0
                ./configure --enable-cxx \
                			--prefix=$PGHOME
                make -j9
                make install
                
                #Download and install mpfr 4.2.1 (needed for SFCGAL)
                cd ~/src
                wget https://ftp.gnu.org/gnu/mpfr/mpfr-4.2.1.tar.gz
                tar -xvf mpfr-4.2.1.tar.gz
                cd mpfr-4.2.1
                ./configure --prefix=$PGHOME
                make -j9
                make install
                
                # Download and install boost 1.83.0 (needed for SFCGAL)
                cd ~/src
                wget https://boostorg.jfrog.io/artifactory/main/release/1.83.0/source/boost_1_83_0.tar.gz
                tar -xzvf boost_1_83_0.tar.gz
                cd boost_1_83_0
                ./bootstrap.sh --prefix=$PGHOME
                ./b2 install -j9
                
                # Download and extract CGAL-5.6 headers (needed for SFCGAL)
                cd ~/src
                wget https://github.com/CGAL/cgal/releases/download/v5.6/CGAL-5.6.tar.xz
                tar -xvf CGAL-5.6.tar.xz
                
                # Download and install SFCGAL-1.5.0
                cd ~/src
                wget https://gitlab.com/SFCGAL/SFCGAL/-/archive/v1.5.0/SFCGAL-v1.5.0.tar.gz
                tar -xvf SFCGAL-v1.5.0.tar.gz
                cd SFCGAL-v1.5.0
                mkdir build && cd build
                export CXX=c++
                export CGAL_DIR=$HOME/src/CGAL-5.6
                cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
                -DCMAKE_BUILD_TYPE="Release"
                make -j9
                make install
                
                # Install PostGIS 3.3
                cd ~/src
                wget https://download.osgeo.org/postgis/source/postgis-3.3.3.tar.gz
                tar zvxf postgis-3.3.3.tar.gz
                cd postgis-3.3.3/
                ./configure --with-pgconfig=$PGHOME/bin/pg_config \
                            --with-gdalconfig=$PGHOME/bin/gdal-config \
                            --with-geosconfig=$PGHOME/bin/geos-config \
                            --with-projdir=$PGHOME \
                            --with-jsondir=$PGHOME
                make -j9
                make install
                
                # Connect to PostgreSQL and create PostGIS and SFCGAL extension
                $PGHOME/bin/psql -h localhost -p XXXXX -d postgistest -c "CREATE EXTENSION postgis;"
                $PGHOME/bin/psql -h localhost -p XXXXX -d postgistest -c "CREATE EXTENSION postgis_sfcgal;"
                
                # Check PostgreSQL version: 
                $PGHOME/bin/postgres --version
                
                # Verify PostGIS installation within PostgreSQL: 
                $PGHOME/bin/psql -h localhost -p XXXXX -d postgistest -c "SELECT PostGIS_Full_Version();"

                  nick Hi Nick,
                  Thank you very much for your time. This fixed SFCGAL dependency, but as you can see from SELECT PostGIS_Full_Version(); there is still no GDAL (very important library for raster support).

                  -------------------
                   POSTGIS="3.3.3 2355e8e" [EXTENSION] PGSQL="150" GEOS="3.10.5-CAPI-1.16.2" SFCGAL="SFCGAL 1.5.0
                  , CGAL 5.6, BOOST 1.83.0" PROJ="9.2.1" LIBXML="2.9.1" LIBJSON="0.17" LIBPROTOBUF="1.4.0" WAGYU=
                  "0.5.0 (Internal)"
                  (1 row)

                  Hopefully, there is a fix for this too as my project depends on it. Apologies for posting such a noxious problem 🙂

                  • nick replied to this.

                    arbormagna Try installing the raster extension:

                    CREATE EXTENSION postgis_raster;

                    And if you require topology support:

                    CREATE EXTENSION postgis_topology;

                    Confirmed:

                                                                                                                   
                                               postgis_full_version                                                
                                                                                              
                    -----------------------------------------------------------------------------------------------
                    -----------------------------------------------------------------------------------------------
                    --------------------------------------------------------------------------
                     POSTGIS="3.3.3 2355e8e" [EXTENSION] PGSQL="150" GEOS="3.10.5-CAPI-1.16.2" SFCGAL="SFCGAL 1.5.0
                    , CGAL 5.6, BOOST 1.83.0" PROJ="9.2.1" GDAL="GDAL 3.6.4, released 2023/04/17" LIBXML="2.9.1" LI
                    BJSON="0.17" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER
                    (1 row)

                    Note, you will probably want to export these variables before interacting with your PSQL server, or add them to your session via .bash_profile or .bashrc file in your base $HOME dir.

                    export PATH=~/opt/bin:~/apps/psql15/bin:$PATH;
                    export PGHOME=~/apps/psql15;
                    export PGDATA=~/apps/psql15/pgdata;
                    export LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib64:$LD_LIBRARY_PATH;
                    export C_INCLUDE_PATH=$PGHOME/include:$C_INCLUDE_PATH;
                    export CPLUS_INCLUDE_PATH=$PGHOME/include:$CPLUS_INCLUDE_PATH;
                    export CPPFLAGS="-I$PGHOME/include $CPPFLAGS";
                    export LDFLAGS="-L$PGHOME/lib64 -L$PGHOME/lib $LDFLAGS"

                      nick Dear Nick, this finally did the trick 🙂. Thank you very much for your patience and time, it is highly appreciated 🙏🙏🙏.

                      Note: There is a typo in your intallation script: just add .xz to the end of tar xf gmp-6.3.0.tarcommand.

                      • nick replied to this.

                        arbormagna You're welcome 🙂

                        Note: There is a typo in your intallation script: just add .xz to the end of tar xf gmp-6.3.0.tarcommand.

                        All fixed, thanks for spotting that.

                        Let us know if there is anything else.

                        Mastodon