[postgis-users] Create_graph_tables

[Ville Antti Ensio Leinonen]

  There is also pgrouting, wich is based to pgdijkstra. pgrouting can be 
found from http://www.postlbs.org/postlbs-cms/index.php, but when 
comparing it to pgdijkstra it is much harder to get working. ( it dosen't 
have ready made functions to create route-enabled tables like pgdijkstra 
has. but in other hand it has dijkstra and a* methods to calculate path)


---------------------------------
  --	Ville Leinonen		 --
----------------------------------

On Sat, 16 Dec 2006, Pradeep B V wrote:

> ERROR:  duplicate key violates unique constraint "roadl_edges_source_key"
>
>
> This occurs because of duplicate edges in the graph.
>
>
> So you do the following to clean it up.
>
> The following lines are taken from
> http://www.cartoweb.org/doc/cw3.3/xhtml/user.install.html
>
> But if the data quality is poor, you need to delete the duplicates edges
> (they have the same source-target pairs of vertices). For example, to check
> that you have duplicated edges, you can type:
>
> $ SELECT * FROM (SELECT source_id, target_id, count(*) AS c FROM
> roads_europe group by
> source_id, target_id order by c)
> AS foo where foo.c = 2;
>
> If there is duplicated edges, to delete one of two rows, you can type:
>
> $ CREATE TABLE doublons AS SELECT * FROM roads_europe WHERE gid  in
> (SELECT gid FROM (SELECT DISTINCT on (source_id, target_id) source_id, gid
> FROM roads_europe) AS doublon);
> $ DELETE FROM roads_europe;
> $ INSERT INTO roads_europe (SELECT * FROM doublons);
> $ DROP TABLE doublons;
>
> Hope it helps.
>
> Pradeep B V
> http://mapunity.org
>
> On 12/16/06, Sasanka Gandavarapu <horryglory at gmail.com> wrote:
>> 
>> Hi all
>> 
>> While using the Create_graph_tables function for integrating pgdijkstra
>> with postgreSQL i am encountering the following error:
>> I am directly using the demo datafiles(from cartoweb, hence i dont think
>> there  is any problem with the data).
>> Also, before doing this, i created source and target fields in the
>> database by adding three new columns and using assign_vertex_id.
>> 
>> can someone help, please...
>> 
>> NOTICE:  CREATE TABLE will create implicit sequence
>> "roadl_vertices_id_seq" for serial column "roadl_vertices.id"
>> CONTEXT:  SQL statement "CREATE TABLE roadl_vertices (id serial, geom_id
>> int4  NOT NULL UNIQUE)"
>> PL/pgSQL function "create_graph_tables" line 11 at execute statement
>> 
>> NOTICE:  CREATE TABLE / UNIQUE will create implicit index
>> "roadl_vertices_geom_id_key" for table "roadl_vertices"
>> CONTEXT:  SQL statement "CREATE TABLE roadl_vertices (id serial, geom_id
>> int4  NOT NULL UNIQUE)"
>> PL/pgSQL function "create_graph_tables" line 11 at execute statement
>> 
>> NOTICE:  CREATE TABLE will create implicit sequence "roadl_edges_id_seq"
>> for serial column "roadl_edges.id"
>> CONTEXT:  SQL statement "CREATE TABLE roadl_edges (id serial, source int,
>> target int, cost float8, reverse_cost float8, UNIQUE (source, target))"
>> PL/pgSQL function "create_graph_tables" line 15 at execute statement
>> 
>> NOTICE:  CREATE TABLE / UNIQUE will create implicit index
>> "roadl_edges_source_key" for table "roadl_edges"
>> CONTEXT:  SQL statement "CREATE TABLE roadl_edges (id serial, source int,
>> target int, cost float8, reverse_cost float8, UNIQUE (source, target))"
>> PL/pgSQL function "create_graph_tables" line 15 at execute statement
>> 
>> ERROR:  duplicate key violates unique constraint "roadl_edges_source_key"
>> 
>> CONTEXT:  SQL statement "INSERT INTO roadl_edges (id, source, target)
>> VALUES (91, '107', '109')"
>> PL/pgSQL function "create_graph_tables" line 27 at execute statement
>> 
>> Thanks in advance,
>> Horry
>> 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
>> 
>> 
>