Hi,
I am new to using GIS software and the SQL language. I have installed PostgreSQL 9.2.4 and pgRouting 2.0 dev on Windows 7. I’ve been using the SQL window from pgAdmin3, and DB Manager from QGIS to follow the tutorials on http://anitagraser.com/tag/pgrouting/
I was thinking of using a combination of find_node_by_nearest_link_within_distance and pgr_kdijkstraCost in order to create my Distance Matrix where the vertices are set out like square grid points. For each grid point, I want to create a table that lists the closest 8 grid points, with columns for the source vertexID, target vertexID, x1, y1, x2, y2, distance in km. I have a .shp file with the grid points, and a .shp file with the road network. I want the final results to be outputted into a .csv file.
So far, I haven’t managed to get past getting find_node_by_nearest_link_within_distance to work. I can run pgr_kdijkstraCost using the example on http://docs.pgrouting.org/dev/src/kdijkstra/doc/index.html just fine.
I also ran pgrouting.sql and matching.sql in the SQL window, and then finally the following code:
SELECT id(foo.x)
FROM (
SELECT find_node_by_nearest_link_within_distance(
‘POINT(103.6953034 1.381645751)’,
0.5,
‘public.roads’)::link_point as x
) AS foo
… and then I get the following error:
ERROR: column “the_geom” does not exist
LINE 1: select getsrid(the_geom) as srid from public.roads where gid…
^
QUERY: select getsrid(the_geom) as srid from public.roads where gid = (select min(gid) from public.roads)
CONTEXT: PL/pgSQL function find_node_by_nearest_link_within_distance(character varying,double precision,character varying) line 13 at FOR over EXECUTE statement
********** Error **********
ERROR: column “the_geom” does not exist
SQL state: 42703
Context: PL/pgSQL function find_node_by_nearest_link_within_distance(character varying,double precision,character varying) line 13 at FOR over EXECUTE statement
I am now completely lost as to what to do. Some kind advice would be greatly appreciated.
Regards,
Vi