Converting PostGIS point to text causing loss of data?

Question

I have a road network and want to test connectivity between the features. I am having trouble with two specific features, the rest of the dataset is functioning just fine.

When I get those 2 links' start & end locations using ST_StartPoint and ST_EndPoint, the result is that they are slightly different. However, when I convert the result to text by wrapping it like so:

ST_AsText(ST_StartPoint(geom)) and ST_AsText(ST_EndPoint(geom))

then the results are exactly the same. Is it possible they are actually off by a tiny amount like .000000001 and the ST_AsText loses a decimal point?

Answer 1

You are likely correct in your thought that the conversion using ST_AsText is causing a loss of precision. This is actually stated as a warning in the documentation found here: ST_AsText

WKT format does not maintain precision so to prevent floating truncation, use ST_AsBinary or ST_AsEWKB format for transport.

Basically you want to do any operations while the geometry is in a binary form to ensure you get full precision.

Now, in the larger context of your question, this would seem to indicate that there is some gap between the start of one segment and the end of another. If they are supposed to be coincident, then you have two options:

1. Edit the layer and snap the endpoints together. Continue on.
2. If this data is supplied from an authoritative source, you may want to let them know about it so the data may be fixed there then propagated out again. This may take longer, but would contribute to better data quality in the long-run.

Answer 2

In postggis 2.5 you can set the precision.

select ST_AsText(geometry g1, integer maxdecimaldigits=15);