Re: [RDB] How do I merge datasets from individual MySQL Tables?

[Jonathan Callahan <Jonathan.S.Callahan@xxxxxxxx>]

cch,

After connecting with a database, LAS issues a data request of this form:

SELECT ~vars~ FROM ~table~ WHERE ~constraint~

The <db_table> item specified in the dataset .xml is used for ~table~.

There is currently no way in LAS to specify a table join.  If your datasets are small I would suggest creating a merged table.  This would be the simplest solution.

Otherwise you might be able to modify xml for the combined dataset to specify two tables and then modify las/xml/perl/TMAP/TMAPDBI.pm to do a table join if more than one table (separated by commas?) is used.  I don't recommend this method as it requires you to break core code in LAS.

We'd appreciate knowing some more details about your data so we can decide if this request for table joins is one we should handle in a future version of LAS.  It would also be nice to have a look at your LAS to see how you're using it and what your data looks like.

I hope I've helped you at least understand the nature of the problem.


-- Jon

cch wrote:

Hello all,

I successfully added mysql data in las6.3  following the instructions on http://ferret.pmel.noaa.gov/LASdoc/serve/cache/20.html .

But my boss hopes that the variables belonging to different Tables could consolidated into one dataset.

I tried to move <properties>....</properties> content into <variable> tag,

but I got error message.

The dataset's configure is shown below. (I use one variable for test!)

Anybody knows how to do this work? thanks!

 

<datasets>
 <testTable name="testTable"
            url=""
            lonString="longitude"
            latString="latitude">
  <variables>
   <sst name="sst" units="C">

  <properties>
   <ui>
    <default>file:ui.xml#insituSurface</default>
   </ui>
   <database_access name="MySQL">
    <db_type>mysql</db_type>
    <db_name>testDB</db_name>
    <db_title>testDB</db_title>
    <db_host>localhost.localdomain</db_host>
    <db_login>root</db_login>
    <db_passwd></db_passwd>
    <db_table>testTable</db_table>
    <longitude>lo</longitude>
    <lon_domain>0:360</lon_domain>
    <latitude>la</latitude>
    <depth></depth>
    <depth_units></depth_units>
    <time>time</time>
    <time_sample>19990101</time_sample>
    <profID></profID>
    <metadata>x,y,t</metadata>
    <executable>/usr/bin/asciival</executable>
    <missing>-1.E+34</missing>
    <timeout>80</timeout>
   </database_access>
  </properties>
    <link match="/lasdata/grids/testTable_XYZT_grid"/>
   </sst>
  </variables>
 </testTable>
</datasets>
<grids>
 <testTable_XYZT_grid>
  <link match="/lasdata/axes/testTable_X"/>
  <link match="/lasdata/axes/testTable_Y"/>
  <link match="/lasdata/axes/testTable_T"/>
 </testTable_XYZT_grid>
</grids>
<axes>
 <testTable_X units="degree_east" type="x">
  <arange start="10" step="1" size="71"/>
 </testTable_X>
 <testTable_Y units="degree_north" type="y">
  <arange start="10" step="1" size="71"/>
 </testTable_Y>
 <testTable_T units="month" type="t">
  <arange start="19990101" step="1" size="5"/>
 </testTable_T>
</axes>