def includeZonasDatabase(fileExcel):
postgre = databaseConnection.connectdb()
cursor = postgre.cursor()
# Usar o caminho do arquivo zonas-trafego-sjc-sp-csv.csv
df = pd.read_excel(fileExcel)
# salva no banco de dados os dados da tabela do excel
for row in df.iterrows():
val0, val1, val2, val3, val4, val5, val6, val7, val8, val9, val10, val11, val12 = row[1]
cursor.execute("INSERT INTO excel (zonatrafego, pop_ibge, dom_ibge, macrozona, nome_zt, pop_od, dom_od, perc_pop, perc_dom, area_km, x_centroid, y_centroid, pop_area) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",(str(val0), str(val1), str(val2), str(val3), str(val4), str(val5), str(val6), str(val7), str(val8), str(val9), str(val10), str(val11), str(val12)))
postgre.commit()
cursor.close()
def recordDistancesDirections(i, j, origins, destinations, tempmode, distance,
paths, city, state, duration, processed):
# Connect with database and check if connection have benn performed sucessfully
con = databaseConnection.connectdb()
cursor = con.cursor()
if con == False:
print("Database connection ERROR!")
exit()
# Create composed name of table
tableName = "distances_" + city + "_" + state
# Create composed name of table where intermediate paths are
intermediateTableName = "intermediate_distances_" + city + "_" + state
# Variable pointing out return value
varreturn = True
# Create variable to keep an string with full path from origin to destination
path_str = ""
# Get the number of parts into full path
numDirections = len(paths)
# Include each path block in path string
k = 0
while k < numDirections:
# Get the number of points in current path block
tmp = paths[k]
encoded_points = tmp[4]
# Create an string with all coded path points. Each block is separated by " "
path_str = path_str + " " + encoded_points
k += 1
# Remove white spaces in the begin and at the end of string
path_str = path_str.strip()
# Insert record into distance table
sql = "INSERT INTO " + tableName + " (idx_origin, idx_destination, origin, destination, distance_meters, mode, path, duration, processed) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)"
# Check if query have benn performed successfully
result = cursor.execute(
sql, (str(i), str(j), str(origins), str(destinations), str(distance),
str(tempmode), str(path_str), str(duration), processed))
con.commit()
if result == None:
print("Success in distances insertion!!")
varreturn = True
if distance != -1:
# Get the id of just include record
sql = "SELECT currval(pg_get_serial_sequence('" + tableName + "','id'));"
result = cursor.execute(sql)
row = cursor.fetchone()
row = str(row)
row = row.strip("[")
row = row.strip("(")
row = row.strip("]")
row = row.strip(")")
row = row.strip(",")
id_inserted_row = row
# Include each path block in database
w = 0
while w < numDirections:
# Get the number of points in current path block
temp = paths[w]
distance = temp[0]
duration = temp[1]
start_location = temp[2]
end_location = temp[3]
encoded_points = temp[4]
travel_mode = temp[5]
# Insert record into intermediate distance table
tt = 'id_' + tableName
sql = "INSERT INTO " + intermediateTableName + " (" + tt + ", duration_seconds, start_point, end_point, mode, path, distance_meters, processed) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(
sql,
(str(id_inserted_row), str(duration), str(start_location),
str(end_location), str(travel_mode), str(encoded_points),
str(distance), processed))
con.commit()
if result == None:
print("Success in intermediate distances insertion!!")
varreturn = True
else:
msg = "Error in intermediate distances insertion!! " + sql
print(msg)
varreturn = False
w += 1
if varreturn == True:
print("Success in intermediate distances insertion!!")
sql = "UPDATE " + tableName + " SET intermediate_paths_ok = '1' WHERE id = " + id_inserted_row
print(sql)
cursor.execute(sql)
con.commit()
else:
msg = "Error in distances insertion!! " + sql
print(msg)
varreturn = False
return varreturn
def yValue(self, row):
postgre = databaseConnection.connectdb()
cursor = postgre.cursor()
cursor.execute("SELECT y_centroid FROM excel where id = %d" % row)
return cursor.fetchone()
import databaseConnection
import csv
# import xlrd
postgre = databaseConnection.connectdb()
cursor = postgre.cursor()
# Usar o caminho do arquivo zonas-trafego-sjc-sp-csv.csv
wb1 = csv.reader(
open(
"/home/luizhenrique/3_Semestre/IniciacaoCientifica/Excel/zonas-trafego-sjc-sp-csv.CSV"
))
# wb2 = xlrd.open_workbook("/home/luiz/IniciacaoCientifica/zonas-trafego-sjc-sp.xls")
# salva no banco de dados os dados da tabela do excel
for row in wb1:
cursor.execute(
"INSERT INTO excel (zonatrafego, pop_ibge, dom_ibge, macrozona, nome_zt, pop_od, dom_od, perc_pop, perc_dom, area_km, x_centroid, y_centroid, pop_area) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
row)
postgre.commit()
cursor.close()
class Spreadsheet_Excel_Reader:
sheets = []
colnames = []
def getCol(self, col):
if (isinstance(col, str)):
def generateKMLFileWithIntermediatePath(numRecords, city, state):
# Connect with database and check if connection have been performed successfully
con = databaseConnection.connectdb()
cursor = con.cursor()
if not con:
print("Database connection ERROR!")
exit()
directory = "./KML_files/" + city + "_" + state
if not os.path.exists(directory):
os.makedirs(directory)
# Create composed name of table
tableName = "distances_" + city + "_" + state
# Create composed name of table where intermediate paths are
intermediateTableName = "intermediate_distances_" + city + "_" + state
# Get all the records in database that have been processed yet
sql = "SELECT * FROM " + tableName + " where processed = 0 ORDER BY id"
result = cursor.execute(sql)
i = 0
# Create an object of class Polyline to decode path
#polyline = PolylineCode.decode_polyline()
# Create a KML file for each not processed record
# row = pg_fetch_row(result)
row = cursor.fetchall()
while i < len(row):
id = row[i][0]
id_origin = row[i][1]
id_destination = row[i][2]
mode = row[i][6]
print(id)
# Invert coordinates (lat, long to long, lat) for origin and destination. Also removes "(" and ")" from string
origin = row[i][3]
origin = origin.strip("(")
origin = origin.strip(")")
tmp = origin.split(",")
origin = tmp[0] + ", " + tmp[1]
destination = row[i][4]
destination = destination.strip("(")
destination = destination.strip(")")
tmp = destination.split(",")
destination = tmp[0] + ', ' + tmp[1]
path_str = ""
# Get the encoded path and transform it into a string
encoded_points = row[i][7].split(" ")
decoded_points = []
numPaths = len(encoded_points)
j = 0
while j < numPaths:
tmp = encoded_points[j]
decoded_points = PolylineCode.decode_polyline(tmp)
k = 0
while k < len(decoded_points):
tmp = decoded_points[k]
path_str = path_str + str(tmp[1]) + "," + str(tmp[0]) + ",0.0"
k += 1
j += 1
path_str = path_str.strip()
# Create coordinates for origin and destination
tmp = origin.split(",")
path_origin = str(tmp[1]) + "," + str(tmp[0]) + ",0.00"
tmp = destination.split(",")
path_destination = str(tmp[1]) + "," + str(tmp[0]) + ",0.00"
###################################
#INICIO AREA TO GET THE INTERMEDIATE PATH
###################################
sql2 = "select * from " + intermediateTableName + " where id_distances_" + city + "_" + state + "='" + str(
id) + "' order by id "
result = cursor.execute(sql2)
row2 = cursor.fetchall()
print(row2)
print(sql2)
#input("Pressione qualquer coisa")
i2 = 0
content = []
while i2 < len(row2):
if ((i == 0 or i == len(row) - 1)
and (i2 == 0 or i2 == len(row2) - 1)):
i2 += 1
print(True)
continue
id_full_path2 = row2[i2][0]
# Invert coordinates (lat,long to long,lat) for origin and destination. Also removes "(" and ")" from string
origin2 = row2[i2][3]
origin2 = origin2.strip("(")
origin2 = origin2.strip(")")
tmp2 = origin2.split(",")
origin2 = tmp2[0] + ', ' + tmp2[1]
nameorigin2 = origin2
destination2 = row2[i2][4]
destination2 = destination2.strip("(")
destination2 = destination2.strip(")")
tmp2 = destination2.split(",")
destination2 = tmp2[0] + ', ' + tmp2[1]
namedestination2 = destination2
mode2 = row2[i2][5].strip()
path_str2 = ""
# Get the encoded path and transform it into a string
encoded_points2 = row2[i2][6].split(" ")
decoded_points2 = PolylineCode.decode_polyline(encoded_points2[0])
k = 0
print(encoded_points2)
print(decoded_points2)
while k < len(decoded_points2):
tmp2 = decoded_points2[k]
path_str2 = path_str2 + str(tmp2[1]) + "," + str(
tmp2[0]) + ",0.0"
k += 1
path_str2 = path_str2.strip()
# Create coordinates for origin and destination
tmp2 = origin2.split(",")
path_origin2 = str(tmp2[1]) + "," + str(tmp2[0]) + ",0.00"
tmp2 = destination2.split(",")
path_destination2 = str(tmp2[1]) + "," + str(tmp2[0]) + ",0.00"
content.append(
[origin2, path_origin2, destination2, path_destination2])
print("CONTENT ", content)
#print(i2)
#input("Pressione qualquer coisa")
i2 += 1
###################################
# FIM AREA TO GET THE INTERMEDIATE PATH
###################################
# Create a file to write KML marked language
file_name = "./KML_files/" + city + "_" + state + "/path-file-" + str(
id
) + "_(" + id_origin + ", " + id_destination + ")_mode-" + mode + ".kml"
fp = open(file_name, "w")
fp.write("<?xml version='1.0' encoding='UTF-8'?>\n")
fp.write("<kml xmlns='http://www.opengis.net/kml/2.2'>\n")
fp.write(" <Document>\n")
fp.write(" <name>Directions from " + origin + " to " +
destination + "</name>\n")
fp.write(" <Placemark>\n")
fp.write(" <styleUrl>#line-1267FF-5</styleUrl>\n")
fp.write(" <name>Directions from " + origin + " to " +
destination + "</name>\n")
fp.write(" <ExtendedData>\n")
fp.write(" </ExtendedData>\n")
fp.write(" <LineString>\n")
fp.write(" <tessellate>1</tessellate>\n")
fp.write(" <coordinates>" + path_str +
"</coordinates>\n")
fp.write(" </LineString>\n")
fp.write(" </Placemark>\n")
fp.write(" <Placemark>\n")
fp.write(" <styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write(" <name>" + origin + "</name>\n")
fp.write(" <ExtendedData>\n")
fp.write(" </ExtendedData>\n")
fp.write(
" <description><![CDATA[Origin]]></description>\n")
fp.write(" <Point>\n")
fp.write(" <coordinates>" + path_origin +
"</coordinates>\n")
fp.write(" </Point>\n")
fp.write(" </Placemark>\n")
i3 = 0
while i3 < len(content):
fp.write(" <Placemark>\n")
fp.write(" <styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write(" <name>" + content[i3][0] + "</name>\n")
fp.write(" <ExtendedData>\n")
fp.write(" </ExtendedData>\n")
fp.write(
" <description><![CDATA[Origin]]></description>\n"
)
fp.write(" <Point>\n")
fp.write(" <coordinates>" + content[i3][1] +
"</coordinates>\n")
fp.write(" </Point>\n")
fp.write(" </Placemark>\n")
fp.write(" <Placemark>\n")
fp.write(" <styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write(" <name>" + content[i3][2] + "</name>\n")
fp.write(" <ExtendedData>\n")
fp.write(" </ExtendedData>\n")
fp.write(
" <description><![CDATA[Origin]]></description>\n"
)
fp.write(" <Point>\n")
fp.write(" <coordinates>" + content[i3][3] +
"</coordinates>\n")
fp.write(" </Point>\n")
fp.write(" </Placemark>\n")
i3 += 1
fp.write(" <Placemark>\n")
fp.write(" <styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write(" <name>" + destination + "</name>\n")
fp.write(" <ExtendedData>\n")
fp.write(" </ExtendedData>\n")
fp.write(
" <description><![CDATA[Destination]]></description>\n"
)
fp.write(" <Point>\n")
fp.write(" <coordinates>" + path_destination +
"</coordinates>\n")
fp.write(" </Point>\n")
fp.write(" </Placemark>\n")
fp.write(" <Style id='icon-503-DB4436'>\n")
fp.write(" <IconStyle>\n")
fp.write(" <color>ff3644DB</color>\n")
fp.write(" <scale>1.1</scale>\n")
fp.write(" <Icon>\n")
fp.write(
" <href>http://www.gstatic.com/mapspro/images/stock/503-wht-blank_maps.png</href>\n"
)
fp.write(" </Icon>\n")
fp.write(" </IconStyle>\n")
fp.write(" </Style>\n")
fp.write(" <Style id='line-1267FF-5'>\n")
fp.write(" <LineStyle>\n")
fp.write(" <color>ffFF6712</color>\n")
fp.write(" <width>5</width>\n")
fp.write(" </LineStyle>\n")
fp.write(" </Style>\n")
fp.write("\t</Document>\n")
fp.write("</kml>\n")
fp.close()
# Get all the records in database that have not been processed yet
# sql2 = "UPDATE " + tableName + " SET processed = '1' WHERE id = " + id
# result2 = pg_query(con, sql2)'''
#input("ALGO")
i += 1
def roadsMappingService(pp, inputFile, city, state):
# Connect with database and check if connection have been performed successfully
con = databaseConnection.connectdb()
cursor = con.cursor()
if not con:
print("Database connection ERROR!")
exit()
# Read from a XLS a matrix where each line is a pair of coords lat/long
regions = importGeographicalPointsFromXLS.importGeographicalPointsFromXLS(
inputFile)
# Get the total of regions into region array
nr = len(regions)
# Create composed name of table
tableName = "distances_" + city + "_" + state
# Get last record in database
sql = "SELECT * FROM " + tableName + " ORDER BY id DESC"
cursor.execute(sql)
row = cursor.fetchall()
#row = []
# Setup initial index of origin and destination (in regions matrix) as the last index in database
if len(row) == 0:
idx_origin = 0
idx_destination = 1
# else:
# idx_origin = row[1]
# idx_destination = row[2] + 1
idx_origin = 0
idx_destination = 1
# Create pairs (origin and destination) for all the regions into regions array. For each point we have lat, long coordinates (respectively)
cc = 1
i = idx_origin
while i < nr - 1:
temp = regions[i]
origins = str(temp[1]) + "," + str(temp[0])
# Check if J index should start from I + 1 or using database recovered index
if i == idx_origin:
j = idx_destination
else:
j = i + 1
while j < nr:
temp = regions[j]
destinations = str(temp[1]) + ',' + str(temp[0])
''' Get the path for driving mode forward and backward '''
# Mode of travel
tempmode = "driving"
# Get the distance for forward path
[
distance, duration, paths
] = getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(
origins, destinations, tempmode)
#print(paths)
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(
i, j, destinations, origins, tempmode, distance, paths, city,
state, duration)
if output == False:
exit()
# Get the distance for backward path
[
distance, duration, paths
] = getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(
destinations, origins, tempmode)
print(paths)
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(
i, j, destinations, origins, tempmode, distance, paths, city,
state, duration)
if output == False:
exit()
''' Get the path for walking mode forward and backward '''
# Mode of travel
tempmode = "driving"
# Get the distance for forward path
[
distance, duration, paths
] = getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(
origins, destinations, tempmode)
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(
i, j, destinations, origins, tempmode, distance, paths, city,
state, duration)
if output == False:
exit()
# Get the distance for backward path
[
distance, duration, paths
] = getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(
destinations, origins, tempmode)
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(
i, j, destinations, origins, tempmode, distance, paths, city,
state, duration)
if output == False:
exit()
# Check if the script has already been executed the correct number of times. If yes stop the code. This process have been implemented because Google API has a limit of querys daily
if cc < pp:
cc += 1
else:
cc = cc
break
j += 1
if cc >= pp:
break
i += 1
def generateKMLFileIntermediatePath(numRecords, city, state):
# Connect with database and check if connection have been performed successfully
con = databaseConnection.connectdb()
cursor = con.cursor()
if not con:
print("Database connection ERROR!")
exit()
#Set directory location
directory = "./KML_files/Intermediate"
if not os.path.exists(directory):
os.makedirs(directory)
directory = "./KML_files/Intermediate/"+city+"_"+state
if not os.path.exists(directory):
os.makedirs(directory)
# Create composed name of table
tableName = "distances_" + city + "_" + state
# Create composed name of table where intermediate paths are
intermediateTableName = "intermediate_distances_" + city + "_" + state
# Create the name of the foreing key into intermediate table
idname = "id_distances_" + city + "_" + state
# Get all the records in database that have not been processed yet
sql = "SELECT * FROM " + tableName + " WHERE processed = 0 ORDER BY id"
result = cursor.execute(sql)
i = 0
# Create a KML file for each not processed record
row = cursor.fetchall()
while i < len(row):
id_full_path = row[i][0]
id_origin = row[i][1]
id_destination = row[i][2]
mode = row[i][6]
print(id_full_path)
sql2 = "select * from "+intermediateTableName+" where id_distances_sjc_sp='"+str(id_full_path)+"' order by id "
result = cursor.execute(sql2)
row2 = cursor.fetchall()
path_file = str(id_full_path)+"_("+id_origin+", "+id_destination+")_mode-"+mode
directory2 = directory+"/"+path_file
if not os.path.exists(directory2):
os.makedirs(directory2)
i2 = 0
while i2 < len(row2):
id_full_path2 = row2[i2][0]
# Invert coordinates (lat,long to long,lat) for origin and destination. Also removes "(" and ")" from string
origin = row2[i2][3]
origin = origin.strip("(")
origin = origin.strip(")")
tmp = origin.split(",")
origin = tmp[0] + ', ' + tmp[1]
nameorigin = origin
destination = row2[i2][4]
destination = destination.strip("(")
destination = destination.strip(")")
tmp = destination.split(",")
destination = tmp[0] + ', ' + tmp[1]
namedestination = destination
mode = row2[i2][5].strip()
path_str = ""
# Get the encoded path and transform it into a string
encoded_points = row2[i2][6].split(" ")
decoded_points = PolylineCode.decode_polyline(encoded_points[0])
k = 0
while k < len(decoded_points):
tmp = decoded_points[k]
path_str = path_str + str(tmp[1]) + "," + str(tmp[0]) + ",0.0"
k += 1
path_str = path_str.strip()
# Create coordinates for origin and destination
tmp = origin.split(",")
path_origin = str(tmp[1]) + "," + str(tmp[0]) + ",0.00"
tmp = destination.split(",")
path_destination = str(tmp[1]) + "," + str(tmp[0]) + ",0.00"
# Create a file to write KML marked language
file_name = directory2+"/path-file-" + str(id_full_path2) + ".kml"
fp = open(file_name, "w")
fp.write("<?xml version='1.0' encoding='UTF-8'?>\n")
fp.write("<kml xmlns='http://www.opengis.net/kml/2.2'>\n")
fp.write(" <Document>\n")
fp.write(" <name>Directions from " + origin + " to " + destination + "</name>\n")
fp.write(" <Placemark>\n")
fp.write(" <styleUrl>#line-1267FF-5</styleUrl>\n")
fp.write(" <name>Directions from " + origin + " to " + destination + "</name>\n")
fp.write(" <ExtendedData>\n")
fp.write(" </ExtendedData>\n")
fp.write(" <LineString>\n")
fp.write(" <tessellate>1</tessellate>\n")
fp.write(" <coordinates>" + path_str + "</coordinates>\n")
fp.write(" </LineString>\n")
fp.write(" </Placemark>\n")
fp.write(" <Placemark>\n")
fp.write(" <styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write(" <name>" + origin + "</name>\n")
fp.write(" <ExtendedData>\n")
fp.write(" </ExtendedData>\n")
fp.write(" <description><![CDATA[Origin]]></description>\n")
fp.write(" <Point>\n")
fp.write(" <coordinates>" + path_origin + "</coordinates>\n")
fp.write(" </Point>\n")
fp.write(" </Placemark>\n")
fp.write(" <Placemark>\n")
fp.write(" <styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write(" <name>" + destination + "</name>\n")
fp.write(" <ExtendedData>\n")
fp.write(" </ExtendedData>\n")
fp.write(" <description><![CDATA[Destination]]></description>\n")
fp.write(" <Point>\n")
fp.write(" <coordinates>" + path_destination + "</coordinates>\n")
fp.write(" </Point>\n")
fp.write(" </Placemark>\n")
fp.write(" <Style id='icon-503-DB4436'>\n")
fp.write(" <IconStyle>\n")
fp.write(" <color>ff3644DB</color>\n")
fp.write(" <scale>1.1</scale>\n")
fp.write(" <Icon>\n")
fp.write(
" <href>http://www.gstatic.com/mapspro/images/stock/503-wht-blank_maps.png</href>\n")
fp.write(" </Icon>\n")
fp.write(" </IconStyle>\n")
fp.write(" </Style>\n")
fp.write(" <Style id='line-1267FF-5'>\n")
fp.write(" <LineStyle>\n")
fp.write(" <color>ffFF6712</color>\n")
fp.write(" <width>5</width>\n")
fp.write(" </LineStyle>\n")
fp.write(" </Style>\n")
fp.write("\t</Document>\n")
fp.write("</kml>\n")
fp.close()
# Get all the records in database that have not been processed yet
# sql2 = "UPDATE " + tableName + " SET processed = '1' WHERE id = " + id
# result2 = pg_query(con, sql2)
i2 += 1
i += 1
def roadsMappingServiceTXT(pp, inputFile, city, state, date):
# Connect with database and check if connection have been performed successfully
con = databaseConnection.connectdb()
if not con:
print("Database connection ERROR!")
exit()
cursor = con.cursor()
apikey = "AIzaSyAr11wo7Q90rmf7ZReZFVz3OV50g5iW1hI"
# Read from a XLS a matrix where each line is a pair of coords lat/long
regions = importGeographicalPointsFromTXT.importGeographicalPointsFromTXT(inputFile)
# Get the total of regions into region array
nr = len(regions)
# Create composed name of table
tableName = "distances_" + city + "_" + state
# Get last record in database
sql = "SELECT * FROM " + tableName + " ORDER BY id DESC"
cursor.execute(sql)
row = cursor.fetchone()
if row != None:
row = list(row)
# Setup initial index of origin and destination (in regions matrix) as the last index in database
if row == None:
idx_origin = 0
idx_destination = 1
else:
idx_origin = int(row[1])
idx_destination = int(row[2])
#verify where stopped in the last execution, because the code include i,j and j,i for each
#loop. Since then, if i=1 and j=2, the last include data is i=2 and j=1 when finalize the loop.
if idx_origin > idx_destination:
aux = idx_origin
idx_origin = idx_destination
idx_destination = aux
idx_destination += 1
# Create pairs (origin and destination) for all the regions into regions array. For each point we have lat, long coordinates (respectively)
cc = 1
i = idx_origin
while i < nr - 1:
temp = regions[i]
origins = str(temp[1]) + "," + str(temp[0])
print(origins)
# Check if J index should start from I + 1 or using database recovered index
if i == idx_origin:
j = idx_destination
else:
j = i + 1
while j < nr:
temp = regions[j]
destinations = str(temp[1]) + ',' + str(temp[0])
''' Get the path for driving mode forward and backward '''
# Mode of travel
tempmode = "driving"
#A to B
try:
# Get the distance for forward path
[distance, duration, paths]= getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(origins, destinations, tempmode, date, apikey)
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(i, j, origins, destinations, tempmode, distance, paths, city, state, duration, 0)
except:
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(i, j, origins, destinations, tempmode, '-1', [], city, state, '-1', 1)
#B to A
try:
# Get the distance for backward path
[distance, duration, paths] = getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(destinations, origins, tempmode, date, apikey)
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(j, i, destinations, origins, tempmode, distance, paths, city, state, duration, 0)
except:
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(j, i, destinations, origins, tempmode, '-1', [], city, state, '-1', 1)
''' Get the path for walking mode forward and backward '''
# Mode of travel
tempmode = "walking"
#A TO B
try:
# Get the distance for forward path
[distance, duration, paths]= getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(origins, destinations, tempmode, date, apikey)
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(i, j, origins, destinations, tempmode, distance, paths, city, state, duration, 0)
except:
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(i, j, origins, destinations, tempmode, '-1', [], city, state, '-1', 1)
#B TO A
try:
# Get the distance for backward path
[distance, duration, paths] = getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(destinations, origins, tempmode, date, apikey)
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(j, i, destinations, origins, tempmode, distance, paths, city, state, duration, 0)
except:
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(j, i, destinations, origins, tempmode, '-1', [], city, state, '-1', 1)
# Check if the script has already been executed the correct number of times. If yes stop the code. This process have been implemented because Google API has a limit of querys daily
if cc < pp:
cc += 1
else:
cc = cc
break
j += 1
if cc >= pp:
break
i += 1
def exportMatrix(city, state):
# Connect with database and check if connection have been performed successfully
con = databaseConnection.connectdb()
cursor = con.cursor()
if not con:
print("Database connection ERROR!")
exit()
# Create a directory called Matrix
directory = "./Matrix"
if not os.path.exists(directory):
os.makedirs(directory)
# Create composed name of table
tableName = "distances_" + city + "_" + state
# Get all the records in database that have not been processed yet
sql = "SELECT * FROM " + tableName + " ORDER BY CAST(idx_origin AS int), CAST(idx_destination AS int) DESC, CAST(id AS int) DESC"
print(sql)
result = cursor.execute(sql)
# Create a .XMLS for each situation (driving and walking) with a variable(time and distance)
dfWalkingDistance = pd.DataFrame()
dfWalkingTime = pd.DataFrame()
dfDrivingDistance = pd.DataFrame()
dfDrivingTime = pd.DataFrame()
# Create a variable to store the max idx_origin and idx_destination
row = cursor.fetchall()
maxIdx = int(row[0][2])
i = 0
nada = [0 for i in range(maxIdx + 1)]
for x in range(maxIdx + 1):
nada[x] = 'X'
for x in range(maxIdx + 1):
dfDrivingTime.insert(x, x, nada)
dfDrivingDistance.insert(x, x, nada)
dfWalkingDistance.insert(x, x, nada)
dfWalkingTime.insert(x, x, nada)
#Put All datas in the .XMLS
while i < len(row):
idOrigin = int(row[i][1])
idDestination = int(row[i][2])
time = str(round((float(row[i][8]) / 3600), 2)) + 'h'
distance = str(round((float(row[i][5]) / 1000), 2)) + 'km'
processed = int(row[i][10])
mode = row[i][6]
if (processed == 0):
if (mode == 'walking'):
#WALKING DISTANCE
dfWalkingDistance[idDestination][idOrigin] = distance
# WALKING TIME
dfWalkingTime[idDestination][idOrigin] = time
else:
# DRIVING DISTANCE
dfDrivingDistance[idDestination][idOrigin] = distance
# DRIVING TIME
dfDrivingTime[idDestination][idOrigin] = time
print('include line ' + str(i))
i += 1
for x in range(maxIdx + 1):
dfWalkingDistance[x][x] = '0km'
dfWalkingTime[x][x] = '0h'
dfDrivingDistance[x][x] = '0km'
dfDrivingTime[x][x] = '0h'
print("X: {}".format(x))
# Create a path to save a Pandas Excel
excelName = "./Matrix/Matrix_" + city + "_" + state + '.xlsx'
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter(excelName, engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
dfWalkingDistance.to_excel(writer, sheet_name='WalkingDistance')
dfWalkingTime.to_excel(writer, sheet_name='WalkingTime')
dfDrivingDistance.to_excel(writer, sheet_name='DrivingDistance')
dfDrivingTime.to_excel(writer, sheet_name='DrivingTime')
# Close the Pandas Excel writer and output the Excel file.
writer.save()
def generateKMLFile(numRecords, city, state):
# Connect with database and check if connection have been performed successfully
con = databaseConnection.connectdb()
cursor = con.cursor()
if not con:
print("Database connection ERROR!")
exit()
# Create composed name of table
tableName = "distances_" + city + "_" + state
# Create composed name of table where intermediate paths are
intermediateTableName = "intermiate_distances_" + city + "_" + state
# Create the name of the foreing key into intermediate table
idname = "id_distances_" + city + "_" + state
# Get all the records in database that have not been processed yet
sql = "SELECT * FROM " + tableName + " WHERE processed = 0 ORDER BY id"
result = cursor.execute(sql)
i = 0
# Create an object of class Polyline to decode path
# polyline = Polyline
# Create a KML file for each not processed record
row = cursor.fetchall()
while i < len(row):
id_full_path = row[i][0]
print(i)
# Invert coordinates (lat,long to long,lat) for origin and destination. Also removes "(" and ")" from string
origin = row[i][3]
origin = origin.strip("(")
origin = origin.strip(")")
tmp = origin.split(",")
origin = tmp[0] + ', ' + tmp[1]
nameorigin = origin
destination = row[i][4]
destination = destination.strip("(")
destination = destination.strip(")")
tmp = destination.split(",")
destination = tmp[0] + ', ' + tmp[1]
namedestination = destination
mode = row[i][6].strip()
path_str = ""
# Get the encoded path and transform it into a string
encoded_points = row[i][7].split(" ")
decoded_points = []
numPaths = len(encoded_points)
j = 0
while j < numPaths:
tmp = encoded_points[j]
decoded_points = PolylineCode.decode_polyline(tmp)
# decoded_points = polylineCode.PolylineClass.Par(polylineCode.PolylineClass.Decode(tmp))
k = 0
while k < len(decoded_points):
tmp = decoded_points[k]
path_str = path_str + str(tmp[1]) + "," + str(tmp[0]) + ",0.0 "
k += 1
j += 1
path_str = path_str.strip()
# Create coordinates for origin and destination
tmp = origin.split(", ")
path_origin = str(tmp[1]) + ',' + str(tmp[0]) + ',0.00'
tmp = destination.split(", ")
path_destination = str(tmp[1]) + ',' + str(tmp[0]) + ',0.00'
file_name = "C:/Users/Luiz/Google Drive/0 - Projeto Iniciação Científica/IniciacaoCientifica/IniciacaoCientifica/new_KML_file/" + mode + "_from_" + nameorigin + "_to_" + namedestination + "_part_00.kml"
fp = open(file_name, 'w')
# print(fp, xmlstring)
# Assembly XML string
fp.write("<?xml version='1.0' encoding='UTF-8'?>")
fp.write("<kml xmlns='http://www.opengis.net/kml/2.2'>")
fp.write("\t<Document>\n")
fp.write("\t\t<name>Directions from " + origin + " to " + destination + "</name>\n")
fp.write("\t\t\t<Placemark>\n")
fp.write("\t\t\t\t<styleUrl>#line-1267FF-5</styleUrl>\n")
fp.write("\t\t\t\t<name>Directions from " + origin + " to " + destination + "</name>\n")
fp.write("\t\t\t\t<ExtendedData>\n")
fp.write("\t\t\t\t</ExtendedData>\n")
fp.write("\t\t\t\t<LineString>\n")
fp.write("\t\t\t\t<tessellate>1</tessellate>\n")
fp.write("\t\t\t\t\t<coordinates>" + path_str + "</coordinates>\n")
fp.write("\t\t\t\t</LineString>\n")
fp.write("\t\t\t</Placemark>\n")
fp.write("\t\t\t<Placemark>\n")
fp.write("\t\t\t<styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write("\t\t\t\t<name>" + origin + "</name>\n")
fp.write("\t\t\t\t<ExtendedData>\n")
fp.write("\t\t\t\t</ExtendedData>\n")
fp.write("\t\t\t\t<description><![CDATA[Origin]]></description>\n")
fp.write("\t\t\t\t<Point>\n")
fp.write("\t\t\t\t\t<coordinates>" + path_origin + "</coordinates>\n")
fp.write("\t\t\t\t</Point>\n")
fp.write("\t\t\t</Placemark>\n")
fp.write("\t\t\t<Placemark>\n")
fp.write("\t\t\t\t<styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write("\t\t\t\t<name>" + destination + "</name>\n")
fp.write("\t\t\t\t<ExtendedData>\n")
fp.write("\t\t\t\t</ExtendedData>\n")
fp.write("\t\t\t\t<description><![CDATA[Destination]]></description>\n")
fp.write("\t\t\t\t<Point>\n")
fp.write("\t\t\t\t\t<coordinates>" + path_destination + "</coordinates>\n")
fp.write("\t\t\t\t</Point>\n")
fp.write("\t\t\t</Placemark>\n")
fp.write("\t\t\t<Style id='icon-503-DB4436'>\n")
fp.write("\t\t\t\t<IconStyle>\n")
fp.write("\t\t\t\t\t<color>ff3644DB</color>\n")
fp.write("\t\t\t\t\t<scale>1.1</scale>\n")
fp.write("\t\t\t\t\t<Icon>\n")
fp.write("\t\t\t\t\t\t<href>http://www.gstatic.com/mapspro/images/stock/503-wht-blank_maps.png</href>\n")
fp.write("\t\t\t\t\t</Icon>\n")
fp.write("\t\t\t\t</IconStyle>\n")
fp.write("\t\t\t</Style>\n")
fp.write("\t\t\t<Style id='line-1267FF-5'>\n")
fp.write("\t\t\t\t<LineStyle>\n")
fp.write("\t\t\t\t\t<color>ffFF6712</color>\n")
fp.write("\t\t\t\t\t<width>5</width>\n")
fp.write("\t\t\t\t</LineStyle>\n")
fp.write("\t\t\t</Style>\n")
fp.write("\t</Document>\n")
fp.write("</kml>\n")
fp.close()
# Update database record status to point out that it have already been processed
sql2 = "UPDATE " + tableName + " SET processed = '1' WHERE id = '" + str(id_full_path) + "'"
result2 = cursor.execute(sql2)
i += 1
# Get intermediate paths from an specific id
sql2 = "SELECT * FROM " + intermediateTableName + " WHERE '" + str(idname) + "' = '" + str(id_full_path) + "' ORDER BY id DESC"
resultIntermediate = cursor.execute(sql2)
part = 1
# For each intermediate part of path, construct a KML file
rowIntermediate = cursor.fetchall()
while rowIntermediate:
# Get data from database for intermediate paths
distance = rowIntermediate[7]
duration = rowIntermediate[2]
# Format origin and destination data from database to fit xml file
origin = rowIntermediate[3]
origin = origin.strip("(")
origin = origin.strip(")")
tmp = origin.split(", ")
origin = tmp[0] + ', ' + tmp[1]
destination = rowIntermediate[4]
destination = destination.strip("(")
destination = destination.strip(")")
tmp = destination.split(", ")
destination = tmp[0] + ', ' + tmp[1]
tmp = origin.split(", ")
path_origin = tmp[1] + ',' + tmp[0] + ',0.00'
tmp = destination.split(", ")
path_destination = tmp[1] + ',' + tmp[0] + ',0.00'
# Get the encoded path and transform it into a string
path_str = ""
encoded_points = rowIntermediate[6].split(" ")
decoded_points = []
numPaths = len(encoded_points)
j = 0
while j < numPaths:
tmp = encoded_points[j]
decoded_points = PolylineCode.decode_polyline(tmp)
# decoded_points = polyline.PolylineClass.Par(polyline.PolylineClass.Decode(tmp))
w = 0
while w < len(decoded_points):
tmp = decoded_points[w]
path_str = path_str + str(tmp[1]) + "," + str(tmp[0]) + ",0.0 "
w += 1
j += 1
path_str = path_str.strip()
travel_mode = rowIntermediate[5]
# Create a file to write KML marked language
file_name = ""
if part < 10:
file_name = "C:/Users/Luiz/Google Drive/0 - Projeto Iniciação Científica/IniciacaoCientifica/IniciacaoCientifica/intermediate_KML_file/" + mode + "_from_" + nameorigin + "_to_" + namedestination + "_part_0" + part + ".kml"
else:
file_name = "C:/Users/Luiz/Google Drive/0 - Projeto Iniciação Científica/IniciacaoCientifica/IniciacaoCientifica/intermediate_KML_file/" + mode + "_from_" + nameorigin + "_to_" + namedestination + "_part_" + part + ".kml"
fp = open(file_name, 'w')
# print(fp, xmlstring)
# Assembly XML string
fp.write("<?xml version='1.0' encoding='UTF-8'?>")
fp.write("<kml xmlns='http://www.opengis.net/kml/2.2'>")
fp.write("\t<Document>\n")
fp.write("\t\t<name>Directions from " + origin + " to " + destination + "</name>\n")
fp.write("\t\t\t<Placemark>\n")
fp.write("\t\t\t\t<styleUrl>#line-1267FF-5</styleUrl>\n")
fp.write("\t\t\t\t<name>Directions from " + origin + " to " + destination + "</name>\n")
fp.write("\t\t\t\t<ExtendedData>\n")
fp.write("\t\t\t\t</ExtendedData>\n")
fp.write("\t\t\t\t<LineString>\n")
fp.write("\t\t\t\t<tessellate>1</tessellate>\n")
fp.write("\t\t\t\t\t<coordinates>" + path_str + "</coordinates>\n")
fp.write("\t\t\t\t</LineString>\n")
fp.write("\t\t\t</Placemark>\n")
fp.write("\t\t\t<Placemark>\n")
fp.write("\t\t\t<styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write("\t\t\t\t<name>" + origin + "</name>\n")
fp.write("\t\t\t\t<ExtendedData>\n")
fp.write("\t\t\t\t</ExtendedData>\n")
fp.write("\t\t\t\t<description><![CDATA[Origin]]></description>\n")
fp.write("\t\t\t\t<Point>\n")
fp.write("\t\t\t\t\t<coordinates>" + path_origin + "</coordinates>\n")
fp.write("\t\t\t\t</Point>\n")
fp.write("\t\t\t</Placemark>\n")
fp.write("\t\t\t<Placemark>\n")
fp.write("\t\t\t\t<styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write("\t\t\t\t<name>" + destination + "</name>\n")
fp.write("\t\t\t\t<ExtendedData>\n")
fp.write("\t\t\t\t</ExtendedData>\n")
fp.write("\t\t\t\t<description><![CDATA[Destination]]></description>\n")
fp.write("\t\t\t\t<Point>\n")
fp.write("\t\t\t\t\t<coordinates>" + path_destination + "</coordinates>\n")
fp.write("\t\t\t\t</Point>\n")
fp.write("\t\t\t</Placemark>\n")
fp.write("\t\t\t<Style id='icon-503-DB4436'>\n")
fp.write("\t\t\t\t<IconStyle>\n")
fp.write("\t\t\t\t\t<color>ff3644DB</color>\n")
fp.write("\t\t\t\t\t<scale>1.1</scale>\n")
fp.write("\t\t\t\t\t<Icon>\n")
fp.write("\t\t\t\t\t\t<href>http://www.gstatic.com/mapspro/images/stock/503-wht-blank_maps.png</href>\n")
fp.write("\t\t\t\t\t</Icon>\n")
fp.write("\t\t\t\t</IconStyle>\n")
fp.write("\t\t\t</Style>\n")
fp.write("\t\t\t<Style id='line-1267FF-5'>\n")
fp.write("\t\t\t\t<LineStyle>\n")
fp.write("\t\t\t\t\t<color>ffFF6712</color>\n")
fp.write("\t\t\t\t\t<width>5</width>\n")
fp.write("\t\t\t\t</LineStyle>\n")
fp.write("\t\t\t</Style>\n")
fp.write("\t</Document>\n")
fp.write("</kml>\n")
fp.close()
part += 1
def generateKMLFile(numRecords, city, state):
# Connect with database and check if connection have been performed successfully
con = databaseConnection.connectdb()
cursor = con.cursor()
if not con:
print("Database connection ERROR!")
exit()
# Create composed name of table
tableName = "distances_" + city + "_" + state
# Get all the records in database that have benn processed yet
sql = "SELECT * FROM " + tableName + " WHERE processed = 0 ORDER BY id"
result = cursor.execute(sql)
i = 0
# Create an object of class Polyline to decode path
#polyline = PolylineCode.decode_polyline()
# Create a KML file for each not processed record
# row = pg_fetch_row(result)
row = cursor.fetchall()
while i < numRecords:
id = row[i][0]
print(id)
# Invert coordinates (lat, long to long, lat) for origin and destination. Also removes "(" and ")" from string
origin = row[i][3]
origin = origin.strip("(")
origin = origin.strip(")")
tmp = origin.split(",")
origin = tmp[0] + ", " + tmp[1]
destination = row[i][4]
destination = destination.strip("(")
destination = destination.strip(")")
tmp = destination.split(",")
destination = tmp[0] + ', ' + tmp[1]
path_str = ""
# Get the encoded path and transform it into a string
encoded_points = row[i][7].split(" ")
decoded_points = []
numPaths = len(encoded_points)
j = 0
while j < numPaths:
tmp = encoded_points[j]
decoded_points = PolylineCode.decode_polyline(tmp)
k = 0
while k < len(decoded_points):
tmp = decoded_points[k]
path_str = path_str + str(tmp[1]) + "," + str(tmp[0]) + ",0.0"
k += 1
j += 1
path_str = path_str.strip()
# Create coordinates for origin and destination
tmp = origin.split(",")
path_origin = str(tmp[1]) + "," + str(tmp[0]) + ",0.00"
tmp = destination.split(",")
path_destination = str(tmp[1]) + "," + str(tmp[0]) + ",0.00"
# Create a file to write KML marked language
file_name = "/home/luizhenrique/3_Semestre/Iniciacao_Cientifica/KML_files/path-file-" + str(id) + ".kml"
fp = open(file_name, "w")
fp.write("<?xml version='1.0' encoding='UTF-8'?>\n")
fp.write("<kml xmlns='http://www.opengis.net/kml/2.2'>\n")
fp.write("\t<Document>\n")
fp.write("\t\t<name>Directions from " + origin + " to " + destination + "</name>\n")
fp.write("\t\t\t<Placemark>\n")
fp.write("\t\t\t\t<styleUrl>#line-1267FF-5</styleUrl>\n")
fp.write("\t\t\t\t<name>Directions from " + origin + " to " + destination + "</name>\n")
fp.write("\t\t\t\t<ExtendedData>\n")
fp.write("\t\t\t\t</ExtendedData>\n")
fp.write("\t\t\t\t<LineString>\n")
fp.write("\t\t\t\t<tessellate>1</tessellate>\n")
fp.write("\t\t\t\t\t<coordinates>" + path_str + "</coordinates>\n")
fp.write("\t\t\t\t</LineString>\n")
fp.write("\t\t\t</Placemark>\n")
fp.write("\t\t\t<Placemark>\n")
fp.write("\t\t\t<styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write("\t\t\t\t<name>" + origin + "</name>\n")
fp.write("\t\t\t\t<ExtendedData>\n")
fp.write("\t\t\t\t</ExtendedData>\n")
fp.write("\t\t\t\t<description><![CDATA[Origin]]></description>\n")
fp.write("\t\t\t\t<Point>\n")
fp.write("\t\t\t\t\t<coordinates>" + path_origin + "</coordinates>\n")
fp.write("\t\t\t\t</Point>\n")
fp.write("\t\t\t</Placemark>\n")
fp.write("\t\t\t<Placemark>\n")
fp.write("\t\t\t\t<styleUrl>#icon-503-DB4436</styleUrl>\n")
fp.write("\t\t\t\t<name>" + destination + "</name>\n")
fp.write("\t\t\t\t<ExtendedData>\n")
fp.write("\t\t\t\t</ExtendedData>\n")
fp.write("\t\t\t\t<description><![CDATA[Destination]]></description>\n")
fp.write("\t\t\t\t<Point>\n")
fp.write("\t\t\t\t\t<coordinates>" + path_destination + "</coordinates>\n")
fp.write("\t\t\t\t</Point>\n")
fp.write("\t\t\t</Placemark>\n")
fp.write("\t\t\t<Style id='icon-503-DB4436'>\n")
fp.write("\t\t\t\t<IconStyle>\n")
fp.write("\t\t\t\t\t<color>ff3644DB</color>\n")
fp.write("\t\t\t\t\t<scale>1.1</scale>\n")
fp.write("\t\t\t\t\t<Icon>\n")
fp.write("\t\t\t\t\t\t<href>http://www.gstatic.com/mapspro/images/stock/503-wht-blank_maps.png</href>\n")
fp.write("\t\t\t\t\t</Icon>\n")
fp.write("\t\t\t\t</IconStyle>\n")
fp.write("\t\t\t</Style>\n")
fp.write("\t\t\t<Style id='line-1267FF-5'>\n")
fp.write("\t\t\t\t<LineStyle>\n")
fp.write("\t\t\t\t\t<color>ffFF6712</color>\n")
fp.write("\t\t\t\t\t<width>5</width>\n")
fp.write("\t\t\t\t</LineStyle>\n")
fp.write("\t\t\t</Style>\n")
fp.write("\t</Document>\n")
fp.write("</kml>\n")
fp.close()
# Get all the records in database that have not been processed yet
# sql2 = "UPDATE " + tableName + " SET processed = '1' WHERE id = " + id
# result2 = pg_query(con, sql2)
i += 1
def recordDistancesDirectionsWhen(i, j, origins, destinations, tempmode,
distance, paths, city, state, duration,
processed, date):
# Connect with database and check if connection have benn performed sucessfully
con = databaseConnection.connectdb()
cursor = con.cursor()
if con == False:
print("Database connection ERROR!")
exit()
# Create composed name of table
tableName = "distances_" + city + "_" + state + "_when"
# Create composed name of table to use in tt variable
tableName2 = "distances_" + city + "_" + state
# Create composed name of table where intermediate paths are
intermediateTableName = "intermediate_distances_" + city + "_" + state + "_when"
# Variable pointing out return value
varreturn = True
# Create variable to keep an string with full path from origin to destination
path_str = ""
# Get the number of parts into full path
numDirections = len(paths)
# Include each path block in path string
k = 0
while k < numDirections:
# Get the number of points in current path block
tmp = paths[k]
encoded_points = tmp[4]
# Create an string with all coded path points. Each block is separated by " "
path_str = path_str + " " + encoded_points
k += 1
# Remove white spaces in the begin and at the end of string
path_str = path_str.strip()
# Insert record into distance table
sql = "INSERT INTO " + tableName + " (idx_origin, idx_destination, origin, destination, distance_meters, mode, path, duration, processed, whentime) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
# Check if query have benn performed successfully
result = cursor.execute(
sql,
(str(i), str(j), str(origins), str(destinations), str(distance),
str(tempmode), str(path_str), str(duration), processed, str(date)))
con.commit()
if result == None:
print("Success in distances insertion!!")
varreturn = True
if distance != -1:
# Get the id of just include record
sql = "SELECT currval(pg_get_serial_sequence('" + tableName + "','id'));"
result = cursor.execute(sql)
row = cursor.fetchone()
row = str(row)
row = row.strip("[")
row = row.strip("(")
row = row.strip("]")
row = row.strip(")")
row = row.strip(",")
id_inserted_row = row
# Include each path block in database
w = 0
while w < numDirections:
# Get the number of points in current path block
temp = paths[w]
distance = temp[0]
duration = temp[1]
start_location = temp[2]
end_location = temp[3]
encoded_points = temp[4]
travel_mode = temp[5]
# Insert record into intermediate distance table
tt = 'id_' + tableName2
sql = "INSERT INTO " + intermediateTableName + " (" + tt + ", duration_seconds, start_point, end_point, mode, path, distance_meters, processed, whentime) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(
sql,
(str(id_inserted_row), str(duration), str(start_location),
str(end_location), str(travel_mode), str(encoded_points),
str(distance), processed, str(date)))
con.commit()
if result == None:
print("Success in intermediate distances insertion!!")
varreturn = True
else:
msg = "Error in intermediate distances insertion!! " + sql
print(msg)
varreturn = False
w += 1
if varreturn == True:
print("Success in intermediate distances insertion!!")
sql = "UPDATE " + tableName + " SET intermediate_paths_ok = '1' WHERE id = " + id_inserted_row
print(sql)
cursor.execute(sql)
con.commit()
else:
msg = "Error in distances insertion!! " + sql
print(msg)
varreturn = False
return varreturn
#recordDistancesDirectionsWhen(0, 1, '-23.190637250293204,-45.886701608658385', '-23.202251682962157,-45.85807214575006', 'driving', 4991, [[121, 37, '-23.1906501,-45.8868201', '-23.1895721,-45.8869473', 'plplCrgawG]BcBJi@Dk@B', 'DRIVING'], [467, 112, '-23.1895721,-45.8869473', '-23.1866982,-45.8836194', 'xeplClhawGs@y@_BkBIMKKY_@uEwFCEiAsAGGUSUYOQGIIU', 'DRIVING'], [374, 50, '-23.1866982,-45.8836194', '-23.186378,-45.8800484', 'zsolCrs`wGIYMeAIm@C[K{@K}@Ea@Is@OkBAQC]AM?I?I@ELaANy@DO', 'DRIVING'], [246, 21, '-23.186378,-45.8800484', '-23.1882702,-45.8790132', 'zqolCh}_wG?EDU@C@EFQBEBCDEDCFA@AdAYTGd@MB?v@WB?VIh@ORG`@KNI', 'DRIVING'], [651, 137, '-23.1882702,-45.8790132', '-23.1929285,-45.8752322', 't}olCxv_wG^QTKVOTMf@WZOXOLG~As@NGrB{ALIXSFIDGx@}@x@{@DCDEp@m@`@a@\\YPOPODCb@g@z@w@TWTQPS', 'DRIVING'], [457, 58, '-23.1929285,-45.8752322', '-23.1910376,-45.8712648', 'xzplCd__wGa@gAKY_@y@_@{@MYKYc@oAi@}AISs@wBM[k@{Ak@{A', 'DRIVING'], [387, 61, '-23.1910376,-45.8712648', '-23.1935057,-45.8686082', '~nplCjf~vGdBgBz@aAj@o@j@o@FEz@q@RWTWX]@AZ[b@m@NWHOFGFE', 'DRIVING'], [61, 5, '-23.1935057,-45.8686082', '-23.1939899,-45.8683317', 'l~plCxu}vGZQ@?JGLGHGBARIBA@?', 'DRIVING'], [883, 62, '-23.1939899,-45.8683317', '-23.2005533,-45.8637032', 'laqlC`t}vG\\WXUVWFGLOZc@`@e@rCmDvA{Ah@e@t@i@f@[l@Yv@]tAe@dAa@nCcA`FeBVIn@Wl@]\\U', 'DRIVING'], [622, 56, '-23.2005533,-45.8637032', '-23.205366,-45.8610754', 'ljrlCbw|vGNSLQPQ`AaAb@c@fBoBj@m@l@e@t@c@n@YRGRGTEZIb@Ep@Kb@Gz@Mj@Ih@Gt@G`@CV?VCH?LA', 'DRIVING'], [458, 85, '-23.205366,-45.8610754', '-23.2028588,-45.8583237', 'phslCvf|vGHCFAHCLGHIBC@EBGBGAG?A?AAEACCCCCACECCACAE?AGACCGGEWU]Yq@q@UQqAeAeAu@c@Yq@i@s@i@q@g@q@g@', 'DRIVING'], [142, 23, '-23.2028588,-45.8583237', '-23.202211,-45.8571439', 'zxrlCnu{vGKIIEUm@uAmD', 'DRIVING'], [55, 14, '-23.202211,-45.8571439', '-23.2018674,-45.8575268', 'xtrlCbn{vGa@f@a@d@', 'DRIVING'], [67, 14, '-23.2018674,-45.8575268', '-23.2023558,-45.8579151', 'trrlCpp{vGrA`ALJ', 'DRIVING']], 'sjc', 'sp', 764, 0, '20-03-2018 15:00:00')
def roadsFailedMappingService(inputFile, city, state):
# Connect with database and check if connection have been performed successfully
con = databaseConnection.connectdb()
if not con:
print('Database connection ERROR!')
exit()
cursor = con.cursor()
# list that contains the google's api key (you can change it)
apikey = [
'AIzaSyAr11wo7Q90rmf7ZReZFVz3OV50g5iW1hI',
'AIzaSyB-kB6JZ-UkSFVGXX0eEIavyMGg-w2whnk',
'AIzaSyClzkVxwXbjphXktb1LZ_MJCprlJMEDvqw',
'AIzaSyDl3vAN7saDAubpUh2f4xb1wIWOhG_lxVc',
'AIzaSyBSFnV2oNW-fR8yiMBzxZkTFKpyOLRM4-8',
'AIzaSyBg9nMa0mAAnZyoyCsCO7l7mDS3fciI32c',
'AIzaSyDZke-btxWiEtGsNisNBicP5zsJhiG4XS0',
'AIzaSyD79s5kzm_4WJGJKnp6sv2lAADpGeGZEZ4',
'AIzaSyDd1KnOEb6u79sJgT4cVV1JrSOcKkvFtBI',
'AIzaSyDwsqAZ-8wB_h23XhKAVshX0MZqa_ty4GI',
'AIzaSyAz1ZrldhSNHUdXjvT9MTDUeiub4MeUND8',
'AIzaSyBQYl-sChyjHoc186BYpKrId6-9nf2Kf9A',
'AIzaSyDAmvH9hkaw5NwDHt1_3VPuNX6KFqtcZTo',
'AIzaSyBUguv05oZBYjCZ7OJtxYlUUyzHoyOqlPU',
'AIzaSyACauD8ibd4RDDCb_1C57xm2iJG4Wmj3c8',
'AIzaSyCIdkrYIOqYX3cogj7LnwAi7Kogk2pDcQc',
'AIzaSyB4mwWf6m898vPQYd0lGG4-5NDQt0myS4o',
'AIzaSyDBRAo1aS3ihZbj9SnwWD07kA7q1B9XpLA',
'AIzaSyAYoIztpjHUTGGuEKD25_1fnwsIdNEm3FA',
'AIzaSyAC8xAfiMw66cZTNqrp0WA8Y9h2hxGNAHg'
]
# variable that control the use of the keys (you can change it)
#The apiKey list starts with the keyUse value
keyUse = 0
if inputFile == "zonas-trafego-sjc-sp":
# Read from a XLS a matrix where each line is a pair of coords lat/long
regions = importGeographicalPointsFromXLS.importGeographicalPointsFromXLS(
inputFile)
elif inputFile == "RJ":
# Read from a XLS a matrix where each line is a pair of coords lat/long
regions = importGeographicalPointsFromTXT.importGeographicalPointsFromTXT(
inputFile)
# Variable that contains the sheet name of the matrix (you can change it)
sheetName = "DrivingDistance"
#Variable that contains the model of travel, it is used by the API of Google Maps (you can change it)
tempmode = "driving"
#Get a list that contains the tuples with de origin and destination pair of the failed mappings
failedMappingList = getFailedMappings.getFailedMappings(
city, state, sheetName)
# Indetify the quantity of tuples in the list, it's necessary to verify the number de interaction over the loop
listLen = len(failedMappingList)
i = 0
j = 0
cc = 1
while i < listLen:
# Setup initial index of origin and destination using the first tuple that inside the failedMappingList
idx_origin = failedMappingList[i][0]
idx_destination = failedMappingList[i][1]
temp = regions[idx_origin]
origins = str(temp[1]) + "," + str(temp[0])
temp = regions[idx_destination]
destinations = str(temp[1]) + ',' + str(temp[0])
print(origins, " - ", destinations)
print(apikey[keyUse])
try:
# Get the distance for forward path
[
distance, duration, paths
] = getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(
origins, destinations, tempmode.upper(), None, apikey[keyUse])
#print(idx_origin, " - ", idx_destination)
# Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(
idx_origin, idx_destination, origins, destinations, tempmode,
distance, paths, city, state, duration, 0)
except:
#Record distance and path into database
output = recordDistancesDirections.recordDistancesDirections(
idx_origin, idx_destination, origins, destinations, tempmode,
'-1', [], city, state, '-1', 1)
#The daily quota of the API is 2500, but to avoid failures, only 1900 requests per key
if cc < 1900:
cc += 1
else:
#You can choose this number
if keyUse == 13:
cc == cc
break
else:
keyUse += 1
cc = 1
i += 1
print(listLen)
print(cc)
def roadsMappingServiceWhen(pp, inputFile, city, state, ori, dest, date1,
date2):
# Connect with database and check if connection have been performed successfully
con = databaseConnection.connectdb()
if not con:
print("Database connection ERROR!")
exit()
cursor = con.cursor()
#list that contains the google's api key
apikey = [
'AIzaSyAr11wo7Q90rmf7ZReZFVz3OV50g5iW1hI',
'AIzaSyB-kB6JZ-UkSFVGXX0eEIavyMGg-w2whnk',
'AIzaSyClzkVxwXbjphXktb1LZ_MJCprlJMEDvqw',
'AIzaSyDl3vAN7saDAubpUh2f4xb1wIWOhG_lxVc',
'AIzaSyBSFnV2oNW-fR8yiMBzxZkTFKpyOLRM4-8',
'AIzaSyBg9nMa0mAAnZyoyCsCO7l7mDS3fciI32c',
'AIzaSyDZke-btxWiEtGsNisNBicP5zsJhiG4XS0',
'AIzaSyD79s5kzm_4WJGJKnp6sv2lAADpGeGZEZ4',
'AIzaSyDd1KnOEb6u79sJgT4cVV1JrSOcKkvFtBI',
'AIzaSyDwsqAZ-8wB_h23XhKAVshX0MZqa_ty4GI',
'AIzaSyAz1ZrldhSNHUdXjvT9MTDUeiub4MeUND8',
'AIzaSyBQYl-sChyjHoc186BYpKrId6-9nf2Kf9A',
'AIzaSyDAmvH9hkaw5NwDHt1_3VPuNX6KFqtcZTo',
'AIzaSyBUguv05oZBYjCZ7OJtxYlUUyzHoyOqlPU',
'AIzaSyACauD8ibd4RDDCb_1C57xm2iJG4Wmj3c8',
'AIzaSyCIdkrYIOqYX3cogj7LnwAi7Kogk2pDcQc',
'AIzaSyB4mwWf6m898vPQYd0lGG4-5NDQt0myS4o',
'AIzaSyDBRAo1aS3ihZbj9SnwWD07kA7q1B9XpLA',
'AIzaSyAYoIztpjHUTGGuEKD25_1fnwsIdNEm3FA',
'AIzaSyAC8xAfiMw66cZTNqrp0WA8Y9h2hxGNAHg'
]
#variable that control the use of the keys
keyUse = 0
#Variable that count the days
countDay = 0
# Read from a XLS a matrix where each line is a pair of coords lat/long
regions = importGeographicalPointsFromXLS.importGeographicalPointsFromXLS(
inputFile)
# Get the total of regions into region array
nr = len(regions)
# Create composed name of table
tableName = "distances_" + city + "_" + state + "_" + "when"
# Get last record in database
sql = "SELECT * FROM " + tableName + " ORDER BY id DESC"
cursor.execute(sql)
row = cursor.fetchone()
if row != None:
row = list(row)
# Setup initial index of origin and destination (in regions matrix) as the last index in database
if row == None:
idx_origin = 0
idx_destination = 1
# Split the datetime (date and time) to get month and year
idx_datetime = date1
firstDay = True
idx_datetime_split = date1.split()
idx_date = idx_datetime_split[0]
idx_month = idx_date.split('-')[1]
idx_year = idx_date.split('-')[2]
# As the row is none the time starts with 0 to go until 23
time = 0
changeOriginDestination = True
else:
idx_origin = int(row[1])
idx_destination = int(row[2])
idx_datetime_split = row[11].split()
idx_date = idx_datetime_split[0]
idx_time = idx_datetime_split[1]
#print(idx_time)
idx_day = int(idx_date.split('-')[0].lstrip('0'))
idx_month = idx_date.split('-')[1]
idx_year = idx_date.split('-')[2]
#print(int(idx_time[0:2].split(':')[0].lstrip('0')))
if not (idx_time[0:2].split(':')[0].lstrip('0') == ''):
time = int(idx_time[0:2].split(':')[0].lstrip('0'))
else:
time = 0
if idx_day == int(date1[0:2].lstrip('0')):
firstDay = True
else:
firstDay = False
# verify where stopped in the last execution, because the code include i,j and j,i for each
# loop. Since then, if i=1 and j=2, the last include data is i=2 and j=1 when finalize the loop.
if idx_origin > idx_destination:
aux = idx_origin
idx_origin = idx_destination
idx_destination = aux
time += 1
changeOriginDestination = True
else:
#aux = idx_origin
#idx_origin = idx_destination
#idx_destination = aux
changeOriginDestination = False
print(idx_origin)
print(idx_destination)
if idx_day == 29:
countDay = 1
if (idx_day == 26 and time == 24) and changeOriginDestination:
firstDay = False
print("ENTROU")
time = 0
#countDay = 1
if (idx_day == 29 and time == 24) and changeOriginDestination:
idx_destination += 1
time = 0
firstDay = True
# Create pairs (origin and destination) for all the regions into regions array. For each point we have lat, long coordinates (respectively)
cc = 1
i = idx_origin
while i < nr - 1:
temp = regions[i]
origins = str(temp[1]) + "," + str(temp[0])
# Check if J index should start from I + 1 or using database recovered index
if i == idx_origin:
j = idx_destination
else:
j = i + 1
while j < nr:
temp = regions[j]
destinations = str(temp[1]) + ',' + str(temp[0])
while countDay < 2:
while time < 24:
if firstDay:
day = int(date1[0:2].lstrip('0'))
else:
day = int(date2[0:2].lstrip('0'))
if day < 10:
day = "0" + str(day)
if time < 10:
hour = "0" + str(time)
else:
hour = str(time)
print(apikey[keyUse])
full_date = str(day) + "-" + str(idx_month) + "-" + str(
idx_year) + " " + hour + ":00:00"
''' Get the path for driving mode forward and backward '''
# Mode of travel
tempmode = "driving"
if (changeOriginDestination == False):
# B to A
try:
# Get the distance for backward path
[
distance, duration, paths
] = getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(
destinations, origins, tempmode, full_date,
apikey[keyUse])
# Record distance and path into database
output = recordDistancesDirectionsWhen.recordDistancesDirectionsWhen(
j, i, destinations, origins, tempmode,
distance, paths, city, state, duration, 0,
full_date)
except:
# Record distance and path into database
output = recordDistancesDirectionsWhen.recordDistancesDirectionsWhen(
j, i, destinations, origins, tempmode, '-1',
[], city, state, '-1', 1, '')
changeOriginDestination = True
else:
#A to B
try:
# Get the distance for forward path
[
distance, duration, paths
] = getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(
origins, destinations, tempmode, full_date,
apikey[keyUse])
# Record distance and path into database
output = recordDistancesDirectionsWhen.recordDistancesDirectionsWhen(
i, j, origins, destinations, tempmode,
distance, paths, city, state, duration, 0,
full_date)
except:
# Record distance and path into database
output = recordDistancesDirectionsWhen.recordDistancesDirectionsWhen(
i, j, origins, destinations, tempmode, '-1',
[], city, state, '-1', 1, '')
#B to A
try:
# Get the distance for backward path
[
distance, duration, paths
] = getDirectionTwoGeographicalPoints.getDirectionTwoGeographicalPoints(
destinations, origins, tempmode, full_date,
apikey[keyUse])
# Record distance and path into database
output = recordDistancesDirectionsWhen.recordDistancesDirectionsWhen(
j, i, destinations, origins, tempmode,
distance, paths, city, state, duration, 0,
full_date)
except:
# Record distance and path into database
output = recordDistancesDirectionsWhen.recordDistancesDirectionsWhen(
j, i, destinations, origins, tempmode, '-1',
[], city, state, '-1', 1, '')
print(cc)
if cc < pp:
cc += 1
else:
if keyUse == 19:
cc = cc
break
else:
keyUse += 1
cc = 1
time += 1
if time == 24:
if firstDay:
firstDay = False
else:
firstDay = True
countDay += 1
time = 0
if cc >= pp:
break
countDay = 0
time = 0
if cc >= pp:
break
j += 1
if cc >= pp:
break
i += 1