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 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
add comment

1 Answer 1

up vote 3 down vote accepted

Start and end node geometries

CREATE OR REPLACE VIEW paths_3d_ext AS
  SELECT *, startpoint(the_geom), endpoint(the_geom)
  FROM paths_3d;

Creating node IDs

CREATE TABLE node AS
  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
    UNION
    SELECT DISTINCT paths_3d_ext.endpoint AS p FROM paths_3d_ext
  ) foo
  GROUP BY foo.p;

Finally, a routable network table

CREATE TABLE network AS
  SELECT a.*, b.id as start_id, c.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 "www.something.com" 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
show 2 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.