Can't view created feature class

[BartKowalski]

Hi,

I've written a script that connects to a SQL Server via pyodbc, and creates a feature class from a table. The code runs fine, but one in Arc, I don't see my feature class, although the attributes are all there. I am new to python, and can't figure out what I am missing.

import arcpy
#reload(arcpy)
import pyodbc
from arcpy import env
cnxn = pyodbc.connect('Driver={SQL Server Native Client 10.0};UID=ID;PWD=Password;SERVER=spk-db11sac.spk.ds.usace.army.mil; DATABASE=ftord; APP=MyApp')

cursor=cnxn.cursor()
cursor.execute("select aquifer, site_id, well_name, station_name, well_type, welltype_desc, northing, easting, coord_source, toc, ground_elev, top_screen_depth, bot_screen_depth, total_depth, diameter, installer, date_inst, date_aban, destroyed, purge_vol, norm_dtw, pump_depth, comments, well_status, destruction_rationale, date_destroyed, ref_document, destruct_permit_number from dbo.Wells_view")
rows = cursor.fetchall()

env.overwriteOutput = True
env.workspace = r"G:\Fort_Ord_GIS\Master_Coverage\Fort_Ord.gdb"
template = r"G:\Fort_Ord_GIS\Master_Coverage\Fort_Ord.gdb\improvement_well\water_well_point"

Wells = arcpy.CreateFeatureclass_management(r"G:\Temp\Bart\scratch.gdb", "ODB11", "POINT",'', "DISABLED", "DISABLED", template)

#arcpy.AddField_management(Wells,'SubTypeIdentifier','TEXT')
arcpy.AddField_management(Wells,'PKWellID','TEXT')  
arcpy.AddField_management(Wells,'PKMApID','TEXT')
arcpy.AddField_management(Wells,'FKMetalID','TEXT')
arcpy.AddField_management(Wells,'FKMediaID','TEXT')
arcpy.AddField_management(Wells,'FKCoordbtID','TEXT')
arcpy.AddField_management(Wells,'FKMnwellID','TEXT')
arcpy.AddField_management(Wells,'WellCatDOM','TEXT')
arcpy.AddField_management(Wells,'WellTypDOM','TEXT')
arcpy.AddField_management(Wells,'WellStaDOM','TEXT')
arcpy.AddField_management(Wells,'CaseMatDOM','TEXT')
arcpy.AddField_management(Wells,'ConstTyDOM','TEXT')
#arcpy.AddField_management(Wells,'HoleDia','TEXT')
arcpy.AddField_management(Wells,'DiaUOM','TEXT')
#arcpy.AddField_management(Wells,'Depthto1st','TEXT')
#arcpy.AddField_management(Wells,'Depthtowat','TEXT')
#arcpy.AddField_management(Wells,'Depthtobdk','TEXT')
#arcpy.AddField_management(Wells,'Totaldepth','TEXT')
#arcpy.AddField_management(Wells,'ReferElev','TEXT')
#arcpy.AddField_management(Wells,'ElevUOM','TEXT')
arcpy.AddField_management(Wells,'DepthUOM','TEXT')
#arcpy.AddField_management(Wells,'Capacity','TEXT')
arcpy.AddField_management(Wells,'CapacUOM','TEXT')
arcpy.AddField_management(Wells,'FKWpumpID','TEXT')
arcpy.AddField_management(Wells,'FKWpumpID','TEXT')
arcpy.AddField_management(Wells,'FKTankID','TEXT')
arcpy.AddField_management(Wells,'FKInstInID','TEXT')
arcpy.AddField_management(Wells,'FKBuildngID','TEXT')
arcpy.AddField_management(Wells,'FKProjectID','TEXT')
arcpy.AddField_management(Wells,'FKStationID','TEXT')
arcpy.AddField_management(Wells,'FKFulzoneID','TEXT')
arcpy.AddField_management(Wells,'FKWatsectID','TEXT')
arcpy.AddField_management(Wells,'FKGaszoneID','TEXT')
arcpy.AddField_management(Wells,'FKHcszoneID','TEXT')
arcpy.AddField_management(Wells,'FKStosectID','TEXT')
arcpy.AddField_management(Wells,'Narrative','TEXT')




Wcur = arcpy.InsertCursor(Wells)

for row in rows:
        #print row.well_name
        t_row = Wcur.newRow()
        t_row.PKWellID = row.well_name
        t_row.WellStaDOM = row.welltype_desc
        Wcur.insertRow(t_row)
#for row in rows:
        #t_row = Wcur.newRow()
        #t_row.PKWellID2 = row.station_name
        #Wcur.insertRow(t_row)

cnxn.commit() 
cursor.close()
cnxn.close()

del row
del rows
del cursor 
del cnxn

Help greatly appreciated in advance.

BArt

[JakeSkinner]

Hi Bart,

Looking at your code it doesn't appear that you insert geometry any where, only attribute information.  This would explain why you can see the attribute information, but no spatial data.

[BartKowalski]

Hi Bart,

Looking at your code it doesn't appear that you insert geometry any where...

JSkinn3, Could you point me to an example how one does that? I'm a newbie to Python.

Thanks,

BArt

[JakeSkinner]

I see that you are querying a Northing and Easting field from the SQL view.  The easiest way would be to create a table, rather than a feature class, and include these field values along with the others.  Then you can create an XY Event layer using these two fields.  Once this is created, you can convert the event layer to a feature class using the Feature Class to Feature Class tool.

[BartKowalski]

Thanks Jake,

I followed your suggestions, and the code works, but there is still one issue I am having aproblem with.
When I try to define projection I get:
ERROR 000622: Failed to execute (Make XY Event Layer). Parameters are not valid.
ERROR 000628: Cannot set input into parameter spatial_reference.

When I comment it out, the code runs, but the new feature class has, obviously, no defined projection. Looking through the other forum posts, I see other folks had similar issues when running stand alone python script. Suggestions?

import arcpy
#reload(arcpy)
import pyodbc
from arcpy import env
cnxn = pyodbc.connect('Driver={SQL Server Native Client 10.0};UID=ID;PWD=Password;SERVER=spk-db11sac.spk.ds.usace.army.mil; DATABASE=ftord; APP=MyApp')

cursor=cnxn.cursor()
cursor.execute("select aquifer, site_id, well_name, station_name, well_type, welltype_desc, northing, easting, coord_source, toc, ground_elev, top_screen_depth, bot_screen_depth, total_depth, diameter, installer, date_inst, date_aban, destroyed, purge_vol, norm_dtw, pump_depth, comments, well_status, destruction_rationale, date_destroyed, ref_document, destruct_permit_number from dbo.Wells_view")
rows = cursor.fetchall()

env.overwriteOutput = True

env.workspace = r"G:\Fort_Ord_GIS\Master_Coverage\Fort_Ord.gdb"
template = r"G:\Fort_Ord_GIS\Master_Coverage\Fort_Ord.gdb\improvement_well\water_well_point"

config_keyword = ""

WellsTabl = arcpy.CreateTable_management(r"G:\Temp\Bart", "WellsTabl.dbf", template)


Wcur = arcpy.InsertCursor(WellsTabl)

for row in rows:
        #print row.well_name
        t_row = Wcur.newRow()
        t_row.well_id = row.well_name
        t_row.well_typ_d = row.welltype_desc
        t_row.coord_x = row.easting
        t_row.coord_y = row.northing
        Wcur.insertRow(t_row)

try:
        in_Table = WellsTabl
        x_coords = "coord_x"
        y_coords = "coord_y"
        out_layer = "Wells_layer"
        saved_layer = r"G:\Temp\Bart\Layers\Wells.lyr"

        #spRef = r"C:\Program Files (x86)\ArcGIS\Desktop10.0\Coordinate Systems\Projected Coordinate Systems\State Plane\NAD 1983 (US Feet)\NAD_1983_StatePlane_California_IV_FIPS_0404_Feet (US Feet).prj"

       
        arcpy.MakeXYEventLayer_management(in_Table, x_coords, y_coords, out_layer, spRef)


        print arcpy.GetCount_management(out_Layer)


        arcpy.SaveToLayerFile_management(out_layer, saved_layer)


except:
        print arcpy.GetMessages()

inFeatures = saved_layer
outLocation = r"G:\Temp\Bart\Scratch.gdb"
outFeatureClass = "Wells_update"
arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass)


cnxn.commit() 
cursor.close()
cnxn.close()

del row
del rows
del cursor 
del cnxn

[JakeSkinner]

Hi Bart,

The underscores within the projection name is what's causing the problem.  Change:

spRef = r"C:\Program Files (x86)\ArcGIS\Desktop10.0\Coordinate Systems\Projected Coordinate Systems\State Plane\NAD 1983 (US Feet)\NAD_1983_StatePlane_California_IV_FIPS_0404_Feet (US Feet).prj"

to:

spRef = r"C:\Program Files (x86)\ArcGIS\Desktop10.0\Coordinate Systems\Projected Coordinate Systems\State Plane\NAD 1983 (US Feet)\NAD 1983 StatePlane California IV FIPS 0404 (US Feet).prj"

and it should work.

[HemingZhu]

Hi Bart,

The underscores within the projection name is what's causing the problem.  Change:

spRef = r"C:\Program Files (x86)\ArcGIS\Desktop10.0\Coordinate Systems\Projected Coordinate Systems\State Plane\NAD 1983 (US Feet)\NAD_1983_StatePlane_California_IV_FIPS_0404_Feet (US Feet).prj"

to:

spRef = r"C:\Program Files (x86)\ArcGIS\Desktop10.0\Coordinate Systems\Projected Coordinate Systems\State Plane\NAD 1983 (US Feet)\NAD 1983 StatePlane California IV FIPS 0404 (US Feet).prj"

and it should work.

Another easiest way to create SR is to use WKID. In your case your WKID =2228. You can create SR as follow:

             
sr = arcpy.SpatialReference()
sr.factoryCode = 2228
sr.create()

[BartKowalski]

Ok, Got it, thanks a lot!
My last question (hepefully) is this; instead of creating a new feature class in a database, I want to update, or replace an existing one. The code below gives me

ExecuteError: ERROR 999999: Error executing function.
The table already exists.
Failed to execute (FeatureClassToFeatureClass).

And here's my (almost) final code:

import arcpy
#reload(arcpy)
import pyodbc
from arcpy import env
cnxn = pyodbc.connect('Driver={SQL Server Native Client 10.0};UID=user;PWD=Password;SERVER=spk-db11sac.spk.ds.usace.army.mil; DATABASE=ftord; APP=MyApp')

cursor=cnxn.cursor()
cursor.execute("select aquifer, site_id, well_name, station_name, well_type, welltype_desc, northing, easting, coord_source, toc, ground_elev, top_screen_depth, bot_screen_depth, total_depth, diameter, installer, date_inst, date_aban, destroyed, purge_vol, norm_dtw, pump_depth, comments, well_status, destruction_rationale, date_destroyed, ref_document, destruct_permit_number from dbo.Wells_view")
rows = cursor.fetchall()

env.overwriteOutput = True

env.workspace = r"G:\Fort_Ord_GIS\Master_Coverage\Fort_Ord.gdb"
#env.workspace = r"G:\Temp\Bart\fort_ord.gdb"
template = r"G:\Fort_Ord_GIS\Master_Coverage\Fort_Ord.gdb\improvement_well\water_well_point"

config_keyword = ""
#config_keyword = "TEXT_UTF16"

WellsTabl = arcpy.CreateTable_management(r"G:\Temp\Bart", "WellsTabl.dbf", template)


Wcur = arcpy.InsertCursor(WellsTabl)

for row in rows:
        #print row.well_name
        t_row = Wcur.newRow()
        t_row.well_id = row.well_name
        t_row.feat_name = row.well_name
        t_row.watwel_d = row.well_type
        t_row.well_typ_d = row.welltype_desc
        t_row.well_sta_d = row.well_status
        t_row.totaldepth = row.total_depth
        t_row.gsurf_elev = row.ground_elev
        t_row.date_inst = row.date_inst
        t_row.norm_dtw = row.norm_dtw
        t_row.pump_depth = row.pump_depth
        t_row.destructio = row.destruction_rationale
        t_row.date_destr = row.date_destroyed
        t_row.ref_docume = row.ref_document
        t_row.destruct_p = row.destruct_permit_number
        t_row.refer_elev = row.toc
        t_row.hole_dia = row.diameter
        t_row.narrative = row.comments
        t_row.cas_sdepth = row.top_screen_depth
        t_row.cas_edepth = row.bot_screen_depth
        t_row.contrct_id = row.installer
        t_row.capacity = row.purge_vol
        t_row.destructio = row.destroyed
        #t_row.comp_date = row.date_inst
        t_row.coord_x = row.easting
        t_row.coord_y = row.northing
        Wcur.insertRow(t_row)

try:
        in_Table = WellsTabl
        x_coords = "coord_x"
        y_coords = "coord_y"
        out_layer = "Wells_layer"
        saved_layer = r"G:\Temp\Bart\Layers\Wells2.lyr"

        
        spRef = r"C:\Program Files (x86)\ArcGIS\Desktop10.0\Coordinate Systems\Projected Coordinate Systems\State Plane\NAD 1983 (US Feet)\NAD 1983 StatePlane California IV FIPS 0404 (US Feet).prj"
        arcpy.MakeXYEventLayer_management(in_Table, x_coords, y_coords, out_layer, spRef)
        saved_layer2 = arcpy.SaveToLayerFile_management(out_layer, saved_layer)

except:
        print arcpy.GetMessages()

inFeatures = saved_layer2
#outLocation = r"G:\Temp\Bart\Scratch.gdb"
outLocation = r"G:\Temp\Bart\fort_ord.gdb\improvement_well"
#outFeatureClass = "Wells_update2"
outFeatureClass = "water_well_point"
arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass)


cnxn.commit() 
cursor.close()
cnxn.close()

del row
del rows
del cursor 
del cnxn

[JakeSkinner]

Hi Bart,

Add the following line of code:

env.overwriteOutput = True

This will allow you to overwrite existing feature classes.

[BartKowalski]

Hi Bart,

Add the following line of code:

env.overwriteOutput = True

Yes, I already have it in the script at the beginning. I tried copying it down before the featureclasstofeaturecalss command, but it didn't work.