[GIS] Importing shapefile issue PostGIS, no import made or recognized reference

importpostgisshp2pgsql

I am doing an exercise where I am about to load shapefiles into a PostGIS-database. I am doing it from an already created database and schema, this will mean that the only new item is the table that is being loaded into the schema and database.

I have been following Adding shapefiles to PostGIS database and if I left out everything on the left side of the "|", it would simulate the import but not make the table itself in the database. So I could confirm it worked.

I am guessing that shp2pgsql doesn't evaluate the shapefile table and generate a copy of the table in psql but it merely imports the shapefile. This resonated because I have been using the PostGIS Shapefile Import/Export Manager.

It seems like the only way I can import a shapefile into a PostGIS DB. But it isn't very automated, which is the case of the task I am doing.

Does anyone have any comments on it?

This is my string that works:

C:\Program Files\PostgreSQL\9.5\bin>shp2pgsql -s SRID PATH_TO_FOLDER\SHAPEFILE.shp SCHEME.TABLE

But it needs the table to be created before the import, it doesn't create the table on import. Furthermore. It doesn't import. This string only simulates the import.

I then tried to do as the cheatsheet made an example about. It includes the "|"

But it only gives me this output:

ERROR: relation "schema.table" does not exist.

Which I read into that fact that shp2pgsql only imports the shapefile if there is a table already.

I then tried to import it with PostGIS Shapefile Import/Export Manager as before mentioned. It imports it (In absolute no time) and creates a table with no more information than otherwise needed. But now the table is there in the database.

So I try again with the table created already in PostGIS, importing the shapefile from the string mentioned before. This time with the credentials left of the "|".

so it looks like this: 

 C:\Program Files\PostgreSQL\9.5\bin>shp2pgsql -s SRID Path_To_folder\SHAPEFILE.shp schema.table | psql -h localhost -d postgres -U postgres

And it outputs this:

ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK ANALYZE

Is there another way I could do this. Already looked into GDAL's SQL statement but no luck either. Maybe there is a methodology that I could try differently in order to narrow down possibilities of errors occurring?

Best Answer

Essential to solving this issue is to make the import in two steps.

  1. First one is to import the setup of the table. Ie. the names of the columns and the structure of the table. But not yet the content.

The code will look like this: 

     shp2pgsql -p -s SRID PATH_TO_SHAPEFILE\Shapefile.shp SCHEMEname.TABLEname | psql -h HOST -d Database -U User

Worth noticing is the flag: "-p" that, according to this cheatsheet means "Prepare mode, only creates the table."

  1. Second step is to make the flag "-d" that: "Drops the table, then recreates it and populates it with current shape file data."

     shp2pgsql -d -s SRID PATH_TO_SHAPEFILE\Shapefile.shp SCHEMEname.TABLEname | psql -h HOST -d Database -U User

Eventually run: ANALYSE VERBOSE SCHEME.TABLE too during the operation to check for changes. Logging into the database required.

Related Question