3

I have a set of lines in a table, I want to calculate the length of each line in kilometers and add is a new column in the table. How would I do this?

my table has been set up as

CREATE TABLE public.line (id integer Not Null, 
                          name VARCHAR (25), 
                          geom geometry (LineString, 27700));

when I try to calculate the distance of the line in km I get an error message saying only lon/lat coordinate systems are supported in geography. how do I fix this?

1 Answer 1

9

You can add the column

ALTER mytable ADD COLUMN (roadlength real);

Then you use ST_LENGTH to update it. Here I will convert it to geography first to get the results in meters, then convert to km.

UPDATE mytable 
SET roadlength = ST_LENGTH(geom::geography) / 1000;

Since your projection unit is already in meters, ignoring any potential projection distortion, you can directly use the geometry.

UPDATE mytable 
SET roadlength = ST_LENGTH(geom) / 1000;
0

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.