# Using SELECT with minimal ST_Distance

With this query, I can find the distance.

``````SELECT a.id_one, c.id_two, MIN(ST_Distance(c.the_geom,a.the_geom))
FROM layerone AS a
JOIN layer2 c ON ST_Distance(c.the_geom,a.the_geom) < 2
GROUP BY 1,2
``````

the output is like:

``````36228   7   0.2399611702090666
36638   7   0.035761501407724466
39717   7   0.23750255524824365
50300   7   1.1792497204634733
70827   7   1.4365117707271136
36228   8   0.04280586621635094
36638   8   0.48885256304101504
39717   8   0.48885256304101504
50300   8   1.5303639723564109
70827   8   1.9387684183055576
70827   9   0.03678290926810888
36228   10  0.032200961567286635
``````

but I want id_one with only the min distance value, like this

``````36638   7   0.035761501407724466
36228   8   0.04280586621635094
70827   9   0.03678290926810888
36228   10  0.032200961567286635
``````

I tried CTE without result.

EDIT This query seems works, but there may be better

``````SELECT DISTINCT ON (c.id1) a.id_2, c.id1, MIN(c.the_geom<->a.the_geom) OVER (PARTITION BY c.id1) AS dist_m
FROM layerone AS a
JOIN layertwo c ON (c.the_geom<->a.the_geom) < 2
GROUP BY 1,2
ORDER BY c.id,dist_m ASC;
``````
• You must not have many features. By using `ST_Distance` in the ON clause you force a Cartesian product (every possible permutation between A and B) without any attempt to use the spatial index. `ST_DWithin` would have been the correct operator is you really wanted the result you got. You should have included your CTE, because using one could have worked (just not as efficiently). Commented Jun 21, 2020 at 14:24
• Vince, I have 112000 features in table and 86000 in other table. I know, D_distance it's not the best way. if I have a soluce with good performance, It's better. Commented Jun 21, 2020 at 14:50

It is not possible to group by the 2 ID, because the min distance is needed to find to appropriate ID for the 2nd table.

Instead, the idea is to do a cross join to link every record of the 2nd table to every record of the 1st (with some conditions) and to pick the closest entry. All attributes of the 2nd table become available.

To use a spatial index, the distance operator `<->` is needed.

``````select a.id,closest_pt.id, closest_pt.dist
from tablea a
CROSS JOIN LATERAL
(SELECT
id ,
a.geom <-> b.geom as dist
FROM tableb b
WHERE ST_DWITHIN(a.geom, b.geom,2)
ORDER BY a.geom <-> b.geom
LIMIT 1) AS closest_pt;
``````
• thk, but results have duplicates, Less than my query, but they're still there. I try a soluce with st_closestPoint ( ST_distance(ST_ClosestPoint(cs.the_geom,ac.the_geom),ac.the_geom)) but it's not working too. Commented Jun 21, 2020 at 14:59
• @pasqal this answer is the single most performant way! `ST_DWithin` would only be needed if you require a distance threshold above which you don't want matches to get considered. However, note the table order here; for each row in `tablea`, find the one closest point in `tableb`; you may need to switch your tables accordingly! Commented Jun 21, 2020 at 18:50
• @pasqal do you have duplicates ID in the 1st table? What do you consider a duplicate? The query returns 1 closest point (thanks to `limit 1`) for every row of table 1...
– JGH
Commented Jun 21, 2020 at 19:20
• @JGH no duplicate ID in table, duplicates for me are like my first output : I have 4 "7" columns, etc. despite LIMIT 1 Commented Jun 21, 2020 at 19:59
• So what should happen if a point from the 2nd table is indeed the closest point to 2 entries in the 1st table? If a point from table 2 can't be used more than once, it becomes a different requirement that should be detailed in the question
– JGH
Commented Jun 21, 2020 at 20:03