CODEWITHSUNDEEP
postgresqlpostgisprecision
Vance Tunnicliffe
Vance Tunnicliffe

Reputation: 23

PostGIS ST_X() precision behaviour

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

Upvotes: 2

Views: 2379

Answers (1)

Jim Jones
Jim Jones

Reputation: 19643

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.

Upvotes: 1

Related Questions