search

qgis / QGIS

QGIS is a free, open source, cross platform (lin/win/mac) geographical information system (GIS)
https://qgis.org
GNU General Public License v2.0
10.35k stars 2.98k forks source link

Unable to import layer to PG database with DB Manager: issue with IGNF:LAMB93 #32255

Open DelazJ opened 4 years ago

DelazJ commented 4 years ago

I try to import to a PG database the shapefile at https://www.data.gouv.fr/fr/datasets/delimitation-parcellaire-des-aoc-viticoles-de-linao/#_ - the data is in RGF93 Lambert 93 (old From DB Manager, I select my database and clicked the "Import layer/File..." button (the only things I change is the schema and a comment) image

Trying to import I get the following error

Error 2 Creation of data source "agriculture"."aire_parcellaire" failed: ERREUR: AddGeometryColumn() - SRID must be <= 998999 CONTEXT: fonction PL/pgsql addgeometrycolumn(character varying,character varying,character varying,character varying,integer,character varying,integer,boolean), ligne 47 à RAISE instruction SQL « SELECT public.AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7) » fonction PL/pgsql addgeometrycolumn(character varying,character varying,character varying,integer,character varying,integer,boolean), ligne 5 à instruction SQL

Using

QGIS version 3.9.0-Master QGIS code revision 0eff3733c4
Compiled against Qt 5.11.2 Running against Qt 5.11.2
Compiled against GDAL/OGR 3.1.0dev Running against GDAL/OGR 3.1.0dev
Compiled against GEOS 3.8.0-CAPI-1.13.1 Running against GEOS 3.8.0-CAPI-1.13.1
Compiled against SQLite 3.29.0 Running against SQLite 3.29.0
PostgreSQL Client Version 11.5 SpatiaLite Version 4.3.0
QWT Version 6.1.3 QScintilla2 Version 2.10.8
Compiled against PROJ 7.0.0 Running against PROJ Rel. 7.0.0, March 1st, 2020
OS Version Windows 10 (10.0) This copy of QGIS writes debugging output.
Active python plugins arrayplus; cadastre; DissolveWithStats; firstaid; GroupStats; MapsPrinter; pluginbuilder3; plugin_reloader; db_manager; MetaSearch; processing
DelazJ commented 4 years ago

For information, using 3.8 works fine (the srid is identified as epsg:2154); Might have to do with the proj upgrade?

gioman commented 4 years ago

ERREUR: AddGeometryColumn() - SRID must be <= 998999

@DelazJ it works on 3.4.12 and also I don't think the error comes from QGIS (it seems to come from PostGIS) https://lists.osgeo.org/pipermail/postgis-users/2012-July/034715.html I think this is related to the version of PostGIS you are using.

DelazJ commented 4 years ago

it works on 3.4.12

With which epsg id? i'm on postgresql 9.6 with postgis 2.4 and as stated above, it works on 3.8. That means that there's something changed in QGIS 3.10 that triggers this failure. And afaics, only the srid id is different.

gioman commented 4 years ago

With which epsg id?

the one that comes with the shape. I'm on Postgres 10 and PostGIS 2.5

DelazJ commented 4 years ago

I meant how is it recognized in QGIS? In 3.10, the DB Manager GUI reports IGNF:RGF93LAMB93 - RGF93 Lambert 93 In 3.8 it shows EPSG:2154 - RGF93 / Lambert-93

gioman commented 4 years ago

I meant how is it recognized in QGIS?

in 3.4.12 shows as 2154.

gioman commented 4 years ago

Might have to do with the proj upgrade?

@DelazJ I guess to. The two CRS proj definitions seems identical

+proj=lcc +lat_1=44 +lat_2=49 +lat_0=46.5 +lon_0=3 +x_0=700000 +y_0=6600000 +ellps=GRS80 +towgs84=0.0000,0.0000,0.0000,0,0,0,0 +units=m +no_defs

+proj=lcc +lat_1=49 +lat_2=44 +lat_0=46.5 +lon_0=3 +x_0=700000 +y_0=6600000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs

but IGNF:LAMB93 maybe has no match in the PostGIS spatial_ref_sys table, so the import fails (my guess). Maybe @rouault can help us understand :)

FERRATON commented 4 years ago

I suggest adding the IGNF register to PG : https://geodesie.ign.fr/index.php?p=61&page=documentation#titre3

FERRATON commented 4 years ago

In fact the addition of the IGNF registry in PG does not solve the problem. On the PostGIS tab of the QGIS message log, the message is :

'Requête erronée : SELECT AddGeometryColumn('public','delim_parcellaire_aoc_viticoles_shp','geom',310024140,'MULTIPOLYGON',2) a retourné 7 [ERREUR: AddGeometryColumn() - SRID must be CONTEXT: fonction PL/pgsql addgeometrycolumn(character varying,character varying,character varying,character varying,integer,character varying,integer,boolean), ligne 45 à RAISE instruction SQL « SELECT public.AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7) » fonction PL/pgsql addgeometrycolumn(character varying,character varying,character varying,integer,character varying,integer,boolean), ligne 5 à instruction SQL ] '

The number 310024140 in the AddGeometryColumn(...) is curious because it is the auth_srid and not the srid which is 931007.

Even if you remove the constraint on srid in spatial_ref_sys in PG, the message still appears.

FERRATON commented 4 years ago

I note that in the srs.db database of QGIS the srid of the IGNF:LAMB93 is 310024140, it seems to me that it should be 931007

FERRATON commented 4 years ago

would it be possible to have some explanation of the solution implemented? If I understand correctly, the choice will necessarily be EPSG? it could have been interesting to choose IGNF:LAMB93 for the import in PG since we have a SQL script from IGN to add these projections in PG. It seems to me that lat_1 and lat_2 are inverted between 2154 and IGNF:LAMB93, is it possibly usable?

rouault commented 4 years ago

If I understand correctly, the choice will necessarily be EPSG?

When several high-confidence matches are found by the OGR Shapefile driver, the one from EPSG is indeed preferred as it is the better choice for general interoperability

It seems to me that lat_1 and lat_2 are inverted between 2154 and IGNF:LAMB93, is it possibly usable?

For the Lambert Conic Conformal (2SP) projection, the 2 standard parallels can be indifferently exchanged.

FERRATON commented 4 years ago

OK,Thanks. If I understand the choice of srs when loading the shapefile will be EPSG:2154, but it doesn't solve the problem of exporting to PG if the user explicitly chooses IGNF:LAMB93 as projection. right?

rouault commented 4 years ago

but it doesn't solve the problem of exporting to PG if the user explicitly chooses IGNF:LAMB93 as projection. right?

no,it doesn't

FERRATON commented 4 years ago

So I think we should reopen that ticket...

FERRATON commented 4 years ago

if I modify the function addgeometrycolumn in PG (line 42) with IF ( new_srid_in > 0 ) THEN IF new_srid_in = 310024140 THEN new_srid_in := 931007 ; END IF; --IF new_srid_in > 998999 THEN --EXCEPTION 'AddGeometryColumn() - SRID must be <= %', 998999; --END IF; It is possible to export an IGNF:LAMB93 layer to PG from DBManager. This seems to me to strongly indicate confusion between auth_id and srid when calling the addgeometrycolumn function in QGIS. I haven't found the exact origin of the problem. Changing 310024140 to 931007 in the tbl_srs table of srs.db doesn't seem to be enough.

nyalldawson commented 4 years ago

This seems to me to strongly indicate confusion between auth_id and srid

Correct. The postures provider and dbmanager need to be reworked to lookup a crs by definition from the target postgis install, instead of relying on a hardcoded list of values (which may not exist on the postgis server or may have been altered by a user)

FERRATON commented 4 years ago

Thank you, Nyall. I didn't know that CRSs were hard-coded (maybe in qgscoordinatereferencesystem_legacy.h?). Maybe QGIS could at first use srs.db, which would allow changes to be applied without having to recompile ? It is not only DBManager that is affected by the problem, but also the algorithms for exporting QGIS to PostgreSQL.

disarticulate commented 3 years ago

just came to post Me 2. This has been through QGIS 3.18.2

I'm using a ESRI:103741 srid so i don't even know why that's happening.

gioman commented 3 years ago

ESRI:103741 srid so i don't even know why that's happening.

@disarticulate is that CRS in your spatial_ref_sys?

gubuntu commented 3 years ago

I have the same issue.

Whether I drag a layer into PG in the browser or use DBManager I get

Error 2
Creation of data source "public"."Reservoirs" 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

My CRS is ESRI:102566, which does exist in the target spatial_ref_sys. And 102566 is less than 998999 so why is that even an error?

Also, why is the tool using addgeometrycolumn() in any case when geometry_columns has been a view for years and that function is redundant?

disarticulate commented 3 years ago

@disarticulate is that CRS in your spatial_ref_sys?

@gioman: SELECT * FROM spatial_ref_sys WHERE srid > 100000

103741  "ESRI"  103741  "PROJCS[""unnamed"",GEOGCS[""unnamed ellipse"",DATUM[""unknown"",SPHEROID[""unnamed"",6378498.189,298.2572221008817],TOWGS84[0,0,0,0,0,0,0]],PRIMEM[""Greenwich"",0],UNIT[""degree"",0.0174532925199433]],PROJECTION[""Lambert_Conformal_Conic_2SP""],PARAMETER[""standard_parallel_1"",44.96666666666667],PARAMETER[""standard_parallel_2"",45.33333333333334],PARAMETER[""latitude_of_origin"",44.89138888888889],PARAMETER[""central_meridian"",-94.75],PARAMETER[""false_easting"",500000],PARAMETER[""false_northing"",100000],UNIT[""Foot_US"",0.3048006096012192]]"  "+proj=lcc +lat_1=44.96666666666667 +lat_2=45.33333333333334 +lat_0=44.89138888888889 +lon_0=-94.75 +x_0=152400.3048006096 +y_0=30480.06096012192 +a=6378498.189 +b=6357112.29214201 +towgs84=0,0,0,0,0,0,0 +units=us-ft +no_defs"
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"
900914          "PROJCS[""unnamed"",GEOGCS[""unnamed ellipse"",DATUM[""unknown"",SPHEROID[""unnamed"",6378498.189,298.2572221008791],TOWGS84[0,0,0,0,0,0,0]],PRIMEM[""Greenwich"",0],UNIT[""degree"",0.0174532925199433]],PROJECTION[""Lambert_Conformal_Conic_2SP""],PARAMETER[""standard_parallel_1"",44.96666666666667],PARAMETER[""standard_parallel_2"",45.33333333333334],PARAMETER[""latitude_of_origin"",44.89138888888889],PARAMETER[""central_meridian"",-94.75],PARAMETER[""false_easting"",500000],PARAMETER[""false_northing"",100000],UNIT[""Foot_US"",0.3048006096012192]]"  "+proj=lcc +lat_1=44.96666666666667 +lat_2=45.33333333333334 +lat_0=44.89138888888889 +lon_0=-94.75 +x_0=152400.3048006096 +y_0=30480.06096012192 +a=6378498.189 +b=6357112.29214201 +towgs84=0,0,0,0,0,0,0 +units=us-ft +no_defs "
926915  "epsg"  26915   "PROJCS[""NAD83 / UTM zone 15N"",GEOGCS[""NAD83"",DATUM[""North_American_Datum_1983"",SPHEROID[""GRS 1980"",6378137,298.257222101,AUTHORITY[""EPSG"",""7019""]],AUTHORITY[""EPSG"",""6269""]],PRIMEM[""Greenwich"",0,AUTHORITY[""EPSG"",""8901""]],UNIT[""degree"",0.01745329251994328,AUTHORITY[""EPSG"",""9122""]],AUTHORITY[""EPSG"",""4269""]],UNIT[""metre"",1,AUTHORITY[""EPSG"",""9001""]],PROJECTION[""Transverse_Mercator""],PARAMETER[""latitude_of_origin"",0],PARAMETER[""central_meridian"",-93],PARAMETER[""scale_factor"",0.9996],PARAMETER[""false_easting"",500000],PARAMETER[""false_northing"",0],AUTHORITY[""EPSG"",""26915""],AXIS[""Easting"",EAST],AXIS[""Northing"",NORTH]]"    "+proj=utm +zone=15 +ellps=GRS80 +datum=NAD83 +units=m +no_defs "

I've got tables defined like:

CREATE TABLE operations.site_layout
(
    fid bigint NOT NULL,
    project_id character varying(255) COLLATE pg_catalog."default",
    item character varying(20) COLLATE pg_catalog."default",
    item_date date,
    units character varying(20) COLLATE pg_catalog."default",
    proposed boolean,
    layer character varying(40) COLLATE pg_catalog."default",
    feature_name character varying(32) COLLATE pg_catalog."default",
    area_ac numeric(10,2),
    area_sf numeric(10,2),
    length_lf numeric(10,2),
    length_vf numeric(10,2),
    volume_cy numeric(10,2),
    elevation numeric(10,2),
    geom_point geometry(PointZ,103741),
    geom_line geometry(LineStringZ,103741),
    geom_poly geometry(PolygonZ,103741),
    width_lf numeric(10,2),
    ea numeric(10,2),
    CONSTRAINT site_layout_pkey PRIMARY KEY (fid)
)

Which get brought in just fine. To reproduce:

  1. New file

  2. Scratchlayer w/QGIS CRS: NAD_1983_HARN_Adj_MN_Kandiyohi_Feet

    WKT
PROJCRS["NAD_1983_HARN_Adj_MN_Kandiyohi_Feet",
BASEGEOGCRS["GCS_NAD_1983_HARN_Adj_MN_Kandiyohi",
    DATUM["D_NAD_1983_HARN_Adj_MN_Kandiyohi",
        ELLIPSOID["S_GRS_1980_Adj_MN_Kandiyohi",6378498.189,298.257222100883,
            LENGTHUNIT["metre",1]]],
    PRIMEM["Greenwich",0,
        ANGLEUNIT["degree",0.0174532925199433]],
    ID["ESRI",104733]],
CONVERSION["NAD_1983_HARN_Adj_MN_Kandiyohi_Feet",
    METHOD["Lambert Conic Conformal (2SP)",
        ID["EPSG",9802]],
    PARAMETER["Latitude of false origin",44.8913888888889,
        ANGLEUNIT["degree",0.0174532925199433],
        ID["EPSG",8821]],
    PARAMETER["Longitude of false origin",-94.75,
        ANGLEUNIT["degree",0.0174532925199433],
        ID["EPSG",8822]],
    PARAMETER["Latitude of 1st standard parallel",44.9666666666667,
        ANGLEUNIT["degree",0.0174532925199433],
        ID["EPSG",8823]],
    PARAMETER["Latitude of 2nd standard parallel",45.3333333333333,
        ANGLEUNIT["degree",0.0174532925199433],
        ID["EPSG",8824]],
    PARAMETER["Easting at false origin",500000,
        LENGTHUNIT["US survey foot",0.304800609601219],
        ID["EPSG",8826]],
    PARAMETER["Northing at false origin",100000,
        LENGTHUNIT["US survey foot",0.304800609601219],
        ID["EPSG",8827]]],
CS[Cartesian,2],
    AXIS["(E)",east,
        ORDER[1],
        LENGTHUNIT["US survey foot",0.304800609601219]],
    AXIS["(N)",north,
        ORDER[2],
        LENGTHUNIT["US survey foot",0.304800609601219]],
USAGE[
    SCOPE["unknown"],
    AREA["USA - Minnesota"],
    BBOX[43.49,-97.22,49.38,-89.49]],
ID["ESRI",103741]]
Proj4
+proj=lcc +lat_0=44.8913888888889 +lon_0=-94.75 +lat_1=44.9666666666667 +lat_2=45.3333333333333 +x_0=152400.30480061 +y_0=30480.0609601219 +a=6378498.189 +rf=298.257222100883 +units=us-ft +no_defs
Extent
-97.22, 43.49, -89.49, 49.38

  3. Create generic layer:

{
"type": "FeatureCollection",
"name": "test",
"bbox": [ -0.4038435, -0.1857830, 0.6525332, 1.9571101 ],                                                                          
"features": [
{ "type": "Feature", "properties": { "test": "test" }, "bbox": [ -0.4038435, -0.185783, 0.6525332, 1.9571101 ], "geometry": { "type": "LineString", "coordinates": [ [ 0.2799124, 1.9571101 ], [ -0.4038435, 1.0550882 ], [ 0.6525332, 0.6697436 ], [ -0.0099722, -0.185783 ] ] } }
]
}
  1. Import Layer/File with SRID: ESRI:103741 in QGIS source/target
  2. ERROR:
Error 2
Creation of data source "operations"."New scratch layer" 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

note, I'm on windows 10, connecting to postgis on unix. Perhaps theres weird linefeed issues or some other thing. I agree with @gubuntu that this error message is nonsensical in what we know.

Version:


QGIS version
3.18.2-Zürich
QGIS code revision
515138c171
Compiled against Qt
5.11.2
Running against Qt
5.11.2
Compiled against GDAL/OGR
3.1.4
Running against GDAL/OGR
3.1.4
Compiled against GEOS
3.8.1-CAPI-1.13.3
Running against GEOS
3.8.1-CAPI-1.13.3
Compiled against SQLite
3.29.0
Running against SQLite
3.29.0
PostgreSQL Client Version
11.5
SpatiaLite Version
4.3.0
QWT Version
6.1.3
QScintilla2 Version
2.10.8
Compiled against PROJ
6.3.2
Running against PROJ
Rel. 6.3.2, May 1st, 2020
OS Version
Windows 10 (10.0)
Active python plugins
AnotherDXF2Shape; 
autoSaver; 
changeDataSource; 
d3datavis; 
db-style-manager; 
postgis_geoprocessing; 
QGeoloGIS; 
qgis_resource_sharing; 
track_profile_2_web; 
volume_calculation_tool; 
db_manager; 
processing
disarticulate commented 3 years ago

For anyone who reaches here:

A work around:

Use GDAL from processing "Export to PostgreSQL" under "Vector miscellaneous": image

srdudaramos commented 2 years ago

If you're using DB Manager to load data from QGIS into PostgreSQL+PostGIS DB you need to specify source SRID and Target SRID.

image

This wasn't needed in my previous 3.10 version. I could leave this options unchecked and it worked.

magick93 commented 1 year ago

If with these options unchecked its not working:

image

gubuntu commented 1 year ago

This bug is still not fixed, I've just tried 102564 via DB Manager and dragging into the DB in the Browser and they both give the same error. I tried @disarticulate's workaround but that doesn't work because of #54610