I have a sql table which has large number of records, more than 1,000,000 with lat lon values along with other attributes.
ID field uniquely identifies each row. The table is updated everyday and will keep growing.
With all the online help, I could write a script where it creates a featureclass and converts to a feature class.
I am experiencing two problems.
1. The script runs fine and creates a featureclass, but after sometime, it stops giving a runtime error saying 'This application has requested the runtime to terminate it in an unusual way' and points to pythonw.exe
Is it because the sql table size is large?
It does create a featureclass and does insert points there until that error.
2. since the sql table gets updated everyday, i want to find the max id from featureclass and update to exising featureclass only those rows from sql where ID greater than featurelcassid
So, the first time it is okay that it creates featureclass, but next time, I just want it to update the existing featureclass with newer records.
import pyodbc
import os, sys
import arcpy
import datetime
from arcpy import env
arcpy.env = r'\\C\test.gdb'
if arcpy.Exists("fcTest"):
arcpy.Rename_management(fcTest, 'fcTest + str(datetime.date.today().day))
db = r'\\C\test.gdb'
spatial_reference = r'\\C\test.gdb\fcSR'
fcTest=r'\\C\test.gdb\fcTest'
conn = pyodbc.connect(r"......")
query = 'select * FROM tbl1'
arcpy.CreateFeatureclass_management(db,"fcTest", "POINT", "", ENABLED", "DISABLED", spatial_reference)
arcpy.AddField_management(fcTest, "ID", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "NAME", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "POSITION", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "TITLE", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "OTHER", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "HIREDATE", "DATE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "Lat", "FLOAT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "Long", "FLOAT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
insertCur = arcpy.InsertCursor(fcTest)
for row in conn.execute(query):
curRow = insertCur.newRow()
curRow.Shape = arcpy.PointGeometry(arcpy.Point(float(row.LONG),float(row.LAT)))
curRow.ID = row.ID
if row.NAME is not None:
curRow.NAME = row.NAME
elif row.NAME is None:
curRow.NAME = None
if row.POSITION is not None:
curRow.POSITION = row.POSITION
elif row.POSITION is None:
curRow.POSITION = None
if row.TITLE is not None:
curRow.TITLE = row.TITLE
elif row.TITLE is None:
curRow.TITLE = None
if row.OTHER is not None:
curRow.OTHER = row.OTHER
elif row.OTHER is None:
curRow.OTHER = None
if row.HIREDATE is not None:
curRow.HIREDATE = row.HIREDATE
elif row.HIREDATE is None:
curRow.HIREDATE = None
if row.LAT is not None:
curRow.LAT = float(row.LAT)
elif row.LAT is None:
curRow.LAT = None
if row.LONG is not None:
curRow.LONG = float(row.LONG )
elif row.LONG is None:
curRow.LONG = None
insertCur.insertRow(curRow)
if insertCur: del insertCur
if curRow: del curRow
