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