From: Florian Schweikert Date: Wed, 2 Nov 2011 01:06:01 +0000 (+0100) Subject: get nearby stations from sqlite3 db X-Git-Url: https://vcs.maemo.org/git/?p=pywienerlinien;a=commitdiff_plain;h=5c845c3d17397daf245737f2a38a3fa40e36c5f0 get nearby stations from sqlite3 db db file generated with pyosm tools --- diff --git a/gotovienna-qml b/gotovienna-qml index f8041fe..0a82381 100755 --- a/gotovienna-qml +++ b/gotovienna-qml @@ -15,6 +15,7 @@ from PySide.QtDeclarative import QDeclarativeView from gotovienna.utils import * from gotovienna.realtime import * +from gotovienna.gps import * import urllib2 import os @@ -114,6 +115,14 @@ class Gui(QObject): def get_departures(self): return self.current_departures + @Slot(float, float, result=str) + def get_nearby_stations(self, lat, lon): + try: + return ', '.join(get_nearby_stations(lat, lon)) + except Exception as e: + print e.message + return '' + @Slot(str, str) def search(self, line, station): line = line.upper() diff --git a/gotovienna/defaults.py b/gotovienna/defaults.py index 5491e0d..9f5df69 100644 --- a/gotovienna/defaults.py +++ b/gotovienna/defaults.py @@ -15,6 +15,10 @@ if not path.exists(cache_folder): cache_lines = path.join(cache_folder, 'lines.json') cache_stations = path.join(cache_folder, 'stations.json') +# SQL & GPS +sql_gps_query = 'SELECT name FROM stations WHERE lat > ? and lat < ? and lon > ? and lon < ?' +sql_file = path.expanduser('~/.gotovienna/stations.db') + # iTip line_overview = 'http://www.wienerlinien.at/itip/linienwahl/' diff --git a/gotovienna/gps.py b/gotovienna/gps.py new file mode 100644 index 0000000..228f805 --- /dev/null +++ b/gotovienna/gps.py @@ -0,0 +1,9 @@ +#select name, lat, lon from stations where lat > 48.23739 and lat < 48.2424 and lon > 16.31106 and lon < 16.3203 +import defaults +import sqlite3 + +def get_nearby_stations(lat, lon): + conn = sqlite3.connect(defaults.sql_file) + c = conn.cursor() + c.execute(defaults.sql_gps_query, (lat - 0.004, lat + 0.004, lon - 0.005, lon + 0.005)) + return map(lambda x: x[0], list(set(c.fetchall()))) diff --git a/gotovienna/sqlite3/__init__.py b/gotovienna/sqlite3/__init__.py new file mode 100644 index 0000000..7864134 --- /dev/null +++ b/gotovienna/sqlite3/__init__.py @@ -0,0 +1,23 @@ +# pysqlite2/__init__.py: the pysqlite2 package. +# +# Copyright (C) 2005 Gerhard Haering +# +# This file is part of pysqlite. +# +# This software is provided 'as-is', without any express or implied +# warranty. In no event will the authors be held liable for any damages +# arising from the use of this software. +# +# Permission is granted to anyone to use this software for any purpose, +# including commercial applications, and to alter it and redistribute it +# freely, subject to the following restrictions: +# +# 1. The origin of this software must not be misrepresented; you must not +# claim that you wrote the original software. If you use this software +# in a product, an acknowledgment in the product documentation would be +# appreciated but is not required. +# 2. Altered source versions must be plainly marked as such, and must not be +# misrepresented as being the original software. +# 3. This notice may not be removed or altered from any source distribution. + +from dbapi2 import * diff --git a/gotovienna/sqlite3/dbapi2.py b/gotovienna/sqlite3/dbapi2.py new file mode 100644 index 0000000..8c54d39 --- /dev/null +++ b/gotovienna/sqlite3/dbapi2.py @@ -0,0 +1,87 @@ +# pysqlite2/dbapi2.py: the DB-API 2.0 interface +# +# Copyright (C) 2004-2005 Gerhard Haering +# +# This file is part of pysqlite. +# +# This software is provided 'as-is', without any express or implied +# warranty. In no event will the authors be held liable for any damages +# arising from the use of this software. +# +# Permission is granted to anyone to use this software for any purpose, +# including commercial applications, and to alter it and redistribute it +# freely, subject to the following restrictions: +# +# 1. The origin of this software must not be misrepresented; you must not +# claim that you wrote the original software. If you use this software +# in a product, an acknowledgment in the product documentation would be +# appreciated but is not required. +# 2. Altered source versions must be plainly marked as such, and must not be +# misrepresented as being the original software. +# 3. This notice may not be removed or altered from any source distribution. + +import datetime +import time + +from _sqlite3 import * + +paramstyle = "qmark" + +threadsafety = 1 + +apilevel = "2.0" + +Date = datetime.date + +Time = datetime.time + +Timestamp = datetime.datetime + +def DateFromTicks(ticks): + return Date(*time.localtime(ticks)[:3]) + +def TimeFromTicks(ticks): + return Time(*time.localtime(ticks)[3:6]) + +def TimestampFromTicks(ticks): + return Timestamp(*time.localtime(ticks)[:6]) + +version_info = tuple([int(x) for x in version.split(".")]) +sqlite_version_info = tuple([int(x) for x in sqlite_version.split(".")]) + +Binary = buffer + +def register_adapters_and_converters(): + def adapt_date(val): + return val.isoformat() + + def adapt_datetime(val): + return val.isoformat(" ") + + def convert_date(val): + return datetime.date(*map(int, val.split("-"))) + + def convert_timestamp(val): + datepart, timepart = val.split(" ") + year, month, day = map(int, datepart.split("-")) + timepart_full = timepart.split(".") + hours, minutes, seconds = map(int, timepart_full[0].split(":")) + if len(timepart_full) == 2: + microseconds = int(timepart_full[1]) + else: + microseconds = 0 + + val = datetime.datetime(year, month, day, hours, minutes, seconds, microseconds) + return val + + + register_adapter(datetime.date, adapt_date) + register_adapter(datetime.datetime, adapt_datetime) + register_converter("date", convert_date) + register_converter("timestamp", convert_timestamp) + +register_adapters_and_converters() + +# Clean up namespace + +del(register_adapters_and_converters) diff --git a/gotovienna/sqlite3/dump.py b/gotovienna/sqlite3/dump.py new file mode 100644 index 0000000..409a405 --- /dev/null +++ b/gotovienna/sqlite3/dump.py @@ -0,0 +1,63 @@ +# Mimic the sqlite3 console shell's .dump command +# Author: Paul Kippes + +def _iterdump(connection): + """ + Returns an iterator to the dump of the database in an SQL text format. + + Used to produce an SQL dump of the database. Useful to save an in-memory + database for later restoration. This function should not be called + directly but instead called from the Connection method, iterdump(). + """ + + cu = connection.cursor() + yield('BEGIN TRANSACTION;') + + # sqlite_master table contains the SQL CREATE statements for the database. + q = """ + SELECT name, type, sql + FROM sqlite_master + WHERE sql NOT NULL AND + type == 'table' + """ + schema_res = cu.execute(q) + for table_name, type, sql in schema_res.fetchall(): + if table_name == 'sqlite_sequence': + yield('DELETE FROM sqlite_sequence;') + elif table_name == 'sqlite_stat1': + yield('ANALYZE sqlite_master;') + elif table_name.startswith('sqlite_'): + continue + # NOTE: Virtual table support not implemented + #elif sql.startswith('CREATE VIRTUAL TABLE'): + # qtable = table_name.replace("'", "''") + # yield("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"\ + # "VALUES('table','%s','%s',0,'%s');" % + # qtable, + # qtable, + # sql.replace("''")) + else: + yield('%s;' % sql) + + # Build the insert statement for each row of the current table + res = cu.execute("PRAGMA table_info('%s')" % table_name) + column_names = [str(table_info[1]) for table_info in res.fetchall()] + q = "SELECT 'INSERT INTO \"%(tbl_name)s\" VALUES(" + q += ",".join(["'||quote(" + col + ")||'" for col in column_names]) + q += ")' FROM '%(tbl_name)s'" + query_res = cu.execute(q % {'tbl_name': table_name}) + for row in query_res: + yield("%s;" % row[0]) + + # Now when the type is 'index', 'trigger', or 'view' + q = """ + SELECT name, type, sql + FROM sqlite_master + WHERE sql NOT NULL AND + type IN ('index', 'trigger', 'view') + """ + schema_res = cu.execute(q) + for name, type, sql in schema_res.fetchall(): + yield('%s;' % sql) + + yield('COMMIT;') diff --git a/qml/MainPage.qml b/qml/MainPage.qml index a0f5cdf..af36767 100644 --- a/qml/MainPage.qml +++ b/qml/MainPage.qml @@ -1,5 +1,6 @@ import QtQuick 1.1 import com.nokia.meego 1.0 + import "UIConstants.js" as UIConstants import "ExtrasConstants.js" as ExtrasConstants diff --git a/qml/main.qml b/qml/main.qml index bbffda0..5236447 100644 --- a/qml/main.qml +++ b/qml/main.qml @@ -1,5 +1,6 @@ import QtQuick 1.1 import com.nokia.meego 1.0 +import QtMobility.location 1.1 PageStackWindow { id: appWindow @@ -10,6 +11,13 @@ PageStackWindow { id: mainPage } + PositionSource { + id: positionSource + updateInterval: 15000 + + active: !(position.longitudeValid && position.latitudeValid) + } + ToolBarLayout { id: commonTools visible: true @@ -41,6 +49,28 @@ PageStackWindow { left: parent.left leftMargin: 10 } + + MouseArea { + anchors.fill: parent + onClicked: { + console.debug(itip.get_nearby_stations(positionSource.position.coordinate.latitude, positionSource.position.coordinate.longitude)) + debugText.text = itip.get_nearby_stations(positionSource.position.coordinate.latitude, positionSource.position.coordinate.longitude) + } + } + } + + Text { + id: debugText + text: '' + + anchors { + bottomMargin: 10 + bottom: parent.bottom + left: logo.right + leftMargin: 10 + top: logo.top + } + font.pixelSize: 16 } }