Update OGM Data

gistfile1.py

# Update OGM Well and Bottom HoleData
# 7-19-11
# Scott Davis - stdavis@utah.gov
# Bert Granberg - bgranberg@utah.gov
#
# Updated by Scott 6-3-13

import arcpy, os, itertools
from agrc import logging, email, arcpy_helpers

# overwrite output
arcpy.overwriteoutput = 1

# init agrc objects
logger = logging.Logger()
emailer = email.Emailer(toAddress='...', testing=False)

#### Set these variables
workspace = os.path.dirname(os.path.abspath(__file__)) # current directory

# TEST
# sdeconnection = os.path.join(workspace, 'SGID10 as ENERGY on local.sde')
# indian_country = r'C:\MapData\DEQEnviro\ReferenceData.gdb\Total_IC_and_ReservationTribalLand'

sdeconnection = os.path.join(workspace, 'SGID10_Energy.sde')
ogmconnection = os.path.join(workspace, 'OGMUSER.odc')
indian_country = r'\\168.178.43.239\gis\AQGIS\GISSHARED\GISData\Total IC and Reservation_TribalLand\Total_IC_and_ReservationTribalLand.shp'

ogmTableName = 'dbo.vw_AGRC_Well_Data'
ogmSurfXField = 'COORDS_SURF_E'
ogmSurfYField = 'COORDS_SURF_N'
ogmBHXField = 'COORDS_BHL_E'
ogmBHYField = 'COORDS_BHL_N'

tempFGDB = os.path.join(workspace, 'scratch.gdb')

outputSurfFCName = 'SGID10.ENERGY.DNROilGasWells'
outputBHFCName = 'SGID10.ENERGY.DNROilGasWells_HDBottom'
outputBHPathFCName = 'SGID10.ENERGY.DNROilGasWells_HDPath'

wellsLayer = 'wellsLayer'
LA_PA_DATE = 'LA_PA_DATE'
ORIG_COMPL_DATE = 'ORIG_COMPL_DATE'
JURISDICTION = 'JURISDICTION'

# establish connections
ogmTable = os.path.join(ogmconnection, ogmTableName)
surfPointFC = os.path.join(sdeconnection, outputSurfFCName)
bHPointFC = os.path.join(sdeconnection, outputBHFCName)
bHPathFC = os.path.join(sdeconnection, outputBHPathFCName)

install_dir = arcpy.GetInstallInfo()['InstallDir']
utmNAD83 = os.path.join(install_dir, r'Coordinate Systems\Projected Coordinate Systems\UTM\NAD 1983\NAD 1983 UTM Zone 12N.prj')

surfXYLayerName = 'surfXYLayer'
bHXYLayerName = 'bhXYLayer'

surfTempFC = os.path.join(tempFGDB, 'surfTempFC')
bHTempFC = os.path.join(tempFGDB, 'bHTempFC')
bHPointFCTemp = os.path.join(tempFGDB, 'bHPointFCTemp')
bHPathFCTemp = os.path.join(tempFGDB, 'bHPathFCTemp')


try:
    arcpy_helpers.DeleteIfExists([surfTempFC, bHTempFC, bHPointFCTemp, bHPathFCTemp])
    
    # SURFACE HOLE LOCATIONS
    # create xy event layer
    logger.logMsg('Creating xy event layer from surface well location data')
    arcpy.MakeXYEventLayer_management(ogmTable, ogmSurfXField, ogmSurfYField, surfXYLayerName, utmNAD83)
    logger.logGPMsg()

    # export to temp fc
    logger.logMsg('Exporting xy event layer to temp feature class for surface well location')
    arcpy.FeatureClassToFeatureClass_conversion(surfXYLayerName, tempFGDB, 'surfTempFC')
    logger.logGPMsg()

    # delete features from current ogmFC
    logger.logMsg('Deleting old surface well location features')
    arcpy.DeleteFeatures_management(surfPointFC)
    logger.logGPMsg()

    # append new features
    logger.logMsg("Appending new surface well location features")
    arcpy.Append_management(surfXYLayerName, surfPointFC, "NO_TEST")
    logger.logGPMsg()


    # WELL BOTTOM HOLE LOCATIONS
    # create bhxy event layer
    logger.logMsg('Creating bhxy event layer for bottom of hole location data')
    arcpy.MakeXYEventLayer_management(ogmTable, ogmBHXField, ogmBHYField, bHXYLayerName, utmNAD83)
    logger.logGPMsg()

    # export to temp fc
    logger.logMsg('Exporting for bottom of hole xy event layer to temp feature class')
    arcpy.CopyFeatures_management(bHXYLayerName, bHTempFC)
    logger.logGPMsg()

    # delete features from current bottom hole FC
    logger.logMsg('Deleting for bottom of holefeatures from current FC')
    arcpy.DeleteFeatures_management(bHPointFC)
    logger.logGPMsg()

    # NOTE: I had to copy these fc's to a temp file geodatabase in order for the
    # insert cursors below to work. I kept getting an error message about the spatial
    # index being too small. Maybe because I'm running this on a 10.1 client against
    # a 10.0 sde database?
    logger.logMsg('Copying bottom of holes to a temp feature class')
    arcpy.Copy_management(bHPointFC, bHPointFCTemp)
    logger.logGPMsg()

    # delete features from current horizontal path FC
    logger.logMsg('Deleting features from current horizontal path FC')
    arcpy.DeleteFeatures_management(bHPathFC)
    logger.logGPMsg()

    logger.logMsg('Copying paths to temp feature class')
    arcpy.Copy_management(bHPathFC, bHPathFCTemp)
    logger.logGPMsg()

    # Create the insert cursor and point it at the file just created
    queryTxt = '{0} > 200000 and {1} > 4000000 and not ({0} = {2} and {1} = {3})'.format(
            ogmBHXField, ogmBHYField, ogmSurfXField, ogmSurfYField)
    print queryTxt
    edit = arcpy.da.Editor(tempFGDB)
    edit.startEditing(False, False)
    with arcpy.da.SearchCursor(surfTempFC, ['API', 'SHAPE@'], queryTxt) as surfRows, \
        arcpy.da.SearchCursor(bHTempFC, ['API', 'SHAPE@'], queryTxt) as bHRows, \
        arcpy.da.InsertCursor(bHPointFCTemp, ['SHAPE@XY', 'API', 'UTMX_NAD83', 'UTMY_NAD83']) as bHPointInsertCursor, \
        arcpy.da.InsertCursor(bHPathFCTemp, ['SHAPE@', 'API']) as bHPathInsertCursor:

        logger.logMsg('inserting new records...')
        for surfRow, bHRow in itertools.izip(sorted(surfRows), sorted(bHRows)):
            surfFeature = surfRow[1]
            bHFeature = bHRow[1]

            surfPoint = surfFeature.getPart(0)
            bHPoint = bHFeature.getPart(0)

            bHPointInsertCursor.insertRow(((bHPoint.X, bHPoint.Y), surfRow[0], bHPoint.X, bHPoint.Y))

            lineArray = arcpy.Array([surfPoint, bHPoint])

            bHPathInsertCursor.insertRow((arcpy.Polyline(lineArray), surfRow[0]))

    edit.stopEditing(True)

    logger.logMsg('appending features from temp point to sde')
    arcpy.Append_management(bHPointFCTemp, bHPointFC)
    logger.logGPMsg()

    logger.logMsg('appending features from temp path to sde')
    arcpy.Append_management(bHPathFCTemp, bHPathFC)
    logger.logGPMsg()

    logger.logMsg('scrubbing dates')
    arcpy.MakeFeatureLayer_management(surfPointFC, wellsLayer, "[{}] = '1899-12-30 00:00:00'".format(LA_PA_DATE))
    logger.logGPMsg()
    arcpy.CalculateField_management(wellsLayer, LA_PA_DATE, 'None', 'PYTHON')
    logger.logGPMsg()
    arcpy.Delete_management(wellsLayer)
    logger.logGPMsg()
    arcpy.MakeFeatureLayer_management(surfPointFC, wellsLayer, "[{}] = '1899-12-30 00:00:00'".format(ORIG_COMPL_DATE))
    logger.logGPMsg()
    arcpy.CalculateField_management(wellsLayer, ORIG_COMPL_DATE, 'None', 'PYTHON')
    logger.logGPMsg()
    arcpy.Delete_management(wellsLayer)

    logger.logMsg('updating JURISDICTION field')
    arcpy.MakeFeatureLayer_management(surfPointFC, wellsLayer)
    arcpy.CalculateField_management(wellsLayer, JURISDICTION, '"state"', 'PYTHON')
    arcpy.SelectLayerByLocation_management(wellsLayer, "INTERSECT", indian_country)
    arcpy.CalculateField_management(wellsLayer, JURISDICTION, '"indian"', 'PYTHON')
    
except arcpy.ExecuteError:
    logger.logMsg('arcpy.ExecuteError')
    logger.logError()
    logger.logGPMsg()
    emailer.sendEmail(logger.scriptName + ' - arcpy.ExecuteError', logger.log)
    
except:
    logger.logError()
    emailer.sendEmail(logger.scriptName + ' - Python Error', logger.log)
 
finally:
    logger.writeLogToFile()

print "done"