Can't connect to PostGIS database

Question

I have a myriad of issues with a seemingly simple process, starting with the tsk described below. But first some background:
Windows 10
QGIS 2.18.5
PgAdmin 4 (v 2.0)
Postgresql 10 installed
-Postgresql database (hosted on Amazon AWS cloud running PostgreSQL 9.6.5)

I'm very new to postgresql and postgis but after following all the basic instructions, I can't seem to be able to view any sql tables in QGIS. Here's what I have set up:
I'm in pgAdmin 4, logged in as admin (I'm the only user anyway), connected to my Amazon AWS server and connected to the new database I've created.

I've enabled the postgis extension (CREATE EXTENSION postgis;) and two others I read I might need...
All good so far?

I have a shapefile called test_poly.shp (created in ArcCatalog, with one simple shape drawn and one string field created) I want to upload to my database, so in the PostGIS Shapefile importer bundled with Postgresql, I connect to my database first:

And then import the shapefile, manually entering 27700 into the SRID field (British National Grid) and setting the 'Shape' field of the shapefile as the Geo Column (am I meant to do this? what is the Geo Column?). I also change the name of the Schema from 'public' to what I renamed it in pgAdmin.

issue: 1- Shapefile import failed...

==============================
Importing with configuration: test_poly, public, geom, D:\PostGIS\Test_poly.shp, mode=c, dump=1, simple=0, geography=0, index=1, shape=1, srid=0
Shapefile type: Polygon
PostGIS type: MULTIPOLYGON[2]
Shapefile import failed.

What's going on here? I've tried changing a few options including changing the input in the Geo Column to 'geom', then MULTIPOLYGON', then 'POLYGON' and back to 'shape', changing the encoding from UTF8 to LATIN1... no help.

This may help you: gis.stackexchange.com/questions/41799/… — Jacob H
Hej Theo. I did have the same issue some years ago, but using the shape file importer from QGIS it worked just fine. The QGIS extension creates a table based on the shapefile, where all records from the table of contents and the geometry are stored. Quite handy ;-) — Jim Jones
@JacobH so I open CMD prompt as admin, change directory to my PostgreSQL\10\bin folder, and run the cmd line and.... >'shp2pgsl' is not recognised as an internal or external command, operable program or batch file. — Theo F

Jim Jones Jim Jones · Accepted Answer · 2018-02-23T15:27:45

Depending on your needs, a nice way to import geometries to PostGIS is using the Import into PostGIS tool from QGIS.

Just go to the toolbox (Advanced Interface) and search for Import into Postgis. The dialog is quite straightforward (The connection to the database needs to be done beforehand) ...

Based on this shapefile it creates the following table structure (Tested with QGIS 2.8.6-Wien):

CREATE TABLE public."tm_world_borders_simpl-0.3"
(
  id integer NOT NULL DEFAULT nextval('"tm_world_borders_simpl-0.3_id_seq"'::regclass),
  geom geometry(MultiPolygon,4326),
  fips character varying(2),
  iso2 character varying(2),
  iso3 character varying(3),
  un integer,
  name character varying(50),
  area integer,
  pop2005 integer,
  region integer,
  subregion integer,
  lon double precision,
  lat double precision,
  CONSTRAINT "tm_world_borders_simpl-0.3_pkey" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public."tm_world_borders_simpl-0.3"
  OWNER TO disco2;

-- Index: public."sidx_tm_world_borders_simpl-0.3"

-- DROP INDEX public."sidx_tm_world_borders_simpl-0.3";

CREATE INDEX "sidx_tm_world_borders_simpl-0.3"
  ON public."tm_world_borders_simpl-0.3"
  USING gist
  (geom);

Another PostGIS extension to import shapefiles is SPIT, but it is no longer maintained and can be quite bumpy with MultiPolygons. There are also other alternatives.

EDIT How to add a new PostGIS Connection using QGIS:

Go to Add PostGIS Layer and click New

The enter your database address and credentials and click OK

Can't connect to PostGIS database

1 Answers