SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql mathematical operation,database

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by mdnaimuddin View Post
    if question is not clear plz do reply.
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    i think the above explanation is somewhat clear but quite long..

    hope I will get some reply...
    Attached Images Attached Images

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sorry, i still do not understand what you want
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry, i still do not understand what you want
    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..


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •