Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Nov 2008
    Posts
    23

    Unanswered: Nearest Neighbour Query Problem

    Hi Experts,
    Can someone check this code for me:
    Code:
    WITH dist(ID, distance) AS
    ( SELECT sg1.ID, cast(DB2GSE.ST_Distance(sg1.geometry, sg2.geometry) AS Decimal(8, 4)) AS distance
    --DB2GSE.ST_Distance(sg1.geometry, sg2.geometry, 0)
    FROM TABLE sg1, TABLE sg2
    WHERE sg1.ID=5  and sg1.T_IME= '18:44') --what do u think is more appropriate here? 
    SELECT *
    FROM TABLE AS t JOIN dist AS d ON
    t.id = d.id
    WHERE d.distance <= ( SELECT MIN(distance)
    FROM dist )
    Since there is indeed no comparable function or method in DB2 SE. that i could use, i thought what i could
    do is to calculate the distances (possibly only for the objects in a
    certain area around your source point) and then pick the one with the
    smallest distance. Something like the the code i provided above.
    But i am getting an undesirable outcome. I tried all what i could to fix it but no solution.

    Can someone help me check what the problem is? If someone has a better code that is doing a nearest neighbour query please let me have it.
    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The problem that I see is that you are doing a Cartesian join and calculating the distance from your one point in sg1 to every row in sg2. This is very expensive and time consuming. You should try to reduce the number of rows you read from sg2 before you calculate the distance.

    Andy

  3. #3
    Join Date
    Nov 2008
    Posts
    23

    Nearest Neighbour Query Problem

    I did not know how to make it work like you are suggesting to me. ARWinner can you modify my query to do what you are suggesting please.

    My aim is to query the nearest point to a give point, by calculating the shortest or the smallest distance to the given point.

    Can someone help me with this please.
    Thanks

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please supply the DDL for the sg1 and sg2 tables.

    Andy

  5. #5
    Join Date
    Nov 2008
    Posts
    23

    Nearest Neighbour Query Problem

    Here is the query i used to creat my table and define the attributes of the columns.

    Code:
    	 CREATE TABLE TMB888.TABLE (
      ID	INTEGER,
      T_IME TIME,
      GEOMETRY	DB2GSE.ST_GEOMETRY
    )
    I hope this will help.
    here is my insert query as well.

    Code:
    INSERT into TABLE(  ID  ,T_IME  ,GEOMETRY) VALUES (
       5 ,'18:44'  ,DB2GSE.ST_LineString  ('linestring (3 7,3 4)', 1))
    Can you please help.
    Thanks

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What about sg2?

    Andy

  7. #7
    Join Date
    Nov 2008
    Posts
    23

    Nearest Neighbour Query Problem

    I define sg2 from the code below:
    Code:
    ( SELECT sg1.ID, cast(DB2GSE.ST_Distance(sg1.geometry, sg2.geometry) AS Decimal(8, 4)) AS distance
    FROM TABLE sg1, TABLE sg2
    The idea is to query sg1 and sg2 and then calculate the distance between them and then at the end query the smallest distance between a choosen between to the rest of the point.

    I don't know if it is necessary to define sg2, but what i want is to choose a point and calculate the distances from the choosen point say (1 2, 3 4) to the rest of the points and then return the smallest distance.

    Can you please then modify the code for me that will do what i want. Maybe my code is not doing that. This is why i am seeking for assistants in this forum.

    Thanks

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not see where that query defines sg2. It is doing a Cartesian join to sg2.

    Andy

  9. #9
    Join Date
    Nov 2008
    Posts
    23

    Nearest Neighbour Query Problem

    Can you please define it if it is needed and please help me modify it for me.
    Thanks

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How can I define it if I do not know what it is?

    Maybe you should tell me what you have now, and what you want to do?

    Andy

  11. #11
    Join Date
    Nov 2008
    Posts
    23

    Nearest Neighbour Query Problem

    HTML Code:
    3,22:48,446,242,456,252
    1,22:14,507,230,517,240
    1,23:05,464,297,474,307
    2,23:48,423,355,433,365
    2,10:02,40,294,50,304
    3,12:18,59,320,69,330
    3,15:22,169,280,179,290
    4,15:58,194,216,204,226
    4,18:03,227,227,237,237
    I have the above data to insert into my table(Table). I already inserted them into my table.
    Now i need to choose a point say "2,10:02,40,294,50,304" and calculate the distance between "2,10:02,40,294,50,304" and each of the rest of the points. After calculating the distances between "2,10:02,40,294,50,304" and the rest of the points, my query should return the distance that is the closest to the the point"2,10:02,40,294,50,304". so inorder word the query should return the smallest distance between the point "2,10:02,40,294,50,304" and closest point to it. This is what i want to do.

    so far i got the query above and it is returning zeros and not the smallest distance
    After understanding what i want, i want you to please modify my query to help me get what i want.
    You check to see if the parameters are correct. If not then modify it for me or if you think it is not good to give me what i want, then help me with the correct one. I really appreciate your help.
    Thanks

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    WITH dist(ID1, id2, distance) AS ( 
      SELECT 
        sg1.ID, 
        sg2.id, 
        cast(DB2GSE.ST_Distance(sg1.geometry, sg2.geometry) AS Decimal(8, 4)) AS distance
      FROM TABLE sg1, TABLE sg2
      WHERE sg1.ID=5  and sg1.T_IME= '18:44' and sg2.id <> sg1.id
    ) 
    SELECT *
    FROM dist 
    where distance = (select min(distance) from dist)
    It is a bad idea to name a table "TABLE".
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by n_i
    It is a bad idea to name a table "TABLE".
    That explains why I was so confused with the OP's question. I did not realized that he named is table TABLE.

    Andy

  14. #14
    Join Date
    Nov 2008
    Posts
    23

    Nearest Neighbour Query Problem

    Thanks you for the reply. Sorry for naming the table as TABLE.
    the result is better now, only that i needed to output the positions of the choosen point and the one with the shortest distance to the choosen point.

    i.e
    HTML Code:
    ID1   ID2    PT1       PT2       Distance
    1       2     2, 4      9, 1        0.879
    Sorry i forget i did not mention it earlier. How can i show the points.
    Thanks

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2learner2008
    How can i show the points.
    You'll just need to include them in the select list where appropriate. Do you expect us to help you with your paycheck too?
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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