ST_MakeLine based on a field or equivalent

Question

I want to create a set of lines taking into account the value of an attribute. As soon as the value of this attribute changes, then I start a new line. Currently, I use the FME LineBuilder tool where I specify a field in the "Connection Break Attributes" settings.

I would like to do the same thing using Python or PostGIS. I have explored several courses on the PostGIS side. I saw that I could use the ST_MakeLine function by using the value of a field. So, I tried to achieve the following result first. As long as the value of the "NumAntennas" field does not change then the "Shape" field takes the same value, otherwise I have an increment of 1, etc.

I tried to do this with functions that would look like row_number() or dense_rank() but I didn't succeed. I don't know if there is a function that would meet my need or if I need to make a loop in PL/pgSQL or Python.

Answer 1

You can achieve what you want with the following SQL codes :

BEWARE : this code will drop a table public.gps and a view public.new_lines, make sure that the table and the view don't already exist to avoid dropping data.

• First, create a table public.gps and populate it with sample data :

--DROP TABLE IF EXISTS public.gps;
CREATE TABLE public.gps (
  objectid SERIAL PRIMARY KEY,
  numantennas INTEGER,
  shape INTEGER
);

SELECT AddGeometryColumn('public', 'gps', 'geom', 4326, 'POINT', 2);

INSERT INTO public.gps (numantennas, shape, geom) VALUES
(16, 0, ST_GeomFromText('POINT(1 1)', 4326)),
(16, 0, ST_GeomFromText('POINT(1 2)', 4326)),
(7, 1, ST_GeomFromText('POINT(1 3)', 4326)),
(7, 1, ST_GeomFromText('POINT(2 3)', 4326)),
(7, 1, ST_GeomFromText('POINT(2 4)', 4326)),
(7, 1, ST_GeomFromText('POINT(2 5)', 4326)),
(8, 2, ST_GeomFromText('POINT(3 4)', 4326)),
(8, 2, ST_GeomFromText('POINT(4 4)', 4326)),
(8, 2, ST_GeomFromText('POINT(5 4)', 4326)),
(7, 3, ST_GeomFromText('POINT(4 3)', 4326)),
(7, 3, ST_GeomFromText('POINT(3 2)', 4326)),
(7, 3, ST_GeomFromText('POINT(2 1)', 4326))
;

• Make a query for creating the view (you can just use the SELECT for a table creation or a bind with Python Psycopg2 or ORM) :

--DROP VIEW IF EXISTS public.new_lines;
CREATE VIEW public.new_lines AS
SELECT
  gps.numantennas,
  gps.shape,
  ST_MakeLine(array_agg(geom)) AS new_geom
FROM
  (SELECT * FROM public.gps ORDER BY objectid) gps
GROUP BY
  gps.numantennas,
  gps.shape
;

• Finally, load this view under QGIS by selecting numantennas and shape as Feature ID's (for unique identification).

---

For generate automatically the "shape" field, it's a little bit tricky :

WITH RECURSIVE t1 AS (
  SELECT
    gps.objectid,
    gps.numantennas,
    lag(gps.objectid) OVER(ORDER BY gps.objectid) AS idprec
  FROM
    public.gps
  ORDER BY
    gps.objectid
),

t2 AS (
  SELECT
    t1.objectid,
    t1.numantennas,
    t1.idprec,
    0 AS shape
  FROM
    t1
  WHERE
    t1.idprec IS NULL

  UNION ALL

  SELECT
    t1.objectid,
    t1.numantennas,
    t1.idprec,
    CASE
      WHEN t1.numantennas = t2.numantennas THEN shape
      WHEN t1.numantennas <> t2.numantennas THEN shape + 1
    END
  FROM
    t2 INNER JOIN t1 ON t1.idprec = t2.objectid
)

SELECT
  gps.numantennas,
  t2.shape,
  ST_MakeLine(array_agg(geom)) AS new_geom
FROM
  (SELECT * FROM public.gps ORDER BY objectid) gps
  INNER JOIN t2 ON t2.objectid = gps.objectid
GROUP BY
  gps.numantennas,
  t2.shape
;

Answer 2

My next answer involves working on a csv file and using the pandas module of Python. This module also allows the export and import of a csv file from a database.

import pandas as pd 
# Read my csv
df = pd.read_csv("test.csv")

# Initialization of a counter that will correspond to the value in your "Shape" field
counter = 0

# Iteration on my csv file
for i, row in df.iterrows():

    # Value of the "NumAntennas" field
    value_numAntennas = row['NumAntennas']

    # Try because I have an index error on the first line (see below)
    try :

        # If the line has the same value as the previous one then the counter does not move 
        # and the same value is applied
        # the i corresponds to the index of the line
        if value_numAntennas == df.loc[i-1,'NumAntennas']:
            df.at[i,'Shape'] = counter

        # If the line has a different value than the previous one then the counter increments by 1 
        # and the new value is applied
        else :
            counter += 1
            df.at[i, 'Shape'] = counter

    # Error for the first line because index -1 does not exist
    except KeyError:
        df.at[i, 'Shape'] = counter

# Export in the new csv
df.to_csv("test.csv")