Connect multiple (longitude/latitude) points with other ones

[SebastianSteffen]

Hi,
I'm trying to graph cab movement in NYC. I have a .xls-file that looks like this:
car   pickup_lat   pickup_lon   dropoff_lat   dropoff_lon
1     73.15724     -40.12345     73.12345    -40.98766
1 ... ...
2 ...
3 ...
...
I just want to solve each route, so I was thinking about using an OD Cost Matrix.

Any ideas how I could accomplish this? Any links to tutorials on how to solve many routing-problems at once? It is not possible to solve them all by typing it in by hand.
Later on I would like to add pickup and dropoff times to make an animation.

Thanks for your help!

Sebastian

[NicoleHanson]

Here's a link to the OD Matrix training: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/OD_cost_matrix_analysis/00470000004r00...

Hope that helps!

[SebastianSteffen]

Hi, thank you very much. I had already looked at that. I guess my main question concerns an earlier stage: How do I make my points, which are stored in an .xls-file, into a layer or loadable network analysis objects? OD Cost Matrix requires me to add my start points as one layer and my end points as another. Is there maybe another tutorial on this?

Best,

Sebastian

[NicoleHanson]

Hi, thank you very much. I had already looked at that. I guess my main question concerns an earlier stage: How do I make my points, which are stored in an .xls-file, into a layer or loadable network analysis objects? OD Cost Matrix requires me to add my start points as one layer and my end points as another. Is there maybe another tutorial on this?

Best,

Sebastian

Hi Sebastian-

It sounds like you just need to create points from the spreadsheet, you'll probably want to seperate out the Start from the End data.  And then will need to create a Network Dataset. 

Nicole

[SebastianSteffen]

Hi Nicole,

Thank you very much for your reply. Now, since I am a complete beginner with ArcGIS, could you (or someone else) tell me how to create a Network dataset out of a .xls spreadsheet?

Happy Holidays,

Sebastian

[JaySandhu · Accepted solution]

Make sure to have some ID field in your excel sheet that you can use later to pair up the pick up and drop off points. Then add the excel spread sheet to ArcMap (from catalog window). Then from the File menu choose Add Data and then choose Add XY data. On the form for AddXY data, choose the excel table and then map the first set of Longitude and Latitude to the X and Y fields and click OK. This will create a new point layer in ArcMap. Repeate the above process with the second set of Longitude and Latitude and click OK. Now you have two feature classes with the pick up and drop of points.

To create "routes" between these pairs, use the Route solver. DO NOT USE the OD Cost Matrix Solver for this as that will only create distances (not route shapes) between ALL pairs.

To use the Route solver, you need a network dataset of streets. If you do not have one, then use the free streetmap network that comes with ArcGIS. Once you add that to ArcMap, use the NA toolbar to create the route layer. Then make the NA window active and right click on the Stops and choose Load Locations. Now load the first point layer MAKING SURE that you map the RouteName property to the common ID. Repeate the process with the second point layer using routename and same ID mapping. This will create multiple route pairs in the same route layer. Now Solve and you will get routes from each pickup to each drop off point pairs.

Jay Sandhu

[SebastianSteffen]

Thank you very much. I was able to import the data by creating a feature class (thus getting an ObjectID).
However when adding the generated layer to the NY map I have, the coordinate systems of my New York Map and of the points do not seem to coincide. I tried importing the coordinate system from the Map to the dropoff layer and the pickup layer, but it still did not put the points ontop of the map (but rather somewhere totally different).
The map has the following coordinate system:

Projected Coordinate System: NAD_1983_StatePlane_New_York_Long_Island_FIPS_3104_Feet
Projection: Lambert_Conformal_Conic
False_Easting: 984250.00000000
False_Northing: 0.00000000
Central_Meridian: -74.00000000
Standard_Parallel_1: 40.66666667
Standard_Parallel_2: 41.03333333
Latitude_Of_Origin: 40.16666667
Linear Unit:  Foot_US

Geographic Coordinate System: GCS_North_American_1983
Datum:  D_North_American_1983
Prime Meridian:  Greenwich
Angular Unit:  Degree

Any ideas?
Best,

Sebastian

[JaySandhu]

ArcMap takes care of projecting data to the current map projection. So that should not be an issue.
If the points are very far away then most likely when you create the feature class from the excel table you mapped the Latitude as X and Longitude as Y. You need to reverse that and recreate the point feature class mapping Latitude as Y and Longitude as X.

Jay Sandhu

[SebastianSteffen]

Thank you so much, Jay! It's working!
Now for my last question about time enabling and then I will finally shut up.^^
I have the pickup and dropoff times in the following format in my .xls file: 9/14/2011 11:23:41. However when creating a feature class from it, it only keeps the date and not the time (11:23:41 gets cut off). Is the problem already in the excel formatting? Should I split the date&time into a date row and a time row?
Once that works I believe I just have to map TimeWindowStart to pickuptime and TimeWindowEnd to dropofftime and then enable time on the layer to use the time slider, right?

Best,

Sebastian