13

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

13
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.

  • 3
    And the command is "CREATE EXTENSION postgis;"postgis.net/docs/…. – user30184 Aug 18 '14 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 Aug 18 '14 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.

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

0

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
  • 3
    -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 Aug 18 '14 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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