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?
I would go with a view.
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.
check if this works
also, I i've added extra '' in the column definition. Try removing them. I've updated the view definition.
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.
|show 3 more comments|
try this, I think it should work: