[pgrouting-users] alphashape with less than 3 points crashing server process

pgRouting pgrouting-users
1

Hello

I’ve got some SQL wrapped up in a function to calculate alphashapes for multiple input points. The input points have all been assigned a “nearest_node” id and this is used in the SQL. The problem is where I have one point at the end of a long road and when I try to calculate an alphashape with a value that does not return 3 points to make a polygon it crashes the server process. In PgAdmin I see the log has the following:

2016-04-15 14:40:15 BST LOG server process (PID 5044) was terminated by exception 0xC0000005

2016-04-15 14:40:15 BST LOG terminating any other active server processes

2016-04-15 14:40:15 BST WARNING terminating connection because of crash of another server process

So, I guess my question is, is there any way I can get my function to fail gracefully when I hit this exception and stop my connection terminating and the process crashing?

Postgres 9.2.15 64bit

PostGIS 2.2.1

pgRouting 2.1.0

Windows Server 2008 r2

My SQL (could probably be done better)

CREATE TEMPORARY TABLE node AS

SELECT id,

ST_X(geometry) AS x,

ST_Y(geometry) AS y,

geometry

FROM (

SELECT source AS id,

ST_Startpoint(geometry) AS geometry

FROM itn_network

UNION

SELECT target AS id,

ST_Startpoint(geometry) AS geometry

FROM itn_network

) AS node;

SELECT ST_SetSRID(ST_MakePolygon(ST_AddPoint(foo.openline, ST_StartPoint(foo.openline))),27700) AS geometry

FROM (

SELECT ST_Makeline(points ORDER BY id) AS openline

FROM (

SELECT row_number() over() AS id, ST_MakePoint(x, y) AS points

FROM pgr_alphashape(’

SELECT *

FROM node

JOIN

(SELECT * FROM pgr_drivingDistance(‘’

SELECT gid AS id,

source::int4 AS source,

target::int4 AS target,

cost_time::float8 AS cost,

rcost_time::float8 AS reverse_cost

FROM itn_network’',

35819, --for this node

700, --this fails when I set it to 600

true,

true)) AS dd ON node.id = dd.id1’::text)

) AS a

) AS foo;

Thanks

Ross

Ross McDonald | GIS Data Coordinator | Resources Department, IT Division | Angus Council, Angus House, Orchardbank Business Park, Forfar, DD8 1AT

T: 01307 476419 | F: 01307 476401 | E: mcdonaldr@angus.gov.uk

2

Hello Ross:
I wonder if you can post this issue in (while I start working on it)
https://github.com/pgRouting/pgrouting/issues

please also post the node table data sample that makes it crash.

So that we can be able to reproduce the problem.

I must say, that version 2.2 is to be announced this weekend so it will not be fixed in 2.2.0

But I’ll see what I can do for a v2.2.1 patch.

Vicky

···

On Fri, Apr 15, 2016 at 9:03 AM, McDonaldR <McDonaldR@angus.gov.uk> wrote:

Hello

I’ve got some SQL wrapped up in a function to calculate alphashapes for multiple input points. The input points have all been assigned a “nearest_node” id and this is used in the SQL. The problem is where I have one point at the end of a long road and when I try to calculate an alphashape with a value that does not return 3 points to make a polygon it crashes the server process. In PgAdmin I see the log has the following:

2016-04-15 14:40:15 BST LOG server process (PID 5044) was terminated by exception 0xC0000005

2016-04-15 14:40:15 BST LOG terminating any other active server processes

2016-04-15 14:40:15 BST WARNING terminating connection because of crash of another server process

So, I guess my question is, is there any way I can get my function to fail gracefully when I hit this exception and stop my connection terminating and the process crashing?

Postgres 9.2.15 64bit

PostGIS 2.2.1

pgRouting 2.1.0

Windows Server 2008 r2

My SQL (could probably be done better)

CREATE TEMPORARY TABLE node AS

SELECT id,

ST_X(geometry) AS x,

ST_Y(geometry) AS y,

geometry

FROM (

SELECT source AS id,

ST_Startpoint(geometry) AS geometry

FROM itn_network

UNION

SELECT target AS id,

ST_Startpoint(geometry) AS geometry

FROM itn_network

) AS node;

SELECT ST_SetSRID(ST_MakePolygon(ST_AddPoint(foo.openline, ST_StartPoint(foo.openline))),27700) AS geometry

FROM (

SELECT ST_Makeline(points ORDER BY id) AS openline

FROM (

SELECT row_number() over() AS id, ST_MakePoint(x, y) AS points

FROM pgr_alphashape(’

SELECT *

FROM node

JOIN

(SELECT * FROM pgr_drivingDistance(‘’

SELECT gid AS id,

source::int4 AS source,

target::int4 AS target,

cost_time::float8 AS cost,

rcost_time::float8 AS reverse_cost

FROM itn_network’',

35819, --for this node

700, --this fails when I set it to 600

true,

true)) AS dd ON node.id = dd.id1’::text)

) AS a

) AS foo;

Thanks

Ross

Ross McDonald | GIS Data Coordinator | Resources Department, IT Division | Angus Council, Angus House, Orchardbank Business Park, Forfar, DD8 1AT

T: 01307 476419 | F: 01307 476401 | E: mcdonaldr@angus.gov.uk

This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment.

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://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

Thanks Vicky,

I’ll try get the issue and node table posted on github next week.

The node that is causing the problem is the node at the end of a long stretch of road with no other nodes within the specified cost of ~650 seconds (I’m working with travel time as cost). If I ignore the node then my function works fine. If I adjust the cost of the link in question then I can make it work too. But that’s enough for a Friday evening…

Have a good weekend and looking forward to 2.2.0

Thanks

Ross

···

Hello Ross:
I wonder if you can post this issue in (while I start working on it)
https://github.com/pgRouting/pgrouting/issues

please also post the node table data sample that makes it crash.

So that we can be able to reproduce the problem.

I must say, that version 2.2 is to be announced this weekend so it will not be fixed in 2.2.0

But I’ll see what I can do for a v2.2.1 patch.

Vicky

On Fri, Apr 15, 2016 at 9:03 AM, McDonaldR <McDonaldR@angus.gov.uk> wrote:

Hello

I’ve got some SQL wrapped up in a function to calculate alphashapes for multiple input points. The input points have all been assigned a “nearest_node” id and this is used in the SQL. The problem is where I have one point at the end of a long road and when I try to calculate an alphashape with a value that does not return 3 points to make a polygon it crashes the server process. In PgAdmin I see the log has the following:

2016-04-15 14:40:15 BST LOG server process (PID 5044) was terminated by exception 0xC0000005

2016-04-15 14:40:15 BST LOG terminating any other active server processes

2016-04-15 14:40:15 BST WARNING terminating connection because of crash of another server process

So, I guess my question is, is there any way I can get my function to fail gracefully when I hit this exception and stop my connection terminating and the process crashing?

Postgres 9.2.15 64bit

PostGIS 2.2.1

pgRouting 2.1.0

Windows Server 2008 r2

My SQL (could probably be done better)

CREATE TEMPORARY TABLE node AS

SELECT id,

ST_X(geometry) AS x,

ST_Y(geometry) AS y,

geometry

FROM (

SELECT source AS id,

ST_Startpoint(geometry) AS geometry

FROM itn_network

UNION

SELECT target AS id,

ST_Startpoint(geometry) AS geometry

FROM itn_network

) AS node;

SELECT ST_SetSRID(ST_MakePolygon(ST_AddPoint(foo.openline, ST_StartPoint(foo.openline))),27700) AS geometry

FROM (

SELECT ST_Makeline(points ORDER BY id) AS openline

FROM (

SELECT row_number() over() AS id, ST_MakePoint(x, y) AS points

FROM pgr_alphashape(’

SELECT *

FROM node

JOIN

(SELECT * FROM pgr_drivingDistance(‘’

SELECT gid AS id,

source::int4 AS source,

target::int4 AS target,

cost_time::float8 AS cost,

rcost_time::float8 AS reverse_cost

FROM itn_network’',

35819, --for this node

700, --this fails when I set it to 600

true,

true)) AS dd ON node.id = dd.id1’::text)

) AS a

) AS foo;

Thanks

Ross

Ross McDonald | GIS Data Coordinator | Resources Department, IT Division | Angus Council, Angus House, Orchardbank Business Park, Forfar, DD8 1AT

T: 01307 476419 | F: 01307 476401 | E: mcdonaldr@angus.gov.uk

This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment.

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://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

2016-04-15 14:40:15 BST LOG server process (PID 5044) was terminated by exception 0xC0000005

2016-04-15 14:40:15 BST LOG terminating any other active server processes

2016-04-15 14:40:15 BST WARNING terminating connection because of crash of another server process

So, I guess my question is, is there any way I can get my function to fail gracefully when I hit this exception and stop my connection terminating and the process crashing?

5

On 4/15/2016 9:32 PM, Daniel Kastl wrote:

2016-04-15 14:40:15 BST LOG server process (PID 5044) was terminated
by exception 0xC0000005

2016-04-15 14:40:15 BST LOG terminating any other active server
processes

2016-04-15 14:40:15 BST WARNING terminating connection because of
crash of another server process

So, I guess my question is, is there any way I can get my function to
fail gracefully when I hit this exception and stop my connection
terminating and the process crashing?

I remember, that this was an issue in the past, but I thought it had be
resolved long time ago by returning an error and not crashing the
process, so I don't even remember when.
We will look into this. Thanks for reporting.

The short answer is this is a bug and the way to stop the server from crashing is to fix the bug. The best way to do this is to find a case where you can repeat the issue and file a test case, then we can debug the issues and put in a fix for it.

The reason for the crash is because when something bad and unexpected happens the database can not be sure that all the other processes are in a stable state so it kills all the database processes so that the database will get recovered into a known stable state. While this may be annoying it is better than continuing with possible damaged and inconsistent data that will impact future results or potential destroy more data or return bad data.

If you have a test case that fails we would really like to get it so we can address this problem.

Thanks,
   -Steve

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

6

it doesnt have the try catch

···

On Fri, Apr 15, 2016 at 11:03 PM, Stephen Woodbridge <woodbri@swoodbridge.com> wrote:

On 4/15/2016 9:32 PM, Daniel Kastl wrote:

2016-04-15 14:40:15 BST LOG server process (PID 5044) was terminated
by exception 0xC0000005

2016-04-15 14:40:15 BST LOG terminating any other active server
processes

2016-04-15 14:40:15 BST WARNING terminating connection because of
crash of another server process

So, I guess my question is, is there any way I can get my function to
fail gracefully when I hit this exception and stop my connection
terminating and the process crashing?

I remember, that this was an issue in the past, but I thought it had be
resolved long time ago by returning an error and not crashing the
process, so I don’t even remember when.
We will look into this. Thanks for reporting.

The short answer is this is a bug and the way to stop the server from crashing is to fix the bug. The best way to do this is to find a case where you can repeat the issue and file a test case, then we can debug the issues and put in a fix for it.

The reason for the crash is because when something bad and unexpected happens the database can not be sure that all the other processes are in a stable state so it kills all the database processes so that the database will get recovered into a known stable state. While this may be annoying it is better than continuing with possible damaged and inconsistent data that will impact future results or potential destroy more data or return bad data.

If you have a test case that fails we would really like to get it so we can address this problem.

Thanks,
-Steve

This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://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
7

It may be missing a try catch, but is the input data valid ?

16 April 2016 08:00, “Vicky Vergara” <vicky@georepublic.de> wrote:

it doesnt have the try catch

On Fri, Apr 15, 2016 at 11:03 PM, Stephen Woodbridge <woodbri@swoodbridge.com> wrote:

On 4/15/2016 9:32 PM, Daniel Kastl wrote:

2016-04-15 14:40:15 BST LOG server process (PID 5044) was terminated
by exception 0xC0000005

2016-04-15 14:40:15 BST LOG terminating any other active server
processes

2016-04-15 14:40:15 BST WARNING terminating connection because of
crash of another server process

So, I guess my question is, is there any way I can get my function to
fail gracefully when I hit this exception and stop my connection
terminating and the process crashing?

I remember, that this was an issue in the past, but I thought it had be
resolved long time ago by returning an error and not crashing the
process, so I don’t even remember when.
We will look into this. Thanks for reporting.

The short answer is this is a bug and the way to stop the server from crashing is to fix the bug. The best way to do this is to find a case where you can repeat the issue and file a test case, then we can debug the issues and put in a fix for it.

The reason for the crash is because when something bad and unexpected happens the database can not be sure that all the other processes are in a stable state so it kills all the database processes so that the database will get recovered into a known stable state. While this may be annoying it is better than continuing with possible damaged and inconsistent data that will impact future results or potential destroy more data or return bad data.

If you have a test case that fails we would really like to get it so we can address this problem.

Thanks,
-Steve

This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://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
8

New issue logged on Github together with sample network edges and nodes.

https://github.com/pgRouting/pgrouting/issues/544

Thanks

Ross

···

From: Pgrouting-users [mailto:pgrouting-users-bounces@lists.osgeo.org] On Behalf Of dave.potts@pinan.co.uk
Sent: 16 April 2016 08:36
To: pgRouting users mailing list
Subject: Re: [pgrouting-users] alphashape with less than 3 points crashing server process

It may be missing a try catch, but is the input data valid ?

16 April 2016 08:00, “Vicky Vergara” <vicky@georepublic.de> wrote:

it doesnt have the try catch

On Fri, Apr 15, 2016 at 11:03 PM, Stephen Woodbridge <woodbri@swoodbridge.com> wrote:

On 4/15/2016 9:32 PM, Daniel Kastl wrote:

2016-04-15 14:40:15 BST LOG server process (PID 5044) was terminated
by exception 0xC0000005

2016-04-15 14:40:15 BST LOG terminating any other active server
processes

2016-04-15 14:40:15 BST WARNING terminating connection because of
crash of another server process

So, I guess my question is, is there any way I can get my function to
fail gracefully when I hit this exception and stop my connection
terminating and the process crashing?

I remember, that this was an issue in the past, but I thought it had be
resolved long time ago by returning an error and not crashing the
process, so I don’t even remember when.
We will look into this. Thanks for reporting.

The short answer is this is a bug and the way to stop the server from crashing is to fix the bug. The best way to do this is to find a case where you can repeat the issue and file a test case, then we can debug the issues and put in a fix for it.

The reason for the crash is because when something bad and unexpected happens the database can not be sure that all the other processes are in a stable state so it kills all the database processes so that the database will get recovered into a known stable state. While this may be annoying it is better than continuing with possible damaged and inconsistent data that will impact future results or potential destroy more data or return bad data.

If you have a test case that fails we would really like to get it so we can address this problem.

Thanks,
-Steve

This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://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