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