I have a table with three points in it and I want to create a line from them using PostGIS.

enter image description here

when I try to create a line from this geometry using:

select id, 
       st_makeline(geom order by id desc) as geom 
from public."point_table"
group by id;

I get null geometery

enter image description here

What am I doing incorrectly?

PostGIS version 2.2.1 PostgreSQL version 9.5.1

  • Which PostGIS version? – JGH Dec 14 '17 at 17:44
  • Does each row in your table have a multipoint geometry that has only one member? In that case you are feeding only one point for ST_MakeLine and that's not enough for making a line. Remove id from your select as well as group byand you may have better success. – user30184 Dec 14 '17 at 18:08
  • @JGH see edits. – Matt Dec 14 '17 at 18:26
  • @user30184, I tried that previously and again and the result is the same. – Matt Dec 14 '17 at 18:26

As per the doc, this feature is not available in the version you are using. You can try transforming the multi-points into simple points first, then creating the lines.

Availability: 2.3.0 - Support for multipoint input elements was introduced

|improve this answer|||||

You want just one line, so remove the GROUP BY clause:

select id, 
   st_makeline(geom order by id desc) as geom 
from point_table;

For a "real" example, if you have a table full of points from different lines, like:

CREATE TABLE line_pts (
  line_id integer,
  pt_id integer,
  geom Geometry(Point)

Then you'd group on the line_id:

select line_id, 
   st_makeline(geom order by pt_id desc) as geom 
from line_pts
group by line_id;
|improve this answer|||||
  • Hi Paul, saw your talk at FOSS4G and it was great! I tried removing the group by, but because it's an aggregate, it won't let me select the id field without grouping by it. – Matt Dec 14 '17 at 18:34
  • Of course not... you're putting three things into the line, how are the three ids supposed to fit into the one row of output? You could use array_agg to also group them up, I suppose. – Paul Ramsey Dec 14 '17 at 23:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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