I have a query which return me two dimension data:

SELECT POINT(value , extract(EPOCH FROM datetime)) AS pseudo_geom
FROM table

gaves me


What I would like to do is to convert these data into a path type to be able to simplificate through ST_Simplify which accept a geometry type.

My goal is actually to reduce the number of points to display on a graph.

I tried with the path function and playing around with point(), string_agg() and grouping my data, with no result...

What could be the easiest and shortest way to do this?

2 Answers 2


I finally solved this using this scheme (not as simple as firstly expected):

SELECT ST_X(geom) as value, ST_Y(geom) as datetime from (
    select (ST_DumpPoints(simplifiedLine)).* as g1 from (
        SELECT ST_Simplify(ST_MakeLine(the_geom.resulted), 15) as simplifiedLine FROM (
            SELECT ST_MakePoint(value, extract(EPOCH FROM datetime)) as resulted FROM table 
            ORDER BY datetime ASC
        ) as the_geom
    ) as g   
) as pt

Hope that will help others.


In PostGIS a path is called line.

You can use ST_MakeLine to conncat your points to a line. So extend your query with somthinge like:

SELECT ST_Simplify(ST_MakeLine(POINT(value , extract(EPOCH FROM datetime)))) AS simplified_line
FROM table;

You should consider to define an order by clause.

  • Thanks for the track with ST_MakeLine! However I got `Error: the function st_makeline(point) does not exists. Commented Nov 6, 2015 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.