8

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

5
  • Thanks. I corrected the first two errors you pointed out. As for the ST_Union(geom), I have no idea. I just copy pasted the code from the source I mentioned, made some changes and hoped for the best. Jan 8, 2023 at 15:22
  • In the example dataset, 1 point and ~ 400 polygons. Jan 8, 2023 at 15:24
  • @BERA Minimum distance between a single point (just one thankfully) and all polygons. In the original dataset, the number of polygons can be as high as 2000. Jan 8, 2023 at 15:28
  • @Babel I get this error message: [ERROR: syntax error at or near ";" LINE 1: CREATE TABLE sar.distances; WITH sar.distances AS (SELE... ^ ] Jan 8, 2023 at 15:29
  • 1
    Based on your question, the use of ST_Union() is not required, perhaps you would need to replace it with ST_Dump(), although... Jan 8, 2023 at 16:15

2 Answers 2

5

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.

enter image description here

0
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: enter image description here

Calculating the length of the lines with Field Calculator: enter image description here

6
  • Thanks. That's very useful for visual inspection, but in this case I need to measure the distances between the point and the polygons. Jan 8, 2023 at 15:45
  • No problem. Simply add a length() function to calculate the length of the red lines. Enclose the expression from above as argument in the function: length ( [expression_from_above] ) and you're done
    – Babel
    Jan 8, 2023 at 15:48
  • 2
    @Babel, it is about PostGIS ^_^
    – Taras
    Jan 8, 2023 at 16:03
  • Yes, that's why i added "If you are fine to try QGIS expressions" and OP states that they use QGIS - so they might feel free to try different ways to achieve the goal
    – Babel
    Jan 8, 2023 at 16:07
  • 1
    Use Field calculator on the polygon layer and the expression will create a new attribute field with the lengths. Define a field name and field type first, then insert the expression (you get a preview at the bottom) - see screenshot at the bottom of my updated answer.
    – Babel
    Jan 8, 2023 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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