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:
Te mostramos comentarios y puntuaciones
¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)