Okay, I realize I may draw the scorn from the community for asking the way I'm asking but ...Is there an easy way (easier than what I'm doing) to read Lat/Lon values stored in a non-spatial SQL table, reproject them and write the new values back to same or other table? Since this is for a non-GIS audience, I don't have any use for creating an intermediate feature class. I found arcpy.AddXY_management. But I believe, as most other scenarios I could come up with, requires a feature class. Basically, I'm curious if there is another way to do this without creating a feature class.
This is what I tried:
import pyodbc sql_conn = pyodbc.connect('''bla bla bla''') query = '''SELECT [LATITUDE],[LONGITUDE] FROM MyTable''' cursor = sql_conn.cursor() cursor.execute(query) ptDataRows = [] for row in cursor.fetchall(): #Turn cursor row into list ptDataRows.append([x for x in row])
I was bringing in additional attributes from my source table which I'm leaving out in this example. But a longer list would be the only difference. Next I'm working with the Lat/Lon columns to create point geometries and then
import arcpy src_sr = arcpy.SpatialReference(4267) dest_sr = arcpy.SpatialReference(4326) for i,row in enumerate(ptDataRows): pt = arcpy.Point(row[0],row[1]) ptGeom = arcpy.PointGeometry(pt, src_sr) newpoint = ptGeom.projectAs(dest_sr,'NAD_1927_To_WGS_1984_4') newcoord = [round(newpoint.centroid.X,7),round(newpoint.centroid.Y,7)] pt_attrs = row.extend(newcoord)
Now, I have a new list of lists, each containing the data for a new table row that I can write back to a target table. -- I also looked at arcpy.project_management. But besides the hilarious name that will make any PMP laugh, I couldn't get it to work. I think it requires a feature class again.
Since this works as it, I have to mention that I'm needing to append additional spatial attributes such as UTM zones. That's why I'm wondering if this is the "best" approach.
I tried a workflow starting with a non-spatial table in SQL Server using Convert Coordinate Notation with no change in actual notation but a change in projection, then run Add XY Coordinates but its long winded. The right people here at Esri have been informed.