Calculate the distance between a single point and multiple polygons using PostGIS

Question

I’m working with a very large dataset and I need to calculate the shortest distance between a single point and the closest edge from multiple polygons. I need to use PostGIS because such a task with a take a very long time in QGIS or GRASS GIS.

Unfortunately, I’m relatively new to PostGIS. I’ve been googling and found a QGIS plugin that has code for doing similar tasks in PostGIS (https://github.com/NaturalGIS/naturalgis_postgis_geoprocessing). Based on its “minimum distance” algorithm, I wrote the following query that unfortunately does not work:

My schema is called "sar", the point is a vector called "point" and the polygons are in a vector called "rep".

CREATE TABLE sar.distances; 
WITH sar.distances AS (SELECT ST_Union(geom) AS geom FROM sar.rep_ex) 
SELECT ST_ShortestLine(g1.geom,g2.geom)::geometry(LINESTRING,3857) AS geom, ST_Distance(g1.geom,g2.geom) AS distance, g1.id AS id from 
FROM sar.point_ex AS g1, sar.distances AS g2

The error is message is:

[ERROR:  syntax error at or near ";"
LINE 1: CREATE TABLE sar.distances;  WITH sar.distances AS (SELECT S...
                                  ^
]

What am I doing wrong?

I'm attaching a small sample of the data I'm working with (point_ex.shp and rep_ex.shp).

https://figshare.com/articles/dataset/example_data_zip/21836508

Answer 1

You can cross join all points to all polygons to measure distances and create shortest lines:

select  pnt.id as pointid, 
        poly.id as polyid,
        st_distance(pnt.geom, poly.geom) as distance, 
        st_shortestline(pnt.geom, poly.geom) as geom
from public.pointtable pnt
cross join public.polygontable poly

There's a line from each point to all polygons, 2581*65=167765 lines.

Answer 2

If you are fine to try QGIS expressions, you can use this expression to create the lines:

with_variable (
    'point',
    geometry (
        get_feature_by_id(
            'point_ex',
            1
        )
    ),
    make_line (
        @point,
        closest_point (
            $geometry,
            @point
        )
    )
)

If you want to calculate the length of these lines, add a length() function: enclose the whole expression from above as argument in the function: length ( [expression_from_above] ) and you get the length of the lines.

Red lines created by the expression above:

Calculating the length of the lines with Field Calculator: