Wiki Navigation

Forum #22 - Topic #59 - Message List

Tutorial Problems

Hi,

I am trying to follow the tutorial regarding how to load data but I am getting these errors :

routing=# ALTER TABLE victoria RENAME COLUMN the_geom TO geom; ALTER TABLE routing=# SELECT AddGeometryColumn?('victoria','the_geom',54004,'MULTILINESTRING',2); ERROR: constraint "enforce_srid_the_geom" for relation "victoria" already exists CONTEXT: SQL statement "ALTER TABLE public.victoria ADD CONSTRAINT enforce_srid_the_geom CHECK (SRID(the_geom) = 54004)" PL/pgSQL function "addgeometrycolumn" line 100 at execute statement SQL statement "SELECT AddGeometryColumn?(,, $1 , $2 , $3 , $4 , $5 )" PL/pgSQL function "addgeometrycolumn" line 4 at select into variables routing=# UPDATE victoria SET the_geom=geom; ERROR: column "the_geom" of relation "victoria" does not exist

Can someone please tell me where is the error in the tutorial or in what I am doing please?

Also anyone knows of any other good tutorials I can give a read through?

Thanks

Tree View Flat View (newer first) Flat View (older first)

Message #208
Hi,

I hope not to disappoint you, but this tutorial is the easiest and most tested tutorial I know. I did it the copy&paste way dozens of times and it always worked.

Well, I guess you tried to add the geometry column twice, because it says that it already exists. So what you can do is (three possibilities):
1. Drop the database and start from beginning,
2. Remove the geometry column with "Select DropGeometryColumn? ..." (see the PostGIS manual)
3. Just don't add it again and try to proceed with the next steps.
If none of those works, just post here again and I'll try to find another solution.
daniel

11/06/07 23:26:00

Message #209

Hi,

Thanks for your fast reply. I dropped the database.

Now I am readding the database but am being halted here :

psql -U postgres -f routing.sql routing psql -U postgres -f routing_wrappers.sql routing

I cannot locate these files .. what I found in the pgrouting is routing_core.sql and routing_core_wrappers.sql .. is it the same?

Thanks

yancho

11/06/07 23:39:01

Message #210

Thank you for remembering me! I forgot to correct this line. It's now routing_core.sql as you said.

daniel

11/06/07 23:43:55

Message #212

I am still getting the same problem .. anything which I should change please?

Welcome to psql 8.1.4 (server 8.1.10), the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit

routing=# \i /home/yancho/gis/viktoria/victoria.sql BEGIN psql:/home/yancho/gis/viktoria/victoria.sql:46: NOTICE: CREATE TABLE will creat e implicit sequence "victoria_gid_seq" for serial column "victoria.gid" psql:/home/yancho/gis/viktoria/victoria.sql:46: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "victoria_pkey" for table "victoria" CREATE TABLE

addgeometrycolumn

public.victoria.the_geom SRID:-1 TYPE:MULTILINESTRING DIMS:2

(1 row)

COMMIT routing=# ALTER TABLE victoria RENAME COLUMN the_geom TO geom; ALTER TABLE routing=# SELECT AddGeometryColumn?('victoria','the_geom',54004,'MULTILINESTRING' ,2); ERROR: constraint "enforce_srid_the_geom" for relation "victoria" already exist s CONTEXT: SQL statement "ALTER TABLE public.victoria ADD CONSTRAINT enforce_srid _the_geom CHECK (SRID(the_geom) = 54004)" PL/pgSQL function "addgeometrycolumn" line 100 at execute statement SQL statement "SELECT AddGeometryColumn?(,, $1 , $2 , $3 , $4 , $5 )" PL/pgSQL function "addgeometrycolumn" line 4 at select into variables routing=#

yancho

11/06/07 23:58:55

Message #215
Well, this is the first time to run into this. Indeed the geometry column is already there. You could check this with
```
\d victoria
SELECT * FROM geometry_columns;
```
To have the projection set correctly you needed to drop the geometry column and then add it with the right EPSG code 54004. Forget about the renaming then.
daniel

11/07/07 00:06:05

Message #217

I cannot even drop the column either :

routing=# select * from geometry_columns;

f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type

| public | victoria | the_geom | 2 | -1 | MULTILINESTRING

(1 row)

routing=# select dropgeometrycolumn ('routing','victoria','the_geom'); NOTICE: Invalid schema name - using current_schema() CONTEXT: SQL statement "SELECT DropGeometryColumn?(, $1 , $2 , $3 )" PL/pgSQL function "dropgeometrycolumn" line 4 at select into variables ERROR: column "the_geom" of relation "victoria" does not exist CONTEXT: SQL statement "ALTER TABLE public.victoria DROP COLUMN the_geom" PL/pgSQL function "dropgeometrycolumn" line 48 at execute statement SQL statement "SELECT DropGeometryColumn?(, $1 , $2 , $3 )" PL/pgSQL function "dropgeometrycolumn" line 4 at select into variables routing=#

yancho

11/07/07 00:17:46

Message #218

I did like this :

- Renamed back to the_geom - Deleted the column - Readded the column

routing=# ALTER TABLE victoria RENAME COLUMN geom TO the_geom; ALTER TABLE routing=# select dropgeometrycolumn ('routing','victoria','the_geom'); NOTICE: Invalid schema name - using current_schema() CONTEXT: SQL statement "SELECT DropGeometryColumn?(, $1 , $2 , $3 )" PL/pgSQL function "dropgeometrycolumn" line 4 at select into variables

dropgeometrycolumn

public.victoria.the_geom effectively removed.

(1 row)

routing=# SELECT AddGeometryColumn?('victoria','the_geom',54004,'MULTILINESTRING',2);

addgeometrycolumn

public.victoria.the_geom SRID:54004 TYPE:MULTILINESTRING DIMS:2

(1 row)

Do you think its good like this please?

yancho

11/07/07 00:38:13

Message #219

The problem still continues .. when I went to start the query regarding Dijkistra .. this is what I got

NOTICE: CREATE TABLE will create implicit sequence "vertices_tmp_id_seq" for serial column "vertices_tmp.id" CONTEXT: SQL statement "CREATE TABLE vertices_tmp (id serial)" PL/pgSQL function "assign_vertex_id" line 19 at execute statement ERROR: cannot EXECUTE a null querystring CONTEXT: PL/pgSQL function "assign_vertex_id" line 54 at execute statement routing=#

Could it be something wrong with my shape files? this is how i created the .sql : yancho@Ubuntu:~$ shp2pgsql -D -c NRN_BC_4_0_ROADSEG.shp victoria > victoria.sql

yancho

11/07/07 00:42:49

Message #216

I am still getting the same problem .. anything which I should change please?

Welcome to psql 8.1.4 (server 8.1.10), the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit

routing=# \i /home/yancho/gis/viktoria/victoria.sql BEGIN psql:/home/yancho/gis/viktoria/victoria.sql:46: NOTICE: CREATE TABLE will creat e implicit sequence "victoria_gid_seq" for serial column "victoria.gid" psql:/home/yancho/gis/viktoria/victoria.sql:46: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "victoria_pkey" for table "victoria" CREATE TABLE

addgeometrycolumn

public.victoria.the_geom SRID:-1 TYPE:MULTILINESTRING DIMS:2

(1 row)

COMMIT routing=# ALTER TABLE victoria RENAME COLUMN the_geom TO geom; ALTER TABLE routing=# SELECT AddGeometryColumn?('victoria','the_geom',54004,'MULTILINESTRING' ,2); ERROR: constraint "enforce_srid_the_geom" for relation "victoria" already exist s CONTEXT: SQL statement "ALTER TABLE public.victoria ADD CONSTRAINT enforce_srid _the_geom CHECK (SRID(the_geom) = 54004)" PL/pgSQL function "addgeometrycolumn" line 100 at execute statement SQL statement "SELECT AddGeometryColumn?(,, $1 , $2 , $3 , $4 , $5 )" PL/pgSQL function "addgeometrycolumn" line 4 at select into variables routing=#

yancho

11/07/07 00:07:04

Message #221

Is it possible, that your PostGIS dosn't work correctly?

Or could you try to use the victoria.sql file with the prepared data. You got your data via "shp2pgsql -D -c NRN_BC_4_0_ROADSEG.shp", right?

I'm currently too busy to look into this problem more. You could also try to download the VMWare image with Ubuntu on it. 40 people did the workshop at the FOSS4G workshop using the online tutorial and VMWare image,

I'll get back here again once I have a little time.

daniel

11/07/07 20:14:54

Message #224

The PostGIS should / is working perfectly, I mean I already did some other stuff with the PostGIS, created some shortest distance queries etc, so it is working

victoria.sql was created with that command, copy pasting from my terminal : yancho@Ubuntu:~$ shp2pgsql -D -c NRN_BC_4_0_ROADSEG.shp victoria > victoria.sql

Is there some IRC channel where maybe someone else can help me please? I appreciate your busy timetable and thank you for the interest you putting :) And the thing is I need to configure well my PostGIS not rely on another VMWare image, apart from that my system will halt with 2 OS's load on it .. old junk here hehe :)

Thanks and regards mate

yancho

11/08/07 21:36:03
Message #225

I am trying another way .. and now this query SELECT assign_vertex_id('victoria', 0.001, 'the_geom', 'gid'); is being processed .. no errors yet .. till now 1.3 m ms hehe .. will keep you updated whta happed :)

cheers

yancho

11/08/07 21:39:34

Message #227

Just would like to say that now it worked out .. and basically what I did different is that paragraph where the_geom is changed I disregarded it.

Now the shpfile of djskarta displayed perfect :) is it a of pgrouting that it displays the line in broken line ie - - - - .. as u can see here : http://solutions-lab.net/thesis/path.jpg ?

Thanks alot for ur help daniel :)

yancho

11/10/07 01:36:01

Message #229

It looks like the topology wasn't generated correctly.

anton

11/12/07 09:00:37

Tree View Flat View (newer first) Flat View (older first)