def readAndInsertTurnCounts(self, file, street1, street2, user, logger, tzinfo=None): """ Parses the given excel file representing turn counts and inserts them into the countdracula database. * *file* is the Excel workbook file * *street1* is the name of the NS-oriented street * *street2* is the name of the EW-oriented street * *user* is the django User to associate with the count * *logger* is a logging instance On success, returns number of successful counts inserted. On failure, removes all counts from this workbook so it can be fixed and inserted again, and returns -1. Note that this method is a little counter-intuitive because the arguments determine the way the movements are stored in the database. For example, suppose you have an intersection where the street changes names from "SouthOne Street" to "SouthTwo Street" as it passes through an intersection with "EastWest Street". This method would be called with street1="SouthOne Street" and street2="EastWest Street" and the through movement would be stored with from_street="SouthOne Street" fromdir="SB", tostreet="SouthOne Street" todir="SB" even though "SouthOne Street" doesn't have a southbound link from the intersection, since it's really "SouthTwo Street". Thus, using this implementation, the fromstreet and tostreet define the intersection only, and not the movement. """ turn_counts = TurnCount.objects.filter(sourcefile=file) if len(turn_counts) > 0: logger.error(" readAndInsertTurnCounts() called on %s, but %d turn counts already "\ "exist with that sourcefile. Skipping." % (file, len(turn_counts))) return -1 try: NSstreetslist = StreetName.getPossibleStreetNames(street1) if len(NSstreetslist) == 0: raise CountsWorkbookParserException( "readTurnCounts: Street %s not found." % street1) EWstreetslist = StreetName.getPossibleStreetNames(street2) if len(EWstreetslist) == 0: raise CountsWorkbookParserException( "readTurnCounts: Street %s not found." % street2) # look for intersections of these streets; intersections maps # { (streetname1 StreetName instance, streetname2 StreetName instance) -> QuerySet of intersections } intersections = {} for NSstreet in NSstreetslist: for EWstreet in EWstreetslist: intersection_ids = Node.objects.filter(street_to_node__street_name=NSstreet) \ .filter(street_to_node__street_name=EWstreet) if len(intersection_ids) > 0: intersections[(NSstreet, EWstreet)] = intersection_ids # ideally, we'll have one intersection with one intersection id if len(intersections) == 0: raise CountsWorkbookParserException( "readTurnCounts: No intersections found for %s and %s" % (street1, street2)) if len(intersections) > 1: raise CountsWorkbookParserException( "readTurnCounts: Multiple intersections found for %s and %s: %s" % (street1, street2, str(intersections))) if len(intersections.values()[0]) > 1: raise CountsWorkbookParserException( "readTurnCounts: Multiple intersections found for %s and %s: %s" % (street1, street2, str(intersections))) # len(intersections) == 1 final_NSstreet = intersections.keys()[0][0] final_EWstreet = intersections.keys()[0][1] final_intid = intersections[(final_NSstreet, final_EWstreet)][0] logger.info("intersection id = %d" % final_intid.id) # logger.debug("final_NSstreet=[%s], final_EWstreet=[%s]" % (final_NSstreet, final_EWstreet)) # go through the sheets and read the data book = xlrd.open_workbook(file) sheetnames = book.sheet_names() counts_saved = 0 # read any other link info from geo worksheet (adtl_inlinks, adtl_outlinks) = self.readGeo(book) # add the StreetName objs to the adtl_links for street in StreetName.objects.filter(nodes=final_intid): for designation, list in adtl_inlinks.iteritems(): if list[0].upper() == street.street_name: adtl_inlinks[designation].append(street) for designation, list in adtl_outlinks.iteritems(): if list[0].upper() == street.street_name: adtl_outlinks[designation].append(street) logger.info("adtl_inlinks = %s" % str(adtl_inlinks)) logger.info("adtl_outlinks = %s" % str(adtl_outlinks)) valid_fromdirs = ["NB", "WB", "EB", "SB"] for indesig in adtl_inlinks.keys(): if len(adtl_inlinks[indesig]) == 3: valid_fromdirs.append(indesig + "_") for sheet_idx in range(len(sheetnames)): if sheetnames[sheet_idx].lower() in ["source", "geo"]: continue activesheet = book.sheet_by_name(sheetnames[sheet_idx]) # create date from sheetname in date format tmp_date = sheetnames[sheet_idx].split('.') date_yyyy_mm_dd = date(int(tmp_date[0]), int(tmp_date[1]), int(tmp_date[2])) project = "" sections = self.findSectionStarts(activesheet, logger) logger.debug("sheet %s sections = %s" % (sheetnames[sheet_idx], str(sections))) # loop through the different sections in the workbook for section in sections: # project section? if activesheet.cell_value(section[0], 0).upper() == "PROJECT": project = activesheet.cell_value(section[0] + 1, 0) continue # figure out the vehicle type and row with the column labels assert (activesheet.cell_value(section[0] + 1, 0).upper() == "TURNS") label_row = section[0] + 1 # figure out the vehicle type code vehicle = activesheet.cell_value(section[0], 0) if type(vehicle) in [types.FloatType, types.IntType ] and vehicle in range(16): vtype = vehicle elif type(vehicle) in [ types.StringType, types.UnicodeType ]: vtype = self.vehicleTypeForString(vehicle) else: vtype = 0 #TODO: fix logger.info(" Worksheet %20s Vehicle=%2d %s" % (sheetnames[sheet_idx], vtype, vehicle)) if vtype == 1: logger.info( "Pedestrian crossings are not handled yet -- skipping" ) continue # iterate through the columns for column in range( 1, self.numNonBlankColumns(activesheet, label_row)): # Read the label header movement = activesheet.cell_value(label_row, column) t_fromdir = movement[:2] turntype = movement[2:] if t_fromdir not in valid_fromdirs: raise CountsWorkbookParserException( "readTurnCounts: Could not parse column header of %s!%s; expect movement to start with direction. Movement=[%s] Column=%d Type=%d" % (file, sheetnames[sheet_idx], movement, column, activesheet.cell_type(label_row, column))) # todo: special inbound link if t_fromdir in ["NB", "SB"]: t_fromstreet = final_NSstreet elif t_fromdir in ["EB", "WB"]: t_fromstreet = final_EWstreet elif t_fromdir[1] == "_": t_fromstreet = adtl_inlinks[t_fromdir[0]][2] t_fromdir = adtl_inlinks[t_fromdir[0]][1] # determine direction of outbound link # special case if turntype[-2:-1] == "_" and turntype[ -1:] in adtl_outlinks.keys(): t_todir = adtl_outlinks[turntype[-1:]][1] t_tostreet = adtl_outlinks[turntype[-1:]][2] # regular case else: compass = ['N', 'E', 'S', 'W'] if turntype == "TH": # through t_todir = t_fromdir elif turntype in [' U-Turn', 'UT', 'U-Turn']: t_todir = compass[compass.index(t_fromdir[0]) - 2] + 'B' elif turntype == 'RT': # right turn t_todir = compass[compass.index(t_fromdir[0]) - 3] + 'B' elif turntype == 'LT': # left turn t_todir = compass[compass.index(t_fromdir[0]) - 1] + 'B' elif turntype == 'PD': # through - pedestrian? t_todir = t_fromdir vtype = 1 else: raise CountsWorkbookParserException( "readTurnCounts: Could not parse column header of %s; turntype [%s] not understood." % (file, turntype)) # Determine Street names and order if turntype in [ 'TH', ' U-Turn', 'U-Turn', 'UT', 'PD' ]: if t_fromdir in ["NB", "SB"]: t_tostreet = final_NSstreet t_intstreet = final_EWstreet else: t_tostreet = final_EWstreet t_intstreet = final_NSstreet else: #turning movement and to and from streets are different if t_fromdir in ["NB", "SB"]: t_tostreet = final_EWstreet t_intstreet = final_EWstreet else: #TODO added maybe by mistake !!! (check it) t_tostreet = final_NSstreet t_intstreet = final_NSstreet # logger.debug("movement [%s] from %s %s to %s %s" % (movement, t_fromstreet, t_fromdir, t_tostreet, t_todir)) # look for the turn count location in countdracula try: turn_count_location = TurnCountLocation.objects.get( from_street=t_fromstreet, from_dir=t_fromdir, to_street=t_tostreet, to_dir=t_todir, intersection=final_intid) except ObjectDoesNotExist: turn_count_location = TurnCountLocation( from_street=t_fromstreet, from_dir=t_fromdir, to_street=t_tostreet, to_dir=t_todir, intersection_street=t_intstreet, intersection=final_intid) turn_count_location.save() for row in range(section[0] + 2, section[1] + 1): count = activesheet.cell_value(row, column) if count == "": continue (starttime, period) = self.createtimestamp( activesheet.cell_value(row, 0), tzinfo=tzinfo) turn_count = TurnCount( location=turn_count_location, count=count, count_date=date_yyyy_mm_dd, start_time=starttime, period_minutes=period, vehicle_type=vtype, sourcefile=file, project=project, upload_user=user) turn_count.clean() turn_count.save() counts_saved += 1 logger.info(" Processed %s into countdracula" % file) logger.info(" Successfully saved %4d turn counts" % counts_saved) return counts_saved except Exception as e: logger.error(" Failed to process %s" % file) logger.error(" " + str(e)) logger.error(" " + traceback.format_exc()) # remove the rest of the counts for this sourcefile so it can be retried turn_counts = TurnCount.objects.filter(sourcefile=file) if len(turn_counts) > 0: logger.debug( " Removing %d counts from countdracula so sourcefile %s can be reprocessed" % (len(turn_counts), file)) turn_counts.delete() else: logger.debug( " No counts to remove for sourcefile %s; sourcefile can be reprocessed" % file) return -1
def readAndInsertTurnCounts(self, file, street1, street2, user, logger, tzinfo=None): """ Parses the given excel file representing turn counts and inserts them into the countdracula database. * *file* is the Excel workbook file * *street1* is the name of the NS-oriented street * *street2* is the name of the EW-oriented street * *user* is the django User to associate with the count * *logger* is a logging instance On success, returns number of successful counts inserted. On failure, removes all counts from this workbook so it can be fixed and inserted again, and returns -1. Note that this method is a little counter-intuitive because the arguments determine the way the movements are stored in the database. For example, suppose you have an intersection where the street changes names from "SouthOne Street" to "SouthTwo Street" as it passes through an intersection with "EastWest Street". This method would be called with street1="SouthOne Street" and street2="EastWest Street" and the through movement would be stored with from_street="SouthOne Street" fromdir="SB", tostreet="SouthOne Street" todir="SB" even though "SouthOne Street" doesn't have a southbound link from the intersection, since it's really "SouthTwo Street". Thus, using this implementation, the fromstreet and tostreet define the intersection only, and not the movement. """ turn_counts = TurnCount.objects.filter(sourcefile=file) if len(turn_counts) > 0: logger.error(" readAndInsertTurnCounts() called on %s, but %d turn counts already "\ "exist with that sourcefile. Skipping." % (file, len(turn_counts))) return -1 try: NSstreetslist = StreetName.getPossibleStreetNames(street1) if len(NSstreetslist) == 0: raise CountsWorkbookParserException("readTurnCounts: Street %s not found." % street1) EWstreetslist = StreetName.getPossibleStreetNames(street2) if len(EWstreetslist) == 0: raise CountsWorkbookParserException("readTurnCounts: Street %s not found." % street2) # look for intersections of these streets; intersections maps # { (streetname1 StreetName instance, streetname2 StreetName instance) -> QuerySet of intersections } intersections = {} for NSstreet in NSstreetslist: for EWstreet in EWstreetslist: intersection_ids = Node.objects.filter(street_to_node__street_name=NSstreet) \ .filter(street_to_node__street_name=EWstreet) if len(intersection_ids) > 0: intersections[(NSstreet, EWstreet)] = intersection_ids # ideally, we'll have one intersection with one intersection id if len(intersections) == 0: raise CountsWorkbookParserException("readTurnCounts: No intersections found for %s and %s" % (street1, street2)) if len(intersections) > 1: raise CountsWorkbookParserException("readTurnCounts: Multiple intersections found for %s and %s: %s" % (street1, street2, str(intersections))) if len(intersections.values()[0]) > 1: raise CountsWorkbookParserException("readTurnCounts: Multiple intersections found for %s and %s: %s" % (street1, street2, str(intersections))) # len(intersections) == 1 final_NSstreet = intersections.keys()[0][0] final_EWstreet = intersections.keys()[0][1] final_intid = intersections[(final_NSstreet,final_EWstreet)][0] logger.info("intersection id = %d" % final_intid.id) # logger.debug("final_NSstreet=[%s], final_EWstreet=[%s]" % (final_NSstreet, final_EWstreet)) # go through the sheets and read the data book = xlrd.open_workbook(file) sheetnames = book.sheet_names() counts_saved = 0 # read any other link info from geo worksheet (adtl_inlinks, adtl_outlinks) = self.readGeo(book) # add the StreetName objs to the adtl_links for street in StreetName.objects.filter(nodes=final_intid): for designation,list in adtl_inlinks.iteritems(): if list[0].upper() == street.street_name: adtl_inlinks[designation].append(street) for designation,list in adtl_outlinks.iteritems(): if list[0].upper() == street.street_name: adtl_outlinks[designation].append(street) logger.info("adtl_inlinks = %s" % str(adtl_inlinks)) logger.info("adtl_outlinks = %s" % str(adtl_outlinks)) valid_fromdirs = ["NB", "WB", "EB", "SB"] for indesig in adtl_inlinks.keys(): if len(adtl_inlinks[indesig]) == 3: valid_fromdirs.append(indesig+"_") for sheet_idx in range(len(sheetnames)) : if sheetnames[sheet_idx].lower() in ["source","geo"]: continue activesheet = book.sheet_by_name(sheetnames[sheet_idx]) # create date from sheetname in date format tmp_date = sheetnames[sheet_idx].split('.') date_yyyy_mm_dd = date(int(tmp_date[0]),int(tmp_date[1]),int(tmp_date[2]) ) project = "" sections = self.findSectionStarts(activesheet, logger) logger.debug("sheet %s sections = %s" % (sheetnames[sheet_idx], str(sections))) # loop through the different sections in the workbook for section in sections: # project section? if activesheet.cell_value(section[0],0).upper() == "PROJECT": project = activesheet.cell_value(section[0]+1,0) continue # figure out the vehicle type and row with the column labels assert(activesheet.cell_value(section[0]+1,0).upper() == "TURNS") label_row = section[0]+1 # figure out the vehicle type code vehicle = activesheet.cell_value(section[0], 0) if type(vehicle) in [types.FloatType, types.IntType] and vehicle in range(16): vtype = vehicle elif type(vehicle) in [types.StringType, types.UnicodeType]: vtype = self.vehicleTypeForString(vehicle) else: vtype = 0 #TODO: fix logger.info(" Worksheet %20s Vehicle=%2d %s" % (sheetnames[sheet_idx], vtype, vehicle)) if vtype==1: logger.info("Pedestrian crossings are not handled yet -- skipping") continue # iterate through the columns for column in range(1,self.numNonBlankColumns(activesheet, label_row)): # Read the label header movement = activesheet.cell_value(label_row,column) t_fromdir = movement[:2] turntype = movement[2:] if t_fromdir not in valid_fromdirs: raise CountsWorkbookParserException("readTurnCounts: Could not parse column header of %s!%s; expect movement to start with direction. Movement=[%s] Column=%d Type=%d" % (file, sheetnames[sheet_idx], movement, column, activesheet.cell_type(label_row,column))) # todo: special inbound link if t_fromdir in ["NB","SB"]: t_fromstreet = final_NSstreet elif t_fromdir in ["EB", "WB"]: t_fromstreet = final_EWstreet elif t_fromdir[1] == "_": t_fromstreet = adtl_inlinks[t_fromdir[0]][2] t_fromdir = adtl_inlinks[t_fromdir[0]][1] # determine direction of outbound link # special case if turntype[-2:-1]=="_" and turntype[-1:] in adtl_outlinks.keys(): t_todir = adtl_outlinks[turntype[-1:]][1] t_tostreet = adtl_outlinks[turntype[-1:]][2] # regular case else: compass = ['N','E','S','W'] if turntype == "TH": # through t_todir = t_fromdir elif turntype in [' U-Turn','UT','U-Turn']: t_todir = compass[compass.index(t_fromdir[0])-2] + 'B' elif turntype == 'RT': # right turn t_todir = compass[compass.index(t_fromdir[0])-3] + 'B' elif turntype == 'LT': # left turn t_todir = compass[compass.index(t_fromdir[0])-1] + 'B' elif turntype == 'PD': # through - pedestrian? t_todir = t_fromdir vtype = 1 else: raise CountsWorkbookParserException("readTurnCounts: Could not parse column header of %s; turntype [%s] not understood." % (file, turntype)) # Determine Street names and order if turntype in ['TH',' U-Turn','U-Turn','UT','PD']: if t_fromdir in ["NB","SB"]: t_tostreet = final_NSstreet t_intstreet = final_EWstreet else: t_tostreet = final_EWstreet t_intstreet = final_NSstreet else: #turning movement and to and from streets are different if t_fromdir in ["NB","SB"]: t_tostreet = final_EWstreet t_intstreet = final_EWstreet else: #TODO added maybe by mistake !!! (check it) t_tostreet = final_NSstreet t_intstreet = final_NSstreet # logger.debug("movement [%s] from %s %s to %s %s" % (movement, t_fromstreet, t_fromdir, t_tostreet, t_todir)) # look for the turn count location in countdracula try: turn_count_location = TurnCountLocation.objects.get(from_street = t_fromstreet, from_dir = t_fromdir, to_street = t_tostreet, to_dir = t_todir, intersection = final_intid) except ObjectDoesNotExist: turn_count_location = TurnCountLocation(from_street = t_fromstreet, from_dir = t_fromdir, to_street = t_tostreet, to_dir = t_todir, intersection_street = t_intstreet, intersection = final_intid) turn_count_location.save() for row in range(section[0]+2, section[1]+1): count = activesheet.cell_value(row,column) if count == "" : continue (starttime, period) = self.createtimestamp(activesheet.cell_value(row,0), tzinfo=tzinfo) turn_count = TurnCount(location = turn_count_location, count = count, count_date = date_yyyy_mm_dd, start_time = starttime, period_minutes = period, vehicle_type = vtype, sourcefile = file, project = project, upload_user = user) turn_count.clean() turn_count.save() counts_saved += 1 logger.info(" Processed %s into countdracula" % file) logger.info(" Successfully saved %4d turn counts" % counts_saved) return counts_saved except Exception as e: logger.error(" Failed to process %s" % file) logger.error(" "+str(e)) logger.error(" "+traceback.format_exc()) # remove the rest of the counts for this sourcefile so it can be retried turn_counts = TurnCount.objects.filter(sourcefile=file) if len(turn_counts) > 0: logger.debug(" Removing %d counts from countdracula so sourcefile %s can be reprocessed" % (len(turn_counts), file)) turn_counts.delete() else: logger.debug(" No counts to remove for sourcefile %s; sourcefile can be reprocessed" % file) return -1
def readAndInsertMainlineCounts(self, file, primary_street, cross_street1, cross_street2, user, logger, tzinfo=None): """ Parses the given excel file representing mainline counts and inserts those counts into the countdracula database. * *file* is the Excel workbook file name * *primary_street* is the street on which the counts were taken * *cross_street1* and *cross_street2* are the bounding cross streets * *user* is the django User to associate with the count * *logger* is a logging instance On success, returns number of successful counts inserted. On failure, removes all counts from this workbook so it can be fixed and inserted again, and returns -1. """ # logger.info("primary_street=[%s], cross_street1=[%s] cross_street2=[%s]" % (primary_street, cross_street1, cross_street2)) mainline_counts = MainlineCount.objects.filter(sourcefile=file) if len(mainline_counts) > 0: logger.error(" readAndInsertMainlineCounts() called on %s, but %d mainline counts already "\ "exist with that sourcefile. Skipping." % (file, len(mainline_counts))) return -1 try: primary_street_list = StreetName.getPossibleStreetNames( primary_street) if len(primary_street_list) == 0: raise CountsWorkbookParserException( "readMainlineCounts: primary street %s not found." % primary_street) cross_street1_list = StreetName.getPossibleStreetNames( cross_street1) if len(cross_street1_list) == 0: raise CountsWorkbookParserException( "readMainlineCounts: cross street 1 %s not found." % cross_street1) cross_street2_list = StreetName.getPossibleStreetNames( cross_street2) if len(cross_street2_list) == 0: raise CountsWorkbookParserException( "readMainlineCounts: cross street 2 %s not found." % cross_street2) # looking for a primary street that intersects with both one of cross_street1 cross_Street2 # collect this info in two dictionaries: # { primary_street_name (StreetName instance) -> { cross_street1_name (StreetName instance) -> QuerySet of Node instances }} # { primary_street_name (StreetName instance) -> { cross_street2_name (StreetName instance) -> QuerySet of Node instances }} intersections1 = {} intersections2 = {} for primary_street_name in primary_street_list: intersections1[primary_street_name] = {} intersections2[primary_street_name] = {} for cross_street1_name in cross_street1_list: intersections1[primary_street_name][cross_street1_name] = Node.objects.filter(street_to_node__street_name=primary_street_name) \ .filter(street_to_node__street_name=cross_street1_name) # don't bother if it's an empty set if len(intersections1[primary_street_name] [cross_street1_name]) == 0: del intersections1[primary_street_name][ cross_street1_name] for cross_street2_name in cross_street2_list: intersections2[primary_street_name][cross_street2_name] = Node.objects.filter(street_to_node__street_name=primary_street_name) \ .filter(street_to_node__street_name=cross_street2_name) # don't bother if it's an empty set if len(intersections2[primary_street_name] [cross_street2_name]) == 0: del intersections2[primary_street_name][ cross_street2_name] # ideally, there will be exactly one primary street with a cross street 1 candidate and a cross street 2 candidate primary_street_name_final = None for primary_street_name in primary_street_list: if len(intersections1[primary_street_name]) == 0: continue if len(intersections2[primary_street_name]) == 0: continue if len(intersections1[primary_street_name]) > 1: raise CountsWorkbookParserException( "readMainlineCounts: Street %s and cross street 1 %s have multiple intersections: %s" % (primary_street_name, cross_street1, str(intersections1[primary_street_name]))) if len(intersections2[primary_street_name]) > 1: raise CountsWorkbookParserException( "readMainlineCounts: Street %s and cross street 2 %s have multiple intersections: %s" % (primary_street_name, cross_street2, str(intersections2[primary_street_name]))) # already found one? if primary_street_name_final: raise CountsWorkbookParserException( "readMainlineCounts: Multiple primary streets (%s,%s) intersect with %s/%s" % (primary_street_name, primary_street_name_final, cross_street1, cross_street2)) primary_street_name_final = primary_street_name if not primary_street_name_final: raise CountsWorkbookParserException( "readMainlineCounts: Street %s and cross streets %s,%s have no intersections: %s %s" % (primary_street, str(cross_street1_list), str(cross_street2_list), str(intersections1), str(intersections2))) # finalize the cross street names and intersection ids cross_street1_name_final = intersections1[ primary_street_name_final].keys()[0] cross_street2_name_final = intersections2[ primary_street_name_final].keys()[0] # go through the sheets and read the data book = xlrd.open_workbook(file) sheetnames = book.sheet_names() counts_saved = 0 for sheet_idx in range(len(sheetnames)): if sheetnames[sheet_idx].lower() == "source": continue activesheet = book.sheet_by_name(sheetnames[sheet_idx]) # Create date from sheetname in date format tmp_date = sheetnames[sheet_idx].split('.') date_yyyy_mm_dd = date(int(tmp_date[0]), int(tmp_date[1]), int(tmp_date[2])) project = "" sections = self.findSectionStarts(activesheet, logger) # loop through the different sections in the workbook for section in sections: # project section? if activesheet.cell_value(section[0], 0).upper() == "PROJECT": project = activesheet.cell_value(section[0] + 1, 0) continue # figure out the vehicle type and row with the column labels assert (activesheet.cell_value(section[0] + 1, 0).upper() == "MAINLINE") label_row = section[0] + 1 # figure out the vehicle type code vehicle = activesheet.cell_value(section[0], 0) if type(vehicle) in [types.FloatType, types.IntType ] and vehicle in range(16): vtype = vehicle elif type(vehicle) in [ types.StringType, types.UnicodeType ]: vtype = self.vehicleTypeForString(vehicle) else: vtype = 0 #TODO: fix logger.info(" Worksheet %20s Vehicle=%2d %s" % (sheetnames[sheet_idx], vtype, vehicle)) for column in range( 1, self.numNonBlankColumns(activesheet, label_row)): # Read the label header ml_ondir_temp = activesheet.cell_value( label_row, column) ml_ondir = ml_ondir_temp[:2] direction = ml_ondir[0] # The convention is that cross street 1 is always north or west of cross street 2 # so use this cue to determine the origin/destination of the movement if (direction == 'S' or direction == 'E'): ml_fromstreet = cross_street1_name_final ml_fromint = intersections1[ primary_street_name_final][ml_fromstreet][0] ml_tostreet = cross_street2_name_final ml_toint = intersections2[ primary_street_name_final][ml_tostreet][0] else: ml_fromstreet = cross_street2_name_final ml_fromint = intersections2[ primary_street_name_final][ml_fromstreet][0] ml_tostreet = cross_street1_name_final ml_toint = intersections1[ primary_street_name_final][ml_tostreet][0] # look for the mainline count location in countdracula try: mainline_count_location = MainlineCountLocation.objects.get( from_int=ml_fromint, to_int=ml_toint, on_street=primary_street_name_final, on_dir=ml_ondir) except ObjectDoesNotExist: mainline_count_location = MainlineCountLocation( on_street=primary_street_name_final, on_dir=ml_ondir, from_street=ml_fromstreet, from_int=ml_fromint, to_street=ml_tostreet, to_int=ml_toint) mainline_count_location.save() # process the rows for row in range(section[0] + 2, section[1] + 1): count = activesheet.cell_value(row, column) if count == "": continue (starttime, period) = self.createtimestamp( activesheet.cell_value(row, 0), tzinfo=tzinfo) mainline_count = MainlineCount( location=mainline_count_location, count=count, count_date=date_yyyy_mm_dd, start_time=starttime, period_minutes=period, vehicle_type=vtype, reference_position= -1, # reference position unknown, it's not in the workbook sourcefile=file, project=project, upload_user=user) mainline_count.clean() mainline_count.save() counts_saved += 1 logger.info(" Processed %s into countdracula" % file) logger.info(" Successfully saved %4d mainline counts" % counts_saved) return counts_saved except Exception as e: logger.error(" Failed to process %s" % file) logger.error(" " + str(e)) logger.error(" " + traceback.format_exc()) # remove the rest of the counts for this sourcefile so it can be retried mainline_counts = MainlineCount.objects.filter(sourcefile=file) if len(mainline_counts) > 0: logger.debug( " Removing %d counts from countdracula so sourcefile %s can be reprocessed" % (len(mainline_counts), file)) mainline_counts.delete() else: logger.debug( " No counts to remove for sourcefile %s; sourcefile can be reprocessed" % file) return -1
def readAndInsertMainlineCounts(self, file, primary_street, cross_street1, cross_street2, user, logger, tzinfo=None): """ Parses the given excel file representing mainline counts and inserts those counts into the countdracula database. * *file* is the Excel workbook file name * *primary_street* is the street on which the counts were taken * *cross_street1* and *cross_street2* are the bounding cross streets * *user* is the django User to associate with the count * *logger* is a logging instance On success, returns number of successful counts inserted. On failure, removes all counts from this workbook so it can be fixed and inserted again, and returns -1. """ # logger.info("primary_street=[%s], cross_street1=[%s] cross_street2=[%s]" % (primary_street, cross_street1, cross_street2)) mainline_counts = MainlineCount.objects.filter(sourcefile=file) if len(mainline_counts) > 0: logger.error(" readAndInsertMainlineCounts() called on %s, but %d mainline counts already "\ "exist with that sourcefile. Skipping." % (file, len(mainline_counts))) return -1 try: primary_street_list = StreetName.getPossibleStreetNames(primary_street) if len(primary_street_list) == 0: raise CountsWorkbookParserException("readMainlineCounts: primary street %s not found." % primary_street) cross_street1_list = StreetName.getPossibleStreetNames(cross_street1) if len(cross_street1_list) == 0: raise CountsWorkbookParserException("readMainlineCounts: cross street 1 %s not found." % cross_street1) cross_street2_list = StreetName.getPossibleStreetNames(cross_street2) if len(cross_street2_list) == 0: raise CountsWorkbookParserException("readMainlineCounts: cross street 2 %s not found." % cross_street2) # looking for a primary street that intersects with both one of cross_street1 cross_Street2 # collect this info in two dictionaries: # { primary_street_name (StreetName instance) -> { cross_street1_name (StreetName instance) -> QuerySet of Node instances }} # { primary_street_name (StreetName instance) -> { cross_street2_name (StreetName instance) -> QuerySet of Node instances }} intersections1 = {} intersections2 = {} for primary_street_name in primary_street_list: intersections1[primary_street_name] = {} intersections2[primary_street_name] = {} for cross_street1_name in cross_street1_list: intersections1[primary_street_name][cross_street1_name] = Node.objects.filter(street_to_node__street_name=primary_street_name) \ .filter(street_to_node__street_name=cross_street1_name) # don't bother if it's an empty set if len(intersections1[primary_street_name][cross_street1_name]) == 0: del intersections1[primary_street_name][cross_street1_name] for cross_street2_name in cross_street2_list: intersections2[primary_street_name][cross_street2_name] = Node.objects.filter(street_to_node__street_name=primary_street_name) \ .filter(street_to_node__street_name=cross_street2_name) # don't bother if it's an empty set if len(intersections2[primary_street_name][cross_street2_name]) == 0: del intersections2[primary_street_name][cross_street2_name] # ideally, there will be exactly one primary street with a cross street 1 candidate and a cross street 2 candidate primary_street_name_final = None for primary_street_name in primary_street_list: if len(intersections1[primary_street_name]) == 0: continue if len(intersections2[primary_street_name]) == 0: continue if len(intersections1[primary_street_name]) > 1: raise CountsWorkbookParserException("readMainlineCounts: Street %s and cross street 1 %s have multiple intersections: %s" % (primary_street_name, cross_street1, str(intersections1[primary_street_name]))) if len(intersections2[primary_street_name]) > 1: raise CountsWorkbookParserException("readMainlineCounts: Street %s and cross street 2 %s have multiple intersections: %s" % (primary_street_name, cross_street2, str(intersections2[primary_street_name]))) # already found one? if primary_street_name_final: raise CountsWorkbookParserException("readMainlineCounts: Multiple primary streets (%s,%s) intersect with %s/%s" % (primary_street_name, primary_street_name_final, cross_street1, cross_street2)) primary_street_name_final = primary_street_name if not primary_street_name_final: raise CountsWorkbookParserException("readMainlineCounts: Street %s and cross streets %s,%s have no intersections: %s %s" % (primary_street, str(cross_street1_list), str(cross_street2_list), str(intersections1), str(intersections2))) # finalize the cross street names and intersection ids cross_street1_name_final = intersections1[primary_street_name_final].keys()[0] cross_street2_name_final = intersections2[primary_street_name_final].keys()[0] # go through the sheets and read the data book = xlrd.open_workbook(file) sheetnames = book.sheet_names() counts_saved = 0 for sheet_idx in range(len(sheetnames)) : if sheetnames[sheet_idx].lower()=="source": continue activesheet = book.sheet_by_name(sheetnames[sheet_idx]) # Create date from sheetname in date format tmp_date = sheetnames[sheet_idx].split('.') date_yyyy_mm_dd = date(int(tmp_date[0]),int(tmp_date[1]),int(tmp_date[2]) ) project = "" sections = self.findSectionStarts(activesheet, logger) # loop through the different sections in the workbook for section in sections: # project section? if activesheet.cell_value(section[0],0).upper() == "PROJECT": project = activesheet.cell_value(section[0]+1,0) continue # figure out the vehicle type and row with the column labels assert(activesheet.cell_value(section[0]+1,0).upper() == "MAINLINE") label_row = section[0]+1 # figure out the vehicle type code vehicle = activesheet.cell_value(section[0], 0) if type(vehicle) in [types.FloatType, types.IntType] and vehicle in range(16): vtype = vehicle elif type(vehicle) in [types.StringType, types.UnicodeType]: vtype = self.vehicleTypeForString(vehicle) else: vtype = 0 #TODO: fix logger.info(" Worksheet %20s Vehicle=%2d %s" % (sheetnames[sheet_idx], vtype, vehicle)) for column in range(1,self.numNonBlankColumns(activesheet, label_row)): # Read the label header ml_ondir_temp = activesheet.cell_value(label_row,column) ml_ondir = ml_ondir_temp[:2] direction = ml_ondir[0] # The convention is that cross street 1 is always north or west of cross street 2 # so use this cue to determine the origin/destination of the movement if (direction == 'S' or direction == 'E'): ml_fromstreet = cross_street1_name_final ml_fromint = intersections1[primary_street_name_final][ml_fromstreet][0] ml_tostreet = cross_street2_name_final ml_toint = intersections2[primary_street_name_final][ml_tostreet][0] else: ml_fromstreet = cross_street2_name_final ml_fromint = intersections2[primary_street_name_final][ml_fromstreet][0] ml_tostreet = cross_street1_name_final ml_toint = intersections1[primary_street_name_final][ml_tostreet][0] # look for the mainline count location in countdracula try: mainline_count_location = MainlineCountLocation.objects.get(from_int = ml_fromint, to_int = ml_toint, on_street = primary_street_name_final, on_dir = ml_ondir) except ObjectDoesNotExist: mainline_count_location = MainlineCountLocation(on_street = primary_street_name_final, on_dir = ml_ondir, from_street = ml_fromstreet, from_int = ml_fromint, to_street = ml_tostreet, to_int = ml_toint) mainline_count_location.save() # process the rows for row in range(section[0]+2, section[1]+1): count = activesheet.cell_value(row,column) if count == "" : continue (starttime, period) = self.createtimestamp(activesheet.cell_value(row,0), tzinfo=tzinfo) mainline_count = MainlineCount(location = mainline_count_location, count = count, count_date = date_yyyy_mm_dd, start_time = starttime, period_minutes = period, vehicle_type = vtype, reference_position = -1, # reference position unknown, it's not in the workbook sourcefile = file, project = project, upload_user = user) mainline_count.clean() mainline_count.save() counts_saved += 1 logger.info(" Processed %s into countdracula" % file) logger.info(" Successfully saved %4d mainline counts" % counts_saved) return counts_saved except Exception as e: logger.error(" Failed to process %s" % file) logger.error(" " + str(e)) logger.error(" " + traceback.format_exc()) # remove the rest of the counts for this sourcefile so it can be retried mainline_counts = MainlineCount.objects.filter(sourcefile=file) if len(mainline_counts) > 0: logger.debug(" Removing %d counts from countdracula so sourcefile %s can be reprocessed" % (len(mainline_counts), file)) mainline_counts.delete() else: logger.debug(" No counts to remove for sourcefile %s; sourcefile can be reprocessed" % file) return -1