[GIS] Add PostGIS spatial functions to a custom schema other than “public” in PostgreSQL

postgispostgis-2.0postgresql

Recently, I created a PostGIS 2.0.3 database on a PostgreSQL 9.1 database server using pgAdmin. The "PostGIS" extension was found installed in "Extensions". Al spatial functions were added to the "public" schema. That's fine.

Now I want to store all my data into a new scheme called "gc". However, how can I make all spatial function installed in that "gc" schema? There is no single function in the schema. However, when I tried to import/new a feature class from ESRI ArcCatalog 10.1 to this schema, it worked! The feature class could be imported and displayed in QGIS.

Could anyone give me any tip or idea about it?

I am new to PostgreSQL and PostGIS.

Best Answer

Here are example commands for installing the PostGIS extension into a gc schema:

CREATE SCHEMA gc;

CREATE EXTENSION postgis SCHEMA gc;

Here are example commands to move PostGIS and its objects to a new schema for version 2.4.4 of PostGIS in case you've already installed it:

CREATE SCHEMA gc;

UPDATE pg_extension
SET extrelocatable = TRUE
WHERE extname = 'postgis';

ALTER EXTENSION postgis
SET SCHEMA gc;

ALTER EXTENSION postgis
UPDATE TO "2.4.4next";

ALTER EXTENSION postgis
UPDATE TO "2.4.4";

This isn't strictly necessary; just helpful:

ALTER DATABASE whatever
SET search_path = public,gc;
Related Question