Search code examples
sqlpostgresqlpostgis

DBCException: Invalid number of points in LineString (found 1 - must be 0 or >= 2)

I have a postgres database with a table called gps_points. This table has a column index, latitude and longitude

Using the following command I have been able to add and populate a point geometry column point_geom.

ALTER TABLE gps_points ADD COLUMN point_geom geometry(Point, 4326);
UPDATE gps_points SET point_geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

enter image description here

Now I would like to create linestrings based on these points. I was hoping I could achieve this by the following command:

ALTER TABLE gps_points ADD COLUMN line_geom geometry(Linestring,4326);
INSERT INTO gps_points (line_geom)
SELECT ST_MakeLine (gps.point_geom ORDER BY index) AS line_geom
    FROM gps_points AS gps
    GROUP BY gps.index;

However, all rows in the resulting column show the following error:

DBCException: Invalid number of points in LineString (found 1 - must be 0 or >= 2)

Anyone a suggestion what I'm missing?

Solution

  • In your screenshot we can see that there is only a single record per index (which is btw not a recommended label for a column) and the error message says that you need more than one point in order to have a proper LineString. That being said, you have to either look for another column to GROUP BY your points, or just create a single line with the entire table:

    SELECT ST_MakeLine(point_geom ORDER BY index) FROM gps_points;

    Example:

    CREATE TEMPORARY TABLE gps_points (
  index SERIAL,
  latitude NUMERIC,
  longitude NUMERIC
);

    Inserting three distinct points

    INSERT INTO gps_points VALUES 
  (1,52.37,4.92),(2,52.37,4.93),(3,52.375,4.92);

    Creating the geometry column and populating it as you posted

    ALTER TABLE gps_points ADD COLUMN point_geom geometry(Point, 4326);
UPDATE gps_points SET point_geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

    Creating a LineString based on the three points above

    SELECT ST_MakeLine(point_geom ORDER BY index) FROM gps_points;

    enter image description here

    If you need to create a table to access it from a GIS, just run ..

    CREATE TABLE my_long_linestring AS
SELECT ST_MakeLine(point_geom ORDER BY index) FROM gps_points;

    .. and open this new table in your GIS:

    enter image description here

    Note: PostGIS will allow you to create a LineString with a single point (see example bellow), but it would maybe create other problems in your system.. specially in viewers.

    SELECT ST_AsText(ST_MakeLine('SRID=4326;POINT(1 2)'::geometry));
    st_astext    
-----------------
 LINESTRING(1 2)