Relational tables

Dear all,

Is it possible for ODK to write to multiple relational tables from a
single form? For example, if I have a survey with "farmers" and
"observations", in which there might be multiple observations per
farmer, is it possible to write to the tables separately, and then use
a unique identifier to link them?

On a somewhat separate topic, is there a direct link from aggregate to
the generated KML file that can be used with Google maps? If so, how
should the URL be constructed?

Thanks!

Best,
David

david,

with the xform, you can create something like a household form, and
within that form you can have looping prompts about all the people in
that household.
http://code.google.com/p/opendatakit/source/browse/Household+Survey.xml?repo=forms
does something like that.

the household prompts (say location, picture, etc) and set of
repeating prompts for the people in the household (say child name and
favorite color) will be stored in aggregate as one submission. so you
will see the household information, when you get to the part about
repeating prompts, clicking on the link will give you all the answers
for the children in the household.

now, if you want to be able to submit individual (maybe even
unrelated) forms to aggregate and somehow tie them together on a
particular column (like id or imei), you can't really do that in
aggregate. aggregate is designed just to store and forward/export the
data. that said, if you stream the data to fusion tables, you should
be able to do some of those kinds of operations. i haven't tried it
lately, but i'm sure the fusion tables team can help you there...

as to the kml, waylon or mitch will correct me if i'm wrong, but i
don't think you can get a url of that sort. until they confirm, your
best bet is to download the kml and then in google maps, you should be
able to create a new map and upload the kml there.

yaw

··· On Fri, Apr 8, 2011 at 23:21, David wrote: > Dear all, > > Is it possible for ODK to write to multiple relational tables from a > single form? For example, if I have a survey with "farmers" and > "observations", in which there might be multiple observations per > farmer, is it possible to write to the tables separately, and then use > a unique identifier to link them? > > On a somewhat separate topic, is there a direct link from aggregate to > the generated KML file that can be used with Google maps? If so, how > should the URL be constructed? > > Thanks! > > Best, > David > > -- > Post: opendatakit@googlegroups.com > Unsubscribe: opendatakit+unsubscribe@googlegroups.com > Options: http://groups.google.com/group/opendatakit?hl=en >

Yaw,

Thanks for your reply.

The context that I'm considering is to prepare for what will eventually be a
repeat survey. So, what we would want is to have a master table of "farm
characteristics", which will not change, and then to have "observations" at
different point in time for the farms. So, the relational table of
observations would have multiple rows per farm record eventually, but only
the first row would be defined during the first survey. Subsequent
resurveys would then add additional records to the "observations" table with
an identifier that matches that assigned to the farm in the farm table.

The tables could be merged again in Fusion Tables. My question is whether a
single form can send data for specific fields to different tables in
Aggregate without the use of a repeat loop. Thanks again!

Best,
David

··· On Mon, Apr 11, 2011 at 11:57 AM, Yaw Anokwa wrote:

david,

with the xform, you can create something like a household form, and
within that form you can have looping prompts about all the people in
that household.

http://code.google.com/p/opendatakit/source/browse/Household+Survey.xml?repo=forms
does something like that.

the household prompts (say location, picture, etc) and set of
repeating prompts for the people in the household (say child name and
favorite color) will be stored in aggregate as one submission. so you
will see the household information, when you get to the part about
repeating prompts, clicking on the link will give you all the answers
for the children in the household.

now, if you want to be able to submit individual (maybe even
unrelated) forms to aggregate and somehow tie them together on a
particular column (like id or imei), you can't really do that in
aggregate. aggregate is designed just to store and forward/export the
data. that said, if you stream the data to fusion tables, you should
be able to do some of those kinds of operations. i haven't tried it
lately, but i'm sure the fusion tables team can help you there...

as to the kml, waylon or mitch will correct me if i'm wrong, but i
don't think you can get a url of that sort. until they confirm, your
best bet is to download the kml and then in google maps, you should be
able to create a new map and upload the kml there.

yaw

On Fri, Apr 8, 2011 at 23:21, David david.raitzer@gmail.com wrote:

Dear all,

Is it possible for ODK to write to multiple relational tables from a
single form? For example, if I have a survey with "farmers" and
"observations", in which there might be multiple observations per
farmer, is it possible to write to the tables separately, and then use
a unique identifier to link them?

On a somewhat separate topic, is there a direct link from aggregate to
the generated KML file that can be used with Google maps? If so, how
should the URL be constructed?

Thanks!

Best,
David

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

The short answers are no.

(1) W.r.t. linking, Aggregate considers each submission as a separate
isolated data item. You would need to do the linking of the related
submission records through other means (e.g., through a data field holding a
unique ID that your organization controlled). You'd have one form that
captured the initial farm characteristics, then have a separate form for
each visit into which you entered the unique id for the original farm
characteristics submission.

You would then export the datasets separately to Fusion Tables, then
manually apply a "Merge" to join these fusion tables by that data field.

(2) W.r.t. kml, no. Furthermore, this is changing between 0.9.x and 1.0.
In 1.0, you generate the file, then it has a specific URL for downloading
(however, the contents does not get updated as new data comes in). That URL
changes each time you generate a new kml file (it is unique to the moment
you issue the generation request).

Mitch

··· On Sun, Apr 10, 2011 at 10:55 PM, David A. Raitzer wrote:

Yaw,

Thanks for your reply.

The context that I'm considering is to prepare for what will eventually be
a repeat survey. So, what we would want is to have a master table of "farm
characteristics", which will not change, and then to have "observations" at
different point in time for the farms. So, the relational table of
observations would have multiple rows per farm record eventually, but only
the first row would be defined during the first survey. Subsequent
resurveys would then add additional records to the "observations" table with
an identifier that matches that assigned to the farm in the farm table.

The tables could be merged again in Fusion Tables. My question is whether
a single form can send data for specific fields to different tables in
Aggregate without the use of a repeat loop. Thanks again!

Best,
David

On Mon, Apr 11, 2011 at 11:57 AM, Yaw Anokwa yanokwa@gmail.com wrote:

david,

with the xform, you can create something like a household form, and
within that form you can have looping prompts about all the people in
that household.

http://code.google.com/p/opendatakit/source/browse/Household+Survey.xml?repo=forms
does something like that.

the household prompts (say location, picture, etc) and set of
repeating prompts for the people in the household (say child name and
favorite color) will be stored in aggregate as one submission. so you
will see the household information, when you get to the part about
repeating prompts, clicking on the link will give you all the answers
for the children in the household.

now, if you want to be able to submit individual (maybe even
unrelated) forms to aggregate and somehow tie them together on a
particular column (like id or imei), you can't really do that in
aggregate. aggregate is designed just to store and forward/export the
data. that said, if you stream the data to fusion tables, you should
be able to do some of those kinds of operations. i haven't tried it
lately, but i'm sure the fusion tables team can help you there...

as to the kml, waylon or mitch will correct me if i'm wrong, but i
don't think you can get a url of that sort. until they confirm, your
best bet is to download the kml and then in google maps, you should be
able to create a new map and upload the kml there.

yaw

On Fri, Apr 8, 2011 at 23:21, David david.raitzer@gmail.com wrote:

Dear all,

Is it possible for ODK to write to multiple relational tables from a
single form? For example, if I have a survey with "farmers" and
"observations", in which there might be multiple observations per
farmer, is it possible to write to the tables separately, and then use
a unique identifier to link them?

On a somewhat separate topic, is there a direct link from aggregate to
the generated KML file that can be used with Google maps? If so, how
should the URL be constructed?

Thanks!

Best,
David

--
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


University of Washington
mitchellsundt@gmail.com