CODEWITHSUNDEEP
rpostgresql
Sofie
Sofie

Reputation: 23

Find the x minimal values in a distance matrix in R

I have computed a distance matrix between patches of ancient forests and recent forests in PostgresSQL thank to the following code:

CREATE TABLE MatDist as (
SELECT
  a.id a,
  b.id b ,
  st_distance(a.geom, b.geom) dist
FROM public.bvi_foret a, public.bvi_foret b
WHERE a.id != b.id AND a.ANC_FOR != b.ANC_FOR
)

and it works perfectly.

I want now to select the 5 pairs ancient forests (a)/recent forest (b) presenting the minimal distance between them.

So I started working with R, and I can find the unique pair presenting the minim distance, thanks to the following code:

DT <- data.table(df)
DT[ , .SD[which.min(dist)], by = a]

But how can I compute the 5 first pairs? It's probably easy, with a for loop or an apply function in R, but I can't find it...

Thanks in advance for your answers.

Upvotes: 2

Views: 57

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176314

Using pure SQL:

SELECT *
FROM MatDistMat
ORDER BY dist
LIMIT 5;

Thanks for your answer, but I need the 5 first pairs FA/FR for each patch of ancient forest.

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY a ORDER BY dist ASC) as rn
     FROM MatDistMat) sub
WHERE sub.rn <= 5;

Upvotes: 1

Related Questions