CODEWITHSUNDEEP
postgresqlmapspostgisshapefilegeodjango
Shiva Krishna Bavandla
Shiva Krishna Bavandla

Reputation: 26648

ERROR: function addgeometrycolumn(unknown..), when inserting sql file in to postgis database

I am trying to import the spatial file in to my database

Firstly i have created a database using postgis template as below

createdb -T template_postgis database_name;

I have postgis installed already on my machine 

 POSTGIS="2.1.1 r12113" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER

Postgres version : psql (9.3.2, server 9.1.11)

Secondly i have converted the spatial file in to sql file as below

shp2pgsql -s 3425 Aspire.shp test_for_shape_data database_name > shapefile_data.sql

And now i am trying to import this sql file(shapefile_data.sql) in to my database(database_name) like below

psql -d database_name username  -f shapefile_data.sql

But i am getting the following error

user@user:~/user/spice$ psql -d psql -d database_name username  -f shapefile_data.sql
SET
SET
BEGIN
psql:shapefile_data.sql:30: NOTICE:  CREATE TABLE will create implicit sequence "test_for_shape_data_gid_seq" for serial column "test_for_shape_data.gid"
CREATE TABLE
psql:shapefile_data.sql:31: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_for_shape_data_pkey" for table "test_for_shape_data"
ALTER TABLE
psql:shapefile_data.sql:32: ERROR:  function addgeometrycolumn(unknown, unknown, unknown, unknown, unknown, integer) is not unique
LINE 1: SELECT AddGeometryColumn('','test_for_shape_data','geom','42...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
psql:shapefile_data.sql:33: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:shapefile_data.sql:34: ERROR:  current transaction is aborted, commands ignored until end of transaction block
...........
.......

Why it is telling me that i don't have AddGeometryColumn function even though i have created the database with postgis template ?

So how to avoid this ?

Also when i try to manually enable the postgis functions its telling that already exists

database_name=# CREATE EXTENSION postgis;
ERROR:  type "spheroid" already exists
database_name=# CREATE EXTENSION postgis_topology;
ERROR:  required extension "postgis" is not installed

So how to clear this error and insert the shapefile sql file in to database ?

Upvotes: 0

Views: 3413

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324345

It sounds like you've got an old PostGIS install that was created from an SQL script, pre-extension support. It probably only has some of the functionality and features you expect. This sort of behaviour can occur when you've got a PostGIS 1.5 schema and a PostGIS 2.0 install, etc.

Try creating your DB from template0 instead, and running CREATE EXTENSION postgis; then doing a restore. I suspect your template_postgis contains an old version of the extension schema.

See the PostGIS upgrade guide.

Upvotes: 2

Related Questions