Marat's Notes

Postgis extension with Postgresql 9.6.8

Apr 20, 2018
4 minute read

At last week I spent at least two days to figure out how to setup Postgis extension with Postgresql 9.6.8 on my Macbook.

So, let’s go step by step with my nightmare.

First of all I’m tried to install, and check if extension already installed and can be enabled.

/Users/marat ❯❯❯ brew install postgis

Command was successfully completed but when I’m tried to enable it I had error.

/Users/marat ❯❯❯ psql -U postgres -W 

Password for user postgres: psql (9.6.8) Type “help” for help.

postgres=# CREATE EXTENSION postgis; ERROR: could not open extension control file “/usr/local/Cellar/postgresql@9.6/9.6.8/share/postgresql@9.6/extension/postgis.control”: No such file or directory postgres=# \q

After some googling I’ve found what postgis was installed for PG10, and have version 2.4.

I have PG 9.6.8 and have no time to install PG version manager, setup different databased etc.

I found, what for 9.6.8 Postgres I need to install postgis 2.3.

So, let’s reinstall gdal2 first:

brew install gdal2 --with-armadillo --with-complete --with-libkml --with-unsupported

Next - we need to install custom json-c.rb version:

require "formula"

class JsonC < Formula
  homepage "https://github.com/json-c/json-c/wiki"
  url "https://github.com/json-c/json-c/archive/json-c-0.12-20140410.tar.gz"
  version "0.12"
  sha256 "99304a4a633f1ee281d6a521155a182824dd995139d5ed6ee5c93093c281092b"

  bottle do
    cellar :any
    sha256 "fda8051afcd1aca5d5f225a757c0f6333c9f091a" => :yosemite
    sha256 "3e4ba1c8434dde4bcff70dbfa5be90a1f8540f3f" => :mavericks
    sha256 "e4b48b569408080805e236317eec4fa0cc3c2f06" => :mountain_lion
  end

  head do
    url "https://github.com/json-c/json-c.git"

    depends_on "libtool" => :build
    depends_on "automake" => :build
    depends_on "autoconf" => :build
  end

  option :universal

  def install
    ENV.universal_binary if build.universal?

    system "./autogen.sh" if build.head?
    system "./configure", "--disable-dependency-tracking",
                          "--disable-silent-rules",
                          "--prefix=#{prefix}"
    ENV.deparallelize
    system "make", "install"
  end
end

Next - we need to install custom postgis.rb version:

class Postgis < Formula
  desc "Adds support for geographic objects to PostgreSQL"
  homepage "https://postgis.net/"
  url "http://download.osgeo.org/postgis/source/postgis-2.3.2.tar.gz"
  sha256 "e92e34c18f078a3d1a2503cd870efdc4fa9e134f0bcedbbbdb8b46b0e6af09e4"

  bottle do
    cellar :any
    sha256 "4a6e31335c38a83e9119c6d7e5e4228d7be0dbea9c2f4ffd9a043ae4141bc3a5" => :high_sierra
    sha256 "cea4e412efe966694749f6e1feaa11db1dd47970a9f6ac63afd1765b50f56d85" => :sierra
    sha256 "83a1e64c57c69d4e85a1678e772798b2cd04aaba26ab5ce75b678d41d7bc6cf7" => :el_capitan
    sha256 "719efe3d8589e4923ff5a89e542df813053b59695b9d16f1cb2eb88db93e62ce" => :yosemite
  end

  head do
    url "https://svn.osgeo.org/postgis/trunk/"

    depends_on "autoconf" => :build
    depends_on "automake" => :build
    depends_on "libtool" => :build
  end

  option "with-gui", "Build shp2pgsql-gui in addition to command line tools"
  option "without-gdal", "Disable postgis raster support"
  option "with-html-docs", "Generate multi-file HTML documentation"
  option "with-api-docs", "Generate developer API documentation (long process)"

  depends_on "pkg-config" => :build
  depends_on "gpp" => :build
  depends_on "postgresql"
  depends_on "proj"
  depends_on "geos"

  depends_on "gtk+" if build.with? "gui"

  # For GeoJSON and raster handling
  depends_on "json-c"
  depends_on "gdal" => :recommended
  depends_on "pcre" if build.with? "gdal"

  # For advanced 2D/3D functions
  depends_on "sfcgal" => :recommended

  if build.with? "html-docs"
    depends_on "imagemagick"
    depends_on "docbook-xsl"
  end

  if build.with? "api-docs"
    depends_on "graphviz"
    depends_on "doxygen"
  end

  def install
    ENV.deparallelize

    args = [
      "--with-projdir=#{Formula["proj"].opt_prefix}",
      "--with-jsondir=#{Formula["json-c"].opt_prefix}",
      "--with-pgconfig=#{Formula["postgresql"].opt_bin}/pg_config",
      # Unfortunately, NLS support causes all kinds of headaches because
      # PostGIS gets all of its compiler flags from the PGXS makefiles. This
      # makes it nigh impossible to tell the buildsystem where our keg-only
      # gettext installations are.
      "--disable-nls",
    ]

    args << "--with-gui" if build.with? "gui"
    args << "--without-raster" if build.without? "gdal"
    args << "--with-xsldir=#{Formula["docbook-xsl"].opt_prefix}/docbook-xsl" if build.with? "html-docs"

    system "./autogen.sh" if build.head?
    system "./configure", *args
    system "make"

    if build.with? "html-docs"
      cd "doc" do
        ENV["XML_CATALOG_FILES"] = "#{etc}/xml/catalog"
        system "make", "chunked-html"
        doc.install "html"
      end
    end

    if build.with? "api-docs"
      cd "doc" do
        system "make", "doxygen"
        doc.install "doxygen/html" => "api"
      end
    end

    mkdir "stage"
    system "make", "install", "DESTDIR=#{buildpath}/stage"

    bin.install Dir["stage/**/bin/*"]
    lib.install Dir["stage/**/lib/*"]
    include.install Dir["stage/**/include/*"]
    (doc/"postgresql/extension").install Dir["stage/**/share/doc/postgresql/extension/*"]
    (share/"postgresql/extension").install Dir["stage/**/share/postgresql/extension/*"]
    pkgshare.install Dir["stage/**/contrib/postgis-*/*"]
    (share/"postgis_topology").install Dir["stage/**/contrib/postgis_topology-*/*"]

    # Extension scripts
    bin.install %w[
      utils/create_undef.pl
      utils/postgis_proc_upgrade.pl
      utils/postgis_restore.pl
      utils/profile_intersects.pl
      utils/test_estimation.pl
      utils/test_geography_estimation.pl
      utils/test_geography_joinestimation.pl
      utils/test_joinestimation.pl
    ]

    man1.install Dir["doc/**/*.1"]
  end

  def caveats
    <<-EOS
      To create a spatially-enabled database, see the documentation:
        https://postgis.net/docs/manual-2.2/postgis_installation.html#create_new_db_extensions
      If you are currently using PostGIS 2.0+, you can go the soft upgrade path:
        ALTER EXTENSION postgis UPDATE TO "#{version}";
      Users of 1.5 and below will need to go the hard-upgrade path, see here:
        https://postgis.net/docs/manual-2.2/postgis_installation.html#upgrading

      PostGIS SQL scripts installed to:
        #{opt_pkgshare}
      PostGIS plugin libraries installed to:
        #{HOMEBREW_PREFIX}/lib
      PostGIS extension modules installed to:
        #{HOMEBREW_PREFIX}/share/postgresql/extension
      EOS
  end

  test do
    require "base64"
    (testpath/"brew.shp").write ::Base64.decode64 <<-EOS
      AAAnCgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAoOgDAAALAAAAAAAAAAAAAAAA
      AAAAAADwPwAAAAAAABBAAAAAAAAAFEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      AAAAAAAAAAAAAAAAAAEAAAASCwAAAAAAAAAAAPA/AAAAAAAA8D8AAAAAAAAA
      AAAAAAAAAAAAAAAAAgAAABILAAAAAAAAAAAACEAAAAAAAADwPwAAAAAAAAAA
      AAAAAAAAAAAAAAADAAAAEgsAAAAAAAAAAAAQQAAAAAAAAAhAAAAAAAAAAAAA
      AAAAAAAAAAAAAAQAAAASCwAAAAAAAAAAAABAAAAAAAAAAEAAAAAAAAAAAAAA
      AAAAAAAAAAAABQAAABILAAAAAAAAAAAAAAAAAAAAAAAUQAAAAAAAACJAAAAA
      AAAAAEA=
    EOS
    (testpath/"brew.dbf").write ::Base64.decode64 <<-EOS
      A3IJGgUAAABhAFsAAAAAAAAAAAAAAAAAAAAAAAAAAABGSVJTVF9GTEQAAEMA
      AAAAMgAAAAAAAAAAAAAAAAAAAFNFQ09ORF9GTEQAQwAAAAAoAAAAAAAAAAAA
      AAAAAAAADSBGaXJzdCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgIFBvaW50ICAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgU2Vjb25kICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgICBQb2ludCAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgIFRoaXJkICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgICAgUG9pbnQgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICBGb3VydGggICAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgICAgIFBvaW50ICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgQXBwZW5kZWQgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgICAgICBQb2ludCAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAg
    EOS
    (testpath/"brew.shx").write ::Base64.decode64 <<-EOS
      AAAnCgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAARugDAAALAAAAAAAAAAAAAAAA
      AAAAAADwPwAAAAAAABBAAAAAAAAAFEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      AAAAAAAAAAAAAAAAADIAAAASAAAASAAAABIAAABeAAAAEgAAAHQAAAASAAAA
      igAAABI=
    EOS
    result = shell_output("#{bin}/shp2pgsql #{testpath}/brew.shp")
    assert_match /Point/, result
    assert_match /AddGeometryColumn/, result
  end
end

Let’s install json-c version 0.12.

~ ❯❯❯ brew install json-c.rb
Warning: json-c 0.13.1 is available and more recent than version 0.12.
==> Downloading https://github.com/json-c/json-c/archive/json-c-0.12-20140410.tar.gz
==> Downloading from https://codeload.github.com/json-c/json-c/tar.gz/json-c-0.12-20140410
######################################################################## 100.0%
==> ./configure --disable-silent-rules --prefix=/usr/local/Cellar/json-c/0.12
==> make install
🍺  /usr/local/Cellar/json-c/0.12: 27 files, 156.1KB, built in 28 seconds

And install postgis from custom file.

~ ❯❯❯ brew install postgis.rb

Let’s reinstall gdal from source:

brew reinstall gdal --build-from-source

Let’s unlink and link gdal again:

~ ❯❯❯ brew unlink gdal
Unlinking /usr/local/Cellar/gdal/2.3.1_2... 209 symlinks removed
~ ❯❯❯ brew link gdal
Linking /usr/local/Cellar/gdal/2.3.1_2... 209 symlinks created

Let’s check again and install extension.

~ ❯❯❯ psql -U postgres -W
Password for user postgres: 
psql (9.6.8)
Type "help" for help.

postgres=# CREATE EXTENSION postgis;
ERROR:  could not load library "/usr/local/Cellar/postgresql@9.6/9.6.8/lib/rtpostgis-2.3.so": dlopen(/usr/local/Cellar/postgresql@9.6/9.6.8/lib/rtpostgis-2.3.so, 10): Library not loaded: /usr/local/opt/gdal/lib/libgdal.1.dylib
  Referenced from: /usr/local/Cellar/postgresql@9.6/9.6.8/lib/rtpostgis-2.3.so
  Reason: image not found
postgres=# \q

After installing json-c and postgis.

ln -s /usr/local/share/postgresql/extension/postgis* /usr/local/Cellar/postgresql@9.6/9.6.8/share/postgresql@9.6/extension
ln -s /usr/local/lib/postgresql/postgis-2.3.so /usr/local/Cellar/postgresql@9.6/9.6.8/lib/postgis-2.3
ln -s /usr/local/lib/postgresql/rtpostgis-2.3.so /usr/local/Cellar/postgresql@9.6/9.6.8/lib/

Let’s check and add symlink:

❯ cd /usr/local/opt/gdal/lib
❯ ln -s libgdal.20.dylib libgdal.1.dylib
❯ ln -s libproj.13.dylib libproj.12.dylib

Let’s do a final check:

~ ❯❯❯ psql -U postgres -W
Password for user postgres: 
psql (9.6.8)
Type "help" for help.

postgres=# CREATE EXTENSION postgis;
CREATE EXTENSION
postgres=# \q

Hope this manual can be useful for somebody.