I'm not able to add SQL file to postgresql database

Question

I started with basics and did these steps:

• createdb -U postgres -E UTF8 -O Peete gis -OK
• createlang -U postgres plpgsql gis (was already installed) -OK
• psql -U postgres -d gis -f PATH_TO_POSTGRES/share/contrib/postgis-2.1/postgis.sql -OK
• psql -U postgres -d gis -f PATH_TO_FILE/4326.sql (I downloaded file from Spatialreference and chose "PostGIS spatial_ref_sys INSERT statement") - OK
• Then I ran this command shp2pgsql -c -s 4326 -I roads.shp > roads.sql-OK
• Now I wanted to add this SQL file to my database created above so I ran this command: psql -d gis -f roads.sql-FAILED

I got this error message:

ERROR:  AddGeometryColumn() - invalid SRID

CONTEXT:  SQL statement "SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)"

PL/pgSQL function addgeometrycolumn(character varying,character varying,character varying,integer,character varying,integer,boolean) line 5 at SQL statement
********** Error **********

The shapefile I'm using is in WGS84 coordinate system EPSG:4326

Any recommendations?

Answer 1

I was trying to do some changes and this looks like solution since everything works.

I deleted old database and again created new one by creating a spatial database directly in pgAdmin:

• Created new database (name,owner...)
• Opened the query dialog box and wrote

CREATE EXTENSION postgis; and then SELECT postgis_full_version();

This step probably added all coordinate systems to database.After this inside the public schema, you will see one PostGIS-specific metadata table, spatial_ref_sys.

Then i again ran

shp2pgsql -I -s 4326 PATH_TO\roads.shp roads | psql -U postgres -d MyDatabase

Done.