Minimum distance between points and lines 'en PostGIS'

This post is also available in: en

Suppose you want to compute the distance between a set of geographic vector points and a vector line (with multiple entities). There are many ways and implemented functions to accomplish this, but a very simple one can be achieved using PostGIS (inside Postgres).

I used the following code inside QGIS, but if you have your data directly in Postgres, you can run it directly.

SELECT ST_ShortestLine(r.geometry, p.geometry),
        p.ID AS pid,
        r.ID AS rid,
        ROUND(ST_Length(ST_ShortestLine(r.geometry, p.geometry)), 6) AS distance
FROM Points_cover AS p, Lines_cover AS r
GROUP BY p.ID
WHERE r.Tipo IN ('Río', 'Estero')
ORDER BY MIN(distance)

Points_cover make reference to your points layer and Lines_cover to the lines one. You'll have to rename this to your own layers. Also, the code assumes you have an unique identifier called ID in both layers; you can change that to whatever suits you better.

Note that I'm rounding up to six decimal places (you can change that or get rid of that completely) and also using a conditional WHERE clause to just consider lines that contains those strings as attributes.

Inside QGIS you can just add a new "Virtual Layer" and on the query box, you can type the previous query.