ForeignKey to reference rows VS several TABLES

[AdaNebot]

In some cases data is in the form:

time	filter	wavelength	flux
12313	B	0.123	0.4
12314	V	0.234	0.5

How to annotate this in VOTable? In the current document we propose separating the information in different tables, one per filter.

• Cons: These time series are difficult to be interpreted as spectral data.

Alternative: according to VOTable Section 10.4 it is possible to use a ForeignKey to reference rows as an alternative to several TABLES.

• Status: alternative mentioned in the text
• Pros: allows to reference rows
• Cons: applications (Aladin, Topcat) need extra code to understand the relationship. De/serialisation of such tables would be difficult. Complications in forwarding via SAMP to other clients with metadata intact would be hard.

The next step to investigate this as an alternative solution would be to expose an example.

[loumir]

If filter B and filter V are described by a their PhotCal GROUPs, one for each ,
we can replace the filter label, B or V, in the filter column by the VOTABLE ID of the PhotCal GROUP, corresponding to the B and V filter description .

Here is a simple VOTABLE example with 3 Gaia filters .

vot-ex2-severalFiltervaluesInOneColumn_Mireille.xml.zip

[AdaNebot]

I like the idea, since it is clean. But in the example I can see that at the column level ref="phot_sys" but there is no such ID.

[loumir]

In fact I am pointing to the Photcal which corresponds to the Filter involved .
it is important that the PhotCal id shows a bit of the Filter ID /name to make things visible...

[AdaNebot]

Yes, I agree on that statement.

But, I don't think it is valid to ref something that is not defined in the VOTable

<FIELD datatype="float" name="magerr" ucd="stat.error;phot.mag;em.opt" unit="mag" ref="phot_sys">

Perhaps it is a typo, but phot_sys is not defined anywhere in the VOTable. Or am I missing something?

[Bonnarel]

My proposal made on the interop list on tuesday is a little different and relies on the ID/ref collaboration.

In the current note the multiband case is serialized as 3 tables written in sequence. Each table has its own PhotCal GROUP attached.
In the attached example (discussed with Ada, look below ) we propose an alternative: a single table with a "FilterIndentifier" column. 3 different Photcal GROUPS are attached to this table and the appropriate Filteridentifier PARAM in each of these group is referring to the column with the same name in the TABLE. The assumption is that photometric metadata given in the GROUP are valid for all rows where values of FilterIndentifier in the row and in the GROUP PARAM match in such a way that it is working like an implicit "ForeignKey" mechanism. Of course this behavior is specific to this annotation note and ahs to be explained in the note

TimeSeries3filtersFB _2-bis.xml.zip

[loumir]

Hi Ada , ok , that one . sorry it is indeed a Typo, a copy/paste I did not examined properly . Here I have a pb because when the filter reference changes from one line to the next , we also would like to group the flux/mag and error values to the photcal item , but line by line . I missed this point . I suppose we have to duplicate flux/mag and errorcolumns for as many filters used in the list and then the usual ID/Ref system is more relevant. This brings me to another point : I am not aware of all table formats we might encounter for timeseries depending on projects. Can we guess a rough estimate for the percentage of projects handling * simple timeseries * multiband time series * interleaved filter format  : #6 * 'catalog of time series'  : the source is referenced for each line and the table can contain several time series for multiple sources Just to be prepared to describe what the note covers in front of the VO community, and argue if necessary. I am sure you have considered this Ada , but cannot find any trace of this in my notes . Should I make it another git hub issue? cheers , Mireille Le 23/04/2020 à 20:19, AdaNebot a écrit :

…

Yes, I agree on that statement. But, I don't think it is valid to |ref| something that is not defined in the |TABLE| |<FIELD datatype="float" name="magerr" ucd="stat.error;phot.mag;em.opt" unit="mag" ref="phot_sys"> | Perhaps it is a typo, but|phot_sys| is not defined anywhere in the |TABLE|. Or am I missing something?

[AdaNebot]

I see this problem of the referencing the multiple columns, that's most likely the reason why @Bonnarel used theFIELD ID=filt in his example for the column with the filter name, it differences it from the other columns. I'm working on an example which takes what I like the most from your ideas. I'll post it here later for feedback.

Your other point, regarding what cases we cover, good idea, I think you are right and that would be another issue.

Thanks!

[Bonnarel]

Hi all, Le 24/04/2020 à 12:05, Mireille LOUYS a écrit :

Hi Ada , ok , that one . sorry it is indeed a Typo, a copy/paste I did not examined properly . Here I have a pb because when the filter reference changes from one line to the next , we also would like to group the flux/mag and error values to the photcal item , but line by line . I missed this point .

I am not sure I get your point here. Visioconf ?

I suppose we have to duplicate flux/mag and errorcolumns for as many filters used in the list. ??? and then the usual ID/Ref system is more relevant. This brings me to another point : I am not aware of all table formats we might encounter for timeseries depending on projects. Can we guess a rough estimate for the percentage of projects handling * simple timeseries * multiband time series * interleaved filter format  : #6 * 'catalog of time series'  : the source is referenced for each line and the table can contain several time series for multiple sources Just to be prepared to describe what the note covers in front of the VO community, and argue if necessary.

Yes I agree. That's also why I wrote the architecture email with 6 examples yesterday evenning (late). Because if we have all these flavors (with the dataproduct_subtype PARAM to tackle them) we have to know how we combine them (or not) in the same VOTABLE document and ccover all this in the note which must be a guide to data providers and application developpers: Here again, I think by writing emails in different threads, sometimes we adress related issues in parallel. And at the moment i don't think anybody looked at these examples I sent last night. So really a visio on this note with a multi slot agenda should be needed Cheers François

…

I am sure you have considered this Ada , but cannot find any trace of this in my notes . Should I make it another git hub issue? cheers , Mireille Le 23/04/2020 à 20:19, AdaNebot a écrit : > > Yes, I agree on that statement. > > But, I don't think it is valid to |ref| something that is not defined > in the |TABLE| > > |<FIELD datatype="float" name="magerr" > ucd="stat.error;phot.mag;em.opt" unit="mag" ref="phot_sys"> | > > Perhaps it is a typo, but|phot_sys| is not defined anywhere in the > |TABLE|. Or am I missing something? > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > <#6 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AICF4GIUPVS74DXASC3QF6TROCBC7ANCNFSM4K3DHW6A>. > -- -- Mireille Louys, MCF (Associate Professor) CDS IPSEO, Images, Laboratoire Icube Observatoire de Strasbourg Telecom Physique Strasbourg 11 rue de l'Université 300, Bd Sebastien Brandt CS 10413 F- 67000-STRASBOURG F-67412 ILLKIRCH Cedex Tel: +33 3 68 85 24 34 — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#6 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMP5LTGQ442SNQHCM6OJVP3ROFQAJANCNFSM4K3DHW6A>.

[AdaNebot]

In the examples as shown by @Bonnarel there are different ways to annotate using the foreignKey approach. Some are explicit and others not.

I have a bit of a personal preference for this one:

GROUP name="foreignKey"  ref="photcal"> 
<FIELdref ref="filtid"/>
<FIELdref ref="filt"/>
</GROUP>

to join two tables:

<TABLE ID="photcal" name="photcal" ucd="phot" utype="PhotDM:PhotCal">...</TABLE>
<TABLE name="TimeSeries">...</TABLE>

Where the first table contains the relevant information concerning the selected photometric system metadata.

But this needs more exploration.

[Bonnarel]

various joints solutions (April 24th)
4 tables (they miss the "dataproduct_type" PARAMS)

In logical order

TimeSeries3filtersTableWithForeignKey.xml : Filters are described in a table with 3 rows. Joints are made on the columns names "FilterIdentifier" in the two tables and is expressed as a foreignKey GROUP.

TimeSeries3filtersTableWithRefID.xml :same tables but ForeignKey is only simulated by a simple ref/ID mechanism betxeen lhe two "FilterIdentifier" columns . These kind of ref/id exist in DataLink between service DEscriptor and main tableand did exit in SSA (DAL extension mechanism)

---

When moving from TABLE for filters definitio to Photcal GROUPS system, it may be useful to consider that a Photcal GROUP is equivalent to a single row TABLE. The row constraints the content of "value" attributes of the 4 PARAMs. The two flavors of FOreignKey mechanisms can still apply. Row selection is equivalent to a joint with a single row.

TimeSeries3filtersGroupsWithForeignKey.xml : Here the PARAM with the FilterIndentifier has to be repeated in each ForeignKey GROUP (there is no PARAMref in VOTAble)

TimeSeries3filtersGroupsWithRefID.xml: Here we have the same reeition of the PARAM "FiliterIdentfier" in each Photcal GROUP than just above. The Ref/id mechanism to the column in the main table has to be interprateted as selecting the rows with the value of this target column matching the one in the PARAM. this mechanism has to be explained in the note. This was the proposal I made on the interop mailing list on April 21st.
TimeSeries3Filters.zip

[Bonnarel]

Laurent and I built a new proposal for the multiband lightcurve made of a various number of tables
The point was again about associating "Photcal" GROUPS to specific rows in a single table selected by the value of a FilterIndentifier column. This to tackle use cases where the providers don't want to have a TABLE per filter but a single TABLE with a filter identifier column.
The proposal came from an idea given by Laurent to replace the ForeignKey mechanisms I proposed earlier (see above) by a line selection mechanism operated in a GROUP associating these rows with Photcal GROUPS. After some discussions, we extended this mechanism to allow also column selections in TABLES or mixed situations in order to annotate various time-series information structured in different ways by the provider in a consistent way. This is similar to VO-DM-lite approach but made with standard VOTAble tags.
We have five different use cases which you will find below in 5 different votable documents in the archive

• 1 simple lightcurve: One Photcal / one column association

• 2 single table with FilterIndentifier column, and a single magnitude column. this is the row/Photcal association use case

• 3 a table per Filter (v=band). (mag column in a table)/ photcal association. This is the use case presented by Ada in the note in its April version.

• 4 is a mix of 2 and 3. There is one band in a table and two other bands in the second table

• 5 several mag columns in the table no filter identifier column. (PhotCal/column association ) iterated
  How does it work ?
  Each association GROUP has an "assoc:subset" utype and défines a cohérent subset in the VOTABLE

             it's composed :of a  PARAM which makes a ref  towards a   Photcal GROUP ( thisPARAM has nothing else than the ref  and a utype towards the  GROUP)
  
                                     of a  GROUP "assoc:selector.*" At the moment we use either selector.column or selector.row.
  
                                       These GROUPS contain a  FIELDref towards the   columns  of interest
  
                                        They also contain a  PARAM which "makes reference to the  considered table (useful when several  tables in the document)
  
                                       And in the selector.row case, another PARAM "selector.row.value" which selects the value to be retained for this "subset" in the column identified above.
  
               utypes are used to avoid fixing names in the spec. These are roles in the VOTABLE. can also be seen as a purely technical data model  .
           
           The                       "association"  datamodel encompasses:
  

GROUP assoc:subset --> PARAM ref towards GROUP (here a Photcal GROUP )

--> GROUP assoc:selector.column or ... (see below)

----> FIELDref towards column
-------> PARAM ref towards considered table "assoc:selector.table"

--> alternatively: GROUP "assoc:selector.row"

----> FIELDref towards considered coulmn

----> PARAM assoc:selector.row.value

-------> PARAM ref towards considered table "assoc:selector.table"

              By the way it's no more required to put  ref from data columns  towards photcal GROUPS. this is repalced by the Table/coumn association in the assoc GROUP itself.

That's all folks . François after interop
group-lite.zip