2

I took the values of my StartPoint and EndPoint by doing this procedure:

a)

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:

b)

ALTER TABLE public.roads ALTER COLUMN geom TYPE geometry(LineString, 4326) USING ST_GeometryN(geom, 1);

and finaly

c)

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.

  • 1
    In case of geometry the distance is calculated on the projection plan, in case of geography the distance is calculated on the ellipsoid. See postgis.net/docs/ST_Distance.html – Zoltan Dec 23 '16 at 18:54
  • 2
    The distance between two points (StartPoint and EndPoint in your example) is the "as the bird flies" distance. Not the same as the length of the road at all. – Micha Dec 24 '16 at 17:51
1

The results are different because they are different functions.

ST_Length will return the length of the road (possibly multilinestring)

ST_Distance will return the straight distance between two points.

Imagine a road shaped like the letter C... ST_Distance of the start and end points of C will be much shorter than ST_Length of C.

  • 1
    @StavrosStylos you are welcome. Would you please mark the answer as accepted if I have answered your question? – kttii Jan 10 '17 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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