0

I am having this weird issue that I am trying to wrap my head around to figure out what is wrong but have not yet figured out. Hopefully someone with more experience would be able to provide some input or feedback.

Installed on my OS QGIS 3.22 PostgreSQL 14.2 PostGIS 3.2

I have created a DB with PostGIS extension. I have a bunch of geopackages that are correct in projection epsg:900913 that I want to load into the DB. I do this usually using ogr2ogr commands for example below

ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres dbname=test password=admin port=5432" "grid.gpkg" -t_srs "EPSG:900913" -nln boundaries."grid" -lco LAUNDER=NO

the geopackage loads fine but seems when I check the SRID in PGAdmin it shows SRID 900914. Checking in QGIS the tables in PostgreSQL db shows 900913. When I try to run some PostGIS commands like ST_Intersect it will show unknown coordinate system 900914 and fail.

Things I have attempted/searched on:

  1. Checking the spatial_ref_sys table, I found the SQL file in D:\Program Files\PostgreSQL\14\share\contrib\postgis-3.2 and in the SQL I did find a reference to

INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES (900913,'spatialreferencing.org',900913,'PROJCS["Popular Visualisation CRS / Mercator (deprecated)",GEOGCS["Popular Visualisation CRS",DATUM["Popular_Visualisation_Datum",SPHEROID["Popular Visualisation Sphere",6378137,0,AUTHORITY["EPSG","7059"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6055"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4055"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Mercator_1SP"],PARAMETER["central_meridian",0],PARAMETER["scale_factor",1],PARAMETER["false_easting",0],PARAMETER["false_northing",0],AUTHORITY["EPSG","3785"],AXIS["X",EAST],AXIS["Y",NORTH]]','+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs' ); COMMIT; BEGIN;

Just looking at how it is coded, its not referencing 'EPSG' but spatialreference.org, so I attempted to add the "spatialreference.org:900913" in the ogr2ogr line but it says that it was invalid. "EPSG:900913" seems to work though.

  1. Attempted to UpdateGeometrySRID in pgadmin to 900913 (from 900914) and that seems to give the correct 900913 when using Find_SRID, but when comparing in QGIS, the geometries are shifted by on avg 10km.

  2. Attempted to see if t_srs or a_srs made any difference, still shows 900914.

  3. Attempted to manually load the table into PostgreSQL vs QGIS DB manager, setting the target as 900913 but this error pops up.

Error 2 Creation of data source "boundaries"."HexGrid_5km_2" failed: ERROR: AddGeometryColumn() - SRID must be <= 998999 CONTEXT: PL/pgSQL function addgeometrycolumn(character varying,character varying,character varying,character varying,integer,character varying,integer,boolean) line 45 at RAISE SQL statement "SELECT public.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

Any ideas or suggestions on what I am doing wrong? Or last resort should I just be working in EPSG:3857 instead??

4
  • 3
    You should be using 3857, 900913 was deprecated years ago
    – Ian Turton
    Aug 31, 2022 at 5:56
  • 1
    here is an explanation about the 900914. It is basically the max locally known SRID + 1 :-)
    – JGH
    Aug 31, 2022 at 13:04
  • Looks like 3857 is the way to go, but just curious as the 900913 is in the spatial_ref_sys, shouldn't we be able get the table into 900913 projection? Aug 31, 2022 at 16:36
  • 900913 was never an EPSG code
    – nmtoken
    Sep 23, 2022 at 9:01

1 Answer 1

0

I had a similar problem using org2ogr from GDAL 3.4 version. I guess something has changed in GDAL in the 3.4 version. GDAL identifying SRID from prj in shapefiles

1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Sep 23, 2022 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.