6

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?

3 Answers 3

6

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.

1
4

I would go with a view.

Just:

CREATE OR REPLACE VIEW lines_with_length AS

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

I would use a view because:

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.

Check if this works

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

1.73205080756888

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

0
4

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);
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.