search

opendatasicilia / tansignari

"T'ansignari e t'appeddiri"
http://tansignari.opendatasicilia.it
Creative Commons Attribution 4.0 International
18 stars 10 forks source link

Importare OpenFileGDB in PostGIS usando ogr2ogr #232

Closed pigreco closed 1 year ago

pigreco commented 1 year ago

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:

fonti:

dopo 30 minuti ha importato solo 73 tabelle (lo vedo da pgAdmin4): image

dopo 40 minuti e 97 tabelle importate, si ferma tutto: image

L'errore nasce appena inizia a caricare le tabelle con geometria!!!

EDIT: ahhhhhhhhhhhhhhhh NON avevo installato l'estensione PostGIS, rifaccio

pigreco commented 1 year ago

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;

image

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

image

ma allora, con quale funzione si scopre il vero tipo di geometria in PostGIS?

image

pigreco commented 1 year ago

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)
pigreco commented 1 year ago

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;

image

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
pigreco commented 1 year ago

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!!!

pigreco commented 1 year ago

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

pigreco commented 1 year ago

Ricetta fatta e pubblicata:

https://tansignari.opendatasicilia.it/ricette/bash/importare_openfilegdb_in_postgis_con_ogr2ogr/

chiudo

pigreco commented 1 year ago

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