I have a travel diary survey dataset that I am attempting to transform into more useful information. Currently, the data comes in the format of:
Survey# , PlaceVisited#, TravelMode, TripPurpose, LocationType 1, 1, NA, NA, home 1, 2, auto, doctor, office 1, 3, auto, shop, grocery_store 1, 4, auto, NA, home
where each place the survey respondent visits is a separate record (row) in the survey table, but this is really one multipurpose trip. What I would like to do with the data is separate each actual trip into a route with various points and purpose information included as (i think) a line using shortest path on a road network (I don't have the route actually taken). Currently, if a respondent leaves their house, and travels to several different stores by car, each store is entered as a separate record, as is their home starting location.
What I would like to have is one "trip" for each time the respondent leaves their house and comes back, regardless of how many places they visit while preserving the information about each place they visit. I am wondering what the best way to tackle this problem would be. I have so far struck out finding prepackaged tools to perform such a task in QGIS, GRASS, ArcGIS, Maptitude, and PostGIS. Should I approach this as a scripting problem using SQL, scripting a shortest path tool in a GIS, or is there another more efficient way to do it. Many thanks in advance!