Getting Examples/Sample.py to work with LBR ODM2 SQLite db

[emiliom]

@denvaar here's what I'm trying. I have Jeff's ODM2.sqlite db for the Little Bear River, with a single timeseries result. I'm trying to run Examples/Sample.py. I was hoping it would work!

I can connect to (read) the database, and query some objects. But I'll jump to the core and major problem I'm running into.

FYI, the sqlite db only has 1 result. No sweat. I run this statement tsResult = read.getTimeSeriesResultByResultId(1) in line 130. The resulting object matches closely the table definition of TimeSeriesResults. But it's clear that in Sample.py what's expected is an object that includes the associated Results; for example, there are statements like these:

tsResult.ResultTypeCV
tsResult.ProcessingLevelObj.Definition

But tsResult doesn't have any of those Results properties.

Ok, moving on, I try to get the time series result values, like this: tsValues = read.getTimeSeriesResultValuesByResultId(1). It doesn't work, and returns None.

read.getResultById(1) does return the single Results record.

The sqlite database looks fine to me, when I inspect its tables via a SQLite browser.

I also have other problems that seem puzzling. For example, read.getAllAffiliations() returns None, not the affiliations record.

Note that I'm not running Sample.py in full. I'm taking each distinct block and running it in a Jupyter notebook. I'm skipping the "add sampling feature" block, b/c I'm not interested in writing to the database at this time.

Thanks!

[emiliom]

I forgot to mention that read.getAllAffiliations() does work as expected on a PostgreSQL database of mine.

[denvaar]

@emiliom I can confirm the errors you're getting. read.getAllAffiliations() is failing because of the IsPrimaryOrganizationContact field. It's marked as boolean, but the problem is that SQLite does not have a native Boolean data type (see https://www.sqlite.org/datatype3.html) so SQLAlchemy is expecting an integer (1 or 0) here instead. @horsburgh It looks like this column is represented using a BIT datatype in the database. I changed my copy from BIT to INTEGER, and it's working, so maybe we could generate the script using INTEGER's for the boolean fields.

As for the other errors, I am working on those and will keep you updated.

[denvaar]

The line, read.getSamplingFeatureByCode('USU-LBR-Mendon') in the Sample.py file also produces an error. It can be avoided by un-commenting out line 562 and commenting out line 563 in models.py. I am not sure what the repercussions might be if we change that for all other situations. @sreeder should have a better idea than me about which way to go for this particular error.

I will continue to work though these errors.

[emiliom]

Thanks, @denvaar . Good to hear you found the problem with read.getAllAffiliations().

For now, I've deliberately avoided all statements dealing with SamplingFeatures ...

I'm looking forward to seeing what you find about Results and TimeSeriesResultValues!

[denvaar]

@emiliom I was able to get the script to run completely without errors.

Most or all of the errors relating to Results I fixed by adding an intermediary ResultObj in the method chains. For example: tsResult.ResultTypeCV becomes tsResult.ResultObj.ResultTypeCV We must have updated the structure of some of the models without changing the sample script.

Take all of my fixes and findings with a grain of salt, as I don't know the intent/design of the API as closely as @sreeder, so I tried not to make many big changes that might affect other programs that use the API.

I have pushed the changes that I've made, including to the sample script and a few other changes in the models and readService classes. You can browse all of the changes that I have made in my latest commit.

[emiliom]

Fantastic, @denvaar! The changes you describe sound very much like what was missing.

I won't be able to test this until late today; I'll report back by tomorrow morning, hopefully today.

[emiliom]

Success! I was able to reproduce Examples/Sample.py.

FYI, I didn't run the script as-is. I created a Jupyter notebook out of most it, except the Sampling Feature creation block. Also, read.getAllAffiliations() is still returning None, but @denvaar already noted the sqlite-specific reason for it.

I'll clean up and share this notebook later.

I'm closing the issue, as you've resolved all the problems. Thanks so much!

[emiliom]

@denvaar, looking at your commit , I'm wondering about your changes to ODM2/models.py. You uncommented statements flagged as "Geoalchemy2" and commented out ones flagged as (or developed for) "Geoalchemy1". Can you explain why? At least one of the statements you commented out were specifically set up by @sreeder around early December to work with geoalchemy1 instead of geoalchemy2; I vaguely remember that they fixed specific problems, and that we tested that they did what we expected. I haven't run into any errors, but I also haven't tested SamplingFeature geometry fields much since you made these changes. Thanks.

[denvaar]

@emiliom I will look at the geoalchemy code again. I was unsure about this part of the code. I know that @sreeder had mentioned switching back to geoalchemy version 1 instead of 2, but as I mentioned before, I don't know the details about it. It sounds like I should change it back. The errors it generates in the example script are likely to be happening because of the way SamplingFeature's feature geometry is stored in the SQLite database that @horsburgh gave us.

[denvaar]

In the SQLite example script, geometries are inserted as text when they should be well known binaries. That's the source of the errors with Sampling Features. I am working on rewriting @horsburgh 's script, but am experiencing the woes of spatialite.

[emiliom]

Interesting. I'm still not totally clear on when and why spatialite is needed. It's not needed for "read" access -- shapely functionality is good enough for basic things. Can you not use shapely to convert from text (WKT?) to WKB before inserting into Sampling Features??

[emiliom]

FYI, here's a Jupyter notebook where I test/demo access to the sqlite database. It's the notebook adaptation of most of Examples/Sample.py that I had mentioned.

@horsburgh, hopefully that notebook will be somewhat useful to you.

[emiliom]

@horsburgh, now that I've closely examined SamplingFeatures (see #29) in the Little Bear ODM2 SQLite file, I've realized that the geometry coordinates inserted into SamplingFeatures.FeatureGeometry are reversed. In the WKT format they were inserted as, the expected, correct order is lon lat (POINT (x y)), but they were instead inserted as lat lon.

[emiliom]

I'm closing this very old issue. Besides the fact that it's very old, pretty much everything here had been addressed, AND I'm about to remove the Examples/Sample.py sample code and replace it with a more useful Jupyter notebook (in addition to another notebook I already loaded into the Examples directory).