buoys2spatialite

# -*- coding: utf-8 -*-
from pysqlite2 import dbapi2 as sqlite
import sys
import glob

sql_connection = sqlite.Connection('iapb.sqlite')

cursor = sqlite.Cursor(sql_connection)

tablename = 'data'
SRID = '4326' # example SRID, as a string
column_names = 'UID INTEGER PRIMARY KEY AUTOINCREMENT, date DATETIME NOT NULL, idx INT NOT NULL, Geometry BLOB NOT NULL'
geometry_column_name = 'Geometry'
geometry_type = 'POINT' # Could be POLYGON, MULTIPOLYGON, LINESTRING, etc

def create_point_geometry_table(cursor, sql_connection, tablename, column_names, SRID):
    cursor.execute("CREATE TABLE '%s'(%s) "% (tablename, column_names))

    cursor.execute("SELECT RecoverGeometryColumn('%s', '%s', %s, '%s', 2)" % (tablename, geometry_column_name, str(SRID), geometry_type)) # The '2' indicates that this is a two-dimensional geometry
    sql_connection.commit()

def ins_function(cursor, sql_connection, tablename,idate,idx,geom_p):
    try:
        ins_test = "INSERT OR REPLACE INTO %s (date,idx,Geometry) VALUES (\'%s\',\'%s\', GeomFromText(\'%s\'))" % (tablename,idate,idx,geom_p)
        print ins_test
        cursor.execute(ins_test)
        sql_connection.commit()
    except:
        print "Insert error!"

def buffer_select(cursor, geometry_column, table, longitude, latitude, wgs84, buffer_distance):
    'Allows one to select features that are within a buffer distance from a point described by the variables longitude and latitude'
    req = '''SELECT date,idx,AsText(%s) FROM %s WHERE Within(%s.Geometry, Buffer(GeomFromText('POINT(%s %s)',%s),%s))''' %\
                   (geometry_column, table, table, longitude, latitude, wgs84, buffer_distance)

    rows=cursor.execute(req)
    ch=0
    for row in rows:
        print u'Date: %s, ID: %s, LatLon: %s' % (row[0],row[1],row[2])
        ch+=1
    print "\n%s records have found" % ch
    #print req
    return cursor.fetchall() #you could also use fetchone or fetchmany

def select_by_id(cursor, geometry_column, table, idx):
    'Allows select byous trajectory by its ID'
    req = '''SELECT date,idx,AsText(%s) FROM %s WHERE (idx=%s AND date LIKE \'%s\') ''' %\
                   (geometry_column, table, idx, '%00:00:00')

    rows=cursor.execute(req)
    ch=0
    for row in rows:
        print u'Date: %s, ID: %s, LatLon: %s' % (row[0],row[1],row[2])
        ch+=1
    print "\n%s records have found" % ch
    #print req
    return cursor.fetchall() #you could also use fetchone or fetchmany

# This is the distance, which will be in the linear value system of the chosen coordinate system (in this case, it is Decimal Degrees)
buffer_distance = '.5'

#rows=buffer_select(cursor, geometry_column_name, tablename, '79.0', '0.0', '4326', buffer_distance)

select_by_id(cursor, geometry_column_name, tablename,'8363')

# create table
#create_point_geometry_table(cursor,sql_connection,tablename,column_names,SRID)

# SELECT data
'''
sel = "SELECT date,idx,AsText(Geometry) FROM %s idx = \'%s\'" % (tablename,idx)
rows=cursor.execute(sel)
for row in rows:
    print u'Точка: %s, Имя: %s, Координаты: %s' % (row[0],row[1],row[2])
'''

#ffiles=glob.glob('./data/C*')
ffiles=[]
for iffile in ffiles:
    ffile=open(iffile,'r')
    lines=ffile.readlines()
    i=0
    for line in lines:
        try:
            iyear,imon,iday,ihour,idx,ilat,ilon=line.split()
            print u'Год: %s, Месяц: %s, Число: %s, Час: %s, ID: %s, Широта: %s, Долгота: %s' % (iyear,imon,iday,ihour,idx,ilat,ilon)
        except:
            iyear,imon,iday,ihour,idx,ilat,ilon=iyear,imon,iday,ihour,idx,'999','999'

        if int(iyear)<1000:
            iyear='19%s' % iyear
        if int(ihour)<10:
            ihour='0%s' % ihour
        if int(iday)<10:
            iday='0%s' % iday
        if int(imon)<10:
            imon='0%s' % imon

        idate='%s-%s-%s %s:00:00' % (iyear,imon,iday,ihour)
        geom_p='POINT(%s %s)' % (ilat,ilon)
        ins_function(cursor, sql_connection, tablename, idate, idx, geom_p)
        i+=1

    ffile.close()


sql_connection.close()