[GIS] How to create a efficent linestring out of geography points using postgis

geojsonkmllinestringpointpostgis

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)

Best Answer

PostGIS has a "cleangeometry()" function which I believe works like SpatiaLite's "SanitizeGeometry()".

My mantra is that there is never a single solution in GIS, so here's two other off-beat approachs:

  1. If you have multiple lines you could pack them all in a multilinestring and use the ST_LineMerge function. It will kill the duplicates and join the lines efficiently. You could then explode the multilinestring
  2. You could simplify the geometery with a small tolerance. Simple and effective :)
Related Question