I am kind of new in using pgrouting and I would really like your help.
I have imported a road network, called "testNic" (from shapefile) into a PostgreSQL database using QGIS and pgAdmin. Then, I added the necessary columns, source, target and cost, and created the geom_idx. The problem that I am facing is that I cannot create the topology using the query:
I get the following error, and i can't locate where the mistake is.
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 14 at EXECUTE statement
ERROR: find_srid() - couldnt find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase missmatch?
CONTEXT: PL/pgSQL function "assign_vertex_id" line 20 at assignment
********** Error **********
ERROR: find_srid() - couldnt find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase missmatch?
SQL state: P0001
Context: PL/pgSQL function "assign_vertex_id" line 20 at assignment
As the error says, there seems to be something wrong with your SRID. You should check if the constraint on “the_geom” and the entry in the “geometry_columns” table are OK? Does it have an SRID or is it set -1?
I am kind of new in using pgrouting and I would really like your help.
I have imported a road network, called “testNic” (from shapefile) into a PostgreSQL database using QGIS and pgAdmin. Then, I added the necessary columns, source, target and cost, and created the geom_idx. The problem that I am facing is that I cannot create the topology using the query:
I get the following error, and i can’t locate where the mistake is.
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 14 at EXECUTE statement
ERROR: find_srid() - couldnt find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase missmatch?
CONTEXT: PL/pgSQL function “assign_vertex_id” line 20 at assignment
********** Error **********
ERROR: find_srid() - couldnt find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase missmatch?
SQL state: P0001
Context: PL/pgSQL function “assign_vertex_id” line 20 at assignment
As the error says, there seems to be something wrong with your SRID. You should check if the constraint on “the_geom” and the entry in the “geometry_columns” table are OK? Does it have an SRID or is it set -1?
I am kind of new in using pgrouting and I would really like your help.
I have imported a road network, called “testNic” (from shapefile) into a PostgreSQL database using QGIS and pgAdmin. Then, I added the necessary columns, source, target and cost, and created the geom_idx. The problem that I am facing is that I cannot create the topology using the query:
I get the following error, and i can’t locate where the mistake is.
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 14 at EXECUTE statement
ERROR: find_srid() - couldnt find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase missmatch?
CONTEXT: PL/pgSQL function “assign_vertex_id” line 20 at assignment
********** Error **********
ERROR: find_srid() - couldnt find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase missmatch?
SQL state: P0001
Context: PL/pgSQL function “assign_vertex_id” line 20 at assignment
As the error says, there seems to be something wrong with your SRID. You should check if the constraint on “the_geom” and the entry in the “geometry_columns” table are OK? Does it have an SRID or is it set -1?
I am kind of new in using pgrouting and I would really like your help.
I have imported a road network, called “testNic” (from shapefile) into a PostgreSQL database using QGIS and pgAdmin. Then, I added the necessary columns, source, target and cost, and created the geom_idx. The problem that I am facing is that I cannot create the topology using the query:
I get the following error, and i can’t locate where the mistake is.
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 14 at EXECUTE statement
ERROR: find_srid() - couldnt find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase missmatch?
CONTEXT: PL/pgSQL function “assign_vertex_id” line 20 at assignment
********** Error **********
ERROR: find_srid() - couldnt find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase missmatch?
SQL state: P0001
Context: PL/pgSQL function “assign_vertex_id” line 20 at assignment
Also, when you ran assign_vetrix_id() your tolerance was something like 0.000001 which is appropriate if your data is in degrees, but this Mercator projection is in meters so your tolerance should be something in a range like 5.0 - 0.5, maybe start with 1.0 and see how that works. if you rebuild you table and try different values for tolerance, you can look at the number of unique node numbers. When the number suddenly gets smaller you probably have it too large.
The shapefile which i imported was in google web mercator, so during
the import I set the SRID = 900913. The geom constraint has the same
value too.
How can I do the necessary checks?
With Regards,
Stephanos Charalambous
On 17 Φεβ 2011, at 16:58, Daniel Kastl <daniel@georepublic.de
<mailto:daniel@georepublic.de>> wrote:
Hello Stephanos,
As the error says, there seems to be something wrong with your
SRID. You should check if the constraint on "the_geom" and the
entry in the "geometry_columns" table are OK? Does it have an SRID
or is it set -1?
I am kind of new in using pgrouting and I would really like
your help.
I have imported a road network, called "testNic" (from
shapefile) into a PostgreSQL database using QGIS and pgAdmin.
Then, I added the necessary columns, source, target and cost,
and created the geom_idx. The problem that I am facing is that
I cannot create the topology using the query:
I get the following error, and i can't locate where the
mistake is.
NOTICE: CREATE TABLE will create implicit sequence
"vertices_tmp_id_seq" for serial column "vertices_tmp.id
<http://vertices_tmp.id>"
CONTEXT: SQL statement "CREATE TABLE vertices_tmp (id serial)"
PL/pgSQL function "assign_vertex_id" line 14 at EXECUTE statement
ERROR: find_srid() - couldnt find the corresponding SRID - is
the geometry registered in the GEOMETRY_COLUMNS table? Is
there an uppercase/lowercase missmatch?
CONTEXT: PL/pgSQL function "assign_vertex_id" line 20 at
assignment
********** Error **********
ERROR: find_srid() - couldnt find the corresponding SRID - is
the geometry registered in the GEOMETRY_COLUMNS table? Is
there an uppercase/lowercase missmatch?
SQL state: P0001
Context: PL/pgSQL function "assign_vertex_id" line 20 at
assignment
I tried the thinks that you have told me.
1. As Daniel suggested, I ran the command: psql gis "C:\Users\User\Desktop\900913.sql" in the psql shell and when I checked the file "spatial_ref_sys.sql", the entry 900913 is in the table.
2. As Stephen suggested, I edited the epsg file in the "proj" folder and added the Spherical Mercator projection entry.
3. I changed the assign_vertex_id command to:
SELECT assign_vertex_id('testNic', 1.0, 'the_geom', 'gid')
4. Restart Computer
THE RESULT: The same error occurs.
Where did I do wrong?
Thanks,
Stephanos
--- Στις Πέμ., 17/02/11, ο/η Stephen Woodbridge <woodbri@swoodbridge.com> έγραψε:
Από: Stephen Woodbridge <woodbri@swoodbridge.com>
Θέμα: Re: [pgrouting-dev] Cannot create topology
Προς: pgrouting-dev@lists.osgeo.org
Ημερομηνία: Πέμπτη, 17 Φεβρουάριος 2011, 20:27
You might also need to add it to:
Also, when you ran assign_vetrix_id() your tolerance was
something like
0.000001 which is appropriate if your data is in degrees,
but this
Mercator projection is in meters so your tolerance should
be something
in a range like 5.0 - 0.5, maybe start with 1.0 and see how
that works.
if you rebuild you table and try different values for
tolerance, you can
look at the number of unique node numbers. When the number
suddenly gets
smaller you probably have it too large.
-Steve
On 2/17/2011 1:14 PM, Daniel Kastl wrote:
> I think SRID = 900913 is not in the spatial_ref_sys
table, if you're
> using an older version of PostGIS.
> You can take it from here:
> http://trac.openstreetmap.org/browser/applications/utils/export/osm2pgsql/900913.sql
>
> <http://trac.openstreetmap.org/browser/applications/utils/export/osm2pgsql/900913.sql>Daniel
>
>
> 2011/2/18 Stephanos Charalambous <stephanos_ch@yahoo.gr
> <mailto:stephanos_ch@yahoo.gr>>
>
> Thanks Daniel for the quick
response.
>
> The shapefile which i imported
was in google web mercator, so during
> the import I set the SRID =
900913. The geom constraint has the same
> value too.
>
> How can I do the necessary
checks?
>
> With Regards,
>
> Stephanos Charalambous
>
> On 17 Φεβ 2011, at 16:58,
Daniel Kastl <daniel@georepublic.de
> <mailto:daniel@georepublic.de>>
wrote:
>
>> Hello Stephanos,
>>
>> As the error says, there
seems to be something wrong with your
>> SRID. You should check if
the constraint on "the_geom" and the
>> entry in the
"geometry_columns" table are OK? Does it have an SRID
>> or is it set -1?
>>
>> Daniel
>>
>>
>> 2011/2/17 Stephanos
Charalambous
>> <<mailto:stephanos_ch@yahoo.gr>stephanos_ch@yahoo.gr
>> <mailto:stephanos_ch@yahoo.gr>>
>>
>> Hello,
>>
>> I am kind of
new in using pgrouting and I would really like
>> your help.
>>
>> I have
imported a road network, called "testNic" (from
>> shapefile)
into a PostgreSQL database using QGIS and pgAdmin.
>> Then, I
added the necessary columns, source, target and cost,
>> and created
the geom_idx. The problem that I am facing is that
>> I cannot
create the topology using the query:
>>
>> SELECT
assign_vertex_id('testNic', 0.00001, 'the_geom', 'gid');
>>
>> I get the
following error, and i can't locate where the
>> mistake is.
>>
>>
NOTICE: CREATE TABLE will create
implicit sequence
>>
"vertices_tmp_id_seq" for serial column
"vertices_tmp.id
>> <http://vertices_tmp.id>"
>>
CONTEXT: SQL statement "CREATE TABLE
vertices_tmp (id serial)"
>> PL/pgSQL
function "assign_vertex_id" line 14 at EXECUTE statement
>> ERROR:
find_srid() - couldnt find the corresponding SRID - is
>> the geometry
registered in the GEOMETRY_COLUMNS table? Is
>> there an
uppercase/lowercase missmatch?
>>
CONTEXT: PL/pgSQL function
"assign_vertex_id" line 20 at
>> assignment
>>
>> **********
Error **********
>>
>> ERROR:
find_srid() - couldnt find the corresponding SRID - is
>> the geometry
registered in the GEOMETRY_COLUMNS table? Is
>> there an
uppercase/lowercase missmatch?
>> SQL state:
P0001
>> Context:
PL/pgSQL function "assign_vertex_id" line 20 at
>> assignment
>>
>> Any help
would be really appreciated.
>>
>> Regards
>> Stephanos
Charalambous
>>
>>
>>
_______________________________________________
>>
pgrouting-dev mailing list
>>
<mailto:pgrouting-dev@lists.osgeo.org>pgrouting-dev@lists.osgeo.org
>>
<mailto:pgrouting-dev@lists.osgeo.org>
>> <http://lists.osgeo.org/mailman/listinfo/pgrouting-dev>http://lists.osgeo.org/mailman/listinfo/pgrouting-dev
>>
>>
>>
>>
>> --
>> Georepublic UG &
Georepublic Japan
>> eMail:
>> <mailto:daniel.kastl@georepublic.de>daniel.kastl@georepublic.de
>> <mailto:daniel.kastl@georepublic.de>
>> Web: <http://georepublic.de/>http://georepublic.de
>>
_______________________________________________
>> pgrouting-dev mailing
list
>> pgrouting-dev@lists.osgeo.org
<mailto:pgrouting-dev@lists.osgeo.org>
>> http://lists.osgeo.org/mailman/listinfo/pgrouting-dev
>
>
_______________________________________________
> pgrouting-dev mailing list
> pgrouting-dev@lists.osgeo.org
<mailto:pgrouting-dev@lists.osgeo.org>
> http://lists.osgeo.org/mailman/listinfo/pgrouting-dev
>
>
>
>
> --
> Georepublic UG & Georepublic Japan
> eMail: daniel.kastl@georepublic.de
<mailto:daniel.kastl@georepublic.de>
> Web: http://georepublic.de/>
>
>
>
> _______________________________________________
> pgrouting-dev mailing list
> pgrouting-dev@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-dev
As Daniel suggested, I ran the command: psql gis “C:\Users\User\Desktop\900913.sql” in the psql shell and when I checked the file “spatial_ref_sys.sql”, the entry 900913 is in the table.
As Stephen suggested, I edited the epsg file in the “proj” folder and added the Spherical Mercator projection entry.
I changed the assign_vertex_id command to:
SELECT assign_vertex_id(‘testNic’, 1.0, ‘the_geom’, ‘gid’)
Restart Computer
THE RESULT: The same error occurs.
Where did I do wrong?
That’s not clear to me. Would you be able to use a newer version of PostGIS? I think they contain 900913 projection.
Or you could try to reproject your geometry once to WGS84 and then try again and see if this makes a difference.
Also, when you ran assign_vetrix_id() your tolerance was
something like
0.000001 which is appropriate if your data is in degrees,
but this
Mercator projection is in meters so your tolerance should
be something
in a range like 5.0 - 0.5, maybe start with 1.0 and see how
that works.
if you rebuild you table and try different values for
tolerance, you can
look at the number of unique node numbers. When the number
suddenly gets
smaller you probably have it too large.
As the error says, there
seems to be something wrong with your
SRID. You should check if
the constraint on “the_geom” and the
entry in the
“geometry_columns” table are OK? Does it have an SRID
or is it set -1?
I am kind of
new in using pgrouting and I would really like
your help.
I have
imported a road network, called “testNic” (from
shapefile)
into a PostgreSQL database using QGIS and pgAdmin.
Then, I
added the necessary columns, source, target and cost,
and created
the geom_idx. The problem that I am facing is that
I cannot
create the topology using the query:
CONTEXT: SQL statement “CREATE TABLE
vertices_tmp (id serial)”
PL/pgSQL
function “assign_vertex_id” line 14 at EXECUTE statement
ERROR:
find_srid() - couldnt find the corresponding SRID - is
the geometry
registered in the GEOMETRY_COLUMNS table? Is
there an
uppercase/lowercase missmatch?
CONTEXT: PL/pgSQL function
“assign_vertex_id” line 20 at
assignment
Error **********
ERROR:
find_srid() - couldnt find the corresponding SRID - is
the geometry
registered in the GEOMETRY_COLUMNS table? Is
there an
uppercase/lowercase missmatch?
SQL state:
P0001
Context:
PL/pgSQL function “assign_vertex_id” line 20 at
assignment
I also try to build topology on a WGS84 shapefile with no result
This could be a problem with my geometry data?
Hi Stephanos,
I’m sorry to hear that your so much struggling with this.
As this function seems to work for the majority of users, it could be a problem with your data as you said.
Have you tried to look at the workshop? The data there is in WGS84 projection, but there is a data.tar.gz file with sample data with and without topology. You could try it once and compare it with your data.