I need some insight on how to get data out of a telemetry DB in just the right way and fumbling with this produces either errors or incorrect data
I have a table like this:
CREATE TABLE Telemetry ( pk SERIAL PRIMARY KEY NOT NULL, id VARCHAR(36) NOT NULL, organizationid VARCHAR(36) NOT NULL, lat REAL NOT NULL, lon REAL NOT NULL, basetype VARCHAR(16) NOT NULL, name VARCHAR(64) NOT NULL, updatetimestamp TIMESTAMP NOT NULL );
and a query like this:
select id, ST_Multi(point(lon,lat)::geometry) as cords from telemetry GROUP BY id;
But that produces:
 ERROR: column "telemetry.lon" must appear in the GROUP BY clause or be used in an aggregate function Position: 27
so, I give this a swing:
SELECT id, organizationid, basetype, name, ST_Multi(point(lon, lat) :: GEOMETRY) AS cords FROM telemetry GROUP BY id, organizationid, basetype, name, updatetimestamp, cords ORDER BY updatetimestamp;
which produces no error, but then doesn't group as I would expect.
d333aa7f-223d-4a13-8a4b-50e7ecbe424e,...,PACKAGE,xHSUPlzbizu8IOybrtDw,... 8e272cf7-bc03-4d70-b139-03081f6fa1b4,...,VEHICLE,... d333aa7f-223d-4a13-8a4b-50e7ecbe424e,...,PACKAGE,xHSUPlzbizu8IOybrtDw,...
d333.. repeats? What I need is
id,orgid,baseType -> [(lat,lonn),(lat,lon)...]
id, org, basetype will all be the same
I need all the telemetry positions for a given id sorted by time; each telemetry event is captured with a timestamp and I need each point in the multipoint to be added as it was found in time series (or it's no good).
In case it isn't obvious, I need to track positional data as something moved and draw these points on a map.