Mysql mathematical operation,database

Databases
1

I have 4 column in my database, view name column, X cordinate,Y co ordinate ,Z coordinate

for each xyz coordinate there is one view name.

I want to run an sql query whcih will calculate for eg. ( sqrt root of ((x -x1)square+(y -y1)square + (z -z1)square)) and for each value of x,y,z cordinate in my whole column , it should show me the name of associated with that column if the ans is less than 100, beside each column for each value of x,y,z

can anybody help?
if question is not clear plz do reply.

simplified way of question is- i want to do some calculation with my xyz co ordinate with all the other cordinate in this table and if the ans is less than 100 it should show me the name of all that column for which the value is less than 100. and put it beside the xyz cordinate for which it got result…

and do the same for each xyz cordinate and put the result beside it…

i did it using excel…but not able to implement it,in myphpmyadmin sql database…
thanks…

2

it’s not

perhaps you could show a few sample rows of data in this table, and then indicate what the query should produce for those sample rows

3

Ok…thanks for ur reply…

I am attaching a printscreen of my database…

  1. let us suppose the value of x,y,z which are in table are x1,y1,z1,x2,y2,z2 and so on.

2.I want shortest distance between the first value x1,y1,z1 and second value of x,y,z i.e x2,y2,z2. using formulae : sqrt{(x2-x1)^2+(y2-y1)^2+(z2-z1)^2}=d1
again with second value x,y,z i.e d2=sqrt{(x3-x1)^2+(y3-y1)^2+(z3-z1)^2}.
similarly it will calculate,d1,d2,d3,d4… till the last row for first value of x1,y1,z1.

now in all this distances it should me the 5 “link” which is having the distance value less than or equal to 400 in Row R1,R2,R3,R4 and R5 in the first row as this all distance has been calculated for only first value of x,y,z .i.e x1,y1,z1

than again this query will run for second value of x,y,z i.e x2,y2,z2 of all the other value of x,y,z from x1,y1,z1 till the last value of x,y,z.

and show me the 5 “link” which is having the distance value less than or equal to 400. in Row R1,R2,R3,R4 and R5 in the second row as this all distance has been calculated for second value of x,y,z i.e x2,y2,z2

in the same way this query will run for each x,y,z till last value of x,y,z and show the result in R1,R2,R3,R4 and R5 till the last row.

:slight_smile: i think the above explanation is somewhat clear but quite long…

hope I will get some reply…

4

sorry, i still do not understand what you want

5

its a simple mathematics…

first we have to calculate distance between one point to all the other point and if the distance is less than 400. show the text which is there in “link” column.
eg…

link X Y Z R1 R2 R3

cow x1 y1 z1 cow monk tony
goat x2 y2 z2 goat raj tatoo
monk X3 y3 z3 monk cow tony
raj x4 y4 z4
jony x5 y5 z5
tatoo x6 y6 z6
tony x7 y7 z7

In the above eg for x1,y1,z1 we have found cow,jony and tony…thats means if we will use the above formula between point x1,y1,z1 and x1,y1,z1

x1,y1,z1 and x5,y5 and z5
x1,y1,z1 and x7,y7 and z7

the value we will get is less than 400

similarly for goat we got goat,raj and tatoo
and for monk we got monk, cow and tony

i think now it is clear…