I took the values of my StartPoint and EndPoint by doing this procedure:
SELECT COUNT( CASE WHEN ST_NumGeometries(geom) > 1 THEN 1 END ) AS multi, COUNT(geom) AS total FROM public.roads;
because AS_Text(geom) was giving me multi-linestrings.the multi was 0 so I updated the geometry type to linestring:
ALTER TABLE public.roads ALTER COLUMN geom TYPE geometry(LineString, 4326) USING ST_GeometryN(geom, 1);
INSERT INTO myschema.nodes SELECT ST_StartPoint(ST_GeometryN(ST_Multi(geometry),1)), ST_EndPoint(ST_GeometryN(ST_Multi(geometry),1)) FROM public.roads
is that procedure right?
and after that I was looking the way to find the length of my roads. the only command that worked to me was the:
SELECT ST_Length(geom::geography)/1000 from public.roads;
but I thought that maybe I can find it by:
SELECT ST_DISTANCE(st_p::geography,en_p::geography)/1000,weight FROM myschema.nodes
the results are different and I'd like to understand the reason.
I have not been working on GIS for very long and I'm a little bit confused.