PostGIS 2.2.0 came out this month, and the SFCGAL extension that offers advanced 3D and volumetric support, in addition to some extended 2D functions like
ST_ApproximateMedialAxis became a standard PostgreSQL extension
and seems to be a fairly popular extension.
I’ve seen several reports on GIS Stack Exchange of people trying to install PostGIS SFCGAL and getting things such as
ERROR: could not open extension control file /Applications/Postgres.app/Contents/Versions/9.5/share/postgresql/extension/postgis_sfcgal.control
as in this question: SFCGAL in PostGIS problem.
There are two main causes for this:
CREATE EXTENSION postgis_sfcgal; syntax is new in PostGIS 2.2. Although SFCGAL was available in PostGIS 2.1,
you needed to use the sfcgal.sql script in contrib to install it. So if you have PostGIS 2.1, you definitely can’t use the CREATE EXTENSION syntax.
postgis_sfcgal is not a default built extension when you build PostGIS. There are 3 prequisites to get the extension during compile:
a. Need Boost, CGAL, and SFCGAL.
SFCGAL is compiled with CMake similar to pgRouting. Note that Boost and CGAL are also prequisites of pgRouting, so you are almost there if you have pgRouting.
SFCGAL is a relatively thin wrapper for CGAL to allow CGAL functions to be used with SQL/MM 2D/3D types.
b. You need to have in your PostGIS configure script:
--with-sfcgal=/path/to/sfcgal-config as noted in Configuration
If you are getting PostGIS from a package distribution, be warned that few packagers that offer PostGIS 2.2 have postgis_sfcgal.
I’m not even sure if any beyond the Windows application PostGIS 2.2. bundle stackbuilder package have it.
If you want it and your package maintainer doesn’t carry it, perhaps you can encourage them to.
As a related warning:
Although the PostgreSQL extension system will allow it, do not try to use postgis_sfcgal packaged with PostGIS 2.2 with PostGIS 2.1.
The combination will be disastrous because you will end up with 2 PostGIS backends fighting each other.
As of PostGIS 2.3, the postgis extension was changed to no longer allow relocation. All function calls within the extension are now schema qualified.
While this change fixed some issues with database restore, it created the issue of if you installed PostGIS in a schema other than the one you wanted to it is not intuitive how to move it to a different schema. Luckily there is a way to do this.
For this exercise, I will install PostGIS in the default schema and then demonstrate how to move it into another schema location.
You can run these steps using psql or pgAdmin or any other PostgreSQL tool you want.
The error ‘postgis.backend’ is already set comes up every so often in PostGIS mailing list. The issue arises often during or after an upgrade. I’ll go over causes for this I am aware of and how to fix.
The question goes something like this
After upgrading to Postgis 2.3 from 2.1, my server log is filled with these messages :
“WARNING ‘postgis.backend’ is already set and cannot be changed until you reconnect”
This raster question comes up quite a bit on PostGIS mailing lists and stack overflow and the best answer often involves
the often forgotten
ST_Reclass function that has existed since PostGIS 2.0.
People often resort to the much slower though more flexible
ST_MapAlgebra or dumping out
their rasters as Pixel valued polygons they then filter
with WHERE val > 90,
ST_Reclass does the same thing but orders of magnitude faster.