losing information when creating a shapefile

497
7
Jump to solution
01-16-2018 10:21 AM
sotokan80_
New Contributor III

I m using ArcGIS 10.4.1 and NetworkAnalyst. The following code is used to read a table in a database and create a points shapefile:


import arcpy
from arcpy import env
import csv
import pyodbc
TABLE_NAME = "ad0127_2403$";
try:
   cn = pyodbc.connect('DRIVER={SQL Server};SERVER=*******;DATABASE=routing;trusted_connection=yes')
   sql = "select Row_ID, Sys_Date, Latitude, Longitude, Dop, Speed, Mobile_ID_1, is_pod from dbo."+TABLE_NAME+" where is_pod = 224 order by Sys_Date"
   cur = cn.cursor()
   cur.execute(sql)
   the_data = cur.fetchall()
   cn.commit()

   OPoint = arcpy.Point()
   pointGeometryList = []

   for row in the_data:
      OPoint.Y = float(row[2])
      OPoint.X = float(row[3])
      Speed = float(row[4])

      spRef = arcpy.SpatialReference("WGS 1984")
      pointGeometry = arcpy.PointGeometry(OPoint,spRef)
      pointGeometryList.append(pointGeometry)

   # Output the result
   # Copy features to shapefile
   arcpy.CopyFeatures_management(pointGeometryList,    r"C:\Users\*****\Desktop\ROUTES\testingRoutess\testing.shp")
   arcpy.AddXY_management(r"C:\Users\******\Desktop\ROUTES\testingRoutess\testing.shp")

except Exception as e:
# If an error occurred, print line number and error message
import traceback, sys
tb = sys.exc_info()[2]
print "An error occured on line %i" % tb.tb_lineno
print str(e)

The problem that i m facing has to do with the fact that the created shapefile's attribute table is missing crucial information.The source table has the following fields

the shapefile's attribute table has the following fields

Is there any way or python method to auto add more fields (i.e Speed) in the attribute table? Any help would be appreciated.

0 Kudos
1 Solution

Accepted Solutions
DanPatterson_Retired
MVP Emeritus

You can use an insertcursor and do it that way, or you could simply add Delete Field in a loop.  This option is much simpler particularly if your field names remain the same between runs, as they might)

View solution in original post

7 Replies
DanPatterson_Retired
MVP Emeritus

you only create the x, y coordinates for the output.  do you have a common field that you can use in a join?

0 Kudos
sotokan80_
New Contributor III

The common fields are those with the coordinates. But I was trying to avoid joining the tables because i get too many unused fields or empty fields. Is there any other way to add fields in the attribute table?

0 Kudos
DanPatterson_Retired
MVP Emeritus

You can use an insertcursor and do it that way, or you could simply add Delete Field in a loop.  This option is much simpler particularly if your field names remain the same between runs, as they might)

JakeSkinner
Esri Esteemed Contributor

You can simplify this by running the Make XY Event Layer function in your script.  This will retain all of your attributes.  You can then use the CopyFeatures function to copy this to a shapefile.

sotokan80_
New Contributor III

Can you give me a small tip on how to use the make X Y event function.Do i have to create a temporary table because the function's syntax specifies that there is an input table

0 Kudos
JakeSkinner
Esri Esteemed Contributor

You would reference your database table through a Database Connection.  In the below example I'm connecting to a SQL Server database (not a geodatabase) and referencing a table called XY, creating the event layer, then exporting to a shapefile:

import arcpy
from arcpy import env
env.workspace = r"Database Connections\SQLSERVER - DBO@NONGDB.sde"

table = "XY"

sr = arcpy.SpatialReference(4326)

arcpy.MakeXYEventLayer_management(table, "x", "y", "XY_Layer", sr)

arcpy.CopyFeatures_management("XY_Layer", r"D:\temp\python\XY_Points.shp")
DanPatterson_Retired
MVP Emeritus

Jake... cool new tool in Pro... simplifies the process XY Table to Point