Subversion Repositories Misc

#!/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("&","&amp;")+", "+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()