Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have table paths_3D which has column "the_geom". I need to create a new column source_id which will have starting points from the geom. This statement doesn't work:

ALTER TABLE paths_3d ADD COLUMN source_id AS SELECT *, startpoint(the_geom)FROM paths_3d;
share|improve this question
Please clarify: Do you already have a table of network nodes with IDs or do you need to create IDs from scratch? If you have a nodes table, how does it look like? – underdark Jul 2 '11 at 12:59
I had it, but I messed it up and I need to create everything again from scratch. So I imported my paths_3d.shp with shp2pgsql and executed file in phpPgAdmin tool. Now I have table paths_3d with gid, cat and the_geom column. Next step is to create source_id and target_id columns from the_geom column, is that right or? – againstflow Jul 2 '11 at 13:05
up vote 3 down vote accepted

Start and end node geometries

  SELECT *, startpoint(the_geom), endpoint(the_geom)
  FROM paths_3d;

Creating node IDs

  SELECT row_number() OVER (ORDER BY foo.p)::integer AS id,
         foo.p AS the_geom
  FROM (
    SELECT DISTINCT paths_3d_ext.startpoint AS p FROM paths_3d_ext
    SELECT DISTINCT paths_3d_ext.endpoint AS p FROM paths_3d_ext
  ) foo
  GROUP BY foo.p;

Finally, a routable network table

  SELECT a.*, as start_id, as end_id
  FROM paths_3d_ext AS a
    JOIN node AS b ON a.startpoint = b.the_geom
    JOIN node AS c ON a.endpoint = c.the_geom

... Like in my pgRouting guide.

share|improve this answer
I did it succesfully, thank you. Now I need to name some nodes, but I need to see them in some gui, because I don't know where are they just by id or something. How can I name it? – againstflow Jul 2 '11 at 14:02
After adding a name column to nodes table, you can use QGIS to view and edit the nodes table. – underdark Jul 2 '11 at 14:28
I can't connect to database through QGIS and SPIT, it returns error. I'm using the same parameters like those in Mapfile for connecting to database. Is there any other solution except QGIS? – againstflow Jul 2 '11 at 15:05
You don't need SPIT. Use "Add PostGIS layer". If it doesn't work: What error message are you getting? Is the database running on your local computer? – underdark Jul 2 '11 at 15:23
I tried with Add PostGIS layer but it gives me error: "could not connect to server: Connection timed out. Is the server running on host "" and accepting TCP/IP connections on port 5433?" Database is running on some other server, not on my local computer. When showing layer in Mapfile, I'm using the same parameters and it works great so I can't understand what's the difference. – againstflow Jul 2 '11 at 16:20

Your Answer


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.