topic losing information when creating a shapefile in ArcGIS Network Analyst Questions

losing information when creating a shapefile

sotokan80_ — Tue, 16 Jan 2018 18:21:52 GMT

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.

Re: losing information when creating a shapefile

DanPatterson_Retired — Tue, 16 Jan 2018 19:12:42 GMT

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

Re: losing information when creating a shapefile

sotokan80_ — Tue, 16 Jan 2018 19:36:49 GMT

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?

Re: losing information when creating a shapefile

DanPatterson_Retired — Tue, 16 Jan 2018 19:45:24 GMT

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)

Re: losing information when creating a shapefile

JakeSkinner — Tue, 16 Jan 2018 20:09:11 GMT

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.

Re: losing information when creating a shapefile

sotokan80_ — Tue, 16 Jan 2018 20:17:38 GMT

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

Re: losing information when creating a shapefile

JakeSkinner — Sat, 11 Dec 2021 20:57:50 GMT

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")‍‍‍‍‍‍‍‍‍‍‍

Re: losing information when creating a shapefile

DanPatterson_Retired — Tue, 16 Jan 2018 21:20:34 GMT

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