I'm trying to create a linestring out of geography points. At the moment I'm doing it like this:
select ST_GeographyFromText(ST_Makeline(a.record_point)) from ( select (record_point::geometry) from records order by record_timestamp) a
Problem is that most points have unnecessary decimal places (6 is sufficient) and some points are exactly the same. I can set the decimal place precision when converting to KML but is there another way to do this and how to get rid of overlapping points?
My own solution: It is PostgreSQL specific because of the window function. Basically I just leave out spatially equal sequential geometry points and reduce the precision with geojson converter (kml converter has the same option).
select st_asgeojson(ST_MakeLine(points.record_point),6) from ( select (record_point)::geometry as record_point lag( (record_point)::geometry, 1) over w as prev_point, from records window w as (order by record_id) order by record_timestamp ) points where not st_equals(record_point, prev_point)