Using makeline() function for more than 2 points in spatialite?

Question

Is it possible to use makeline() function to make linestring geometries in spatialite for connecting more than 2 sequence points? Suppose I have four sequence points P1, P2, P3, P4. Or are there any other way to do this instead of using makeline()?

Answer 1

You could, of course, do this with conversion via Well Known Text:

spatialite> SELECT AsText(GeomFromText("LINESTRING(1 2, 3 4, 1 3)"));
AsText(GeomFromText("LINESTRING(1 2, 3 4, 1 3)"))
LINESTRING(1 2, 3 4, 1 3)

However it is definitely possible to use MakeLine() with more than two points.

Apart from the obvious version that you know about already, there are two more ways to call the MakeLine() function. The first is to use a MultiPoint geometry (if you already had one, or can make one from what you have):

spatialite> SELECT AsText(MakeLine(GeomFromText("MULTIPOINT(1 2,3 4,1 3)"), 0));
LINESTRING(1 3, 3 4, 1 2)
spatialite> SELECT AsText(MakeLine(GeomFromText("MULTIPOINT(1 2,3 4,1 3)"), 1));
LINESTRING(1 2, 3 4, 1 3)

The last argument (0 in the first example, 1 in the second) turns on reversal of order. You usually want this set to 1 (not reorder).

The other option is to use the aggregate function form. Lets say you have a table of point geometries:

spatialite> CREATE TABLE somepoints (label);
spatialite> SELECT AddGeometryColumn('somepoints', 'geom', 0, 'POINT', 'XY');
1
spatialite> INSERT INTO somepoints VALUES('a', MakePoint(1,2));
spatialite> INSERT INTO somepoints VALUES('b', MakePoint(3,4));
spatialite> INSERT INTO somepoints VALUES('c', MakePoint(1,3));

Lets see what that looks like:

spatialite> .headers on
spatialite> SELECT label, AsText(geom) FROM somepoints;
label|AsText(geom)
a|POINT(1 2)
b|POINT(3 4)
c|POINT(1 3)

Now you can select points to turn into a linestring:

spatialite> SELECT AsText(MakeLine(geom)) FROM somepoints;
AsText(MakeLine(geom))
LINESTRING(1 2, 3 4, 1 3)

Note that the SELECT could have some condition (WHERE clause) so you can filter if that suits your needs.