Dev for a spatialite P190 database



In [12]:

    
from rockfish2.utils.loaders import get_example_file
from rockfish2.navigation.p190 import P190



In [13]:

    
p190 = P190(filename=get_example_file('MGL1407MCS15.TEST.p190'), database='temp.p190.sqlite')









    



[2014-10-22 14:43] INFO: rockfish: Creating new database: temp.p190.sqlite
[2014-10-22 14:43] INFO: rockfish: Creating new database: temp.p190.sqlite
[2014-10-22 14:43] INFO: rockfish: Reading p190 data from: /Users/ncm/Dev/Rockfish2/rockfish2/navigation/p190/tests/data/MGL1407MCS15.TEST.p190



In [14]:

    
p190._init_spatial()

How to create geometry table and add data



In [15]:

    
sql = """DROP TABLE IF EXISTS test_geom"""
p190.execute(sql)

sql = """CREATE TABLE test_geom (
  id INTEGER NOT NULL
    PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  easting DOUBLE NOT NULL,
  northing DOUBLE NOT NULL);"""
p190.execute(sql)

sql = """SELECT AddGeometryColumn('test_geom', 'geom',
  4326, 'POINT', 'XY');"""
p190.execute(sql)

sql = """
INSERT INTO test_geom
    (id, name,  easting, northing, geom)
  VALUES (NULL, 'first point', 1.01, 2.02,
    GeomFromText('POINT(1.01 2.02)', 4326));"""

p190.execute(sql)
p190.commit()



In [28]:

    
# add new point in two steps
sql = """INSERT INTO test_geom
    (id, name, easting, northing)
    VALUES (NULL, 'second point', 2., 3.)"""

print sql

p190.execute(sql)
p190.commit()









    



INSERT INTO test_geom
    (id, name, easting, northing)
    VALUES (NULL, 'second point', 13., 923.)



In [35]:

    
sql = "SELECT easting, northing, rowid FROM test_geom"
dat = p190.read_sql(sql)

i = 1
sql = "UPDATE test_geom SET geom=GeomFromText('POINT(? ?)', 4326) WHERE id=?"

print sql

p190.executemany(sql, np.asarray(dat))
p190.commit()









    



UPDATE test_geom SET geom=GeomFromText('POINT(? ?)', 4326) WHERE id=?






    



---------------------------------------------------------------------------
P190DatabaseError                         Traceback (most recent call last)
<ipython-input-35-c6e56d5caf1a> in <module>()
      7 print sql
      8 
----> 9 p190.executemany(sql, np.asarray(dat))
     10 p190.commit()

/Users/ncm/Dev/Rockfish2/rockfish2/navigation/p190/database.pyc in executemany(self, *args)
    206             msg += ' while executing: {:}'.format(args[0])
    207 
--> 208             _process_exception(e, msg)
    209 
    210     def insert(self, table, **kwargs):

/Users/ncm/Dev/Rockfish2/rockfish2/navigation/p190/database.pyc in _process_exception(exception, message)
     99         raise P190DatabaseIntegrityError(message)
    100     else:
--> 101         raise P190DatabaseError(message)
    102 
    103 

P190DatabaseError: executemany() failed with 'DatabaseError: executemany() failed with 'ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.' while executing: UPDATE test_geom SET geom=GeomFromText('POINT(? ?)', 4326) WHERE id=?' while executing: UPDATE test_geom SET geom=GeomFromText('POINT(? ?)', 4326) WHERE id=?

Adding geometry to p190 tables



In [5]:

    
# add geometry column to coordinates table
p190.EPSG = 32619

sql = "SELECT addGeometryColumn('{:}', 'geom', {:}, 'POINT', 'XY');"\
    .format(p190.COORDINATE_TABLE,  p190.EPSG)
    
p190.execute(sql)









    Out[5]:





<pysqlite2.dbapi2.Cursor at 0x1126c4490>



In [6]:

    
# add data
sql = "INSERT INTO {:}(geom) VALUES (GeomFromText('POINT(? ?)', {:}))"\
    .format(p190.COORDINATE_TABLE, p190.EPSG)

dat = p190.read_sql('SELECT easting, northing FROM {:}'.format(p190.COORDINATE_TABLE))

p190.executemany(sql, np.asarray(dat))









    



---------------------------------------------------------------------------
P190DatabaseError                         Traceback (most recent call last)
<ipython-input-6-c31410a0f128> in <module>()
      4 dat = p190.read_sql('SELECT easting, northing FROM {:}'.format(p190.COORDINATE_TABLE))
      5 
----> 6 p190.executemany(sql, np.asarray(dat))

/Users/ncm/Dev/Rockfish2/rockfish2/navigation/p190/database.pyc in executemany(self, *args)
    206             msg += ' while executing: {:}'.format(args[0])
    207 
--> 208             _process_exception(e, msg)
    209 
    210     def insert(self, table, **kwargs):

/Users/ncm/Dev/Rockfish2/rockfish2/navigation/p190/database.pyc in _process_exception(exception, message)
     99         raise P190DatabaseIntegrityError(message)
    100     else:
--> 101         raise P190DatabaseError(message)
    102 
    103 

P190DatabaseError: executemany() failed with 'DatabaseError: executemany() failed with 'ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 2 supplied.' while executing: INSERT INTO coordinates(geom) VALUES (GeomFromText('POINT(? ?)', 32619))' while executing: INSERT INTO coordinates(geom) VALUES (GeomFromText('POINT(? ?)', 32619))



In [ ]:

    
sql = "INSERT INTO coordinates(geom) VALUES (GeomFromText('POINT(63520.2 3600513.)', 32619)) WHERE rowid=1"
p190.execute(sql)



In [ ]:

    
dat[0:1]



In [ ]: