1

I'm using sqlalchemy for a mostly non geographic database, queried and populated using a desktop program that knows nothing of geometries.

I'm now slowly adding geographic awareness to it and I'm going to write a separate program, I'm not going to enhance the old desktop application, which should be able to continue doing its job regardless whether or not the database has been expanded to a spatial database.

The program is about botanic collections, and in the expanded database plants would be located to a point in space. The decision is not definitive, but I'm considering a spatial database (a point column) rather than just adding the two lat and lon columns to the plant table.

If I go for a spatial database, even though I do not plan to let the user insert coordinates in the desktop application, still it would be nice to be able to show the coordinates for plants that do have coordinates, for databases that did get expanded spatially.

If I go for the lat and lon columns, I can expand the SQLAlchemy database model as to include the coordinates of plants and there would be no technical issues.

If the database system is PostgreSQL+PostGIS, I execute this and it works:

session = object_session(row)
result = session.execute('select id, coords from plant where id=%d' % row.id)
myid, mycoords = result.fetchone()
if mycoords[:2] == '01':
    r = '>Q'
else:
    r = '<Q'
import struct
lon = struct.unpack('d', struct.pack(r, int(mycoords[-32:-16], 16)))[0]
lat = struct.unpack('d', struct.pack(r, int(mycoords[-16:], 16)))[0]

but with SQLite, the above query gives me a None where I would expect at least a BLOB. I do get a 60 bytes BLOB if I connect to the database directly like this:

import sqlite3
cn = sqlite3.connect("/home/mario/.bauble/geo-btuu-11.db")
cr = cn.cursor()
cr.execute("select id, coords from plant where coords is not null")
r = cr.fetchall()

In any case I do not know how to handle the BLOB should I manage to retrieve it.

mariotomo
  • 9,438
  • 8
  • 47
  • 66
  • Your code assumes that the database returns a string representation of the hexdump of the blob, but SQLite returns the blob directly. You should be able to unpack it directly. What is `r[1].encode('hex')`? – CL. Oct 30 '16 at 16:12
  • `str(r[0][1]).encode('hex')` gives '0001e6100000e23034098a3d54c085ab12bc605e1e40e23034098a3d54c085ab12bc605e1e407c01000000e23034098a3d54c085ab12bc605e1e40fe'. but then the problem remains that SQLite with SQLAlchemy does not return the BLOB, it just gives `None`. Meaning: my code assumes the behaviour I observe from PostgreSQL+PostGIS, but SQLite returns None. – mariotomo Oct 30 '16 at 17:57
  • So your *actual* question is not about the code you've shown above, but how to make SQLAlchemy to return a blob? – CL. Oct 30 '16 at 20:05
  • I'm not interested in a blob from SQLAlchemy just for the sake of it. Just as I'm asking, I would like to retrieve some geographic information in a spatial database, and I'm using SQLAlchemy, I do not want to use GeoAlchemy, I want to retrieve this information from SQLite just as well as I already can retrieve it from PostgreSQL+PostGIS.. – mariotomo Oct 30 '16 at 22:42

0 Answers0