<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Century Gothic";
panose-1:2 11 5 2 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Century Gothic","sans-serif";
color:windowtext;
font-weight:normal;
font-style:normal;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Hello<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">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:
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">2016-04-15 14:40:15 BST LOG server process (PID 5044) was terminated by exception 0xC0000005
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">2016-04-15 14:40:15 BST LOG terminating any other active server processes
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">2016-04-15 14:40:15 BST WARNING terminating connection because of crash of another server process
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">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?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Postgres 9.2.15 64bit<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">PostGIS 2.2.1<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">pgRouting 2.1.0<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Windows Server 2008 r2<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">My SQL (could probably be done better)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">CREATE TEMPORARY TABLE node AS<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> SELECT id,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> ST_X(geometry) AS x,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> ST_Y(geometry) AS y,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> geometry<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FROM (<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> SELECT source AS id,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> ST_Startpoint(geometry) AS geometry<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FROM itn_network<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> UNION<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> SELECT target AS id,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> ST_Startpoint(geometry) AS geometry<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FROM itn_network<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> ) AS node;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">SELECT ST_SetSRID(ST_MakePolygon(ST_AddPoint(foo.openline, ST_StartPoint(foo.openline))),27700) AS geometry<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FROM (<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> SELECT ST_Makeline(points ORDER BY id) AS openline<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FROM (<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> SELECT row_number() over() AS id, ST_MakePoint(x, y) AS points
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FROM pgr_alphashape('<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> SELECT *<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FROM node<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> JOIN<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> (SELECT * FROM pgr_drivingDistance(''<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> SELECT gid AS id,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> source::int4 AS source,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> target::int4 AS target,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> cost_time::float8 AS cost,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> rcost_time::float8 AS reverse_cost<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FROM itn_network'',<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> 35819, --for this node<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> 700, --this fails when I set it to 600<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> true,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> true)) AS dd ON node.id = dd.id1'::text)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> ) AS a<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> ) AS foo;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Thanks<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Ross<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB">Ross McDonald
</span></b><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB">|<b>
</b>GIS Data Coordinator | Resources Department, IT Division | Angus Council, Angus House, Orchardbank Business Park, Forfar, DD8 1AT<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB">T:
<b>01307 476419</b> | F: 01307 476401 | E: <a href="mailto:mcdonaldr@angus.gov.uk">
<span style="color:#4F6228">mcdonaldr@angus.gov.uk</span></a> <b><o:p></o:p></b></span></p>
<p class="MsoNormal" align="center" style="text-align:center"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div> </div>
<div>
<p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt"><span style="FONT-SIZE: 7.5pt; FONT-FAMILY: 'Arial','sans-serif'; mso-bidi-font-size: 8.0pt; mso-bidi-font-family: 'Times New Roman'"><font size="2">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.<span style="mso-spacerun: yes">
</span>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.
</font></span></p>
</div>
</body>
</html>