How does PostgreSQL store images? How to access them?

Support
1

Hi,

we installed our own ODKAggregate (RC2) server with Tomcat and
PostgreSQL under WIN7 OS. We use the ODK Collect v1.1.7-RC1.apk on the
mobile side. Everything works fine (except for the csv export).

Now we have some questions on how we can access the data with our own
programs.
How does ODKAggregate and PostgreSQL store images? How can we access
them?

Is there any chance to access them via the file system?
If PostgreSQL stores them as BLOBs, how can we export them from the
database?

Thanks for your support!

2

Can you file an issue on the ODK google code site describing you issue
with csv export?

··· On Wed, Oct 5, 2011 at 7:34 AM, LETSGO CeLeKT wrote: > Hi, > > we installed our own ODKAggregate (RC2) server with Tomcat and > PostgreSQL under WIN7 OS. We use the ODK Collect v1.1.7-RC1.apk on the > mobile side. Everything works fine (except for the csv export). > > Now we have some questions on how we can access the data with our own > programs. > How does ODKAggregate and PostgreSQL store images? How can we access > them? > > Is there any chance to access them via the file system? > If PostgreSQL stores them as BLOBs, how can we export them from the > database? > > Thanks for your support! > > -- > Post: opendatakit@googlegroups.com > Unsubscribe: opendatakit+unsubscribe@googlegroups.com > Options: http://groups.google.com/group/opendatakit?hl=en >
3

How does ODKAggregate and PostgreSQL store images? How can we access
them?
binary data are stored as blobs. the most user-friendly way to extract
them is to use odk briefcase
(http://code.google.com/p/opendatakit/downloads/list). export to csv
will only give you urls to
the binary files.

Is there any chance to access them via the file system?
currently, no.

If PostgreSQL stores them as BLOBs, how can we export them from the
database?
if you want to access them programmatically, you'll have to write code
to do that. use odk briefcase if you just want to dump the data.

4

You can use some programming to extract the data:

<?php $images_path= "/tmp" $sql = "SELECT \"_URI\" FROM YOURSCHEMA.\"BUILD_YOURFORM_YOURFORMUUID_CORE\""; $pgconn = pgConnection(); $recordSet = $pgconn->prepare($sql); $recordSet->execute(); while ($row = $recordSet->fetch(PDO::FETCH_ASSOC)) { $sql2 = "SELECT \"_URI\",\"VALUE\" FROM YOURSCHEMA.\"BUILD_YOURFORM_YOURFORMUUID_PHOTOSET_BLB\" WHERE \"_TOP_LEVEL_AURI\"='".$row['_URI'] . "'"; $recordSet2 = $pgconn->prepare($sql2); $recordSet2->execute(); $i = 0; $filenames = array(); while ($row2 = $recordSet2->fetch(PDO::FETCH_ASSOC)) { $i++; $filename= "file_" . $row2['_URI']; $image = null; while (!feof($row2['VALUE'])) { $image .= fread($row2['VALUE'], 2048); } fclose($row2['VALUE']); $myFile = $images_path . "/" . $filename . ".jpg"; $fh = fopen($myFile, 'w') or die("can't open file"); fwrite($fh, $image); fclose($fh); } } ?>
··· -- *Yesid Carrillo*

On Wed, Oct 5, 2011 at 9:34 AM, LETSGO CeLeKT letsgo.space@gmail.comwrote:

Hi,

we installed our own ODKAggregate (RC2) server with Tomcat and
PostgreSQL under WIN7 OS. We use the ODK Collect v1.1.7-RC1.apk on the
mobile side. Everything works fine (except for the csv export).

Now we have some questions on how we can access the data with our own
programs.
How does ODKAggregate and PostgreSQL store images? How can we access
them?

Is there any chance to access them via the file system?
If PostgreSQL stores them as BLOBs, how can we export them from the
database?

Thanks for your support!

--
Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

5

Also, the new breifcase application scheduled to be released later
today (U.S. pacific time) will extract your binary data from
Aggregate.

Cheers,
Waylon

··· On Wed, Oct 5, 2011 at 8:39 AM, W. Brunette wrote: > Can you file an issue on the ODK google code site describing you issue > with csv export? > > On Wed, Oct 5, 2011 at 7:34 AM, LETSGO CeLeKT wrote: >> Hi, >> >> we installed our own ODKAggregate (RC2) server with Tomcat and >> PostgreSQL under WIN7 OS. We use the ODK Collect v1.1.7-RC1.apk on the >> mobile side. Everything works fine (except for the csv export). >> >> Now we have some questions on how we can access the data with our own >> programs. >> How does ODKAggregate and PostgreSQL store images? How can we access >> them? >> >> Is there any chance to access them via the file system? >> If PostgreSQL stores them as BLOBs, how can we export them from the >> database? >> >> Thanks for your support! >> >> -- >> Post: opendatakit@googlegroups.com >> Unsubscribe: opendatakit+unsubscribe@googlegroups.com >> Options: http://groups.google.com/group/opendatakit?hl=en >> >
6

To further add on, Aggregate exposes the image via the web so you can
grab the data from that link if you want to write a script instead of
writing a SQL statement for postgres.

Waylon

··· On Wed, Oct 5, 2011 at 3:31 PM, Yaw Anokwa wrote: >> How does ODKAggregate and PostgreSQL store images? How can we access >> them? > binary data are stored as blobs. the most user-friendly way to extract > them is to use odk briefcase > (http://code.google.com/p/opendatakit/downloads/list). export to csv > will only give you urls to > the binary files. > >> Is there any chance to access them via the file system? > currently, no. > >> If PostgreSQL stores them as BLOBs, how can we export them from the >> database? > if you want to access them programmatically, you'll have to write code > to do that. use odk briefcase if you just want to dump the data. > > -- > Post: opendatakit@googlegroups.com > Unsubscribe: opendatakit+unsubscribe@googlegroups.com > Options: http://groups.google.com/group/opendatakit?hl=en >
7

I reported the csv error on the Google Code site (issue 359).

Thank your replying so quickly, I will have a look at ODK Briefcase.
And I will come back to you if we have any problems with the SQL
queries.

BTW How would a MySQL Database store the images?

··· On Oct 6, 12:45 am, "W. Brunette" wrote: > To further add on, Aggregate exposes the image via the web so you can > grab the data from that link if you want to write a script instead of > writing a SQL statement for postgres. > > Waylon > > > > > > > > On Wed, Oct 5, 2011 at 3:31 PM, Yaw Anokwa wrote: > >> How does ODKAggregate and PostgreSQL store images? How can we access > >> them? > > binary data are stored as blobs. the most user-friendly way to extract > > them is to use odk briefcase > > (http://code.google.com/p/opendatakit/downloads/list). export to csv > > will only give you urls to > > the binary files. > > >> Is there any chance to access them via the file system? > > currently, no. > > >> If PostgreSQL stores them as BLOBs, how can we export them from the > >> database? > > if you want to access them programmatically, you'll have to write code > > to do that. use odk briefcase if you just want to dump the data. > > > -- > > Post: opendatakit@googlegroups.com > > Unsubscribe: opendatakit+unsubscribe@googlegroups.com > > Options:http://groups.google.com/group/opendatakit?hl=en
8

all the dbs we support store the images as blobs.

··· On Thu, Oct 6, 2011 at 00:49, LETSGO CeLeKT wrote: > I reported the csv error on the Google Code site (issue 359). > > Thank your replying so quickly, I will have a look at ODK Briefcase. > And I will come back to you if we have any problems with the SQL > queries. > > BTW How would a MySQL Database store the images? > > > > On Oct 6, 12:45 am, "W. Brunette" wrote: >> To further add on, Aggregate exposes the image via the web so you can >> grab the data from that link if you want to write a script instead of >> writing a SQL statement for postgres. >> >> Waylon >> >> >> >> >> >> >> >> On Wed, Oct 5, 2011 at 3:31 PM, Yaw Anokwa wrote: >> >> How does ODKAggregate and PostgreSQL store images? How can we access >> >> them? >> > binary data are stored as blobs. the most user-friendly way to extract >> > them is to use odk briefcase >> > (http://code.google.com/p/opendatakit/downloads/list). export to csv >> > will only give you urls to >> > the binary files. >> >> >> Is there any chance to access them via the file system? >> > currently, no. >> >> >> If PostgreSQL stores them as BLOBs, how can we export them from the >> >> database? >> > if you want to access them programmatically, you'll have to write code >> > to do that. use odk briefcase if you just want to dump the data. >> >> > -- >> > Post: opendatakit@googlegroups.com >> > Unsubscribe: opendatakit+unsubscribe@googlegroups.com >> > Options:http://groups.google.com/group/opendatakit?hl=en > > -- > Post: opendatakit@googlegroups.com > Unsubscribe: opendatakit+unsubscribe@googlegroups.com > Options: http://groups.google.com/group/opendatakit?hl=en >
9

For more detail, see
http://code.google.com/p/opendatakit/wiki/AggregateDatabaseStructure

Mitch

··· On Thu, Oct 6, 2011 at 7:41 AM, Yaw Anokwa wrote:

all the dbs we support store the images as blobs.

On Thu, Oct 6, 2011 at 00:49, LETSGO CeLeKT letsgo.space@gmail.com wrote:

I reported the csv error on the Google Code site (issue 359).

Thank your replying so quickly, I will have a look at ODK Briefcase.
And I will come back to you if we have any problems with the SQL
queries.

BTW How would a MySQL Database store the images?

On Oct 6, 12:45 am, "W. Brunette" wbrune...@gmail.com wrote:

To further add on, Aggregate exposes the image via the web so you can
grab the data from that link if you want to write a script instead of
writing a SQL statement for postgres.

Waylon

On Wed, Oct 5, 2011 at 3:31 PM, Yaw Anokwa yano...@gmail.com wrote:

How does ODKAggregate and PostgreSQL store images? How can we access
them?
binary data are stored as blobs. the most user-friendly way to extract
them is to use odk briefcase
(http://code.google.com/p/opendatakit/downloads/list). export to csv
will only give you urls to
the binary files.

Is there any chance to access them via the file system?
currently, no.

If PostgreSQL stores them as BLOBs, how can we export them from the
database?
if you want to access them programmatically, you'll have to write code
to do that. use odk briefcase if you just want to dump the data.

--
Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options:http://groups.google.com/group/opendatakit?hl=en

--
Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

--
Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

--
Mitch Sundt
Software Engineer
http://www.OpenDataKit.org
University of Washington
mitchellsundt@gmail.com

10

For technical issues such as these, please ask them on the
opendatakit-developers@ list.

Mitch

··· On Thu, Oct 6, 2011 at 11:20 AM, Mitch Sundt wrote:

For more detail, see
http://code.google.com/p/opendatakit/wiki/AggregateDatabaseStructure

Mitch

On Thu, Oct 6, 2011 at 7:41 AM, Yaw Anokwa yanokwa@gmail.com wrote:

all the dbs we support store the images as blobs.

On Thu, Oct 6, 2011 at 00:49, LETSGO CeLeKT letsgo.space@gmail.com wrote:

I reported the csv error on the Google Code site (issue 359).

Thank your replying so quickly, I will have a look at ODK Briefcase.
And I will come back to you if we have any problems with the SQL
queries.

BTW How would a MySQL Database store the images?

On Oct 6, 12:45 am, "W. Brunette" wbrune...@gmail.com wrote:

To further add on, Aggregate exposes the image via the web so you can
grab the data from that link if you want to write a script instead of
writing a SQL statement for postgres.

Waylon

On Wed, Oct 5, 2011 at 3:31 PM, Yaw Anokwa yano...@gmail.com wrote:

How does ODKAggregate and PostgreSQL store images? How can we access
them?
binary data are stored as blobs. the most user-friendly way to
extract
them is to use odk briefcase
(http://code.google.com/p/opendatakit/downloads/list). export to csv
will only give you urls to
the binary files.

Is there any chance to access them via the file system?
currently, no.

If PostgreSQL stores them as BLOBs, how can we export them from the
database?
if you want to access them programmatically, you'll have to write
code
to do that. use odk briefcase if you just want to dump the data.

--
Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options:http://groups.google.com/group/opendatakit?hl=en

--
Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

--
Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

--
Mitch Sundt
Software Engineer
http://www.OpenDataKit.org
University of Washington
mitchellsundt@gmail.com

--
Mitch Sundt
Software Engineer
http://www.OpenDataKit.org
University of Washington
mitchellsundt@gmail.com