Início / dba / Perguntas / 159615
Accepted
Christian Bongiorno
Asked: 2016-12-31 12:14:28 +0800 CST

id de consulta postgis para consulta multilinha

  • 772

Preciso de algumas dicas sobre como obter dados de um banco de dados de telemetria da maneira certa e me atrapalhar com isso produz erros ou dados incorretos

Tenho uma tabela assim:

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
);

e uma consulta como esta:

select id, ST_Multi(point(lon,lat)::geometry) as cords from telemetry GROUP BY id;

Mas isso produz:

[42803] ERRO: a coluna "telemetry.lon" deve aparecer na cláusula GROUP BY ou ser usada em uma função agregada Posição: 27

então, eu dou um balanço:

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;

que não produz nenhum erro, mas não agrupa como eu esperaria.

d333aa7f-223d-4a13-8a4b-50e7ecbe424e,...,PACKAGE,xHSUPlzbizu8IOybrtDw,...
8e272cf7-bc03-4d70-b139-03081f6fa1b4,...,VEHICLE,...
d333aa7f-223d-4a13-8a4b-50e7ecbe424e,...,PACKAGE,xHSUPlzbizu8IOybrtDw,...

Observe como d333..se repete? O que eu preciso é

id,orgid,baseType -> [(lat,lonn),(lat,lon)...]

id, org, basetype serão todos iguais

Preciso de todas as posições de telemetria para um determinado id classificado por tempo; cada evento de telemetria é capturado com um carimbo de data/hora e preciso que cada ponto no multiponto seja adicionado conforme foi encontrado na série temporal (ou não adianta).

Caso não seja óbvio, preciso rastrear dados posicionais como algo movido e desenhar esses pontos em um mapa.

postgresql postgis

1 respostas

  1. Best Answer

    Existem alguns problemas aqui.

    1. A coluna id em seu esquema não é declarada como exclusiva. O que você faria se tivesse duas identidades iguais? @a_horse_with_no_name lê isso como o , mas por que o maior? Qual é o critério?

      INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,1);
INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,2);

    2. Você está usando point(). Point é uma função de tipo nativo. Se você estiver usando o PostGIS, não use point(). Em vez disso, crie seus pontos com ST_MakePoint. (salva o elenco).

    3. Você deve considerar não armazenar seus tipos como pontos de latitude/log, mas armazená-los como geometry, ou geography. Isso será mais rápido, embora custe um pouco mais no espaço. Além disso, funcionará melhor com a indexação.

    A primeira leitura deste problema é abordada por,@a_horse_with_no_name mas estou pensando que você deseja um multiponto para cada idna tabela. Para isso você precisa de um agregado PostGIS, como ST_Union

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

    Isso deve funcionar.

    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)

    Você pode forçar um MULTIPOINTse realmente quiser isso envolvendo ST_Unioncomo ST_Multi(ST_Union()).

    • 4

relate perguntas