Why would a Python script output an empty feature class for on xy to point process, but not another?

435
7
03-26-2019 06:30 PM
LeahSaunders2
New Contributor III

I have a Python script using Python 2.7 and arcpy (not from Pro).  This script queries a sql server database and writes the results to a table.  I then try to use that tables xy columns to create points with the XY event layer tool and copy features in the script.  All seems to go okay until I get an empty output.  I have run this in desktop, and it works from the same table that is created, I have tried it in Pro with the XYTableToPoints tool and it works.  I have tried creating a model and using that, it works when the model runs in the application, but not in the script.  

I have another similar script that works fine...but for some reason this one is not happy..even though I copied most of the code just modifying the query and the output fields.  Any ideas or has anyone seen this?  The version I'm running on this server is 10.5.1 as there are other dependencies for some automated scripts that run on the same server.

0 Kudos
7 Replies
DanPatterson_Retired
MVP Esteemed Contributor

It is hard to tell the differences in your scenarios, perhaps the code snippets would help

0 Kudos
LeahSaunders2
New Contributor III

The funny (or not so funny thing) is that the code is pretty much the same.  the scripts are over 200 lines long.

This is the gut of the arcpy code that works to create the table, but makes an empty point layer (excuse the mess as I have been attempting everything, usually I am much more clean with my tools):

arcpy.CreateTable_management(arcpy.env.workspace,'THEFTS_TABLE',arcpy.env.scratchWorkspace + '\\TABLE_TEMPLATE')
count = 0
with arcpy.da.InsertCursor(arcpy.env.workspace + '\\THEFTS_TABLE',fields) as insertcur:
for row in result:
report_id = row[0]
reporting_event_number = row[1]
veh_descr = row[2]
vehicle_make = row[3]
vehicle_model = row[4]
license_plate = row[5]
year_of_manufacture = row[6]
vin = row[7]
primary_color = row[8]
secondary_color = row[9]
item_status = row[10]
offense_code_statute = row[11]
nibrs_code = row[12]
nibrs_description = row[13]
stolen_date = row[14]
stolen_address = row[15]
stolen_beat = row[16]
stolen_long_to_map = row[17]
stolen_lat_to_map = row[18]
location_link_type = row[19]
recovered_date = row[20]
recovered_address = row[21]
recovered_precinct = row[22]
recovered_beat = row[23]
recovered_lat = row[24]
recovered_long = row[25]
recovered_by = row[26]
recovered_by_other_name = row[27]

insertcur.insertRow((report_id,reporting_event_number,veh_descr,vehicle_make,vehicle_model,license_plate,year_of_manufacture,vin,primary_color,secondary_color,item_status,offense_code_statute,nibrs_code,nibrs_description,
stolen_date,stolen_address,stolen_beat,stolen_long_to_map,stolen_lat_to_map,location_link_type,recovered_date,recovered_address,recovered_precinct,recovered_beat,recovered_lat,recovered_long,recovered_by,recovered_by_other_name))
print ("Row inserted")
count = count +1
logger.info("Thefts table finished loading. " + str(count) + " rows loaded.")
print (count)

#create data
print ('Running tools')
logger.info ('Creating feature class')
layer = 'xylayer'
spRef = arcpy.SpatialReference("WGS 1984")
#arcpy.CreateMVTFeatures_MVT() Trying to use a model - doesn't work

arcpy.MakeXYEventLayer_management(r'E:\Mark43_MVTUpdate\Thefts.gdb\THEFTS_TABLE',"stolen_long_to_map","stolen_lat_to_map",layer,4326)
arcpy.CopyFeatures_management(layer,'outputfeatures')

This is the one that works (it does call a tool in a toolbox, but I tried that even with the above one and no luck:

arcpy.CreateTable_management(arcpy.env.scratchWorkspace,'INCIDENTS_TABLE',arcpy.env.scratchWorkspace + '\\TABLE_TEMPLATE')
count = 0
with arcpy.da.InsertCursor(arcpy.env.scratchWorkspace + '\\INCIDENTS_TABLE',fields) as insertcur:
for row in result:
report_id = row[0]
report_type = row[1]
reporting_event_number = row[2]
approval_status = row[3]
offense_start_datetime = row[4]
offense_end_datetime = row[5]
offense_order = row[6]
offense_code_statute = row[7]
offense_description = row[8]
nibrs_code = row[9]
nibrs_description = row[10]
offense_type = row[11]
long_to_map = row[12]
lat_to_map = row[13]
coords_used = row[14]
address_to_map = row[15]
apartment_to_map = row[16]
precinct_to_map = row[17]
beat_to_map = row[18]

insertcur.insertRow((report_id,report_type,reporting_event_number,approval_status,offense_start_datetime,offense_end_datetime,offense_order,offense_code_statute,offense_description,nibrs_code,nibrs_description,
offense_type,long_to_map,lat_to_map,coords_used,address_to_map,apartment_to_map,precinct_to_map,beat_to_map))
print "Row inserted"
count = count +1
logger.info("Incidents table finished loading. " + str(count) + " rows loaded.")
print count

#create data
print ('Running tools')
logger.info ('Creating feature class')
arcpy.CreateFeatureClass_DataUpdate(arcpy.env.scratchWorkspace + '\\INCIDENTS_TABLE', 'long_to_map','lat_to_map',arcpy.env.workspace + '\\Incidents_Citywide')
logger.info('Tools have finished!')

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

If you run your script from within the interactive Python window in ArcMap/ArcCatalog, does it work?

0 Kudos
LeahSaunders2
New Contributor III

I hadn't tried..until now!  So it gives me an empty output..but when I run the actual model that gets called on the same data, it creates a point output as expected....not sure what this means!

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

Leah, you might want to format so people have line numbers for referencing your code

/blogs/dan_patterson/2016/08/14/script-formatting 

0 Kudos
LeahSaunders2
New Contributor III

My IDE does, but they don't get copied.  This is a very large script, and I just grabbed a smaller portion of it where arcpy is being used.

I'm thinking about just writing the insert cursor to create the geometry and push it into a feature class, as I can't figure out why only this one script (out of almost 10 that do the same thing) isn't working.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

The syntax for copy and pasting code blocks so they are retains is explained in the link, otherwise it comes out with indentation lost and no line numbering

0 Kudos