4

I have two separate excel documents that I'd like to import into ArcMap, but I don't exactly know how to create a shapefile. The two files are as follows:

  1. Point data. This spreadsheet has a long list of points (road intersections) with unique IDs and XY coordinates. This one shouldn't be too hard to import.
  2. Line data. This spreadsheet has "From" and "To" columns with the unique IDs from the point data spreadsheet. It also has the XY coordinates of the points.

Is there any way to import the line data spreadsheet so that ArcMap draws a line between each of the points?

Another thing to consider is that many of these roads are not simply straight lines, so I don't know how to account for that. I'm using ArcGIS Desktop 10.1.

  • As per the Tour there should be only one question asked per question, and as you can see the answers for points and lines are different. – PolyGeo Feb 13 '17 at 7:10
3

For the line file, it seems to me that the easiest way to get that into your GIS would be a Python script that iterates through each line of the file and then creates a line segment. Something like this, perhaps, should do the job:

import arcpy

outPath = r"C:\Data" #output path for the feature class
featureClass = "roadSegments.shp" #the name of your shapefile
spatialRef = arcpy.SpatialReference("WGS 1984") #the spatial reference you want; set it to whatever you want

arcpy.CreateFeatureclass_management(outPath, featureClass, "POLYLINE", " ", "DISABLED", "DISABLED", spatialRef) #sets up the shapefile we're using

arcpy.AddField_management(outPath + "\\" + featureClass, "segmentID", "SHORT", 15) #I assume that each segment has some sort of ID number that you might want to import into

roadArray = arcpy.Array() #this will hold the coordinates for each line segment until we write it to the shapefile.

roadFile = open(r"C:\Data\roadLines.csv") #the path to your road lines file
headerLine = roadFile.readLine()
indexList = headerLine.split(",")

#now we need the index locations for the columns that contain the latitude and longitude points for the starting and ending points for each line segment.  I'm also assuming that each segment has some sort of ID value in your segment.  Change the value inside the parenthesis to reflect whatever you named your column for those values.

idValueIndex = indexList.index('segmentID')
fromLatValueIndex = indexList.index('fromLatitude')
fromLongValueIndex = indexList.index('fromLongitude')
toLatValueIndex = indexList.index('toLatitude')
toLongValueIndex = indexList.index('toLongitude')

roadCursor = arcpy.InsertCursor(outPath + "\\" + featureClass)

for fileLine in roadFile.readlines():
    readLine = fileLine.split(",")
    idNumber = int(readLine[idValueIndex])
    fromLat = float(readLine[fromLatValueIndex])
    fromLong = float(readLine[fromLongValueIndex])
    toLat = float(readLine[toLatValueIndex])
    toLong = float(readLine[toLongValueIndex])

    #now let's add the points and draw the lines
    fromVertex = arcpy.Point(fromLong, fromLat)
    roadArray.add(fromVertex)
    toVertex = arcpy.Point(toLong, toLat)
    roadArray.add(toVertex)

    roadCursor = arcpy.InsertCursor(outPath + "\\" + featureClass)
    feature = roadCursor.newRow()
    polyline = arcpy.Polyline(roadArray, spatialRef)
    feature.shape = polyline
    feature.setValue("segmentID", idNumber) #adds the segmentID in the attribute table

    roadCursor.insertRow(feature)
    roadArray.removeAll()

del roadCursor

print "Finished!"
print arcpy.GetMessages()

Once you do have the shapefile created, then you can join your excel file to it using the segmentID field (or whatever you call it in the excel file).

Unfortunately, this won't do anything about the road curves that you mentioned in the question, it merely "connects the dots" with your intersection points.

4

For the point data how about adding it using the Import XY Data Tables Tool?

Basically:

  1. File > Add Data > Add XY Data
  2. Select your file and the requisite fields that match lat / lon (x / y).
  3. Set the spatial reference
  4. Import
  5. Right click the layer in the ToC and export data as a shapefile.

Here is an ESRI workflow example.

3

ArcToolbox > Data Management Tools > Features > XY To Line

It asks you for your input table, where you want your output stored, and then you choose the X1, Y1, X2, Y2 columns from the table.

In order to use the data you have, you should add four new columns to the second workbook and do a vlookup for each point ID to get the (X1, Y1) and (X2, Y2) fields in the same sheet as the lines. Then save that out and load into ArcGIS and use the tool mentioned above.

0

In ArcMap, you can open excel sheet (arcgis 10.0 support only .xls file, arcgis 10.3 or higher support *.xlsx file)

In TOC, right click excel sheet and choose Display X, Y data

Export Events of Sheet to Shapefile

Not the answer you're looking for? Browse other questions tagged or ask your own question.