{"action":"created","issue":{"url":"https://api.github.com/repos/ODM2/ODM2PythonAPI/issues/29","repository_url":"https://api.github.com/repos/ODM2/ODM2PythonAPI","labels_url":"https://api.github.com/repos/ODM2/ODM2PythonAPI/issues/29/labels{/name}","comments_url":"https://api.github.com/repos/ODM2/ODM2PythonAPI/issues/29/comments","events_url":"https://api.github.com/repos/ODM2/ODM2PythonAPI/issues/29/events","html_url":"https://github.com/ODM2/ODM2PythonAPI/issues/29","id":130594911,"number":29,"title":"SamplingFeature read failures with SQLite","user":{"login":"emiliom","id":742403,"avatar_url":"https://avatars.githubusercontent.com/u/742403?v=3","gravatar_id":"","url":"https://api.github.com/users/emiliom","html_url":"https://github.com/emiliom","followers_url":"https://api.github.com/users/emiliom/followers","following_url":"https://api.github.com/users/emiliom/following{/other_user}","gists_url":"https://api.github.com/users/emiliom/gists{/gist_id}","starred_url":"https://api.github.com/users/emiliom/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/emiliom/subscriptions","organizations_url":"https://api.github.com/users/emiliom/orgs","repos_url":"https://api.github.com/users/emiliom/repos","events_url":"https://api.github.com/users/emiliom/events{/privacy}","received_events_url":"https://api.github.com/users/emiliom/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":8,"created_at":"2016-02-02T06:57:55Z","updated_at":"2016-02-17T23:08:47Z","closed_at":null,"body":"In #24 we went back forth with some changes to [ODM2/models.py](https://github.com/ODM2/ODM2PythonAPI/blob/master/odm2api/ODM2/models.py) and possibly [ODM2/services/readService.py](https://github.com/ODM2/ODM2PythonAPI/blob/master/odm2api/ODM2/services/readService.py) regarding SamplingFeatures and the use of geoalchemy 1. A couple of merges were involved. These changes sort-of continued in #27, and have some linkage earlier to #13.\r\n\r\nBefore that, we got [Examples/Sample.py](https://github.com/ODM2/ODM2PythonAPI/blob/master/Examples/Sample.py) to work top to bottom with its SQLite sample database (FYI, I never tested the SamplingFeature creation code). Here's a [working IPython notebook version I made on 1/21](http://nbviewer.jupyter.org/github/emiliom/stuff/blob/master/odm2api_sample_fromsqlite.ipynb)\r\n\r\nNow, I'm unable to access `ReadODM2.getSamplingFeature` methods to work anymore. They work on PostgreSQL, but not SQLite. With @horsburgh's ODM2 Little Bear River sqlite sample file, a method request such as `getSamplingFeaturesByType('Site')` leads to this error:\r\n```\r\n(sqlite3.OperationalError) no such function: AsBinary [SQL: u'SELECT \r\nsamplingfeatures.samplingfeatureid AS odm2_samplingfeatures_samplingfeatureid, \r\nsamplingfeatures.samplingfeatureuuid AS odm2_samplingfeatures_samplingfeatureuuid, \r\nsamplingfeatures.samplingfeaturetypecv AS odm2_samplingfeatures_samplingfeaturetypecv, \r\nsamplingfeatures.samplingfeaturecode AS odm2_samplingfeatures_samplingfeaturecode, \r\nsamplingfeatures.samplingfeaturename AS odm2_samplingfeatures_samplingfeaturename, samplingfeatures.samplingfeaturedescription AS odm2_samplingfeatures_samplingfeaturedescription, \r\nsamplingfeatures.samplingfeaturegeotypecv AS odm2_samplingfeatures_samplingfeaturegeotypecv, \r\nsamplingfeatures.elevation_m AS odm2_samplingfeatures_elevation_m, \r\nsamplingfeatures.elevationdatumcv AS odm2_samplingfeatures_elevationdatumcv, \r\nAsBinary(samplingfeatures.featuregeometry) AS odm2_samplingfeatures_featuregeometry\r\n\\nFROM samplingfeatures \r\n\\nWHERE samplingfeatures.samplingfeaturetypecv = ?'] [parameters: ('site',)]\r\n```\r\n\r\nNote the problem with the `AsBinary` SQL function. Probing a bit further, I see the error comes from `sqlalchemy/engine/default.py`, specifically line 450 below:\r\n```Python\r\n449     def do_execute(self, cursor, statement, parameters, context=None):\r\n450         cursor.execute(statement, parameters)\r\n```\r\n\r\nI've gone through the relevant commit history for [ODM2/models.py](https://github.com/ODM2/ODM2PythonAPI/commits/master/odm2api/ODM2/models.py) and [ODM2/services/readService.py](https://github.com/ODM2/ODM2PythonAPI/commits/master/odm2api/ODM2/services/readService.py) since early December, and can't find anything that's different from a time in mid January when things were working fine. I'm stumped.\r\n\r\n@denvaar has moved to greener pastures and @sreeder is still away on maternity leave. So, for now I'm creating this issue as documentation of the problem. I'll keep tackling it, and will add more information in a bit."},"comment":{"url":"https://api.github.com/repos/ODM2/ODM2PythonAPI/issues/comments/185450819","html_url":"https://github.com/ODM2/ODM2PythonAPI/issues/29#issuecomment-185450819","issue_url":"https://api.github.com/repos/ODM2/ODM2PythonAPI/issues/29","id":185450819,"user":{"login":"emiliom","id":742403,"avatar_url":"https://avatars.githubusercontent.com/u/742403?v=3","gravatar_id":"","url":"https://api.github.com/users/emiliom","html_url":"https://github.com/emiliom","followers_url":"https://api.github.com/users/emiliom/followers","following_url":"https://api.github.com/users/emiliom/following{/other_user}","gists_url":"https://api.github.com/users/emiliom/gists{/gist_id}","starred_url":"https://api.github.com/users/emiliom/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/emiliom/subscriptions","organizations_url":"https://api.github.com/users/emiliom/orgs","repos_url":"https://api.github.com/users/emiliom/repos","events_url":"https://api.github.com/users/emiliom/events{/privacy}","received_events_url":"https://api.github.com/users/emiliom/received_events","type":"User","site_admin":false},"created_at":"2016-02-17T23:08:47Z","updated_at":"2016-02-17T23:08:47Z","body":"@cdesyoun, I'm really glad the `shape()` function works for you! Thanks also for pointing out the bug when FeatureGeometry is null; you're right, it's a bug. I'll fix this soon, hopefully tonight, and will push the fix to the `sfgeometry_em_1` branch. I'll think about merging that branch into master around Friday."}}