Saltar al contenido

Importación de la base de datos SQLite Survey123 desde el dispositivo

Te damos la bienvenida a proyecto on line, en este lugar encontrarás la solucíon de lo que buscabas.

Solución:

Construí esta herramienta para exportar datos de Survey123 desde una base de datos SQLite (descargada del dispositivo utilizado para capturar los datos) a una geodatabase de archivos. Crea una clase de entidad para cada tipo de levantamiento en la base de datos SQLite. El script requiere que lo ejecute desde una herramienta ArcToolbox con 2 parámetros: [0] el archivo .sqlite de entrada y 1 la geodatabase del archivo de salida. Aquí está el código:

import csv, sqlite3, json, os, arcpy
arcpy.env.overwriteOutput = True

def readS123db(inDB):
    conn = sqlite3.connect(inDB)
    cur = conn.cursor()
    #conn.text_factory = lambda x: x.decode("utf-16")
    #Status indicates which 'box' teh Survey is in
    #0 - Drafts
    #1 - Outbox
    #2 - Sent
    #3 - Submission Error
    #4 - Inbox

    for row in cur.execute('SELECT name, feature, status, data, snippet from Surveys where status = 1 or status = 3 or status = 2'):
        #arcpy.AddMessage(row)

        dataField = json.loads(row[3])
        dataFieldKeys = dataField.keys()
        #arcpy.AddMessage(str(dataFieldKeys))
        for key in dataField:
            masterKey = key
            #arcpy.AddMessage(str(masterKey))

            fcPath = os.path.join(gdb, str(masterKey))
            if arcpy.Exists(fcPath):
                arcpy.AddMessage("nDeleting prior datasets")
                arcpy.Delete_management(fcPath)  #Delete Existing feature classes with these survey names
            if arcpy.Exists(fcPath + "_tbl"):
                arcpy.Delete_management(fcPath + "_tbl")


    rowNum = 0
    alternateFcNameList = []
    for row in cur.execute('SELECT name, feature, status, data, snippet from Surveys where status = 1 or status = 3 or status = 2'):
        rowNum = rowNum + 1

        dataField = json.loads(row[3])
        snippetField = str(row[4])[:255]

        dataFieldKeys = dataField.keys()
        for masterKey in dataField:
            #output = os.path.split(outFC)

            arcpy.AddMessage("nnRow " + str(rowNum) +"nSurvey:  " + masterKey)
            arcpy.AddMessage("Snippet: " + snippetField)
            tier2keys = dataField[masterKey].keys()
            #arcpy.AddMessage(str(tier2keys))
            gpsField = None
            for tier2key in tier2keys:  #Get the spatial reference

                #arcpy.AddWarning("TIER2KEY: " + tier2key)
                if tier2key == "GPS":
                    #gpsField = tier2key
                    gpsKeys = dataField[masterKey][tier2key]
                    for gpsKey in gpsKeys:
                        arcpy.AddMessage("GPS Key: " + gpsKey)
                        if gpsKey == "spatialReference":
                            sr = dataField[masterKey][tier2key][gpsKey]["wkid"]
                            arcpy.AddMessage("Spatial Reference: WKID " + str(sr))
                            spatial_reference = arcpy.SpatialReference(sr)
                            gpsField = tier2key
                elif tier2key == "site_point": #In one case the GPS field was mis-named as field_2
                    #gpsField = tier2key
                    gpsKeys = dataField[masterKey][tier2key]
                    if gpsKeys != None:
                        for gpsKey in gpsKeys:
                            arcpy.AddMessage("GPS Key: " + gpsKey)
                            if gpsKey == "spatialReference":
                                sr = dataField[masterKey][tier2key][gpsKey]["wkid"]
                                arcpy.AddMessage("Spatial Reference: WKID " + str(sr))
                                spatial_reference = arcpy.SpatialReference(sr)
                                gpsField = tier2key


            arcpy.AddMessage("GPS Field: " + str(gpsField))
            if gpsField != None and dataField[masterKey][gpsField]["x"] != None and dataField[masterKey][gpsField]["y"] != None and dataField[masterKey][gpsField]["z"] != None:

                #Create feature class if necessary
                fcPath = os.path.join(gdb, str(masterKey))
                if not arcpy.Exists(fcPath):
                    arcpy.AddMessage("nCreating output feature class")
                    try:
                        arcpy.CreateFeatureclass_management(gdb, str(masterKey), "POINT", "", "DISABLED", "ENABLED", spatial_reference)
                    except:
                        masterKeyString = str(masterKey)
                        alternateFcName = masterKeyString.translate(None, '[email protected]#$-&*^+=`~?/;:[].,<>') #remove characters not valid in FC name
                        fcPath = os.path.join(gdb, alternateFcName)
                        if not arcpy.Exists(os.path.join(gdb, alternateFcName)):
                            alternateFcNameList.append(alternateFcName)
                            arcpy.AddMessage(masterKey + " is not a valid feature class name.nReplacing with " + alternateFcName + "n")
                            arcpy.CreateFeatureclass_management(gdb, alternateFcName, "POINT", "", "DISABLED", "ENABLED",
                                                                spatial_reference)

                #Get data per row
                fieldList = []
                rowValues = []
                for tier2key in tier2keys:
                    if tier2key != "GPS":
                        if tier2key != "objectid" and tier2key != "ObjectId":
                            arcpy.AddMessage("Import Field: " + tier2key)
                            arcpy.AddField_management(fcPath, str(tier2key), "TEXT")
                            fieldList.append(tier2key)
                            rowValues.append(str(dataField[masterKey][tier2key]))
                arcpy.AddField_management(fcPath, "Snippet", "TEXT") #Adds snippet field from original database

                fieldList.extend(("Snippet", "[email protected]", "[email protected]", "[email protected]"))

                xCoord = dataField[masterKey][gpsField]["x"]
                yCoord = dataField[masterKey][gpsField]["y"]
                zCoord = dataField[masterKey][gpsField]["z"]

                rowValues.extend((snippetField, xCoord, yCoord, zCoord))
                rowValuesTuple = tuple(rowValues) #Create tuple from list of row values
                rowValues = [rowValuesTuple] #because insert cursor expects a list of tuples (one tuple per data row)

                with arcpy.da.InsertCursor(fcPath, fieldList) as cursor:
                    for row in rowValues:
                        arcpy.AddMessage("nAppending data from row " + str(rowNum) + ": n" + str(row))
                        cursor.insertRow(row)


            elif gpsField == None:
                #break (only enable if you want to omit non-spatial tables)
                # Create feature class if necessary
                fcPath = os.path.join(gdb, str(masterKey) + "_tbl")
                if not arcpy.Exists(fcPath):
                    arcpy.AddMessage("nCreating output table")
                    try:
                        arcpy.CreateTable_management(gdb, str(masterKey) + "_tbl")
                    except:
                        masterKeyString = str(masterKey)
                        alternateFcName = masterKeyString.translate(None,
                                                                    '[email protected]#$-&*^+=`~?/;:[].,<>')  + "_tbl"# remove characters not valid in FC name
                        fcPath = os.path.join(gdb, alternateFcName)
                        if not arcpy.Exists(os.path.join(gdb, alternateFcName)):
                            alternateFcNameList.append(alternateFcName)
                            arcpy.AddMessage(
                                masterKey + " is not a valid table name.nReplacing with " + alternateFcName + "n")
                            arcpy.CreateTable_management(gdb, alternateFcName)

                # Get data per row
                fieldList = []
                rowValues = []
                for tier2key in tier2keys:
                    if tier2key != "GPS":
                        if tier2key != "objectid" and tier2key != "ObjectId" and tier2key != "OBJECTID":
                            arcpy.AddMessage("Import Field: " + tier2key)
                            arcpy.AddField_management(fcPath, str(tier2key), "TEXT")
                            fieldList.append(tier2key)
                            rowValues.append(str(dataField[masterKey][tier2key]))

                rowValuesTuple = tuple(rowValues)  # Create tuple from list of row values
                rowValues = [rowValuesTuple]  # because insert cursor expects a list of tuples (one tuple per data row)

                with arcpy.da.InsertCursor(fcPath, fieldList) as cursor:
                    for row in rowValues:
                        arcpy.AddMessage("nAppending data from row " + str(rowNum) + ": n" + str(row))
                        cursor.insertRow(row)



if __name__ == '__main__':

    inDB = arcpy.GetParameterAsText(0)
    gdb = arcpy.GetParameterAsText(1)

    surveys = readS123db(inDB)

    arcpy.AddMessage("nOh Joy!!!nAll Surveys Have Been Exported!n")

Así es como debería verse la herramienta ArcMap que apunta al script anterior:
Así es como debería verse la herramienta ArcMap

Te mostramos comentarios y puntuaciones

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *