[pgrouting-users] Converting a shape file to a network

pgRouting pgrouting-users
1

Hi,

I am trying to convert a shapefile of linestrings into a suitable form for pgroute. The type of issue that I am getting is that from a casual look its looks fine, but if you drill down in to the map, you find there are some line segments should connect, but they don’t due to the way that the data was entered.

Q. Has anybody attempt this type of thing before and what if any solutions did they find ?
Dave.

2

Hi Dave,

Have you tried:

https://docs.pgrouting.org/2.6/en/pgr_nodeNetwork.html

Its has some issues, but does a reasonable work for pgRouting purposes.
Fixing the geometry would be using postGIS

···

On Thu, May 17, 2018 at 2:47 PM, Dave Potts <mrdapotts@gmail.com> wrote:

Hi,

I am trying to convert a shapefile of linestrings into a suitable form for pgroute. The type of issue that I am getting is that from a casual look its looks fine, but if you drill down in to the map, you find there are some line segments should connect, but they don’t due to the way that the data was entered.

Q. Has anybody attempt this type of thing before and what if any solutions did they find ?

Dave.

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

–

Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44, 
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky@[georepublic.de](http://georepublic.de)
Web: [https://georepublic.info](https://georepublic.info)

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
3

I tried this and got an error message to the effect missing column

med_proj=# \d tmp;
Table “public.tmp”
Column | Type | Collation | Nullable | Default

···

On 17 May 2018 at 21:35, Vicky Vergara <vicky@georepublic.de> wrote:

Hi Dave,

Have you tried:

https://docs.pgrouting.org/2.6/en/pgr_nodeNetwork.html

Its has some issues, but does a reasonable work for pgRouting purposes.
Fixing the geometry would be using postGIS

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

On Thu, May 17, 2018 at 2:47 PM, Dave Potts <mrdapotts@gmail.com> wrote:

Hi,

I am trying to convert a shapefile of linestrings into a suitable form for pgroute. The type of issue that I am getting is that from a casual look its looks fine, but if you drill down in to the map, you find there are some line segments should connect, but they don’t due to the way that the data was entered.

Q. Has anybody attempt this type of thing before and what if any solutions did they find ?

Dave.

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

–

Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44, 
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky@[georepublic.de](http://georepublic.de)
Web: [https://georepublic.info](https://georepublic.info)

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
4

Dave:

your table “tmp” does only has id and the_geom columns

···

On Sat, May 19, 2018 at 6:45 AM, Dave Potts <mrdapotts@gmail.com> wrote:

I tried this and got an error message to the effect missing column

med_proj=# \d tmp;
Table “public.tmp”
Column | Type | Collation | Nullable | Default
----------±------------------------±----------±---------±--------------------------------
id | integer | | not null | nextval(‘tmp_id_seq’::regclass)
the_geom | geometry(Geometry,3857) | | |

med_proj=# \d tmp_noded;
Table “public.tmp_noded”
Column | Type | Collation | Nullable | Default
----------±------------------------±----------±---------±--------------------------------------
id | integer | | not null | nextval(‘tmp_noded_id_seq’::regclass)
old_id | bigint | | |
sub_id | bigint | | |
source | bigint | | |
target | bigint | | |
the_geom | geometry(Geometry,3857) | | |

Invoking this, should put the results in tmp_noded, which according to the above and the manual at https://docs.pgrouting.org/2.6/en/pgr_nodeNetwork.html has all the right column

Doing this
med_proj=# select pgr_createTopology(‘tmp’,1);

Given an error, I don’t think there is anything wrong with the functions, it most be my table layout, but according to the documents,everything is correct.
NOTICE: PROCESSING:
NOTICE: pgr_createTopology(‘tmp’, 1, ‘the_geom’, ‘id’, ‘source’, ‘target’, rows_where := ‘true’, clean := f)
NOTICE: Performing checks, please wait …
NOTICE: ----> PGR ERROR in pgr_createTopology: Column source not found
HINT: ----> Check your column name
NOTICE: Unexpected error raise_exception
pgr_createtopology

FAIL

any ideas what I am doing wrong ?

Dave.

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

–

On 17 May 2018 at 21:35, Vicky Vergara <vicky@georepublic.de> wrote:

Hi Dave,

Have you tried:

https://docs.pgrouting.org/2.6/en/pgr_nodeNetwork.html

Its has some issues, but does a reasonable work for pgRouting purposes.
Fixing the geometry would be using postGIS

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

On Thu, May 17, 2018 at 2:47 PM, Dave Potts <mrdapotts@gmail.com> wrote:

Hi,

I am trying to convert a shapefile of linestrings into a suitable form for pgroute. The type of issue that I am getting is that from a casual look its looks fine, but if you drill down in to the map, you find there are some line segments should connect, but they don’t due to the way that the data was entered.

Q. Has anybody attempt this type of thing before and what if any solutions did they find ?

Dave.

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

–

Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44, 
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky@[georepublic.de](http://georepublic.de)
Web: [https://georepublic.info](https://georepublic.info)

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl


Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44, 
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky@[georepublic.de](http://georepublic.de)
Web: [https://georepublic.info](https://georepublic.info)

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
5

Got it sorted, thanks for the help

···

On 19 May 2018 at 15:47, Vicky Vergara <vicky@georepublic.de> wrote:

Dave:

your table “tmp” does only has id and the_geom columns

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

On Sat, May 19, 2018 at 6:45 AM, Dave Potts <mrdapotts@gmail.com> wrote:

I tried this and got an error message to the effect missing column

med_proj=# \d tmp;
Table “public.tmp”
Column | Type | Collation | Nullable | Default
----------±------------------------±----------±---------±--------------------------------
id | integer | | not null | nextval(‘tmp_id_seq’::regclass)
the_geom | geometry(Geometry,3857) | | |

med_proj=# \d tmp_noded;
Table “public.tmp_noded”
Column | Type | Collation | Nullable | Default
----------±------------------------±----------±---------±--------------------------------------
id | integer | | not null | nextval(‘tmp_noded_id_seq’::regclass)
old_id | bigint | | |
sub_id | bigint | | |
source | bigint | | |
target | bigint | | |
the_geom | geometry(Geometry,3857) | | |

Invoking this, should put the results in tmp_noded, which according to the above and the manual at https://docs.pgrouting.org/2.6/en/pgr_nodeNetwork.html has all the right column

Doing this
med_proj=# select pgr_createTopology(‘tmp’,1);

Given an error, I don’t think there is anything wrong with the functions, it most be my table layout, but according to the documents,everything is correct.
NOTICE: PROCESSING:
NOTICE: pgr_createTopology(‘tmp’, 1, ‘the_geom’, ‘id’, ‘source’, ‘target’, rows_where := ‘true’, clean := f)
NOTICE: Performing checks, please wait …
NOTICE: ----> PGR ERROR in pgr_createTopology: Column source not found
HINT: ----> Check your column name
NOTICE: Unexpected error raise_exception
pgr_createtopology

FAIL

any ideas what I am doing wrong ?

Dave.

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

–

Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44, 
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky@[georepublic.de](http://georepublic.de)
Web: [https://georepublic.info](https://georepublic.info)

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl

On 17 May 2018 at 21:35, Vicky Vergara <vicky@georepublic.de> wrote:

Hi Dave,

Have you tried:

https://docs.pgrouting.org/2.6/en/pgr_nodeNetwork.html

Its has some issues, but does a reasonable work for pgRouting purposes.
Fixing the geometry would be using postGIS

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

On Thu, May 17, 2018 at 2:47 PM, Dave Potts <mrdapotts@gmail.com> wrote:

Hi,

I am trying to convert a shapefile of linestrings into a suitable form for pgroute. The type of issue that I am getting is that from a casual look its looks fine, but if you drill down in to the map, you find there are some line segments should connect, but they don’t due to the way that the data was entered.

Q. Has anybody attempt this type of thing before and what if any solutions did they find ?

Dave.

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

–

Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44, 
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky@[georepublic.de](http://georepublic.de)
Web: [https://georepublic.info](https://georepublic.info)

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl