Compare with Previous | Blame | View Log
#!/usr/bin/env python # forgottenislanderbot - makes a DSL coverage map from Bell Aliant's website. # Copyright (c) 2010 Art Ortenburger # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU General Public License # as published by the Free Software Foundation; either version 2 # of the License, or any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. # # fib-dbutil.py # by Art Ortenburger IV <utrrrongeeb a users , sf , net> # written 2010-01-14 - # # forgottenislanderbot -- database utility # # Does various operations on FIB sqlite databases, # including merging, gathering statistics, and # generating Google Earth KML files. # import sys import sqlite3 import fibmod print sys.argv[0],"""(forgottenislanderbot database utility) Version 0.1 Copyright (c) 2010 Art Ortenburger. Licensed under the GNU GPL; no warranty.""" def mergeDBs(outDB,inDB1,inDB2): """ merges two SQlite databases into a third. all params must be sqlite3.Cursor objects.""" print "not implemented" # open input dbs # figure out how to merge dbs, etc # kml file to write kmlf = None # five-digit dsl status titles for output table and list printing outputnames = ["ERROR","EMPTY","NODSL","BASIC","ULTRA","TOTAL"] def genKML(inDB,dslflags,sparsity,exclude,outkml): """ Generates a KML file from a database. inDB: sqlite3.Cursor to FIB database. dslflags: 4 y/n charbools in string sequence. Whether to include in KML output each of EMPTY, NODSL, BASIC, & ULTRA categories. Example: "nyyy" sparsity: int; minimum civic-number distance between houses to output. This is to avoid slowing Google Earth to a crawl with 68023 placemarks. exclude: bool; whether to exclude PEI cities from output outkml: string; path to KML file to write. Will be overwritten.""" try: globals()['kmlf'] = open(outkml,"wb") # @77 consider NASA World Wind support with different header, footer kmlf.write(KML_HEADER) # static KML header, with styles # have module function loop through database, adding addresses # current maxrows is False, for minimum returns; change that? fibmod.processDB(sqCur,kmlItemAction,dslflags,False,sparsity,0,exclude) kmlf.write(KML_FOOTER) # static KML footer kmlf.flush() kmlf.close() except Exception, errdet: print "Error writing KML file:", errdet safeexit() def printStats(inDB,dslflags,sparsity,exclude): """ Prints statistics from civic address database. inDB: sqlite3.Cursor to FIB database sparsity: int; minimum civic-number distance between houses to output. This is to find out what a given sparsity will do to the crawler or Google Earth. For totals, use 1. exclude: bool; whether to exclude PEI cities from output""" # have module function loop through database, counting stats # at the moment maxrows is False, for minimum returns; change it? fibmod.processDB(sqCur,statItemAction,dslflags,False,sparsity,0,exclude) print # newline needed # print little table for i in range(6): print outputnames[i],": ",stats[i] def printList(inDB,dslflags,sparsity,exclude): """ Prints addresses from civic address database. inDB: sqlite3.Cursor to FIB database sparsity: int; minimum civic-number distance between houses to output. This is to find out what a given sparsity will do to the crawler or Google Earth. For totals, use 1. exclude: bool; whether to exclude PEI cities from output""" # have module function loop through database, calling handler # function listItemAction() to print each one. # at the moment maxrows is False, for minimum returns; change it? fibmod.processDB(sqCur,listItemAction,dslflags,False,sparsity,0,exclude) sys.stdout.flush() def kmlItemAction(sqlcur,town,road,civic,delay,status,lon,lat): """ handles an individual selected street address Called by fibmod.processDB, from genKML(). Does not honour delay param. See fibmod.processDB for param description.""" # obsolete due to dslFlags being moved to fibmod.processDB() ## # some statuses will be excluded; just skip them ## if not dslFlags[status]: ## return; # write nicely formatted data to file. kmlf.write(""" <Placemark> <styleUrl>#msn_"""+kmlIconMap[status]+"""-pushpin</styleUrl> <Point> <coordinates>"""+str(lon)+""","""+str(lat)+""",0</coordinates> </Point> </Placemark> """) # The only useful bit in this block is the <name> block. # However, this makes Google Earth rather cluttered, # adds to file size, and raises privacy concerns. # The '&' replacement is very important -- Earth chokes easily. # The <LookAt> block is useless bloat. # This stuff was originally above <styleUrl>. ## <name>"""+str(civic)+" "+road.replace("&","&")+", "+town+"""</name> ## <LookAt> ## <longitude>"""+str(lon)+"""</longitude> ## <latitude>"""+str(lat)+"""</latitude> ## <altitude>0</altitude> ## <range>600</range> ## <tilt>0</tilt> ## <heading>0</heading> ## <altitudeMode>relativeToGround</altitudeMode> ## <gx:altitudeMode>relativeToSeaFloor</gx:altitudeMode> ## </LookAt> # end of kmlItemAction() def statItemAction(sqlcur,town,road,civic,delay,status,lon,lat): """ handles an individual selected street address. Called by fibmod.processDB, from printStats(). Does not honour delay param. For param details, see docstring for fibmod.processDB().""" stats[5] = stats[5] + 1 # increment the total if status < 0: # error stats[0] = stats[0] + 1 elif status == 0: # unknown [EMPTY] stats[1] = stats[1] + 1 elif status == 1: # no [NODSL] stats[2] = stats[2] + 1 elif status == 2: # basic [BASIC] stats[3] = stats[3] + 1 elif status == 3: # ultra [ULTRA] stats[4] = stats[4] + 1 else: # error print "invalid status: ",civic, road,",", town,": ",status def listItemAction(sqlcur,town,road,civic,delay,status,lon,lat): """ prints an individual selected street address. Called by fibmod.processDB, from printList(). Does not honour delay param. For param details, see docstring for fibmod.processDB().""" print outputnames[(status+1)]," ",civic,road,",",town # sqlite3 database connection sqCon = None # sqlite3 database cursor sqCur = None # dsl status mask, as used by various functions. EMPTY, NODSL, BASIC, ULTRA dslFlags = [ False, False, False, False ] # cumulated statistics. Incremented by statItemAction(), through printStats() stats = [0, 0, 0, 0, 0, 0] # error, unknown, no, basic, ultra, total # truncated codes for Google Earth's KML URL pushpin colors. # EMPTY, NODSL, BASIC, ULTRA kmlIconMap = ["wht","ylw","ltblu","grn"] def safeexit(): """ Exits the database utility safely, committing, flushing, and closing anything that needs it.""" if kmlf != None: try: kmlf.flush() except: pass kmlf.close() try: sqCur.close() except: pass try: sqCon.commit() sqCon.close() except: pass # help message to print if necessary HELPSTR = """ Usage: fib-dbutil.py database.sqlite (COMMAND) (OPTIONS) (COMMANDS) (OPTIONS) --merge input1.sqlite input2.sqlite Merges the two databases into the new one, producing the widest range of addresses, with the highest levels of service. --kml (dslmap) (sparsity) (exclude) output.kml Renders a Google Earth KML file from the database. --stats (dslmap) (sparsity) (exclude) Prints the total number of addresses for each service. --list (dslmap) (sparsity) (exclude) Prints the addresses for each service. Shared options: dslmap - which services to show. Four y/n flags, respectively unknown/no/basic/ultra. sparsity - minimum civic-number difference along a road in a community to skip. exclude - exclude cities or not (y/n).""" # static header for Google Earth KML KML_HEADER = """<?xml version="1.0" encoding="UTF-8"?> <kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom"> <Document> <name>forgottenislanderbot</name> <Style id="sn_ltblu-pushpin"> <IconStyle> <scale>1.1</scale> <Icon> <href>http://maps.google.com/mapfiles/kml/pushpin/ltblu-pushpin.png</href> </Icon> <hotSpot x="20" y="2" xunits="pixels" yunits="pixels"/> </IconStyle> <ListStyle> </ListStyle> </Style> <StyleMap id="msn_wht-pushpin"> <Pair> <key>normal</key> <styleUrl>#sn_wht-pushpin</styleUrl> </Pair> <Pair> <key>highlight</key> <styleUrl>#sh_wht-pushpin</styleUrl> </Pair> </StyleMap> <Style id="sn_wht-pushpin"> <IconStyle> <scale>1.1</scale> <Icon> <href>http://maps.google.com/mapfiles/kml/pushpin/wht-pushpin.png</href> </Icon> <hotSpot x="20" y="2" xunits="pixels" yunits="pixels"/> </IconStyle> <ListStyle> </ListStyle> </Style> <Style id="sh_ylw-pushpin"> <IconStyle> <scale>1.3</scale> <Icon> <href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href> </Icon> <hotSpot x="20" y="2" xunits="pixels" yunits="pixels"/> </IconStyle> <ListStyle> </ListStyle> </Style> <Style id="sh_ltblu-pushpin"> <IconStyle> <scale>1.3</scale> <Icon> <href>http://maps.google.com/mapfiles/kml/pushpin/ltblu-pushpin.png</href> </Icon> <hotSpot x="20" y="2" xunits="pixels" yunits="pixels"/> </IconStyle> <ListStyle> </ListStyle> </Style> <StyleMap id="msn_ltblu-pushpin"> <Pair> <key>normal</key> <styleUrl>#sn_ltblu-pushpin</styleUrl> </Pair> <Pair> <key>highlight</key> <styleUrl>#sh_ltblu-pushpin</styleUrl> </Pair> </StyleMap> <Style id="sn_grn-pushpin"> <IconStyle> <scale>1.1</scale> <Icon> <href>http://maps.google.com/mapfiles/kml/pushpin/grn-pushpin.png</href> </Icon> <hotSpot x="20" y="2" xunits="pixels" yunits="pixels"/> </IconStyle> <ListStyle> </ListStyle> </Style> <StyleMap id="msn_ylw-pushpin"> <Pair> <key>normal</key> <styleUrl>#sn_ylw-pushpin</styleUrl> </Pair> <Pair> <key>highlight</key> <styleUrl>#sh_ylw-pushpin</styleUrl> </Pair> </StyleMap> <StyleMap id="msn_grn-pushpin"> <Pair> <key>normal</key> <styleUrl>#sn_grn-pushpin</styleUrl> </Pair> <Pair> <key>highlight</key> <styleUrl>#sh_grn-pushpin</styleUrl> </Pair> </StyleMap> <Style id="sn_ylw-pushpin"> <IconStyle> <scale>1.1</scale> <Icon> <href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href> </Icon> <hotSpot x="20" y="2" xunits="pixels" yunits="pixels"/> </IconStyle> <ListStyle> </ListStyle> </Style> <Style id="sh_grn-pushpin"> <IconStyle> <scale>1.3</scale> <Icon> <href>http://maps.google.com/mapfiles/kml/pushpin/grn-pushpin.png</href> </Icon> <hotSpot x="20" y="2" xunits="pixels" yunits="pixels"/> </IconStyle> <ListStyle> </ListStyle> </Style> <Style id="sh_wht-pushpin"> <IconStyle> <scale>1.3</scale> <Icon> <href>http://maps.google.com/mapfiles/kml/pushpin/wht-pushpin.png</href> </Icon> <hotSpot x="20" y="2" xunits="pixels" yunits="pixels"/> </IconStyle> <ListStyle> </ListStyle> </Style> <Folder> <name>forgottenislanderbot</name> <open>0</open> """ # static footer for Google Earth KML KML_FOOTER = """ </Folder> </Document> </kml> """ # no command takes less than four args, so assume exit. Maybe room to improve? if len(sys.argv) < 5: print HELPSTR exit() # need to load the database here sqCon = sqlite3.connect(sys.argv[1]) sqCur = sqCon.cursor() # parse for various cmd-line flags if sys.argv[2] == "--merge":# not implemented mergeDBs(sys.argv[1],sys.argv[3],sys.argv[4]) elif sys.argv[2] == "--kml": if len(sys.argv) < 7: print HELPSTR exit() genKML(sys.argv[1],sys.argv[3],int(sys.argv[4]),fibmod.char2Bool(sys.argv[5]),sys.argv[6]) elif sys.argv[2] == "--stats": if len(sys.argv) < 6: print HELPSTR exit() printStats(sys.argv[1],sys.argv[3],int(sys.argv[4]),fibmod.char2Bool(sys.argv[5])) elif sys.argv[2] == "--list": if len(sys.argv) < 6: print HELPSTR exit() printList(sys.argv[1],sys.argv[3],int(sys.argv[4]),fibmod.char2Bool(sys.argv[5])) else: print HELPSTR exit() # after everything's done, make sure everything's saved safeexit()