How to add column length in a pgRouting PostGIS database?

Question

I have 50 3d linestrings in postgis database, and I need to add column length so I could use pgRouting for calculating shortest path with Dijkstra. How can I add it?

Answer 1

I would go with a view.

Just:

CREATE OR REPLACE VIEW lines_with_length AS

SELECT
    *,
    ST_Length3D(the_geom) as "length"
FROM lines

EDIT:

well, why with a view?

A) no schema alteration. You won't break applications (it's most likely that adding a column to it would not break anything, but there's always a chance)

B) you don't have to maintain that column yourself. After a few weeks or a few changes to table you Djikstra routing would start to give you wrong results, because the Length field would be outdated. The view is the saner path to take, since it will "automagically" update the length column.

EDIT

check if this works

select st_length3d(st_geomfromtext('LINESTRING(0 0 0, 1 1 1)'))

1.73205080756888

also, I i've added extra '' in the column definition. Try removing them. I've updated the view definition.

Cheers

Answer 2

For 3D geometries, you'll want to use ST_Length3D() (list of 3D functions).

With only 50 geometries, you can easily calculate the length values on the fly. That way you don't need to add the length column in advance. Just use ST_Length3D(the_geom) as a cost value.

If you want to add a column, you'll have to first add the column and then update it's values using the length function.

Answer 3

try this, I think it should work:

ALTER TABLE tableA ADD COLUMN cost_length double precision;
UPDATE tableA SET cost_length = ST_Length3D(the_geom);