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

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
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). – Vince Jun 21 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. – pasqal Jun 21 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
     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;
| improve this answer | |
  • 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. – pasqal Jun 21 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! – geozelot Jun 21 at 18:50
  • 1
    @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 Jun 21 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 – pasqal Jun 21 at 19:59
  • 1
    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 Jun 21 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.