0

I'm currently working with geo point with MySql 5.7. My currently request is :

SELECT pA.id as pa, pB.id as pb, ST_Distance(pA.geo, pB.geo) as distance
FROM point_A pA
JOIN point_B pB 
ORDER BY pa, distance

Sample output :

+-------+-------+---------------------+
| pa    | pb    | distance            |
+-------+-------+---------------------+
|     1 |  5535 | 0.23752813469351355 |
|     1 |  5536 |  0.8110224552241297 |
|     1 |  5533 |  0.8359718468573036 |
|     2 |  5536 |  0.8763100264270072 |
|     2 |  5536 |  0.0665454376162462 |
|     2 |  5533 |  1.0669544991122402 |
|     3 |  5533 |  0.2469039758259646 |
|     3 |  5536 |   0.329452951307464 |
|     3 |  5533 |  1.3593527448933072 |

I want only first occurence of each pa. I can't find a way to do this without using subrequest and a second time ST_Distance ( which has a cost in performance )

expected result :

+-------+-------+---------------------+
| pa    | pb    | distance            |
+-------+-------+---------------------+
|     1 |  5535 | 0.23752813469351355 |
|     2 |  5536 |  0.8763100264270072 |
|     3 |  5533 |  0.2469039758259646 |

I already tried with group by and distinct but all my distance value are unique with couple pa, pb.

user1904731
  • 91
  • 1
  • 11
  • 2
    You need a primary key or a timestamp column to identify which is first entry, sql is unordered and hence there is no way to see who came first on the table unless there is something to identify that. – Abhik Chakraborty Oct 26 '17 at 17:07
  • @AbhikChakraborty I think he means in order of distance. – Barmar Oct 26 '17 at 17:35
  • I checked before other solution with group by and subrequest. Problem : group by : I loose information about pb subrequest: ST_distance is used 2times. This a problem when you have a large set of points. @AbhikChakraborty , thanks for information, one solution is to use a temporary table. Then I have id for each row and I can use group by. – user1904731 Oct 27 '17 at 10:34

0 Answers0