Importing .shp to PostgreSQL?

Question

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

Answer 1

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.

• user30184: And the command is "CREATE EXTENSION postgis;"postgis.net/docs/….
• jpmc26: @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.)

Answer 2

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.

Answer 3

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

• jpmc26: -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.

Answer 4

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