Using ST_ClosestPoint, ST_StartPoint, and ST_EndPoint to find closest object to beginning and end points of lines

Question

I need to find the closest points to either end of a line using geometry from two separate tables. So far I was able to use the following to find the geometry of the end points:

SELECT "id", ST_StartPoint(dmp.geom) AS upstream,
       ST_EndPoint(dmp.geom) AS downstream
FROM sewers.pipes,
        LATERAL ST_Dump("geom") AS dmp
;

Using this, I want to use ST_ClosestPoint to find structures that are the closest to the end points of pipes. This is what I've come up with so far:

SELECT ST_ClosestPoint('POINT(SELECT ST_StartPoint(dmp.geom) AS upstream
FROM sewers.pipes,
        LATERAL ST_Dump("geom") AS dmp)',
                      ('LINESTRING(SELECT geom from sewers.pipes)'))

However, this gives the following error:

ERROR: parse error - invalid geometry
Line 1: SELECT ST_ClosestPoint('POINT(SELECT ST_STartPoint(dmp.geom)...

HINT: "POINT(SE" <-- parse error at position 8 within geometry
SQL state: XX000
Character: 24

I have provided some sample data below.

sewers.pipes <-- these are the lines

| id       | geom |
| -------- | -------------- |
| 822      | 0105000020950B00000100000001020000000200000046243EC3282608418D28242D6C1B3D4128531BE88A2608418284B3EF561B3D41        |
| 6660     | 0105000020950B0000010000000102000000020000004ABF2CBC86B108413B93650696323D413C487924CCB10841925D490495323D41            |

sewers.structures <-- these are points

| id        | geom |
| --------  | -------------- |
| 2014      | 0104000020950B00000100000001010000001026FA48113B07410D6A8412CF1D3D41            |
| 22979     | 0104000020950B0000010000000101000000BA1BF246E6DD0741D064CB58C2E43C41            |

I know that there may be an error that comes from selecting multiple data points, eg. more than one row returned by a subquery used as an expression, so that may be an issue as well. Any help would be greatly appreciated.

Hi, I am confused. Which points you want to get the closest to the first and last points of your linestring? Can you provide a data sample? — Jim Jones, Jul 16 '21 at 13:01
@JimJones I added some more clarity. I also attempted to use a markdown table to add some sample data. My goal is to find the closest structure (point) to a given endpoint of a pipe (linestring). — AThomspon, Jul 16 '21 at 14:00

Jim Jones · Accepted Answer · 2021-07-16T15:33:19.057

2

Try with distinct LATERALs for upstream and downstream:

SELECT p.id,
  (dump_line).geom,
  ST_EndPoint((dump_line).geom) AS downstream,
  geom_closest_downstream,
  ST_StartPoint((dump_line).geom) AS upstream,
  geom_closest_upstream
FROM sewers.pipes p,
  LATERAL ST_Dump(p.geom) dump_line,
  LATERAL (SELECT s.geom 
           FROM sewers.structures s
           ORDER BY ST_EndPoint((dump_line).geom)<->s.geom 
           LIMIT 1) j (geom_closest_downstream),
  LATERAL (SELECT s.geom 
           FROM sewers.structures s
           ORDER BY ST_StartPoint((dump_line).geom)<->s.geom 
           LIMIT 1) i (geom_closest_upstream);

To access the geometries from ST_Dump you have to wrap its output with parenthesis, e.g. (ST_Dump(multiline)).geom
The operator <-> at the ORDER BY clause corresponds to distance. So, using it combined with a LIMIT 1 you get only the closest distance.

Demo: db<>fiddle

edited Jul 16 '21 at 15:33

answered Jul 16 '21 at 14:19

Jim Jones

18,404
3
35
44

When I ran this, it gave me empty columns for downstream and upstream, so it returned the same value for all geom_closest_downstream and geom_closest_upstream. – AThomspon Jul 16 '21 at 14:28
1

@AThomspon did you check my fiddle? I added it in my last edit – Jim Jones Jul 16 '21 at 14:32
I checked the demo, and while it works on the demo database, I am getting an error stating "Operation on mixed SRID geometries." I used 'ST_SetSRID()' to force the 'ST_Dump()' to 4326, but the error persists. – AThomspon Jul 16 '21 at 15:15
1

@AThomspon why are you using `4326`? Your geometries are encoded with `2965` ;) – Jim Jones Jul 16 '21 at 15:27
1

@AThomspon I'm glad I could help. I just noticed you have `multilinestrings` and `multipoints`, both always with a single geometry. In case your table does not have multiple geometries in a single row, consider using `point` and `linestring`. It would make the query a bit easier ;) cheers and happy coding – Jim Jones Jul 16 '21 at 15:31

Using ST_ClosestPoint, ST_StartPoint, and ST_EndPoint to find closest object to beginning and end points of lines

1 Answers1