You would need to group by ID. Then you need to compute the distance and keep the biggest one.
Note that st_maxDistance is usefull to know the distance between a point and the furthest part of a polygon
CREATE TABLE public.vertexmaxdistances AS
SELECT a.id, max(ST_Distance(a.geom, b.geom)) as dist
FROM public.insidepointtable a, public.vertextable b
Thank you everyone for your responses. Link provided by JGH was very helpful.
It could be done in a very simple with a subquery.
Query bellow picks one OSM way and return its osm id and the array of distances between its nodes.
select ln.osm_id, (with pt as (select st_dumppoints(ln.way) as p)
select array_agg(st_distance((p1.p).geom, (p2.p).geom) :: ...
The algorithm might be looking like this one
WITH RECURSIVE generate_points(sec) AS (
SELECT sec + conf.step
FROM generate_points, conf
WHERE sec + conf.step <= conf.num_segments
conf AS (
SELECT SUM(ST_NPoints(geometry)) AS num_points,
SUM(ST_NPoints(geometry)) - 1 AS num_segments,
1 AS starting_point,
Check your units in the Andrew Hedges Haversine tool - it gives me 25.008 km ! (or 15.543 miles)
(also by just looking at your coords, you can see it should be 10s of km and not metres - a degree is usually somewhere in the 50-100km range - this is a very rough approximation but gives you a clue)
Run generate NEAR on itself, add 4 fields to that table. Transfer from coordinates, using IN_FID:
Repeat for "to" coordinates, using NEAR_FID. Use XY to line, with IN_FID as ID:
Note: you'll have lines sharing ends running in opposite directions.
To fill in with the mentioned ST_MaxDistance, assuming your <polygontable> has an <id> that matches insidepointtable.id:
CREATE TABLE public.vertexmaxdistance AS
ST_MaxDistance(a.geom, b.geom) AS dist
FROM public.insidepointtable AS a
JOIN <schema>.<polygontable> AS b
ON a.id = b.<id>
I have a vague memory of reading somewhere that the distance to only does euclidean distances, but I can't find a reference to it.
Also you might actually try using PointGeometry, which accepts the spatial reference when you create the object.
As you have already tried NNjoin plugin, I assume you have a point layer which represents the address.
(1) Open the attribute table of your road (line) layer and start the Field Calculator.
(2) Replace 'point' of the syntax below by your actual point layername and run:
length(shortest_line($geometry, geometry(get_feature_by_id('point', '1'))))
If you ...