CODEWITHSUNDEEP
postgresqlpostgis
Richard
Richard

Reputation: 61289

PostgreSQL (shp2pgsql) AddGeometryColumn gives "No function matches the given name"

I'm working with the PADUS OBI shape file, not that that's probably important.

I'm running the shape file through shp2pgsql using the default options, as in:

shp2pgsql PADUS_1_1_CBI_Edition.shp > PADUS.sql

Then I'm trying to import the SQL into Postgres by doing:

psql -d padusdb -f PADUS.sql

And getting the following error:

psql:PADUS.sql:36: ERROR:  function addgeometrycolumn(unknown, unknown, unknown, unknown, unknown, integer) does not exist
LINE 1: SELECT AddGeometryColumn('','padus_1_1_cbi_edition','the_geo...
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I have PostGIS installed.

The SQL commands leading to the error (being put into an otherwise empty database) are:

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "padus_1_1_cbi_edition" (gid serial PRIMARY KEY,
"us_id" int4,
"category" varchar(10),
"gis_acres" numeric,
...
BUNCH OF COLUMNS, none of which is called "the_geom"
...
"comments" varchar(200),
"shape_leng" numeric,
"shape_area" numeric);
SELECT AddGeometryColumn('','padus_1_1_cbi_edition','the_geom','-1','MULTIPOLYGON',2);
COMMIT;

Any thoughts on what this might mean and how to resolve the problem?

Upvotes: 3

Views: 11713

Answers (4)

RD Harles
RD Harles

Reputation: 58

Thanks atorres757! Your answer solved my problem in minutes. I deleted my database and created a new database and choose the template_postgis as my template. All shapefiles are importing fine with my python script like this:

for lyr in iList:

os.system("shp2pgsql -c -s 4326 -k -I -W UTF-8 "+lyr[:-4]+" "+lyr[:-4]+" | psql -d AWM -p 5432 -U postgres")

Upvotes: 0

atorres757
atorres757

Reputation: 619

It also sounds like the database needs to be spatially enabled. The reason it's throwing that errors is because the function is missing. This resource has a quick and easy answer and solution.

Upvotes: 1

Richard
Richard

Reputation: 61289

So, as it turns out, it is not enough to simply have installed PostGIS on one's machine.

Originally, I'd chosen sudo apt-get install postgresql postgis on Ubuntu 10.10. This left me with a working version of PostGRE 8.4, but no sign of PostGIS.

Therefore, I tried sudo apt-get install postgresql-8.4-postgis.

But one's work doesn't end there! You need to set up the PostGIS database.

This website provides instructions on doing this and using the database afterwards.

Upvotes: 5

David Chan
David Chan

Reputation: 7505

this error indicates that the function cannot be recognized (either function name or parameters types are incorrect)

this is the definitions for AddGeometryColumn in v7.2

text AddGeometryColumn(varchar table_name, varchar column_name, integer srid, varchar type, integer dimension);

text AddGeometryColumn(varchar schema_name, varchar table_name, varchar column_name, integer srid, varchar type, integer dimension);

text AddGeometryColumn(varchar catalog_name, varchar schema_name, varchar table_name, varchar column_name, integer srid, varchar type, integer dimension);

it looks to me like you're trying to use the 2nd definition, try changing it to use the first definition (no schema) and try unquote the srid (-1) since it should be passed as an integer.

You may need to cast everything...

Upvotes: 0

Related Questions