ODM2 Spatialite support

[sreeder]

Need to get the code to work with spatialite databases. it is currently trying to connect with sqlite but that has none of the geospatial functions.

[emiliom]

Rather than create a new issue, I'll post below an exchange on this existing issue. It's also related to #29

[emiliom]

(Feb 12 email from @Castronova):
I’ve talked with @horsburgh and @sreeder quite extensively about how geometries should be represented within an ODM2 RDBMS, and there are two options that I am aware of:

1. represent geometries as plain text (i.e. WKT)
2. express geometry objects as binary objects within a GIS enabled database.

The first option is easy to implement but will require odm2api to convert query results into geometry objects using something like osgeo.gdal or shapely. This approach will probably work fine for 90% of users, however, geometries must be expressed as binary objects within a GIS enabled database if the user wants to perform spatial queries. This is where the use of SpatiaLite comes in. If you look at the SpatialLite documentation, any Sqlite database can be “GIS enabled” by simply loading a compiled library called mod_spatialite. Once this is loaded, spatial queries can be evaluated in a similar manner to PostGIS (e.g. select AsText(geometry) where Intersects(geometry, 'POINT(10 12)')). In the past, we have had difficulty loading the mod_spatialite library, and we were using SpatiaLite since it loads it automatically. I have had some recent success loading mod_spatialite using the apsw module instead of Spatialite, which I can tell you more about this if you are interested.

As far as I know the main issue is getting SqlAlchemy/GeoAlchemy to perform calls to each database using the correct syntax. I have to defer to @sreeder regarding the exact details of what is causing trouble inside the odm2api. The approach that I have taken is to write my own SQL queries using the apsw library and converting the result back into SQLAlchemy objects to work with. Unfortunately, this means that the functions I wrote are specific to SQLite, but I don’t think it would be much effort to simply translate them into other RDBMS’s.

[emiliom]

@Castronova, that's very informative. A question: how can I tell if a SQLite database has mod_spatialite loaded? I'm no SQLite pro. I interact with it mostly through the SQLite Manager Firefox add-on, and programmatically through generic api's and utilities. I used it to examine Jeff's Little Bear ODM2 sample file (see #24 and #29 for discussions on that sample file), and couldn't find any reference to added modules (ie, SQLite Manager might not be able to show that information). I suppose an indirect way could to find out would be to try to use a spatial function in a SELECT statement ...

[emiliom]

(@horsburgh wrote on Feb 16)

My example SQLite database (see #24 and #29) doesn’t use SpatialLite and right now is just storing the geometry as well known text (admittedly with the points in the wrong order as Emilio pointed out earlier, but that I haven’t fixed yet). It actually pre-dates most of the work we have done on feature geometries more recently.

Stephanie or Tony could fill us in more about how to tell if a SQLite database uses SpatialLite – but, in general, the geospatial data would be stored in Well-Known-Binary format instead of a Well-Known-Text string. I think you could use a SQLite database that contains geospatial data stored in WKB without SpatialLite, but you would have to write code to create and/or extract the WKB yourself – whereas SpatialLite does this for you.

[emiliom]

Thanks for that input. After Tony's message, I came to realize what you've confirmed here, that your SQLite database stores the geometry in WKT. I guess one thing that was throwing me off was the fact that the FeatureGeometry field is still defined as a geometry field, but I guess that's where SQLite's fuzzy data types come into play.

I suspect between shapely and geoalchemy the machinery is there to read and write WKB into a SQLite database that doesn't have SpatiaLite installed. I'll have to digest what you and Tony have said about SQLite and SpatiaLite.

[emiliom]

(@Castronova response from Feb 16): I've created an ipython notebook that addresses some of these issues (e.g. fuzzy data types). I'll be on vacation for the rest of the week, but I will try to stay in contact.

[emiliom]

Thank you! The notebook makes things very clear; thanks so much for all the notes you added. Very helpful to learn about "type affinity". That explains a lot. You clearly are a SQLite pro. I'm not. I'll try to think about this later on.

[horsburgh]

@emiliom - I implemented FeatureGeometryWKT in the ODM2 schema as discussed on the phone with @Castronova and @sreeder the other day. I updated the ddl sql scripts in the ODM2 repository and also loaded a new data use case to the ODM2 repository (https://github.com/ODM2/ODM2/tree/master/usecases/WOF_to_ODM2) with my Python scripts for creating a new ODM2.sqlite database, loading the CVs, and then loading a time series of data from a CUAHSI HIS WOF web service into the ODM2.sqlite database. I modified my script to write the latitude and longitude coordinates to the FeatureGeometryWKT field instead of the FeatureGeometry field so I didn't have to spatially enable the SQLite database. I also fixed the order of the point coordinates.