15

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.

3
  • Just noticed: already dealt with at gis.stackexchange.com/questions/43187/…
    – Martin F
    Feb 21, 2014 at 3:47
  • @MartinF There is an (incomplete) answer to this question at the question to which you linked but that question is really asking something very different. Aug 10, 2018 at 14:22
  • This question could also be titled "Installing PostGIS into schema other than public?". Aug 13, 2018 at 14:54

4 Answers 4

5

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;
4

here is your solution:

CREATE SCHEMA gc;

CREATE EXTENSION postgis SCHEMA gc;

If you already installed the postGIS extension in your public schema and want to move in gc then write following code:

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

ALTER EXTENSION postgis 
  SET SCHEMA gc;
2

Kenny Evitt already explained the most important things. Nevertheless, here is some additional information:

Postgresql has databases, tablespaces and schemas, and it is impossible to perform joins from one database to another, because they are strictly seperated. One database can have more than one schema, and it is possible to join something from one schema to another. For further information, look at the docs:

Another possibility to load PostGIS in your gc schema would be the following:

t=# create schema gc;
CREATE SCHEMA
t=# set search_path to gc;
SET
t=# create extension postgis;
CREATE EXTENSION

I am neither an expert on Arc Catalog nor on pgadmin, but I assume one of the following three possibilities happened:

  1. Postgresql supports geometric datatypes natively. But you should not trust this in Geo Contexts, because CRS metadata as well as transformation functionality is not given. You just got lucky and nothing broke.
  2. Arc Catalog somehow, after not finding a PostGIS extension, created it without notifying the User. To check that, you could look at the existing tables before and after that step.

In psql, you can do this by typing:

\d
           List of Relations
 Schema |       Name        |   Type  | Owner 
--------+-------------------+---------+----------
 gc     | geography_columns | View    | postgres
 gc     | geometry_columns  | View    | postgres
 gc     | raster_columns    | View    | postgres
 gc     | raster_overviews  | View    | postgres
 gc     | spatial_ref_sys   | Table   | postgres

If you see this, PostGIS has been installed as an extension. The third possibility would be that ArcCatalog does something like this:

set search_path to $all_schemas;
import to gc;

In this case, you also just got lucky. But maybe, ArcCatalog does something more sophisticated in the end...

0

Just update the search path to include public. So you would run

ALTER DATABASE buzzed_api SET search_path TO gc, publIC;

To set it for the current session only run:

SET search_path = public,gc;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.