calculating distances using geographies
Foley, Peter (RIS-DBN)
Peter.Foley at lexisnexisrisk.com
Mon Jul 15 07:02:12 PDT 2024
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.foley at lexisnexisrisk.com<mailto:peter.foley at 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20240715/1eaba756/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Outlook-eotep3pw.png
Type: image/png
Size: 28867 bytes
Desc: Outlook-eotep3pw.png
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20240715/1eaba756/attachment.png>
More information about the postgis-users
mailing list