[postgis-users] st_transform performance after upgrade

[James Klassen]

On Wed, Aug 23, 2023 at 20:26 Mike Taves <mwtoews at gmail.com> wrote:

> On Thu, 24 Aug 2023 at 13:08, Bo Guo <bo.guo at gisticinc.com> wrote:
> > But we ran into severe (15 times) performance degradation when making CS
> transformation calls such as st_transform (geom, proj4, 4326)
>
> Note the docs say the geometry ST_Transform(geometry geom, text
> from_proj, text to_proj) is not optimized.
>
> Are you able to assign SRIDs to the geometries and use
> ST_Transform(geometry g1, integer srid)? Are there specific SRIDs that
> are slower?


I see your upgrade included going from Proj4 to Proj v6.  Depending on the
transformation, this could account for a large amount of the slowdown.
Proj v6 and later, in an effort to be more accurate than Proj4, takes less
shortcuts than Proj4 did.  This is particularly true if there are multiple
possibilities for how to achieve the transformation and which is best (most
accurate) could depend on location, or if there are datum transformations
involved.  Proj4 more or less ignored these by default and Proj 6+ tries to
take the more accurate approach which can take longer.

Also, Proj v6 was the first implementation of this new strategy. At this
point in time it is rather outdated.  My experience is Proj v7, v8, and v9
each improved performance.


> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230823/f8c4b44f/attachment.htm>