Perguntas[postgis](dba)

Eu tenho uma tabela no postgis da seguinte forma:

CREATE TABLE public.a_meter (
    smid integer NOT NULL,
    smgeometry public.geometry(Point,32640) NOT NULL,
    install_node_code text,
    "ID" text,
    proj_id text,
    voltage_level public.voltage_level_enum2,
    design_status public.mv_pole_des,
    serial text,
    feeder_name text,
    specific_id text,
    sequence_num smallint,
    amp_ratio text,
    man_year integer,
    ins_year integer,
    mspec_no text,
    description text,
    angle smallint DEFAULT 0,
    dist_name public.dist_name_enum,
    manufactor text GENERATED ALWAYS AS (public.get_manufactor(mspec_no)) STORED,
    spec_name text GENERATED ALWAYS AS (public.a_meter_spec_name(specific_id)) STORED,
    type_ad text GENERATED ALWAYS AS (public.a_meter_type_ad(specific_id)) STORED,
    maxmtr text GENERATED ALWAYS AS (public.a_meter_maxmtr(specific_id)) STORED,
    "Type" text GENERATED ALWAYS AS (public.a_meter_type(specific_id)) STORED,
    rat_cur text GENERATED ALWAYS AS (public.a_meter_rat_cur(specific_id)) STORED
);

Como é óbvio, existem várias colunas geradas nele. Eu faço um backup usando o seguinte script:

pg_dump.exe --file "C:\\Users\\rasam\\Desktop\\PG_gis.sql" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=p --inserts --table "public.a_meter" "electricity"

No entanto, o backup gerado não é um código SQL válido, pois o backup das colunas geradas não é feito corretamente. Os valores são armazenados como uma coluna normal da seguinte forma:

INSERT INTO public.a_meter VALUES (204, '0101000020807F00008FC2F528A22A204115AE47A1FFA14F41', '', 'AMP3153546', '', '', 'اجرا شده', '', 'نادر - PDS3570966 - 8', '7', NULL, '', NULL, NULL, '22', '', 180, 'بجنورد', 'BEW', 'AMP017', 'آنالوگ', 'ندارد', 'BE-96', '');

O estranho é que quando eu clone o esquema da tabela e preencho com os dados, o backup é produzido corretamente. Mas não posso fazer isso para todas as mesas de todos os meus clientes.

Sou iniciante no PostgreSQL e tenho duas tabelas A e B, ambas com várias colunas, incluindo uma coluna de ponto (geom):

TableA inclui um determinado 'ID', geom, etc , TableB é um "DB" de locais com uma coluna 'name', geom, etc...

Precisa encontrar para cada TableA.id qual é o 'nome' (colunas de linha) e a distância do local mais próximo na TabelaB.

já consegui calcular a distância entre todos os pontos da TabelaA e da TabelaB com:

ST_DistanceSpheroid(pointA,pointB,[SPHEROID])

Mas o melhor que se pode construir até agora, é uma consulta select onde fornece para cada uma TAbleA.IDtodas as distâncias e respectivos nomes da TabelaB - não encontra o mínimo para cada ID apenas.

Embora necessário ter as novas colunas calculadas (distância e nome) adicionadas à Tabela A.

Ainda não está claro para mim como trabalhar com saída de dados ao usar "selecionar" versus uma saída de dados de tabela real. ou uso de visualizações.

Estou recriando uma tabela que costumava ser atualizada por triggers, para os novos tipos de colunas geradas.

Estou com um problema em que, se a coluna gerada puder ser NULL, ela falhará silenciosamente e será inserida como NULL. Se as colunas forem, NOT NULLa inserção falhará como um arquivo NOT NULL constraint.

Ambos os casos falham e não geram os valores de célula esperados.

create table test_table
(
    coord_array          double precision[]                                                                                                   not null,
    wkb_geometry         geometry generated always as (st_setsrid(st_point(coord_array[0], coord_array[1]), 4326)) stored                     not null,
    wkt_geometry         text generated always as (st_astext(st_setsrid(st_point(coord_array[0], coord_array[1]), 4326))) stored              not null,
    geojson_coord_string jsonb generated always as ((st_asgeojson(st_setsrid(st_point(coord_array[0], coord_array[1]), 4326)))::jsonb) stored not null
);

O ideal é que a inserção só aconteça com valores paracoord_array

 insert into test_table (coord_array) values ('{25.0 ,-26.0}')

Eu preciso criar várias visualizações em dados geoespaciais, cada uma explicitamente lançada para a geometria correta e gostaria de criar um procedimento auxiliar para fazer isso assim:

CREATE PROCEDURE create_view_events(
  view_name TEXT, event_type TEXT, geo_type TEXT
) LANGUAGE plpgsql AS $$
BEGIN
  EXECUTE FORMAT('
     CREATE VIEW %I AS
     SELECT
       id,
       geom::%I AS geom   -- casting required, but how?
     FROM events
     WHERE type = %L
  ', view_name, geo_type, event_type);
END $$;
CALL create_view_events('events_viewX', 'X', GEOMETRY(POINT, 3347));

Executar manualmente a CREATE VIEWinstrução com a conversão substituída para geom::GEOMETRY(POINT, 3347) AS geomfunciona e executar o procedimento sem a conversão também funciona. No entanto, chamar através do procedimento como está gera esse erro, não sei o que está acontecendo.

ERROR: missing FROM-clause entry for table "GEOMETRY(POINT, 3347)"
  Where: PL/pgSQL function create_view_events(text,text,text) line 7 at EXECUTE

Não parece ser um WKB, já que é basicamente uma string, então o que é?

Estou usando o Postgis para:

• calcule a distância acumulada em metros ao longo da linha
• armazene a distância acumulada em "m"

Eu tenho uma tabela com pontos nesta linha e cada linha contém coordenadas lat e lon. O valor "m" é 0 para todas as linhas.

O código a seguir me dá bem a distância entre o primeiro ponto e o próximo.

SELECT a.geom
FLOOR(
ST_Distance(ST_Transform(a.geom, 3857), ST_Transform(b.geom, 3857)) * cosd(42.3521))
AS dist
FROM line a
LEFT JOIN line b
ON a.id = b.id + 1

no entanto, não soma um total nem atualiza o valor "m" ainda.

Eu tentei um conjunto de permutações do seguinte, mas nenhum deles armazena uma soma cumulativa do valor da linha anterior para "m" mais o cálculo da distância entre este ponto e o anterior.

WITH next AS (
    SELECT
    ST_Distance(ST_Transform(a.geom, 3857), ST_Transform(b.geom, 3857)) * cosd(42.3521)
    AS dist,
    a.id AS rowid,
    FROM line a
    JOIN line b
    ON a.id = b.id + 1
)
UPDATE line a
SET m = FLOOR(next.m + next.dist)
FROM next
WHERE a.id = next.rowid
RETURNING a.m, next.dist;

ou

update line a
    set m = FLOOR(a.m + prev.dist) 
    from (select
            l.*,
            ST_Distance(ST_Transform(
                lag(geom) over (order by l.id asc)
            , 3857), ST_Transform(geom, 3857)) * cosd(42.3521) as dist
            from line l
          ) AS prev
    where prev.id = a.id - 1
    returning prev.m, prev.dist, a.m

Alguma ideia?

Preciso determinar o tipo de coluna geométrica e fazendo isso com a seguinte query:

SELECT type 
FROM   geometry_columns 
WHERE  f_table_schema = 'public' 
       AND f_table_name = 'table_name' 
       AND f_geometry_column = 'col_name' 

Funciona bem para geometrias regulares, como Ponto ou Polígono. Mas também existem algumas geometrias extravagantes como PointZ. Como faço para determinar se a coluna tem isso? A consulta acima mencionada retorna apenas PONTO para ela.

Estou tentando instalar várias versões do PostGis e PgRouting na mesma instância do PostgreSQL v9.6, usando o CentOS 7.5.

Tenho lutado com dependências e bibliotecas compartilhadas, para fazer tudo funcionar.

Então a questão é, qual é a melhor maneira de gerenciar as versões de dependências? Se eu instalar uma versão de dependência principal, a versão antiga do Postgis ainda funcionará?

Algumas dependências são: GDAL, GEOS, Proj4, etc.

Estou organizando-os em pastas, contendo subpastas com a versão da dependência, usando --prefix=path/v3.0.0 no script ./configure durante a instalação da dependência.

Exemplo: Pasta -> GEOS - Subpastas: v3.0.0, v3.4.0, etc.

Depois disso, quando eu quiser instalar qualquer versão do PostGIS, eu uso ./configure para usar qualquer caminho absoluto de dependência.

Exemplo: ./configure --with-pgconfig=path --with-gdal=path/version --with-proj4=path/version ...

Mas não tenho certeza se esse é o caminho certo.

Qualquer sugestão é bem-vinda para limpar minha mente.

É minha primeira vez usando o Postgis, tenho lido um pouco sobre como usá-lo. Adicionei um tipo de dados de geografia à minha tabela, mas os valores são armazenados em um formato binário. É uma boa prática também armazenar lat e lon em colunas separadas como texto ou pontos?

O Google Cloud SQL suporta a extensão PostGIS, mas não vi nenhuma documentação sobre como trabalhar com dados raster nele. Para carregar dados raster em uma tabela, temos que usar o driver raster2pgsql.

Nesse sentido, o Cloud SQL é compatível? Além disso, seria muito útil se tivesse suporte raster fora do banco de dados, considerando que poderíamos armazenar dados no Cloud Storage e consultá-los a partir daí.