I am working on a research to calculate the distance of firms (scattering around the nation) to several metropolises via selected national highway system.
The purple lines are national highway system captured by the city map (Beijing ) and black dots represent firms' location. My research wants to calculate the distance of firms to another major city (now as a test, I just need to calculate the distance of firms in Beijing to Shanghai.
I follow a post by underdark to calculate the shortest path from a point to another point via a selected path. I am a newbie to pgrouting and the programming. I have some trouble to generate nodes ID and calculate the shortest path.
And this is my coding to generate nodes ID:
CREATE VIEW highway_ext AS SELECT *, ST_StartPoint(geom), ST_EndPoint(geom) FROM highway CREATE TABLE node1 AS SELECT row_number() OVER (ORDER BY foo.p)::integer AS id, foo.p AS geom FROM ( SELECT DISTINCT highway_ext.ST_StartPoint AS p FROM highway_ext UNION SELECT DISTINCT highway_ext.ST_EndPoint AS p FROM highway_ext ) foo GROUP BY foo.p;
This is my codes to do a shortpath calc:
CREATE TABLE network1 AS SELECT a.*, b.id as ST_start_id, c.id as ST_end_id FROM highway_view AS a JOIN node AS b ON a.ST_StartPoint = b.the_geom JOIN node AS c ON a.ST_EndPoint = c.the_geom; SELECT * FROM pgr_dijkstra(' SELECT gid AS id, ST_start_id::int4 AS source, ST_end_id::int4 AS target, shape_leng::float8 AS cost FROM network', 1, 5110, false, false);
As a result, my nodes table is like nothing
Any idea how should I generate node ID and Edge ID? What else do I need to address them?