From 1e6fbc3a99765e5e7e3b9ae18aab9d70eac9fd30 Mon Sep 17 00:00:00 2001
From: Stefan Huber <shuber@sthu.org>
Date: Fri, 17 Jan 2014 14:56:30 +0100
Subject: [PATCH] Add geolocations table

---
 dvr-managedb | 68 ++++++++++++++++++++++++++++++----------------------
 1 file changed, 39 insertions(+), 29 deletions(-)

diff --git a/dvr-managedb b/dvr-managedb
index a4dba38..fc81930 100755
--- a/dvr-managedb
+++ b/dvr-managedb
@@ -14,6 +14,12 @@ import json
 import gzip
 
 
+geocodeProviders = [
+        "Google",
+        "Bing"
+        ]
+
+
 class Database:
     """Encapsualtes a DVR database."""
 
@@ -53,6 +59,14 @@ class Database:
                      status TEXT NOT NULL \
                      )")
 
+        c.execute("CREATE TABLE IF NOT EXISTS geolocations (\
+                     dvrid INTEGER REFERENCES dvrtable(id) ON DELETE CASCADE, \
+                     provider INTEGER NOT NULL, \
+                     lat REAL NOT NULL, \
+                     lon REAL NOT NULL, \
+                     PRIMARY KEY (dvrid, provider) \
+                     )")
+
         c.execute("CREATE INDEX IF NOT EXISTS purposes_dvrid ON purposes (dvrid)")
 
         c.close()
@@ -117,6 +131,22 @@ class Database:
         c.close()
         self.conn.commit()
 
+    def get_geolocations(self, dvrid):
+        """Return all known geolocations of given DVR-ID, if any."""
+        c = self.conn.cursor()
+        c.execute("SELECT * FROM geolocations WHERE dvrid=?", (dvrid,))
+        return c.fetchall()
+
+    def add_geolocation(self, dvrid, lat, lon, provider):
+        """Add a geolocatoin for a given DVR-ID."""
+        assert(0 <= provider and provider < len(geocodeProviders))
+
+        c = self.conn.cursor()
+        c.execute("INSERT INTO geolocation VALUES (?, ?, ?, ?)", \
+                  (dvrid, provider, lat, lon))
+        c.close()
+        self.conn.commit()
+
 
 def usage():
     """Print help text."""
@@ -134,29 +164,9 @@ OPTIONS:
   -d FILE     Use given sqlite3 database.
   -h          Print this help text.
   -q COND     Query for datasets where the given WHERE SQL-condition holds.
-  -g          Query geographical location for address field of registration.
 """)
 
 
-def queryLocation(address):
-    """Get (lat, lng) pair of given postal address."""
-
-    urlargs = {}
-    urlargs['address'] = address
-    urlargs['sensor'] = "false"
-    urlparam = urllib.parse.urlencode(urlargs)
-
-    url = "http://maps.googleapis.com/maps/api/geocode/json?" + urlparam
-    try:
-        response = urllib.request.urlopen(url).read().decode('utf-8')
-        data = json.loads(response)
-        loc = data['results'][0]['geometry']['location']
-        return loc['lat'], loc['lng']
-    except urllib.error.URLError as e:
-        print(e, file=sys.stderr)
-        return None
-
-
 def printDataset(db, id):
     """Print info for a dataset of given ID."""
 
@@ -181,15 +191,17 @@ def printDataset(db, id):
         print("  Name:       ", name)
         print("  Address:    ", address)
 
-        if showLocation:
-            loc = queryLocation(address)
-            print("  Coordinates:", loc)
+        geolocs = db.get_geolocations(id)
+        for loc in geolocs:
+            p = geocodeProviders[loc[1]]
+            print("  Coordinates: lat %f, lon %f (%s)" % (loc[2], loc[3], p))
 
         purposes = db.get_purposes(id)
         if len(purposes) == 0:
             print("  No purposes known.")
         else:
             for purp in purposes:
+                print("")
                 print("  Purpose %d:" % purp[2])
                 print("    Text:  ", purp[3])
                 print("    Date:  ", purp[4])
@@ -197,13 +209,14 @@ def printDataset(db, id):
     return True
 
 
-def processQuery(db, query, showLocation=False):
+def processQuery(db, query):
     """Process query for given ID."""
 
     ids = db.query(query)
     print("Found %d results." % len(ids))
 
     for id in ids:
+        print("")
         printDataset(db, id)
 
 
@@ -295,10 +308,9 @@ if __name__ == "__main__":
     add = None
     dbfn = None
     query = None
-    showLocation = False
 
     try:
-        opts, args = getopt.getopt(sys.argv[1:], "a:d:hgq:")
+        opts, args = getopt.getopt(sys.argv[1:], "a:d:hq:")
 
         for opt, arg in opts:
             if opt == "-a":
@@ -308,8 +320,6 @@ if __name__ == "__main__":
             elif opt == "-h":
                 usage()
                 sys.exit(os.EX_OK)
-            elif opt == "-g":
-                showLocation = True
             elif opt == "-q":
                 query = arg
             else:
@@ -331,7 +341,7 @@ if __name__ == "__main__":
         processAdd(db, add)
 
     if query is not None:
-        success = processQuery(db, query, showLocation)
+        success = processQuery(db, query)
         if not success:
             sys.exit(1)
 
-- 
2.39.5