Hi every one,
i would like a simple MySQL statement for retrieving image from ODK
aggregate.
This is more appropriate for the opendatakit-developers@ list. Moving the
question there.
···
On Mon, Aug 20, 2012 at 5:23 AM, IMOBI_2 wrote:Hi every one,
i would like a simple MySQL statement for retrieving image from ODK
aggregate.--
Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en
--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com
MySQL has various limits on the length of data that can be stored in an
image. Prior to 1.2, we limited the size to 65KB per "chunk" stored. Image
files are split into perhaps a dozen or more such chunks. For 1.2, we
increased this to an arbitrary size, but have since found that the MySQL
server and communications infrastructure are not configured to handle
arbitrarily large images, so we will return to this smaller chunk size in
our next ODK Aggregate release. Here is a query that will produce a result
set, in order, of the binary data ('data') that need to be concatenated to
reconstruct the image:
select tblb.VALUE data, tref.PART part, tbin.UNROOTED_FILE_PATH filename,
tbin.CONTENT_TYPE, tbin._URI from
YOUR_TABLE_bin tbin, YOUR_TABLE_ref tref, YOUR_TABLE_blb tblb where
tbin._URI = tref._DOM_AURI and tref._SUB_AURI = tblb._URI order by
tbin._URI asc, tref.PART asc
You would add a filter by the tbin._TOP_LEVEL_AURI to restrict this to an
image for a particular instanceID. If the image is in a repeat group, this
would group and return all the image data from within the repeat group,
though the order of the return of the images would be arbitrary.
···
---------------- The easiest way to extract an image is to construct a URL that references the image and access the image through the ODK Aggregate website. If you don't want to deal with authentication, you can enable anonymous access to images via the Site Admin / Preferences page by checking the "Allow anonymous retrieval of images, audio and video data" checkbox. This then can reassemble the image from all the chunks and present it to your application.The way the URL is constructed is:
https://your.org/view/binaryData?blobKey=BLOB_KEY
where BLOB_KEY is the URL encoded string composed of:
FORM_ID[@version=VERSION and
@uiVersion=null]/TLE[@key=INSTANCE_ID]/PATH/TO/IMAGE
For GeoTagger v2, from opendatakit.appspot.com, the form instance
definition is:
So the URL would be constructed as:
geo_tagger_v2[@version=null and
@uiVersion=null]/geotagger[@key=uuid:0504c052-d16a-4b5b-89e5-148daafcaadd]/Image
where "uuid:0504c052-d16a-4b5b-89e5-148daafcaadd" is the primary key
referencing one of the submissions. It is the _TOP_LEVEL_AURI in the
..._BIN table.
The resulting URL, after URL encoding this BLOB_KEY, is then:
Mitch
On Mon, Aug 20, 2012 at 5:23 AM, IMOBI_2 jackson@imobi.co.za wrote:
Hi every one,
i would like a simple MySQL statement for retrieving image from ODK
aggregate.--
Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en
--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com
You need to pull out the binary data from the sequence of
IMAGE_PHOTO_BLB.VALUE byte arrays, either store that somewhere and use the
filename in the tag or base64 encode it and embed that directly in
the tag.
Note that the unrooted filename is not unique. While unlikely, it is
possible it can be used by several submissions during large and long data
collection efforts.
Mitch
···
On Wed, Aug 22, 2012 at 12:27 PM, wrote:Thanks mitch,
Please check the attached file,i failed to identify the image source.
--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com
The simple answer is yes -- otherwise ODK Aggregate would not be able to
retrieve them 
If you are working with a repeat group, you need to pay attention to the
_PARENT_AURI value. That will point to the specific row in the repeat group
containing the image.
···
On Tue, Sep 4, 2012 at 10:51 AM, wrote:Hi Mitch i would like to know whether it's possible to retrieve a specific
image from a repeat group.I have attached a simple MySQL query,the only
problem with it is,it displays all the images in the Groups.thanks
--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com