Importare OpenFileGDB in PostGIS usando ogr2ogr
Closed this issue · 7 comments
Ho un file DBGT_10K_22_V01.gdb (pesa circa 5 GB con oltre 300 tabelle, relazioni e domini), contiene semplici tabelle e tabelle con attributi geometrici (MultiPolygonZM, MultiLineStringZ e PointZM), per importarlo in un database PostGIS (PostGreSQL 14 con PostGIS 3.3.1) ho lanciato da OSGeo4W Shell (con GDAL/OGR 3.6) :
ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=DBGT_10K_22_V01 user=postgres password=xxxxxx" \
"E:\yyyyyyy\DBGT_10K_22_V01\DBGT_10K_22_V01.gdb" -overwrite -progress -skipfailures \
-nlt MULTIPOLYGONZM,MULTILINESTRINGZ,POINTZM --config PG_USE_COPY YES --config OGR_ORGANIZE_POLYGONS SKIP
dove:
-overwrite
: sovrascrive-progress
: compare la famosa progressione di GDAL 0...10...20...-skipfailures
: continua dopo un errore, saltando la funzione fallita.-nlt MULTIPOLYGONZM,MULTILINESTRINGZ,POINTZM
: definisce il tipo di geometria--config PG_USE_COPY YES
: velocizza notevolmente la copia--config OGR_ORGANIZE_POLYGONS SKIP
: salta il controllo degli anelli interni ai poligoni velocizzando il processo
fonti:
- ogr2ogr: https://gdal.org/programs/ogr2ogr.html
- https://gis.stackexchange.com/questions/83016/how-to-import-esri-geodatabase-format-gdb-into-postgis
- https://gis.stackexchange.com/questions/431106/importing-a-gdb-file-into-postgis-using-ogr2ogr
- conversione domini: https://gis.stackexchange.com/questions/107062/converting-file-geodatabase-with-coded-domains-to-postgresql-postgis
dopo 30 minuti ha importato solo 73 tabelle (lo vedo da pgAdmin4):
dopo 40 minuti e 97 tabelle importate, si ferma tutto:
L'errore nasce appena inizia a caricare le tabelle con geometria!!!
EDIT:
ahhhhhhhhhhhhhhhh
NON avevo installato l'estensione PostGIS, rifaccio
Per non perdere troppo tempo, faccio dei test di import con singole tabelle:
"AC_PED_AC_PED_SUP_SR"
dovrebbe essere MULTIPOLYGONZM
C:\OSGeo4W>ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=DBGT_10K_22_V01 user=postgres \
password=xxxxx" "E:\yyyyyy\DBGT_10K_22_V01\DBGT_10K_22_V01.gdb" "AC_PED_AC_PED_SUP_SR" -overwrite -progress \
-skipfailures -nlt MULTIPOLYGONZM --config PG_USE_COPY YES --config OGR_ORGANIZE_POLYGONS SKIP
importa la tabella ma, da una verifica, la geometria risulta solo MULTIPOLYGON
, come se ignorasse -nlt MULTIPOLYGONZM
SELECT DISTINCT ST_GeometryType(shape)
FROM public.ac_ped_ac_ped_sup_sr
group by 1;
C:\OSGeo4W>ogrinfo -al -so E:/yyyyyy/DBGT_10K_22_V01/DBGT_10K_22_V01.gdb AC_PED_AC_PED_SUP_SR
INFO: Open of `E:/Planetek/DBGT_10K_22_V01/DBGT_10K_22_V01.gdb'
using driver `OpenFileGDB' successful.
Layer name: AC_PED_AC_PED_SUP_SR
Geometry: 3D Multi Polygon
Feature Count: 9143
Extent: (428095.344000, 4310357.680000) - (566877.565000, 4567625.048000)
Layer SRS WKT:
PROJCRS["RDN2008 / UTM zone 32N",
BASEGEOGCRS["RDN2008",
DATUM["Rete Dinamica Nazionale 2008",
ELLIPSOID["GRS 1980",6378137,298.257222101,
LENGTHUNIT["metre",1]]],
PRIMEM["Greenwich",0,
ANGLEUNIT["degree",0.0174532925199433]],
ID["EPSG",6706]],
CONVERSION["UTM zone 32N",
METHOD["Transverse Mercator",
ID["EPSG",9807]],
PARAMETER["Latitude of natural origin",0,
ANGLEUNIT["degree",0.0174532925199433],
ID["EPSG",8801]],
PARAMETER["Longitude of natural origin",9,
ANGLEUNIT["degree",0.0174532925199433],
ID["EPSG",8802]],
PARAMETER["Scale factor at natural origin",0.9996,
SCALEUNIT["unity",1],
ID["EPSG",8805]],
PARAMETER["False easting",500000,
LENGTHUNIT["metre",1],
ID["EPSG",8806]],
PARAMETER["False northing",0,
LENGTHUNIT["metre",1],
ID["EPSG",8807]]],
CS[Cartesian,2],
AXIS["(E)",east,
ORDER[1],
LENGTHUNIT["metre",1]],
AXIS["(N)",north,
ORDER[2],
LENGTHUNIT["metre",1]],
USAGE[
SCOPE["GIS."],
AREA["Italy - onshore and offshore - west of 12┬░E."],
BBOX[36.53,5.93,47.04,12]],
ID["EPSG",7791]]
Data axis to CRS axis mapping: 1,2
FID Column = OBJECTID
Geometry Column = Shape
AC_PED_ZON: String (80.0) NOT NULL, alternative name="Zona", domain name=D_AC_PED_Zona
SubRegID: String (70.0) NOT NULL
AC_PED_LIV: String (80.0) NOT NULL, alternative name="Livello", domain name=D_AC_PED_Livello
AC_PED_FON: String (80.0) NOT NULL, alternative name="Fondo", domain name=H_AC_PED_Fondo
ClassREF: String (70.0) NOT NULL
AC_PED_SED: String (80.0) NOT NULL, alternative name="Sede", domain name=H_AC_PED_Sede
Shape_Length: Real (0.0) DEFAULT FILEGEODATABASE_SHAPE_LENGTH
Shape_Area: Real (0.0) DEFAULT FILEGEODATABASE_SHAPE_AREA
ma dallo script di sopra, la geometry è 3D Multi Polygon
cioè MULTIPOLYGONZ
quindi riprovo con -nlt MULTIPOLYGONZ
niente da fare, lascia la geometriA come MULTIPOLYGON
, sotto il comando usato
ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=DBGT_10K_22_V01 user=postgres password=xxxxxx" \
"E:\yyyyyyy\DBGT_10K_22_V01\DBGT_10K_22_V01.gdb" "AC_PED_AC_PED_SUP_SR" -nln test -overwrite -progress -skipfailures \
-nlt MULTIPOLYGONZ --config PG_USE_COPY YES --config OGR_ORGANIZE_POLYGONS SKIP
EDIT:
SELECT GeometryType(shape),ST_AsText(shape)
FROM public.AC_PED_AC_PED_SUP_SR
ma allora, con quale funzione si scopre il vero tipo di geometria in PostGIS?
Per ottenere la lista di tutti i livelli presenti nel DBGT:
ogrinfo -so E:/yyyyyy/DBGT_10K_22_V01/DBGT_10K_22_V01.gdb
sotto un estratto:
INFO: Open of `E:/yyyyyy/DBGT_10K_22_V01/DBGT_10K_22_V01.gdb'
using driver `OpenFileGDB' successful.
Layer: SP_ACQ_SP_ACQ_NOM_T (None)
Layer: ARGINE (None)
Layer: GZ_FER_GZ_FER_TY (None)
Layer: INVASO_INVASO_NOM_T (None)
Layer: GZ_CIC_GZ_CIC_TY (None)
Layer: CS_MAR_CS_MAR_LIN_CS_MAR_NOM_T (None)
Layer: ND_GAS_ND_GAS_TY (None)
Layer: SV_FER_SV_FER_FUN (None)
Layer: SV_FER_SV_FER_NOM_T (None)
...
Group SARDEGNA_DBGT:
Layer: CANALE_CANALE_BSU_L (3D Measured Multi Line String)
Layer: AR_VRD_AR_VRD_SUP_L (3D Measured Multi Line String)
Layer: CANALE_CANALE_PER (3D Measured Multi Line String)
Layer: AC_CIC_AC_CIC_SUP_SR (3D Multi Polygon)
Layer: AR_VRD_AR_VRD_SUP (3D Multi Polygon)
...
Group SIMBOLOGIA:
Layer: SIMBOLOGIA_ARC (3D Measured Multi Line String)
Layer: SIMBOLOGIA_PLG (3D Measured Multi Polygon)
Layer: SIMBOLOGIA_PTS (3D Measured Point)
Per importare in PostGIS solo i layer del DBGT con attributo geometrico (dopo avere indagato con ogrinfo
), eseguo questo script nella OSGeo Shell:
for %f in (CANALE_CANALE_BSU_L AR_VRD_AR_VRD_SUP_L CANALE_CANALE_PER AC_CIC_AC_CIC_SUP_SR AR_VRD_AR_VRD_SUP EDIFC_CR_EDF_IS MN_MAU_MN_MAU_SUP_P AR_STR_AR_STR_SUP_SR CANALE_CANALE_ASU_L CANALE_CANALE_BSU CANALE_CANALE_ASU AB_CDA_AB_CDA_SUP_SR F_NTER_F_NTER_SU_P CV_LIV_CV_LIV_LIN_SG ALVEO_A_ALVEO_A_SU_L OP_POR_OPPR_SUP EDI_MIN_CR_EDF_IS ALVEO_ALVEO_SUP_L F_NTER_F_NTER_SU MU_DIV_MU_DIV_SUP_L MU_SOS_MSOS_SUP_L EDIFC_CR_EDF_ME_SR ES_AMM_ES_AMM_PER ALVEO_A_ALVEO_A_SU CONDOT_CONDOT_CL_SG ACC_PC_ACC_PC_POS EL_TRV_EL_TRV_TRA_SG STATO_STATO_LIM EL_STR_EL_STR_TRA_SG MAN_TR_MAN_TR_SUP_L CL_AGR_CL_AGR_SUP TRALIC_TRAL_BAS EDI_MIN_CR_EDF_ME AB_CDA_AB_CDA_SUP AR_MAR_AR_MAR_SUP_SR AB_CDA_AB_CDA_SUP_SG EL_FER_EL_FER_TRA_SG STATO_STATO_LIM_SG SCR_CR_LINEAXSCR AR_VMS_AR_VMS_SUP_L MAN_TR_MAN_TR_SUP STATO_STATO_EXT SCARPT_SCARPT_SUP_L TRALIC_TRAL_BAS_P CL_AGR_CL_AGR_FIL SCR_CR_BOUNDXSCR CASCATA_CASCATA_SU ALVEO_ALVEO_SUP OP_REG_OP_REG_SUP MN_IND_MN_IND_SUP SD_FER_SD_FER_SUP_SR OP_POR_OPPR_SUP_L TP_STR_TP_STR_PER PT_QUO AR_VMS_AR_VMS_SUP MN_MAU_MN_MAU_SUP CV_AES_CV_AES_EXT_SR ASTA_F_ASTA_F_PER TR_COM FOR_PC_FOR_PC_SUP_SR RT_MET REGION AB_CDA_AB_CDA_SUP_L AR_STR RT_IDR TR_SAC ES_AMM_ES_AMM_TRA EL_ACQ_EL_ACQ_TRA_SG PS_INC AC_PED_AC_PED_SUP_SR GZ_TRV CAPOSD ACC_PC_ACC_PC_ACC LOC_SG AR_MAR SV_AER SCARPT_SCARPT_SUP EDI_MIN_CR_EDF_ME_SR FOR_PC_FOR_PC_SUP_L ASTA_F_ASTA_F_BSU ACC_INT EL_TRV CV_AES SV_POR GZ_VMS AATT TR_OLE SCR_CR_SFONDXSCR TR_GAS BI_IND AC_CIC FOR_PC_FOR_PC_SUP ALVEO_ALVEO_SUP_SR MU_SOS_MSOS_SUP DRE_SUP ALVEO_A_ALVEO_A_SU_SR ALBERO EDIFC_CR_EDF_ME DIGA_DIGA_SUP_L PROVIN A_PVEG GZ_MET Z_FOTO ND_OLE SC_DIS LIM_COM MN_IND_MN_IND_SUP_P AC_VEI_AC_VEI_SUP_SR EL_FUN_EL_FUN_TRA_SG EL_FUN EL_STR CS_MAR_CS_MAR_LIN_SG META MN_CON_MN_CON_SUP_SR PALO ARGINE_ARGN_SUP_SR CV_LIV SCR_CR_POINTXSCR CONDOT PAR_AR A_TRAS SCARPT_SCARPT_SUP_SG EL_FER SV_STR P_CCAT ND_ELE RT_CIC EL_MET_EL_MET_TRA_SG AT_NAV V_RETE EL_MET RT_TRV EL_CIC_EL_CIC_TRA_SG CASCATA_CASCATA_SU_L RT_ST1 AR_VMS_AR_VMS_SUP_SR ND_COM MU_DIV_MU_DIV_SUP COMUNE CT_MET ND_SAC BOSCO_BOSCO_SUP_SR EL_CIC ASTA_F_ASTA_F_ASU EL_VMS_EL_VMS_TRA_SG SP_ACQ_SP_ACQ_SUP_SG RT_VMS RT_FUN AF_ACQ P_FTGR DIGA_DIGA_SUP_SR ZONA_R P_FCAT OP_REG_OP_REG_SUP_L ACQ_IN EL_ACQ PONTE ASTA_F_ASTA_F_ASU_L CV_DIS ASTA_F_ASTA_F_BSU_L TR_ELE OP_REG_OP_REG_SUP_SR ARGINE_ARGN_SUP_L MN_ARR BRK_LN MN_INT TR_AAC UN_VOL GALLER ATTR_SP SED_AMM GZ_STR PT_BTM GHI_NV EL_FNE RT_ST2 TP_STR_TP_STR_TRA DIGA_DIGA_SUP AC_PED ELE_CP P_TRAR SV_ATR EL_VMS ND_IDR FIL_AL ND_AAC LN_BTM GZ_FUN EL_IDR_EL_IDR_TRA_SG SD_FER INVASO_INVASO_SUP_SG MN_CON RT_IDN CM_MON EL_IDR RT_FER EL_DIV PE_UINS SV_FER AC_VEI ND_GAS CS_MAR GZ_CIC INVASO BOSCO GZ_FER ARGINE_ARGN_SUP SP_ACQ DRE_SUP_PLG SIMBOLOGIA_ARC SIMBOLOGIA_PLG SIMBOLOGIA_PTS ) do (ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=DBGT_10K_22_V01 user=postgres password=xxxxxx" "E:\yyyyyyy\DBGT_10K_22_V01\DBGT_10K_22_V01.gdb" %f -overwrite -progress -skipfailures --config PG_USE_COPY YES --config OGR_ORGANIZE_POLYGONS SKIP)
tempo necessario 102 minuti!!
Sembra che l'importazione delle geometrie è corretta:
SELECT
objectid,
classref,
shape,
ST_GeometryType(shape),
ST_AsText(shape),
ST_Dimension(shape),
ST_NDims(shape)
FROM
public.edifc_cr_edf_is
LIMIT
10;
C:\OSGeo4W>ogrinfo -al -so E:/yyyyyy/DBGT_10K_22_V01/DBGT_10K_22_V01.gdb EDIFC_CR_EDF_IS
INFO: Open of `E:/Planetek/DBGT_10K_22_V01/DBGT_10K_22_V01.gdb'
using driver `OpenFileGDB' successful.
Layer name: EDIFC_CR_EDF_IS
Geometry: 3D Measured Multi Polygon
Feature Count: 496486
Extent: (427635.160000, 4303217.100000) - (570114.019000, 4573151.783000)
Layer SRS WKT:
PROJCRS["RDN2008 / UTM zone 32N",
BASEGEOGCRS["RDN2008",
DATUM["Rete Dinamica Nazionale 2008",
ELLIPSOID["GRS 1980",6378137,298.257222101,
LENGTHUNIT["metre",1]]],
PRIMEM["Greenwich",0,
ANGLEUNIT["degree",0.0174532925199433]],
ID["EPSG",6706]],
CONVERSION["UTM zone 32N",
METHOD["Transverse Mercator",
ID["EPSG",9807]],
PARAMETER["Latitude of natural origin",0,
ANGLEUNIT["degree",0.0174532925199433],
ID["EPSG",8801]],
PARAMETER["Longitude of natural origin",9,
ANGLEUNIT["degree",0.0174532925199433],
ID["EPSG",8802]],
PARAMETER["Scale factor at natural origin",0.9996,
SCALEUNIT["unity",1],
ID["EPSG",8805]],
PARAMETER["False easting",500000,
LENGTHUNIT["metre",1],
ID["EPSG",8806]],
PARAMETER["False northing",0,
LENGTHUNIT["metre",1],
ID["EPSG",8807]]],
CS[Cartesian,2],
AXIS["(E)",east,
ORDER[1],
LENGTHUNIT["metre",1]],
AXIS["(N)",north,
ORDER[2],
LENGTHUNIT["metre",1]],
USAGE[
SCOPE["GIS."],
AREA["Italy - onshore and offshore - west of 12┬░E."],
BBOX[36.53,5.93,47.04,12]],
ID["EPSG",7791]]
Data axis to CRS axis mapping: 1,2
FID Column = OBJECTID
Geometry Column = Shape
ClassREF: String (70.0) NOT NULL
Shape_Length: Real (0.0) DEFAULT FILEGEODATABASE_SHAPE_LENGTH
Shape_Area: Real (0.0) DEFAULT FILEGEODATABASE_SHAPE_AREA
concludendo, il comando che importa il DBGT in un database PostGIS è:
ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=DBGT_10K_22_V01 user=postgres password=xxxxx" "E:\yyyyyyy\DBGT_10K_22_V01\DBGT_10K_22_V01.gdb" -overwrite -progress -skipfailures --config PG_USE_COPY YES --config OGR_ORGANIZE_POLYGONS SKIP
Il comando di sopra converte i nomi layer, nomi campi in minuscolo, per evitarlo occorre aggiungere -lco LAUNDER="NO"
OCCHIO a --config OGR_ORGANIZE_POLYGONS SKIP
salta il controllo degli anelli interni ai poligoni velocizzando il processo ma potrebbe creare poligono con errori geometrici!!!
Per visualizzare tutte le tabelle presenti nel database:
ogrinfo -ro -so -oo LIST_ALL_TABLES=YES PG:"dbname=DBGT_10K_22_V01 host='localhost' port='5432' user='postgres' password=xxxxx schemas=public"
--oo LIST_ALL_TABLES=YES
: per visualizzare anche le tabelle senza geometria
doc: https://gdal.org/drivers/vector/pg.html#dataset-open-options
Ricetta fatta e pubblicata:
https://tansignari.opendatasicilia.it/ricette/bash/importare_openfilegdb_in_postgis_con_ogr2ogr/
chiudo
classe 1:
EDIFC_CR_EDF_IS; EDI_MIN_CR_EDF_IS; MN_IND_MN_IND_SUP; MN_MAU_MN_MAU_SUP; MAN_TR_MAN_TR_SUP; AATT; AR_VRD_AR_VRD_SUP; PE_UINS; AR_STR_AR_STR_SUP_SR; AR_VMS_AR_VMS_SUP; SD_FER_SD_FER_SUP_SR; CV_AES_CV_AES_EXT_SR; ATTR_SP; INVASO; OP_POR_OPPR_SUP; A_TRAS; CV_AES;SV_FER_060323; SV_STR_060323