Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 10.1.

Thanks!

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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.

Hope this helps!!

share|improve this answer
    
Thank you!! This does exactly what I want it to. I'm still building the excel file but this is very helpful. Another note - I created the GIS network already, so I realized I only need to join the excel file to my GIS table once I've finished editing my excel data. Either way, you've been a huge help! –  GISNetworkHelp May 31 '13 at 19:17
add comment

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.

share|improve this answer
add comment

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.

Good luck.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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