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
;
"NumAntennas"
change ? And ordered by"OBJECTID"
? In you example,"NumAntennas"
takes two times the value7
, what result do you want ? One line (for all"NumAntennas" = 7
) or two lines (one for each group) ? Note that QGIS has an algorithmpoints to lines
. – J. Monticolo Dec 13 '19 at 12:24