Nearest Neighbour Query Problem

db2learner2008 · #1 (**permalink**) 11-10-08, 14:10

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

ARWinner · #2 (**permalink**) 11-10-08, 15:00

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

db2learner2008 · #3 (**permalink**) 11-11-08, 03:16

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

ARWinner · #4 (**permalink**) 11-11-08, 08:11

Please supply the DDL for the sg1 and sg2 tables.

Andy

db2learner2008 · #5 (**permalink**) 11-11-08, 08:55

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

ARWinner · #6 (**permalink**) 11-11-08, 09:35

What about sg2?

Andy

db2learner2008 · #7 (**permalink**) 11-11-08, 10:26

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

ARWinner · #8 (**permalink**) 11-11-08, 10:53

I do not see where that query defines sg2. It is doing a Cartesian join to sg2.

Andy

db2learner2008 · #9 (**permalink**) 11-11-08, 11:06

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

ARWinner · #10 (**permalink**) 11-11-08, 12:03

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

db2learner2008 · #11 (**permalink**) 11-11-08, 13:03

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

n_i · #12 (**permalink**) 11-11-08, 13:11

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".

ARWinner · #13 (**permalink**) 11-11-08, 14:00

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

db2learner2008 · #14 (**permalink**) 11-11-08, 14:27

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

n_i · #15 (**permalink**) 11-11-08, 14:46

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech