# Thread: Nearest Neighbour Query Problem

1. Registered User
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. Registered User
Join Date
Jan 2003
Posts
4,310
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. Registered User
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. Registered User
Join Date
Jan 2003
Posts
4,310
Please supply the DDL for the sg1 and sg2 tables.

Andy

5. Registered User
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))```
Thanks

6. Registered User
Join Date
Jan 2003
Posts
4,310

Andy

7. Registered User
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. Registered User
Join Date
Jan 2003
Posts
4,310
I do not see where that query defines sg2. It is doing a Cartesian join to sg2.

Andy

9. Registered User
Join Date
Nov 2008
Posts
23

## Nearest Neighbour Query Problem

Thanks

10. Registered User
Join Date
Jan 2003
Posts
4,310
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. Registered User
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. :-)
Join Date
Jun 2003
Location
Posts
5,516
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".

13. Registered User
Join Date
Jan 2003
Posts
4,310
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. Registered User
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. :-)
Join Date
Jun 2003
Location
Posts
5,516