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;