[postgis-users] Create_graph_tables

[Pradeep B V]

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
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061216/29ee65ec/attachment.html>