Início / dba / Perguntas / 210758
Accepted
Randomize
Asked: 2018-06-28 10:21:29 +0800 CST

Como verificar se pelo menos um ponto cai em um polígono com o Postgis?

  • 772

Eu tenho duas tabelas onde a tabela Atem uma coluna de geometria (4326) que contém superfícies (~ 20M). Uma segunda tabela Btem ~ 500K pontos (4326). O que eu quero fazer é "sinalizar" (adicionando uma coluna booleana) cada superfície Aque contém pelo menos um dos pontos em B.

Eu fiz isso:

CREATE TABLE foo_data AS
    SELECT
        A.*,
        (SELECT EXISTS (SELECT ST_Contains(A.geom, B.geom) FROM B)) as flagged
    FROM A;

Mas eu tenho todos os valores "sinalizados" como true(o que é impossível).

Com joins/lateral não fui muito longe.

Qualquer ideia?

postgresql spatial

2 respostas

  1. Best Answer

    Você está perto. Eu não usaria EXISTS, o que é realmente útil apenas para reduzir um conjunto. Eu iria com CROSS JOIN LATERALe depois coalesceo resultado. Veja como isso funciona.

    SELECT a.id, 
  coalesce(result, false) AS flag
FROM a
CROSS JOIN LATERAL (
  SELECT true
  FROM b 
  WHERE ST_Contains(a.geom, b.geom)
  LIMIT 1
) AS r(result);

    Isso deve funcionar em um índice se você tiver um índice espacial em a.geome b.geom(e você deve ter índices para essa carga de trabalho). Isso também parece que você está armazenando em cache os resultados para meras melhorias de desempenho. Eu sugeriria em circunstâncias normais um MATERIALIZED VIEW.

    Alternativamente, você deve ser capaz de ir,

    SELECT 
  a.id, 
  bool_or(b.geom IS NOT NULL) AS flag
FROM a 
LEFT OUTER JOIN b
  ON ST_Contains(a.geom, b.geom)
GROUP BY a.id;
    • 2

  2. O problema é que ST_Contains retornará um valor em qualquer caso (ou seja, se o relacionamento espacial for verdadeiro/falso). Em vez disso, eu escreveria a consulta da seguinte forma:

       SELECT 
     a.id, 
     count(b.geom)>0 as flag
   FROM tbl a 
    LEFT JOIN tbl b 
   ON ST_Contains(a.geom, b.geom)
   GROUP BY a.id

    ou alternativamente:

       SELECT 
     a.id, 
     CASE WHEN count(b.geom)>0 THEN 1 ELSE 0 END as flag
   FROM tbl a 
    LEFT JOIN tbl b 
   ON ST_Contains(a.geom, b.geom)
   GROUP BY a.id
    • 0

relate perguntas