[PostGIS] #5874: ST_LineSubstring returns incorrect lengths in geography

[PostGIS]

#5874: ST_LineSubstring returns incorrect lengths in geography
----------------------+-------------------------------------------
  Reporter:  jorsu    |      Owner:  pramsey
      Type:  defect   |     Status:  new
  Priority:  blocker  |  Milestone:  PostGIS 3.5.3
 Component:  postgis  |    Version:  3.5.x
Resolution:           |   Keywords:  linestring, geography, length
----------------------+-------------------------------------------
Description changed by jorsu:

Old description:

> I am encountering an issue with the {{{ST_LineSubstring}}} function. The
> results remain inconsistent when applying on a {{{geography}}}type.
>
> I am running:
>
> {{{
> PostgreSQL 17.4 (Debian 17.4-1.pgdg110+2) on x86_64-pc-linux-gnu,
> compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
> }}}
>
> {{{
> POSTGIS="3.5.2 dea6d0a" [EXTENSION] PGSQL="170" GEOS="3.9.0-CAPI-1.16.2"
> PROJ="7.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
> USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj
> DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 7.2.1)
> GDAL="GDAL 3.2.2, released 2021/03/05" LIBXML="2.9.10" LIBJSON="0.15"
> LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER
> }}}
>
> I am trying to:
>
> - Extract a subline from a {{{LINESTRING}}}.
> - Accurately measure its length in the {{{geography}}} type.
>
> However, when using {{{ST_LineSubstring}}} directly on a {{{geography}}}
> type, the resulting length is incorrect and does not match the expected
> value.
>
> Here is a minimal reproducible example:
>
> {{{
> WITH
>     "path" AS (
>         SELECT ST_SetSRID(ST_GeomFromText('LINESTRING(2.3522 48.8566,
> 2.3535 48.8570, 2.3548 48.8580, 2.3561 48.8590, 2.3574 48.8600)'), 4326)
> AS path
>     )
> SELECT
>     ST_Length(p."path"::geography) full_length,
>     ST_Length(
>          ST_LineSubstring(p."path"::geometry, 0, 1)::geography
>     ) geom_length,
>     ST_Length(
>          ST_LineSubstring(p."path"::geography, 0, 1)::geography
>     ) geog_length
> from "path" p;
> }}}
>
> This query returns:
>
> {{{
>    full_length         geom_length         geog_length
> 544.8029520664281   544.8029520664281   324.1448393269909
> }}}
>
> Why does {{{ST_LineSubstring}}} on a {{{geography}}} type produce a
> different length than the same operation on a geometry type and the
> original path?
>
> Is this a known issue?

New description:

 I am encountering an issue with the {{{ST_LineSubstring}}} function. The
 results remain inconsistent when applying on a {{{geography}}}type.

 I am running:

 {{{
 PostgreSQL 17.4 (Debian 17.4-1.pgdg110+2) on x86_64-pc-linux-gnu, compiled
 by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
 }}}

 {{{
 POSTGIS="3.5.2 dea6d0a" [EXTENSION] PGSQL="170" GEOS="3.9.0-CAPI-1.16.2"
 PROJ="7.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj
 DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 7.2.1)
 GDAL="GDAL 3.2.2, released 2021/03/05" LIBXML="2.9.10" LIBJSON="0.15"
 LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER
 }}}

 I am trying to:

 - Extract a subline from a {{{LINESTRING}}}.
 - Accurately measure its length in the {{{geography}}} type.

 However, when using {{{ST_LineSubstring}}} directly on a {{{geography}}}
 type, the resulting length is incorrect and does not match the expected
 value.

 Here is a minimal reproducible example:

 {{{
 WITH
     "path" AS (
         SELECT ST_SetSRID(ST_GeomFromText('LINESTRING(2.3522 48.8566,
 2.3535 48.8570, 2.3548 48.8580, 2.3561 48.8590, 2.3574 48.8600)'), 4326)
 AS path
     )
 SELECT
     ST_Length(p."path"::geography) full_length,
     ST_Length(
          ST_LineSubstring(p."path"::geometry, 0, 1)::geography
     ) geom_length,
     ST_Length(
          ST_LineSubstring(p."path"::geography, 0, 1)::geography
     ) geog_length
 from "path" p;
 }}}

 This query returns:

 {{{
    full_length         geom_length         geog_length
 544.8029520664281   544.8029520664281   324.1448393269909
 }}}

 Why does {{{ST_LineSubstring}}} on a {{{geography}}} type produce a
 different length than the same operation on a geometry type and the
 original path?

--
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5874#comment:1>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.