def runTool(inGTFSdir, SQLDbase): try: BBB_SharedFunctions.CheckArcVersion(min_version_pro="1.2") #----- SQLize the GTFS data----- arcpy.AddMessage("SQLizing the GTFS data...") arcpy.AddMessage("(This will take a while for large datasets.)") # GTFS files if not SQLDbase.lower().endswith(".sql"): SQLDbase = SQLDbase + ".sql" # Fix up list of GTFS datasets inGTFSdirList = inGTFSdir.split(";") # Remove single quotes ArcGIS puts in if there are spaces in the filename. for d in inGTFSdirList: if d[0] == "'" and d[-1] == "'": loc = inGTFSdirList.index(d) inGTFSdirList[loc] = d[1:-1] # The main SQLizing work is done in the sqlize_csv module # written by Luitien Pan. # Connect to or create the SQL file. sqlize_csv.connect(SQLDbase) # Create tables. for tblname in sqlize_csv.sql_schema: sqlize_csv.create_table(tblname) # SQLize all the GTFS files, for each separate GTFS dataset. for gtfs_dir in inGTFSdirList: # handle_agency checks for blank values in arrival_time and departure_time sqlize_csv.handle_agency(gtfs_dir) # Create indices to make queries faster. sqlize_csv.create_indices() # Check for non-overlapping date ranges to prevent double-counting. overlapwarning = sqlize_csv.check_nonoverlapping_dateranges() if overlapwarning: arcpy.AddWarning(overlapwarning) arcpy.AddMessage("Successfully created SQL database of GTFS data:") arcpy.AddMessage("- " + SQLDbase) except BBB_SharedFunctions.CustomError: arcpy.AddMessage("Failed to create SQL database of GTFS data.") pass except: arcpy.AddMessage("Failed to create SQL database of GTFS data.") raise
def runTool(inGTFSdir, SQLDbase): try: BBB_SharedFunctions.CheckArcVersion(min_version_pro="1.2") #----- SQLize the GTFS data----- arcpy.AddMessage("SQLizing the GTFS data...") arcpy.AddMessage("(This will take a while for large datasets.)") # GTFS files if not SQLDbase.lower().endswith(".sql"): SQLDbase = SQLDbase + ".sql" # Fix up list of GTFS datasets inGTFSdirList = inGTFSdir.split(";") # Remove single quotes ArcGIS puts in if there are spaces in the filename. for d in inGTFSdirList: if d[0] == "'" and d[-1] == "'": loc = inGTFSdirList.index(d) inGTFSdirList[loc] = d[1:-1] # The main SQLizing work is done in the sqlize_csv module # written by Luitien Pan. # Connect to or create the SQL file. sqlize_csv.connect(SQLDbase) # Create tables. for tblname in sqlize_csv.sql_schema: sqlize_csv.create_table(tblname) # SQLize all the GTFS files, for each separate GTFS dataset. for gtfs_dir in inGTFSdirList: # handle_agency checks for blank values in arrival_time and departure_time sqlize_csv.handle_agency(gtfs_dir) # Create indices to make queries faster. sqlize_csv.create_indices() # Check for non-overlapping date ranges to prevent double-counting. overlapwarning = sqlize_csv.check_nonoverlapping_dateranges() if overlapwarning: arcpy.AddWarning(overlapwarning) arcpy.AddMessage("Successfully created SQL database of GTFS data:") arcpy.AddMessage("- " + SQLDbase) except BBB_SharedFunctions.CustomError: arcpy.AddMessage("Failed to create SQL database of GTFS data.") pass except: arcpy.AddMessage("Failed to create SQL database of GTFS data.") raise
def main(inGTFSdir, OutShapesFC): try: # ----- Set up inputs and other stuff ----- orig_overwrite = arcpy.env.overwriteOutput arcpy.env.overwriteOutput = True outGDB = os.path.dirname(OutShapesFC) OutShapesFCname = os.path.basename(OutShapesFC) SQLDbase = os.path.join(outGDB, OutShapesFCname.replace(".shp", "") + ".sql") # Check the user's version if not ArcVersion or not ProductName: global ArcVersion, ProductName ArcVersionInfo = arcpy.GetInstallInfo("desktop") ArcVersion = ArcVersionInfo['Version'] ProductName = ArcVersionInfo['ProductName'] # ----- SQLize the GTFS data ----- arcpy.AddMessage("SQLizing the GTFS data...") # Fix up list of GTFS datasets inGTFSdirList = inGTFSdir.split(";") # Remove single quotes ArcGIS puts in if there are spaces in the filename. for d in inGTFSdirList: if d[0] == "'" and d[-1] == "'": loc = inGTFSdirList.index(d) inGTFSdirList[loc] = d[1:-1] # The main SQLizing work is done in the sqlize_csv module # originally written by Luitien Pan for GTFS_NATools. # Connect to or create the SQL file. sqlize_csv.connect(SQLDbase) # Create tables. for tblname in sqlize_csv.sql_schema: sqlize_csv.create_table(tblname) # SQLize all the GTFS files, for each separate GTFS dataset. for gtfs_dir in inGTFSdirList: # Run sqlize each GTFS dataset. Check for returned errors GTFSErrors = sqlize_csv.handle_agency(gtfs_dir) if GTFSErrors: for error in GTFSErrors: arcpy.AddError(error) raise CustomError # Create indices to make queries faster. sqlize_csv.create_indices() sqlize_csv.db.close() # Connect to the SQL database global c conn = sqlite3.connect(SQLDbase) c = conn.cursor() # ----- Make dictionary of route info ----- global RouteDict if not sqlize_csv.populate_route_info: arcpy.AddWarning( "Your GTFS trips.txt file does not have a shape_id column. \ This tool can still draw the route shapes in the map, but it will not be able to populate \ the output feature class's attribute table with route information.") else: # Only do this if it's possible to populate the route info arcpy.AddMessage("Collecting Route info...") # Find all routes and associated info. routesfetch = ''' SELECT route_id, agency_id, route_short_name, route_long_name, route_desc, route_type, route_url, route_color, route_text_color FROM routes ;''' c.execute(routesfetch) routelist = c.fetchall() for routeitem in routelist: # Convert from a tuple to a list so the .shp logic below doesn't mess up route = list(routeitem) # {route_id: [all route.txt fields + route_type_text]} try: route_type_text = route_type_dict[int(route[5])] except: route_type_text = "" # Shapefile output can't handle null values, so make them empty strings. if ".shp" in OutShapesFCname: possiblenulls = [1, 4, 6, 7, 8] for idx in possiblenulls: if not route[idx]: route[idx] = "" RouteDict[route[0]] = [ route[1], route[2], route[3], route[4], route[5], route[6], route[7], route[8], route_type_text ] # ----- Create output feature class and prepare InsertCursor ----- arcpy.AddMessage("Creating output feature class...") # Create the output feature class and add the right fields arcpy.management.CreateFeatureclass(outGDB, OutShapesFCname, "POLYLINE", "", "", "", WGSCoords) # Shapefiles can't have field names longer than 10 characters if ".shp" in OutShapesFCname: arcpy.management.AddField(OutShapesFC, "RtShpName", "TEXT") arcpy.management.AddField(OutShapesFC, "shape_id", "TEXT") arcpy.management.AddField(OutShapesFC, "route_id", "TEXT") arcpy.management.AddField(OutShapesFC, "agency_id", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_shrt_nm", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_long_nm", "TEXT") arcpy.management.AddField(OutShapesFC, "route_desc", "TEXT", "", "", max_route_desc_length) arcpy.management.AddField(OutShapesFC, "route_type", "SHORT") arcpy.management.AddField(OutShapesFC, "rt_typ_txt", "TEXT") arcpy.management.AddField(OutShapesFC, "route_url", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_color", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_col_fmt", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_txt_col", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_txt_fmt", "TEXT") else: arcpy.management.AddField(OutShapesFC, "RouteShapeName", "TEXT") arcpy.management.AddField(OutShapesFC, "shape_id", "TEXT") arcpy.management.AddField(OutShapesFC, "route_id", "TEXT") arcpy.management.AddField(OutShapesFC, "agency_id", "TEXT") arcpy.management.AddField(OutShapesFC, "route_short_name", "TEXT") arcpy.management.AddField(OutShapesFC, "route_long_name", "TEXT") arcpy.management.AddField(OutShapesFC, "route_desc", "TEXT", "", "", max_route_desc_length) arcpy.management.AddField(OutShapesFC, "route_type", "SHORT") arcpy.management.AddField(OutShapesFC, "route_type_text", "TEXT") arcpy.management.AddField(OutShapesFC, "route_url", "TEXT") arcpy.management.AddField(OutShapesFC, "route_color", "TEXT") arcpy.management.AddField(OutShapesFC, "route_color_formatted", "TEXT") arcpy.management.AddField(OutShapesFC, "route_text_color", "TEXT") arcpy.management.AddField(OutShapesFC, "route_text_color_formatted", "TEXT") # Create the InsertCursors global StopsCursor if ArcVersion == "10.0": StopsCursor = arcpy.InsertCursor(OutShapesFC) else: # For everything 10.1 and forward if ".shp" in OutShapesFCname: StopsCursor = arcpy.da.InsertCursor(OutShapesFC, [ "SHAPE@", "RtShpName", "shape_id", "route_id", "agency_id", "rt_shrt_nm", "rt_long_nm", "route_desc", "route_type", "rt_typ_txt", "route_url", "rt_color", "rt_col_fmt", "rt_txt_col", "rt_txt_fmt" ]) else: StopsCursor = arcpy.da.InsertCursor(OutShapesFC, [ "SHAPE@", "RouteShapeName", "shape_id", "route_id", "agency_id", "route_short_name", "route_long_name", "route_desc", "route_type", "route_type_text", "route_url", "route_color", "route_color_formatted", "route_text_color", "route_text_color_formatted" ]) # ----- Add the shapes to the feature class ----- arcpy.AddMessage("Adding route shapes to output feature class...") # Get list of shape_ids shapesfetch = ''' SELECT DISTINCT shape_id FROM shapes ;''' c.execute(shapesfetch) shapeslist = c.fetchall() # Actually add the shapes to the feature class unused_shapes = False for shape in shapeslist: if not sqlize_csv.populate_route_info: # Don't worry about populating route info make_GTFS_lines_from_Shapes(shape[0]) else: # Get the route ids that have this shape. # There should probably be a 1-1 relationship, but not sure. shapesroutesfetch = ''' SELECT DISTINCT route_id FROM trips WHERE shape_id='%s' ;''' % shape[0] c.execute(shapesroutesfetch) shapesroutes = c.fetchall() if len(shapesroutes) == 0: # No trips actually use this shape, so skip adding route info make_GTFS_lines_from_Shapes(shape[0]) unused_shapes = True else: for route in shapesroutes: make_GTFS_lines_from_Shapes(shape[0], route[0]) if unused_shapes: arcpy.AddWarning( "One or more of the shapes in your GTFS shapes.txt file are not used by any \ trips in your trips.txt file. These shapes were included in the output from this tool, but route \ information was not populated.") # Clean up. Delete the cursor. del StopsCursor # Close things up and delete the SQL database conn.close() os.remove(SQLDbase) arcpy.AddMessage("Finished!") except CustomError: arcpy.AddError("Failed to generate a feature class of GTFS shapes.") pass except: arcpy.AddError("Failed to generate a feature class of GTFS shapes.") raise finally: # Reset the overwrite output to the user's original setting.. arcpy.env.overwriteOutput = orig_overwrite
# Connect to or create the SQL file. sqlize_csv.connect(SQLDbase) # Create tables. for tblname in sqlize_csv.sql_schema: sqlize_csv.create_table(tblname) # SQLize all the GTFS files, for each separate GTFS dataset. for gtfs_dir in inGTFSdirList: # Run sqlize for each GTFS dataset. Check for returned errors GTFSErrors = sqlize_csv.handle_agency(gtfs_dir) if GTFSErrors: for error in GTFSErrors: arcpy.AddError(error) raise CustomError # Create indices to make queries faster. sqlize_csv.create_indices() # Check for non-overlapping date ranges to prevent double-counting. overlapwarning = sqlize_csv.check_nonoverlapping_dateranges() if overlapwarning: arcpy.AddWarning(overlapwarning) # ----- Connect to SQL locally for further queries and entries ----- # Connect to the SQL database conn = sqlite3.connect(SQLDbase) c = conn.cursor() # ----- Make dictionary of route types ----- # Find all routes and associated info.
# Connect to or create the SQL file. sqlize_csv.connect(SQLDbase) # Create tables. for tblname in sqlize_csv.sql_schema: sqlize_csv.create_table(tblname) # SQLize all the GTFS files, for each separate GTFS dataset. for gtfs_dir in inGTFSdirList: # handle_agency checks for blank values in arrival_time and departure_time GTFSErrors = sqlize_csv.handle_agency(gtfs_dir) if GTFSErrors: for error in GTFSErrors: arcpy.AddError(error) raise CustomError # Create indices to make queries faster. sqlize_csv.create_indices() # Check for non-overlapping date ranges to prevent double-counting. overlapwarning = sqlize_csv.check_nonoverlapping_dateranges() if overlapwarning: arcpy.AddWarning(overlapwarning) arcpy.AddMessage("Successfully created SQL database of GTFS data:") arcpy.AddMessage("- " + SQLDbase) except CustomError: arcpy.AddMessage("Failed to create SQL database of GTFS data.") pass except: arcpy.AddMessage("Failed to create SQL database of GTFS data.")
def main(inGTFSdir, OutShapesFC): try: # ----- Set up inputs and other stuff ----- orig_overwrite = arcpy.env.overwriteOutput arcpy.env.overwriteOutput = True outGDB = os.path.dirname(OutShapesFC) # If the output location is a feature dataset, we have to match the coordinate system global output_coords desc_outgdb = arcpy.Describe(outGDB) if hasattr(desc_outgdb, "spatialReference"): output_coords = desc_outgdb.spatialReference SQLDbaseLoc = os.path.dirname(outGDB) else: output_coords = WGSCoords SQLDbaseLoc = outGDB OutShapesFCname = os.path.basename(OutShapesFC) SQLDbase = os.path.join(SQLDbaseLoc, OutShapesFCname.replace(".shp", "") + ".sql") # Check the user's version if not ArcVersion or not ProductName: global ArcVersion, ProductName ArcVersionInfo = arcpy.GetInstallInfo("desktop") ArcVersion = ArcVersionInfo['Version'] ProductName = ArcVersionInfo['ProductName'] # ----- SQLize the GTFS data ----- arcpy.AddMessage("SQLizing the GTFS data...") # Fix up list of GTFS datasets inGTFSdirList = inGTFSdir.split(";") # Remove single quotes ArcGIS puts in if there are spaces in the filename. for d in inGTFSdirList: if d[0] == "'" and d[-1] == "'": loc = inGTFSdirList.index(d) inGTFSdirList[loc] = d[1:-1] # The main SQLizing work is done in the sqlize_csv module # originally written by Luitien Pan for GTFS_NATools. # Connect to or create the SQL file. sqlize_csv.connect(SQLDbase) # Create tables. for tblname in sqlize_csv.sql_schema: sqlize_csv.create_table(tblname) # SQLize all the GTFS files, for each separate GTFS dataset. for gtfs_dir in inGTFSdirList: # Run sqlize each GTFS dataset. Check for returned errors GTFSErrors = sqlize_csv.handle_agency(gtfs_dir) if GTFSErrors: for error in GTFSErrors: arcpy.AddError(error) raise CustomError # Create indices to make queries faster. sqlize_csv.create_indices() sqlize_csv.db.close() # Connect to the SQL database global conn conn = sqlite3.connect(SQLDbase) # ----- Make dictionary of route info ----- global RouteDict if not sqlize_csv.populate_route_info: arcpy.AddWarning("Your GTFS trips.txt file does not have a shape_id column. \ This tool can still draw the route shapes in the map, but it will not be able to populate \ the output feature class's attribute table with route information.") else: # Only do this if it's possible to populate the route info arcpy.AddMessage("Collecting Route info...") # Find all routes and associated info. c = conn.cursor() routesfetch = ''' SELECT route_id, agency_id, route_short_name, route_long_name, route_desc, route_type, route_url, route_color, route_text_color FROM routes ;''' c.execute(routesfetch) for routeitem in c: # Convert from a tuple to a list so the .shp logic below doesn't mess up route = list(routeitem) # {route_id: [all route.txt fields + route_type_text]} try: route_type_text = route_type_dict[int(route[5])] except: route_type_text = "" # Shapefile output can't handle null values, so make them empty strings. if ".shp" in OutShapesFCname: possiblenulls = [1, 4, 6, 7, 8] for idx in possiblenulls: if not route[idx]: route[idx] = "" RouteDict[route[0]] = [route[1], route[2], route[3], route[4], route[5], route[6], route[7], route[8], route_type_text] # ----- Create output feature class and prepare InsertCursor ----- arcpy.AddMessage("Creating output feature class...") # Create the output feature class and add the right fields arcpy.management.CreateFeatureclass(outGDB, OutShapesFCname, "POLYLINE", "", "", "", output_coords) # Shapefiles can't have field names longer than 10 characters if ".shp" in OutShapesFCname: arcpy.management.AddField(OutShapesFC, "RtShpName", "TEXT") arcpy.management.AddField(OutShapesFC, "shape_id", "TEXT") arcpy.management.AddField(OutShapesFC, "route_id", "TEXT") arcpy.management.AddField(OutShapesFC, "agency_id", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_shrt_nm", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_long_nm", "TEXT") arcpy.management.AddField(OutShapesFC, "route_desc", "TEXT", "", "", max_route_desc_length) arcpy.management.AddField(OutShapesFC, "route_type", "SHORT") arcpy.management.AddField(OutShapesFC, "rt_typ_txt", "TEXT") arcpy.management.AddField(OutShapesFC, "route_url", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_color", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_col_fmt", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_txt_col", "TEXT") arcpy.management.AddField(OutShapesFC, "rt_txt_fmt", "TEXT") else: arcpy.management.AddField(OutShapesFC, "RouteShapeName", "TEXT") arcpy.management.AddField(OutShapesFC, "shape_id", "TEXT") arcpy.management.AddField(OutShapesFC, "route_id", "TEXT") arcpy.management.AddField(OutShapesFC, "agency_id", "TEXT") arcpy.management.AddField(OutShapesFC, "route_short_name", "TEXT") arcpy.management.AddField(OutShapesFC, "route_long_name", "TEXT") arcpy.management.AddField(OutShapesFC, "route_desc", "TEXT", "", "", max_route_desc_length) arcpy.management.AddField(OutShapesFC, "route_type", "SHORT") arcpy.management.AddField(OutShapesFC, "route_type_text", "TEXT") arcpy.management.AddField(OutShapesFC, "route_url", "TEXT") arcpy.management.AddField(OutShapesFC, "route_color", "TEXT") arcpy.management.AddField(OutShapesFC, "route_color_formatted", "TEXT") arcpy.management.AddField(OutShapesFC, "route_text_color", "TEXT") arcpy.management.AddField(OutShapesFC, "route_text_color_formatted", "TEXT") # Create the InsertCursors global StopsCursor if ".shp" in OutShapesFCname: StopsCursor = arcpy.da.InsertCursor(OutShapesFC, ["SHAPE@", "RtShpName", "shape_id", "route_id", "agency_id", "rt_shrt_nm", "rt_long_nm", "route_desc", "route_type", "rt_typ_txt", "route_url", "rt_color", "rt_col_fmt", "rt_txt_col", "rt_txt_fmt"]) else: StopsCursor = arcpy.da.InsertCursor(OutShapesFC, ["SHAPE@", "RouteShapeName", "shape_id", "route_id", "agency_id", "route_short_name", "route_long_name", "route_desc", "route_type", "route_type_text", "route_url", "route_color", "route_color_formatted", "route_text_color", "route_text_color_formatted"]) # ----- Add the shapes to the feature class ----- arcpy.AddMessage("Adding route shapes to output feature class...") # Get list of shape_ids c = conn.cursor() shapesfetch = ''' SELECT DISTINCT shape_id FROM shapes ;''' c.execute(shapesfetch) # Actually add the shapes to the feature class unused_shapes = False c2 = conn.cursor() for shape in c: if not sqlize_csv.populate_route_info: # Don't worry about populating route info make_GTFS_lines_from_Shapes(shape[0]) else: # Get the route ids that have this shape. # There should probably be a 1-1 relationship, but not sure. shapesroutesfetch = ''' SELECT DISTINCT route_id FROM trips WHERE shape_id='%s' ;''' % shape[0] c2.execute(shapesroutesfetch) weresome = False for route in c2: weresome = True make_GTFS_lines_from_Shapes(shape[0], route[0]) if not weresome: # No trips actually use this shape, so skip adding route info make_GTFS_lines_from_Shapes(shape[0]) unused_shapes = True if unused_shapes: arcpy.AddWarning("One or more of the shapes in your GTFS shapes.txt file are not used by any \ trips in your trips.txt file. These shapes were included in the output from this tool, but route \ information was not populated.") # Clean up. Delete the cursor. del StopsCursor # Close things up and delete the SQL database conn.close() os.remove(SQLDbase) arcpy.AddMessage("Finished!") except CustomError: arcpy.AddError("Failed to generate a feature class of GTFS shapes.") pass except: arcpy.AddError("Failed to generate a feature class of GTFS shapes.") raise finally: # Reset the overwrite output to the user's original setting.. arcpy.env.overwriteOutput = orig_overwrite