Hi,
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.
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
select st_distance(st_geometryfromtext('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))',4326)::geography,st_geometryfromtext('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))',4326)::geography,true)
st_distancespheroid using the geometries returns the correct distance:
select st_distancespheroid(st_geometryfromtext('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))',4326),st_geometryfromtext('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))',4326))
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.
Many thanks,
Peter Foley
Senior Data Scientist II
++353 1 247 1527 Direct
peter.fo...@lexisnexisrisk.com<mailto:peter.fo...@lexisnexisrisk.com>
[cid:764600d2-550f-41fa-a0df-f611a45270f2]<https://risk.lexisnexis.com/>
________________________________
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.
