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 am working with pgRouting, following this nice guide:A Beginner’s Guide to pgRouting

I am now in this step:Calculating shortest routes, with these codes below

SELECT * FROM shortest_path('
SELECT gid AS id, 
      start_id::int4 AS source, 
      end_id::int4 AS target, 
      shape_leng::float8 AS cost
FROM network',
1,
5110,
false,
false);

and my network table looks like this enter image description here

apparently I have no column "shape_leng", so I need to create a new column in table network, and it is the length of every row.

But I don't know how I should type the ST_length code.

Can someone give me some advice?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

try to use following sql.

ALTER TABLE network ADD COLUMN shape_leng double precision;
UPDATE network SET shape_leng = length(the_geom);

i hope it helps you...

share|improve this answer
    
thanks, it works! –  Heinz Apr 17 '13 at 9:06
add comment

If the data in the network table is either being updated or added, it would be wise to make a trigger function to update the data, so the line lengths are always up-to-date. To do this, make a trigger procedure, written in PL/pgSQL:

CREATE OR REPLACE FUNCTION shape_leng() RETURNS trigger AS
$BODY$BEGIN
  NEW.shape_leng := ST_Length(NEW.the_geom);
  RETURN NEW;
END;$BODY$ LANGUAGE plpgsql;

(note, I use the ST_Length PostGIS function, but it can be swapped out with other methods.) Then make the trigger fire on the network table for insert and update:

CREATE TRIGGER network_shape_leng
  BEFORE INSERT OR UPDATE ON network
  FOR EACH ROW EXECUTE PROCEDURE shape_leng();

Play around with the data, and you will see it automatically fill this value in for you. Similar triggers can be built for shape area (for polygons).

share|improve this answer
add comment

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.