database design Help from graph

bigdaddysheikh

Hey guys,

I am trying to develop an application but am confused on how I can develop the database structure. The information that is being inserted in the database is from a numerical graph with information from X and Y axis.

For example cell x:1, y:1 = 5, x:1, y:2 =10. I then want to extract the value by selecting those cooardinates. So for example, when I find x:1 y:1, the result should be 5. I was going to make a simple database but am confused on how I can relate the values of the x,y together?

Thanks in advance.

djjjozsi

LEts start like this..

coord
coord_id id, auto increment primary
x (int)
y (int)
x1 (int)
y1 (int)
x1_y1

objects
object_id id, auto increment primary
object_name
coordinates_id

<?
$hostname="***";
$user="***";
$pass="****";
$dbase="***";$connection=mysql_connect("$hostname","$user","$pass");
$db=mysql_select_db($dbase,$connection);


$x=5;
$x=1;
$sql="SELECT * FROM objects INNER JOIN coord ON objects.coordinates_id=coord.coord_id
GROUP BY objects.object_name,coord.x1_y1 HAVING (coord.x =$x AND coord.y=$y);";
$result=mysql_query($sql);
if(mysql_num_rows($result))
{
print '<table border="1" cellspacing="5" cellpadding="5">
<tr>
<th scope="row">Object name</th>
<th scope="row">x1</th>
<th scope="row">y1</th>
</tr>
';

while($row=mysql_fetch_assoc($result))
{
print '
<tr>
<td>'.$row["object_name"].'</td>
<td>'.$row["x1"].'</td>
<td>'.$row["y1"].'</td>
</tr>

';
} //while end
print '</table>';
} //if result is bigger than 0
else
print 'No result...';

?>

bigdaddysheikh

djjjozsi;10891033 wrote:

LEts start like this..

coord
coord_id id, auto increment primary
x (int)
y (int)
x1 (int)
y1 (int)
x1_y1

objects
object_id id, auto increment primary
object_name
coordinates_id

<?
$hostname="***";
$user="***";
$pass="****";
$dbase="***";$connection=mysql_connect("$hostname","$user","$pass");
$db=mysql_select_db($dbase,$connection);


$x=5;
$x=1;
$sql="SELECT * FROM objects INNER JOIN coord ON objects.coordinates_id=coord.coord_id
GROUP BY objects.object_name,coord.x1_y1 HAVING (coord.x =$x AND coord.y=$y);";
$result=mysql_query($sql);
if(mysql_num_rows($result))
{
print '<table border="1" cellspacing="5" cellpadding="5">
<tr>
<th scope="row">Object name</th>
<th scope="row">x1</th>
<th scope="row">y1</th>
</tr>
';

while($row=mysql_fetch_assoc($result))
{
print '
<tr>
<td>'.$row["object_name"].'</td>
<td>'.$row["x1"].'</td>
<td>'.$row["y1"].'</td>
</tr>

';
} //while end
print '</table>';
} //if result is bigger than 0
else
print 'No result...';

?>

Hey,

thank you very much for your response but am a bit clear about the setup.

coord
coord_id id, auto increment primary
x (int)
y (int)
x1 (int)
y1 (int)
x1_y1

Is that the database table structure? Will I also need to setup x2_y2....xn_yn? I have a 10 X 10 table so you can imagine all the values and columns that need to be setup. Is there a more efficient way to struture the table?

igorlopez

Sorry for jumping in here but I can relate to your question in the sense that it appears to represent a surface, i.e a function in 2 dimensions z = f(x,y) .
You say that the data for the table is 10 by 10 which hints to me that it comes from a regular grid and if so the database is very easy where you have 10 records for table xdata and 10 records for table ydata and 100 records for table zdata.

xdata:
x_id
x_val

ydata:
y_id
y_val

z_data:
z_id
z_val
x_ref
y_ref

One note though is that using a database for such type of data if it represents a surface is a little bit overkill when data is regularly spaced in a grid(x and y are two one dimensional arrays and z is a two dimensional array) but it boils down to what your usage is. Normally this type of data is used for lookup tables where such a function is quite simple when the data is properly structured (ordered).

On the other hand if the data is irregularly spaced a data base is justifiable and even handy. In that case you have no use of separate tables for x and y and everything goes inside the zdata table but with reference to x and y replaced by the actual coordinate values.

z_data:
z_id
z_val
x_val
y_val

Interpolation over this type of data is in the way djjjozi has explained, you need to find the neighbor points to the coordinate (x,y) you are interested in by performing queries on the x_val and the y_val fields and this is actually quite complicated to do efficiently.
Brute force calculation can give you the nearest points (records) but you also need to make sure your coordinate is 'inside' the convex hull of the found record coordinates to avoid extrapolation errors. If you search for Delaunay Triangulation you will get a wealth of information.

Good Luck with your application.

djjjozsi

if it saying some error, lets check and correct them 🙂 i've not tested ...

this is the database structure:

CREATE TABLE coordinates (
id int(6) NOT NULL auto_increment,x int(11) NOT NULL, y int(11) NOT NULL, longitude_latitude int(11) NOT NULL
,PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM AUTO_INCREMENT=1;

If you want to select a value for x and y, you will get like this:

<?
error_reporting(E_ERROR | E_PARSE);
ini_set("display_errors", 1);

include("connect.php");  //lets connect to the database...
if($_POST)
{
$x = (int)($_POST['x']);
$y = (int)($_POST['y']);

$query="SELECT * FROM coordinates WHERE x=$x AND y=$y";
$result=mysql_query($query);
$num = mysql_num_rows ($result);
mysql_close();

if ($num > 0 ) {
$i=0;
while ($i < $num) {$x = mysql_result($result,$i,"x");
$y = mysql_result($result,$i,"y");
$longitude_latitude = mysql_result($result,$i,"longitude_latitude");
$id = mysql_result($result,$i,"id");
echo "<b>x:</b> $x<br>";
echo "<b>y:</b> $y<br>";
echo "<b>longitude_latitude:</b> $longitude_latitude<br>";
echo "<br><br>";

$i++; } } else { echo "No match"; }
}
?>

Select the x and y coordinates:<br>
<form id="FormName" action="?" method="post" name="FormName">
<table width="448" border="0" cellspacing="2" cellpadding="0"><tr><td width = "150"><div align="right"><label for="x">x</label></div></td>
<td><input id="x" name="x" type="text" size="25" value="<?=$_POST["x"]?>" maxlength="255"></td></tr><tr><td width = "150"><div align="right"><label for="y">y</label></div></td>
<td><input id="y" name="y" type="text" size="25" value="<?=$_POST["y"]?>" maxlength="255"></td></tr><tr><td width = "150"><div align="right"><label for="longitude_latitude">longitude_latitude</label></div></td>
<td><input id="longitude_latitude" name="longitude_latitude" type="text" size="25" value="" maxlength="255"></td></tr><tr><td width="150"></td><td>
<input type="submit" name="submitButtonName" value="Add"></td>
</tr></table></form>

...html here....

bigdaddysheikh;10891022 wrote:
Hey guys,

I am trying to develop an application but am confused on how I can develop the database structure. The information that is being inserted in the database is from a numerical graph with information from X and Y axis.

For example cell x:1, y:1 = 5, x:1, y:2 =10. I then want to extract the value by selecting those cooardinates. So for example, when I find x:1 y:1, the result should be 5. I was going to make a simple database but am confused on how I can relate the values of the x,y together?

Thanks in advance.

djjjozsi

forget what i told 🙂 i thought that you search a 3th element with two data...

bigdaddysheikh;10891022 wrote:
Hey guys,

I am trying to develop an application but am confused on how I can develop the database structure. The information that is being inserted in the database is from a numerical graph with information from X and Y axis.

For example cell x:1, y:1 = 5, x:1, y:2 =10. I then want to extract the value by selecting those cooardinates. So for example, when I find x:1 y:1, the result should be 5. I was going to make a simple database but am confused on how I can relate the values of the x,y together?

Thanks in advance.

bigdaddysheikh

One note though is that using a database for such type of data if it represents a surface is a little bit overkill when data is regularly spaced in a grid(x and y are two one dimensional arrays and z is a two dimensional array) but it boils down to what your usage is. Normally this type of data is used for lookup tables where such a function is quite simple when the data is properly structured (ordered).

You are correct. This is a grid structure that is from an excel spread sheet. Currently the company is using this excel graph to determine safety points. So someone puts in 5,10 which represent safety points in height and depth respectively. The numerical values are all there, it is just constructing a database structure to accomodate for these values.

I appreciate the feedback and helping me clarify my thoughts. I will sure post the results and my testing.