Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Importare OpenFileGDB in PostGIS usando ogr2ogr #232

Closed
pigreco opened this issue Nov 26, 2022 · 7 comments
Closed

Importare OpenFileGDB in PostGIS usando ogr2ogr #232

pigreco opened this issue Nov 26, 2022 · 7 comments

Comments

@pigreco
Copy link
Contributor

pigreco commented Nov 26, 2022 •
edited

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:

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
Copy link
Contributor Author

pigreco commented Nov 26, 2022 •
edited

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
Copy link
Contributor Author

pigreco commented Nov 26, 2022 •
edited

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
Copy link
Contributor Author

pigreco commented Nov 27, 2022 •
edited

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
Copy link
Contributor Author

pigreco commented Nov 27, 2022 •
edited

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
Copy link
Contributor Author

pigreco commented Nov 30, 2022 •
edited

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
Copy link
Contributor Author

pigreco commented Jan 15, 2023

Ricetta fatta e pubblicata:

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

chiudo

@pigreco pigreco closed this as completed Jan 15, 2023
@pigreco
Copy link
Contributor Author

pigreco commented Mar 18, 2023

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@pigreco