3

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:

[42803] 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,...

Notice how 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.

4

There are a few problems here.

  1. The id column in your schema is not declared as unique. What would you do if you had two of the same ids? @a_horse_with_no_name reads this as , but why the greatest? What's the criteria for?

    INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,1);
    INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,2);
    
  2. You're using point(). Point is a native-type function. If you're using PostGIS do not use point(). Instead, create your points with ST_MakePoint. (saves the casting).

  3. You should consider not storing your types as lat/log points, but instead storing them as geometry, or geography. This will be faster, though it'll cost a bit more in space. Further, it'll work with indexing better.

The first reading of this problem is addressed by @a_horse_with_no_name but I'm thinking you want a multipoint for every id in the table. For that you need a PostGIS aggregate, like ST_Union

SELECT id, ST_Union(ST_point(lon,lat)) AS cords
FROM telemetry
GROUP BY id;

That should work.

SELECT id,
  ST_AsText(ST_Union(ST_MakePoint(lon,lat))) AS cords_with_union,
  ST_AsText(ST_Collect(ST_MakePoint(lon,lat))) AS cords_with_collect
FROM ( VALUES
    (1, 1::real, 2::real),
    (1, 2::real, 2::real),
    (1, 2::real, 2::real), -- a duplicate (for id:1)
    (2, 6::real, 5::real)
) AS t(id,lon,lat)
GROUP BY id;

 id |  cords_with_union   |   cords_with_collect    
----+---------------------+-------------------------
  1 | MULTIPOINT(1 2,2 2) | MULTIPOINT(1 2,2 2,2 2)
  2 | POINT(6 5)          | MULTIPOINT(6 5)

You can force a MULTIPOINT if you really want that by wrapping ST_Union like ST_Multi(ST_Union()).

  • Final query: SELECT DISTINCT ON (id) id, organizationid, basetype, name, st_union(position::geometry) AS cords FROM foo GROUP BY id, organizationid, basetype, name, updatetimestamp ORDER BY id, updatetimestamp; My only outstanding question: The points collected will be in chronological order? Because there is no 'updatetimestamp' being returned. – Christian Bongiorno Jan 2 '17 at 18:18
  • Why are you using DISTINCT ON, and GROUP BY. What you probably want is SELECT foo.*, t.geom FROM foo INNER JOIN (SELECT id, ST_Union(position::geometry) AS geom FROM foo GROUP BY ID) AS t USING (id); – Evan Carroll Jan 2 '17 at 18:23

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.