Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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

share|improve this answer
    
It gives me error: SQL error: ERROR: parse error - invalid geometry LINE 5: ST_Length3D('the_geom') as "length" ^ HINT: You must specify a valid OGC WKT geometry type such as POINT, LINESTRING or POLYGON –  againstflow Jun 28 '11 at 8:31
    
It's ST_Length3D(the_geom) –  underdark Jun 29 '11 at 6:33
    
I succeeded with creating view. Thank you :) –  againstflow Jun 29 '11 at 15:47
add comment

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);
share|improve this answer
    
It gives me an error: ERROR: syntax error at or near "TABLE" LINE 1: SELECT COUNT(*) AS total FROM (ALTER TABLE network ADD COLUM... –  againstflow Jun 26 '11 at 18:36
    
I wrote it this way: ALTER TABLE network ADD COLUMN cost_length double precision; UPDATE network SET cost_length = ST_Length3D(the_geom); –  againstflow Jun 26 '11 at 18:36
    
Which tool are you using to execute the query? RTSqlLayer by any chance? Use PgAdmin or PostGIS Manager Plugin instead. –  underdark Jun 26 '11 at 20:59
    
I'm using phpPgAdmin. –  againstflow Jun 28 '11 at 8:26
    
The query should work if you remove the SELECT part you put in front of the ALTER TABLE command. –  underdark Jun 29 '11 at 6:34
add comment

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.

share|improve this answer
    
I did it. Now I tried to calculate shortest route with Shortest Path Dijkstra, I saw an example on your blog underdark.wordpress.com/2011/02/07/…. But when I execute sql: SELECT * FROM shortest_path('SELECT gid AS id, start_id::int4, end_id::int4, cost_length::double precision AS cost, FROM network', 3, 7, false, false); it gives me an error: SQL error: ERROR: syntax error at or near "FROM" LINE 3: FROM network ^ QUERY: SELECT gid AS id, start_id::int4, end_id::int4, cost_length::double precision AS cost, FROM network –  againstflow Jun 29 '11 at 16:54
    
You have a mis-placed comma after cost in ... AS cost, FROM network –  underdark Jun 29 '11 at 16:55
    
Yeah, that's right, thank you. But now it gives me another error: SQL error: ERROR: Error, query must return columns 'id', 'source', 'target' and 'cost' I don't understand what that means? –  againstflow Jun 29 '11 at 16:59
    
The names of the columns in the sub-select must be 'id', 'source' etc. --> SELECT gid AS id, start_id::int4 as source, end_id::int4 as target, cost_length::double precision AS cost –  underdark Jun 29 '11 at 17:04
    
I just figured it out, but of course it gives me an error: No rows found. Total runtime: 6.056 ms SQL executed. What that means? How is possible that no rows is found? –  againstflow Jun 29 '11 at 17:06
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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