2

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.

enter image description here

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.

enter image description here

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.

  • Is your input layer is a Point Layer ? You want to create a polyline each time the field "NumAntennas" change ? And ordered by "OBJECTID" ? In you example, "NumAntennas" takes two times the value 7, what result do you want ? One line (for all "NumAntennas" = 7) or two lines (one for each group) ? Note that QGIS has an algorithm points to lines. – J. Monticolo Dec 13 '19 at 12:24
  • Thank you for your answer! My input layer is a Point Layer. I want to create a polyline each time the field "NumAntennas" change. The "OBJECTID" field corresponds to the identifier of my points (gps). In my example, I would like 2 lines, 1 for each value 7. I have this case many times. I know this algorithm but I would have liked to do it under PostGIS or Python to avoid opening an interface. – Julien.kasma Dec 13 '19 at 12:41
  • Now, with QGIS, you can create a model with the algorithm and export it in Python. And after create a standalone Python script, see here : gis.stackexchange.com/a/279937/93097 – J. Monticolo Dec 13 '19 at 12:45
  • Thank you I will continue to search for the second result and I will surely draw my lines using the QGIS algorithm. – Julien.kasma Dec 13 '19 at 12:49
3

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")
| improve this answer | |
  • Thank you very much for your request in Python. I get exactly the result I want. I would like to know if it is possible to do it in SQL, I am waiting for the answer from @J. Monticolo. – Julien.kasma Dec 13 '19 at 13:34
4

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
;
| improve this answer | |
  • Thank you very much for this very interesting request. I'm mainly looking for a way to generate the values of the "Shape" field from the "NumAntennas" and "OBJECTID" fields as @Vincent Bré's answer. Do you think it's possible to do the same thing in SQL? – Julien.kasma Dec 13 '19 at 13:33
  • @Julien.kasma: answer edited – J. Monticolo Dec 13 '19 at 16:53
  • 1
    Thank you for your 2 answers. To generate the "Shape" field, I better understand @Vincent Bré's request but I keep yours under control because it is interesting and will be useful to me when I will have progressed in SQL. – Julien.kasma Dec 16 '19 at 14:28

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.