ST_ReducePrecision function does not work properly

Question

I am trying to use ST_ReducePrecision to reduce the number of characters, because of the character limit in power bi cells.

ST_AsText(ST_ReducePrecision(ST_Transform(geom, 4326), 0.000001))

The result that I'm geting is not what I was expecting:

Original WKT: MULTIPOLYGON(((-42.49076772971744 -22.940101731323654,-42.492057937951515 -22.9399517863236,-42.492703038099 -22.93987681565973,...

Using ST_ReducePrecision: MULTIPOLYGON(((-42.492058 -22.939951999999998,-42.492703 -22.939877,-42.493992999999996 -22.939726999999998,...

Expected result: MULTIPOLYGON(((-42.490768 -22.940102,-42.492058 -22.939952,-42.492703 -22.939877,...

Welcome to GIS SE. As a new user, please take the Tour. Unfortunately, your expectation is incorrect. The precision of the data did change, but that does not change unformatted presentation in ST_AsText. — Vince, Commented Dec 6, 2023 at 12:59

dr_jts · Accepted Answer · 2023-12-06 18:27:22Z

5

This is a GEOS bug that was fixed recently in GEOS-956. It's in GEOS 3.12.1, so upgrading might solve this problem.

answered Dec 6, 2023 at 18:27

dr_jts

5,28320 silver badges18 bronze badges

Add a comment |

bugmenot123 · Accepted Answer · 2023-12-06 21:48:46Z

4

ST_ReducePrecision changes the precision of the numeric values.

Floating point numbers are hard for computers so when you want them as text, weird issues can occur.

Try ST_AsText's maxdecimaldigits parameter in addition to it.

answered Dec 6, 2023 at 12:53

bugmenot123

11.3k3 gold badges38 silver badges75 bronze badges

1

ST_ReducePrecision also ensures that reducing the precision of polygonal inputs maintains correct topology so that the output is valid.
– dr_jts
Commented Dec 6, 2023 at 20:51
Oh nice, i edited my answer. Would st_reduceprecision always lead to st_astext using its precision once the geos bug is fixed?
– bugmenot123
Commented Dec 6, 2023 at 21:49
1

Yes, it should.
– dr_jts
Commented Dec 7, 2023 at 0:45
Thank you, I think I solved the problem using ST_AsEWKT: ST_AsText(ST_AsEWKT(ST_Transform(geom, 4326), 6)) Is this ok?
– Matheus Clarkson
Commented Dec 7, 2023 at 12:15

Add a comment |

2 Answers 2