Postgis installation: type "geometry" does not exist

I am trying to create table with Postgis. I do it by this page. But when I import postgis.sql file, I get a lot of errors:

ERROR:  type "geometry" does not exist

Does anybody know how can I fix it?

106909 次浏览
小开

To get psql to stop on the first error, use -v ON_ERROR_STOP=1 (which is off by default, which is why you see many errors). For example:

psql -U postgres -d postgis -v ON_ERROR_STOP=1 -f postgis.sql

The actual error is something like "could not load library X", which can vary on your situation. As a guess, try this command before installing the sql script:

ldconfig

(you might need to prefix with sudo depending on your system). This command updates the paths to all system libraries, such as GEOS.

小开
最佳答案

I had the same problem, but it was fixed by running following code

CREATE EXTENSION postgis;

In detail,

  1. open pgAdmin
  2. select (click) your database
  3. click "SQL" icon on the bar
  4. run "CREATE EXTENSION postgis;" code
小开

Or...

cursor.execute('create extension postgis')

in your python program, using a current cursor from psycopg2.

小开

You can do it from terminal:

psql mydatabasename -c "CREATE EXTENSION postgis";
小开

You must enable the extension on your database.

psql my_database -c "CREATE EXTENSION postgis;"

小开

If the Postgis-Extension is loaded, then your SQL perhaps does not find the geometry-type because of missing search-path to the public schema.

Try

SET search_path = ..., public;

in the first line of your scsript. (replace ... with the other required search-paths)

小开

The answers here may solve your problem, however if you already have postgis enabled on your DB, the issue may be that you are trying to restore a postgis table (with a geometry column) into a schema other than where your postgis extension is enabled. In pgAdmin you can click on the postgis extension and see which schema is specified. If you are trying to restore a table with geometry column into a different schema, you might get this error.

I resolved this by altering my postgis extension - however I'm not sure if that was necessarily the best way to do it. All I know is that it allowed me to restore the table.

小开

First make sure you have (matching to pg version: psql -V) postgis installed:

sudo apt install postgis postgresql-9.6-postgis-2.3

Just before tables creation add:

db.engine.execute('create extension postgis')
db.create_all()
小开

You also need to ensure that the user you are trying to use the postgis extension as, has access to the schema where postgis is setup (which in the tutorials I read is called 'postgis').

I just had this error, and it was solved because I had only given a new user access to the database. In the database I'd created, I ran:

grant all on schema postgis to USERNAME;

And this solved this error

小开

This error may also occur if you try to use postgis types on another schema rather than public.

If you are creating you own schema, using postgis 2.3 or higher and encounter this error, do the following as stated here:

CREATE SCHEMA IF NOT EXISTS my_schema;
CREATE extension postgis;


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


ALTER EXTENSION postgis
SET SCHEMA my_schema;


ALTER EXTENSION postgis
UPDATE TO "2.5.2next";


ALTER EXTENSION postgis
UPDATE TO "2.5.2";


SET search_path TO my_schema;

Then you can proceed to use postgis functinalities.

小开

This has already been answered but I wanted to add a more thorough answer that explains why certain commands work, and in what circumstances to use them, and of course, how to figure out which circumstances you are in.

First, you need to check that PostGIS is actually installed on your box. When connected to postgres, such as via psql, run:

SELECT PostGIS_Full_Version();

If it's not installed, look up distro- and version-specific instructions for installing PostGIS and install it.

Assuming PostGIS is installed, the error is usually the result of not having "created" (this is an unfortunately misleading use of language, the effect is more like "enabling" the extension) the extension for the particular database. The way PostgreSQL is set up, by default new databases do not come with any extensions enabled, and you need to enable ("create") them per-database. In order to do this you need to run the following command.

It only needs to be run once:

CREATE EXTENSION postgis;

I think you need superuser privileges for the particular database in question, in order to run this command.

Assuming postgres is configured so that the permissions allow, you can execute this command from the command line by running the following command:

psql my_database -c "CREATE EXTENSION postgis;"

You may need to use the -U flag and specify a user.

In some cases, however, the extension may have already been created, and installed under a different schema than public, and the problem may be one of permissions. This can arise like in the situation @mozboz describes, if you create a new user but don't give it access to the schema. To detect this case, look for a separate schema in the database, with a table called spatial_ref_sys, as this is created when the extension is created.

In this case you may need to run, when connected to the database:

GRANT USAGE ON SCHEMA schema_name TO username;

In my experience, this situation is rare, and I have never found any reason to set things up this way. The schema_name is often, but not always postgis. By default if you run the first command here, it will create the extension under the public schema.

I think USAGE is usually sufficient for most cases, but you might want to grant more privileges if you want the user to be able to actually edit data; the only time this has ever come up for me was adding new projections to spatial_ref_system, but even this is rare as by default that table includes most commonly used projections.

小开

My experience was I was trying to load the ism file to a different file than "public". I initialised the postgis extension on another schema other than public. So that didn't work. So finally I had to remove the extension, and than created the postgis extension in public. After that I could load the osm into my new schema

Osm always looks for the extension file in public, irregardless of where u intend to install the osm files in another schema

小开

run this query first: "CREATE EXTENSION postgis"

小开

Verify the public search_path is not included for the user:

SELECT usename, useconfig from pg_user;
-- { search_path=pg_catalog, public }


SHOW SEARCH_PATH;
-- public, topology

Method #1: SET the search_path to public


SET search_path = "public";


CREATE TABLE IF NOT EXISTS sample_geom
(
geom_1 GEOMETRY,
geom_2 GEOMETRY(Polygon, 4326) NOT NULL
);


ALTER TABLE sample_geom
OWNER TO root;

Method #2: Use the qualified object name for the GEOMETRY object type (public.GEOMETRY)


CREATE TABLE IF NOT EXISTS sample_geom
(
geom_1 public.GEOMETRY,
geom_2 public.GEOMETRY(Polygon, 4326) NOT NULL
);


ALTER TABLE sample_geom
OWNER TO root;

Source: Demystifying Schemas & search_path through Examples


提交答案