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:
- 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.
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.
Attempted to see if t_srs or a_srs made any difference, still shows 900914.
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??
