PostGIS Query – Converting ID to Multiline Query

postgispostgresql

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.

Best Answer

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()).

Related Question