TransWikia.com

PostGIS ST_X() precision behaviour

Stack Overflow Asked by Vance Tunnicliffe on November 22, 2021

We are investigating using PostGIS to perform some spacial filtering of data that has been gathered from a roving GPS engine. We have defined some start and end points that we use in our processing with the following table structure:

CREATE TABLE IF NOT EXISTS tracksegments
(
   idtracksegments   bigserial   NOT NULL,
   name              text,
   approxstartpoint  geometry,
   approxendpoint    geometry,
   maxpoints         integer
);

If the data in this table is queried:

SELECT ST_AsText(approxstartpoint) FROM tracksegments

we get …

POINT(-3.4525845 58.5133318)

Note that the Long/Lat points are given to 7 decimal places.

To get just the longitude element, we tried:

SELECT ST_X(approxstartpoint) AS long FROM tracksegments

we get …

-3.45

We need much more precision than the 2 decimal places that are returned. We’ve searched the documentation and there does not appear to be a way to set the level of precision. Any help would be appreciated.

Vance

One Answer

Your problem is definitely client related. Your client is most likely truncating double precision values for some reason. As ST_AsText returns a text value, it does not get affected by this behaviour.

ST_X does not truncate the coordinate's precision like that, e.g.

SELECT ST_X('POINT(-3.4525845 58.5133318)');
    st_x    
------------
 -3.4525845
(1 Zeile)

Tested with psql in PostgreSQL 9.5 + PostGIS 2.2 and PostgreSQL 12.3 + PostGIS 3.0 and with pgAdmin III

enter image description here

Note: PostgreSQL 9.5 is a pretty old release! Besides the fact that it will reach EOL next January, you're missing really kickass features in the newer releases. I sincerely recommend you to plan a system upgrade as soon as possible.

Answered by Jim Jones on November 22, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP