1

Using PostgreSQL 9.6, pgAdmin 4 v3.5 and QGIS

I’ve found quite a bit of info on this issue but no clear answer. I’m attempting to import a shapefile that was created using ArcGIS (don’t know what version) by importing it using the loader for PostGIS. The loader appears to import it correctly and the multipolygon displays correctly in the pgAdmin’s geometry viewer but without having a background map. I have read that when importing Esri shapefiles into PostGIS this is a normal phenomenon but not what to do about it (see SQL after import via loader: below).

I’ve tried adding a SRID by using the 2 following lines and after which appears correctly in the geometry_columns as having SRID 4326.

UPDATE "public"."400_map_grid" SET geom = ST_SetSRID(geom, 4326);
ALTER TABLE "public"."400_map_grid" ALTER COLUMN geom TYPE geometry(MultiPolygon, 4326);

But now the multiploygon no longer appears in the viewer, but the background map now does.

When I import it into QGIS it show to have WGS 84 (4326) but the extent appears to be incorrect (see attached image1):

2977708.89153, 9976225.51161 - 3236908.88834, 10200225.50756 

Other shapefiles I have that I didn’t receive from ArcGIS shows the WGS 84 (4326) with extent:

-97.97695, 29.92911 - -97.52100, 30.75366 (see attached image2).

How do I correct the extent to match the latter?

SQL after import via loader:

-- Table: public."400_map_grid"
-- DROP TABLE public."400_map_grid";

CREATE TABLE public."400_map_grid"
(
gid integer NOT NULL DEFAULT nextval('"400_map_grid_gid_seq"'::regclass),
objectid bigint,
perimeter numeric,
grid_id character varying(20) COLLATE pg_catalog."default",
geom geometry(MultiPolygon),
CONSTRAINT "400_map_grid_pkey" PRIMARY KEY (gid)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public."400_map_grid"
OWNER to postgres;

-- Index: 400_map_grid_geom_idx
-- DROP INDEX public."400_map_grid_geom_idx";

CREATE INDEX "400_map_grid_geom_idx"
ON public."400_map_grid" USING gist
(geom)
 TABLESPACE pg_default;

image1

image2

  • 1
    That data is not 4326, no matter what spatial reference was defined for it. Note that is a violation of best practice to use a numeric in the first character of a database object name, and this will likely cause you pain in the near future. – Vince Nov 13 '18 at 1:10
  • Does the shapefile have a .prj file? If so can you add its contents to the question. – mkennedy Nov 14 '18 at 0:47
0

You might have to take this one step at a time

Does the SHP display correctly in QGIS before you import it into PostGIS?

Are the coordinates of the SHP in WGS84?

The extents, as you point out, are not in WGS84, so I'm wondering if the import SRID's were not set correctly (ie. SRID option set to 4326 in the shapefile loader window?)

Then, once loaded, is the geometry type multipolygon already in PostGIS? The shapefile loader will automatically set the geometry type to Multipolygon unless you force it to SinglePart. So I'm wondering if your step to force the geometry type to multipolygon is corrupting your geometry...

If you can share some info from the steps above (screenshots in QGIS, etc.) that might help...

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.