<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
Hi,</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
Following a recent upgrade from postgresql 10 / postgis 2.5.3 to posgresql 15.0 / postgis 3.4.2, we have noticed that distances calculated using geography types appear to return incorrect results in the new version. postgresql 10 / postgis 2.5.3 returns the
correct results.</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
For example, the following wgs84 polygons are 1.65m apart but st_distance and st_dwithin report them as 0m apart when casting them to geographies</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="padding-left: 2px;">
<div class="elementToProof" style="white-space: pre; margin: 0px; font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
<b>select</b> st_distance(<b>st_geometryfromtext</b>(<b>'POLYGON ((-1.7485534738529238 52.477989324720184, -1.7485528426344397 52.47797089391474, -1.748563887653701 52.47797046786393, -1.7485727216213993 52.47797048661752, -1.7485733502887397 52.47798936691661,
-1.7485739840754728 52.478007348228196, -1.748563677771212 52.47800732634904, -1.7485541076315525 52.47800730603188, -1.7485534738529238 52.477989324720184))'</b>,4326)::geography,<b>st_geometryfromtext</b>(<b>'POLYGON ((-1.748665164272404 52.47789651546308,
-1.748705653223533 52.477896601376905, -1.7487057222918938 52.47788446504611, -1.7487307518185344 52.47788451814952, -1.7487299491521497 52.47789620342481, -1.7487660211267058 52.477896279947124, -1.748767153309673 52.477956065736684, -1.7486648265496951 52.477955848635304,
-1.7485639721184771 52.477955634571074, -1.7484653236191374 52.47795587460495, -1.748465659049668 52.47789699092737, -1.7485017310241382 52.477897067530265, -1.7485018001486063 52.47788493119968, -1.7485297717654242 52.47788544009214, -1.7485297103282949 52.477896227941585,
-1.748562835091827 52.47789674776739, -1.7485996432291142 52.47789682590328, -1.7485997123266428 52.47788468957258, -1.7486276865033974 52.47788474894817, -1.748627619972449 52.477896435785155, -1.748665164272404 52.47789651546308))'</b>,4326)::geography,true)</div>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
st_distancespheroid using the geometries returns the correct distance:</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="padding-left: 2px;">
<div style="white-space: pre; margin: 0px; font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
<b>select</b> st_distancespheroid(<b>st_geometryfromtext</b>(<b>'POLYGON ((-1.7485534738529238 52.477989324720184, -1.7485528426344397 52.47797089391474, -1.748563887653701 52.47797046786393, -1.7485727216213993 52.47797048661752, -1.7485733502887397 52.47798936691661,
-1.7485739840754728 52.478007348228196, -1.748563677771212 52.47800732634904, -1.7485541076315525 52.47800730603188, -1.7485534738529238 52.477989324720184))'</b>,4326),<b>st_geometryfromtext</b>(<b>'POLYGON ((-1.748665164272404 52.47789651546308, -1.748705653223533
52.477896601376905, -1.7487057222918938 52.47788446504611, -1.7487307518185344 52.47788451814952, -1.7487299491521497 52.47789620342481, -1.7487660211267058 52.477896279947124, -1.748767153309673 52.477956065736684, -1.7486648265496951 52.477955848635304,
-1.7485639721184771 52.477955634571074, -1.7484653236191374 52.47795587460495, -1.748465659049668 52.47789699092737, -1.7485017310241382 52.477897067530265, -1.7485018001486063 52.47788493119968, -1.7485297717654242 52.47788544009214, -1.7485297103282949 52.477896227941585,
-1.748562835091827 52.47789674776739, -1.7485996432291142 52.47789682590328, -1.7485997123266428 52.47788468957258, -1.7486276865033974 52.47788474894817, -1.748627619972449 52.477896435785155, -1.748665164272404 52.47789651546308))'</b>,4326))</div>
<div class="elementToProof" style="white-space: pre; margin: 0px; font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
<br>
</div>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
Has anyone noticed this issue before? It occurs to me that there may be a configuration option that we need to set when installing postgis.</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
Many thanks,</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="Signature">
<p style="background-color: white;"><span style="font-size: 12pt; color: rgb(237, 28, 36);"><b>Peter Foley</b></span></p>
<p style="background-color: white;"><span style="color: black;">Senior Data Scientist II</span></p>
<p style="background-color: white; margin-top: 7.5pt;"><span style="font-size: 10pt; color: rgb(90, 90, 90);">++353 1 247 1527 Direct</span></p>
<p style="background-color: white;"><span style="font-size: 10pt; color: rgb(90, 90, 90);"><a href="mailto:peter.foley@lexisnexisrisk.com" style="color: rgb(90, 90, 90); margin-top: 0px; margin-bottom: 0px;">peter.foley@lexisnexisrisk.com</a></span></p>
<p><span style="font-size: 10pt; background-color: white;"><a href="https://risk.lexisnexis.com/" style="margin-top: 0px; margin-bottom: 0px;"><img width="375" height="112" style="width: 375px; height: 112px; margin-top: 0px; margin-bottom: 0px;" data-outlook-trace="F:1|T:1" src="cid:764600d2-550f-41fa-a0df-f611a45270f2"></a></span></p>
<p> </p>
</div>
<br>
<hr>
The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. This message may be an attorney-client communication and/or work product and as such is privileged and confidential. If
the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying
of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message.
</body>
</html>