16

I want to import a shp file to postgreSQL. First I create sql file and then run PostgreSQL. To create sql file, I run this command in windows cmd:

shp2pgsql -s 4326 worldCountries.shp worldcountries postgres > worldcountries.sql

and then run:

psql -d postgres -U postgres -p 4321 -f worldcountries.sql

but result is:

psql: worldcountries.sql: 21: ERROR: function addGeometrycolumn(unknown, unknown, unknown, unknown,unknown, integer) does not exist

Improve this question

4 Answers 4

Reset to default
14 
ERROR: **function addGeometrycolumn**(unknown, unknown, unknown, unknown,unknown, integer) does not exist

It seems that PostGIS is not yet installed. PostGIS is an extension of Postgres which allows the use of geographic files.

Install it and your import will work fine.

Improve this answer
2
  • 3
    And the command is "CREATE EXTENSION postgis;"postgis.net/docs/….
    – user30184
    Commented Aug 18, 2014 at 11:42
  • 1
    @user30184 Absolutely correct, but do note that this assumes PostgreSQL 9.1 or higher. (There is very little reason to use anything older, in my opinion.)
    – jpmc26
    Commented Aug 18, 2014 at 15:06
3

The ERROR: function addGeometrycolumn(unknown, unknown, unknown, unknown,unknown, integer) does not exist means that the postgis extension is not installed on your postgresql. Go here and install it.

Improve this answer
2

You have a different projection in your psql; and having a projection is not necessary. Is there a schema you want to add the shape file to?

This should work:

shp2pgsql -s 4326 -I worldcountries.shp schema.worldcountries > dr_worldcountries.sql

psql -f worldcountries.sql -h [host] -d [database] -U postgres
Improve this answer
1
  • 4
    -p for psql tells it the server's port, not the projection. The projection becomes embedded in the SQL script. See the fine manual for psql's behavior. In this case, the port the OP specifies is not the default port, so it is required. I would also generally advise against storing a shape without a projection. This limits your ability to change projections, which can be helpful for getting more accurate calculations.
    – jpmc26
    Commented Aug 18, 2014 at 15:03
1

You can use the build in import plugin that is installed when you install the PostGis extension in PostgreSQL You can find it under Plugins in the menu and is called the PostGIS Shapefile and DBF loader

watch this youtube video as an example

Improve this answer

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.