Mais

Sintaxe PostGIS (função ()). *

Sintaxe PostGIS (função ()). *


Enquanto lia a documentação sobre como usar a função PostGIS ST_PixelAsCentroids () fiquei um pouco confuso com isso(ST_PixelAsPoints (rast, 1)). *sintaxe e como um novato em SQL, gostaria de saber se alguém poderia explicar esta convenção e por que ela é usada?

Obviamente, é usado como um método para descompactar resultados complexos, mas eu nem sei como se chama isso no Google para obter uma compreensão formal dele.

Sei que esta postagem pode ser mais adequada para um fórum de SQL no Stack, mas vou ver o que o GIS Stack vai sugerir.


A função retorna uma tabela com colunas digitadas (um esquema) e linhas (valores). O.*no final significa "todas as colunas da tabela".SELECIONE * DE f (x)daria uma saída semelhante aSELECIONE (f (x)). *.

Devido a uma peculiaridade do PostgreSQL, funções com múltiplas colunas podem ser chamadas uma vez para cada coluna pelo planejador de consultas nos casos acima. Portanto, é aconselhável usar uma expressão de tabela comum (CTE) ou subconsulta para evitar esse comportamento, escrevendo, por exemplo:

SELECT (mf). * FROM (SELECT my_func (x) AS mf FROM alguma_tabela OFFSET 0) sub;

ou em PostgreSQL 9.3

SELECT mf. * FROM alguma_tabela ESQUERDA JOIN LATERAL minha_func (alguma_tabela.x) AS mf ON true;

Esta é mais uma questão de PostgreSQL do que uma questão de PostGIS, mas ainda assim é uma boa pergunta.

Funções comoST_PixelAsPoints,ST_PixelAsPolygons, eST_PixelAsCentroidsretorna um conjunto de tipo de dados de registro composto, que possui vários campos. Isso é muito semelhante ao ST_Dump, descrito aqui. Para obter acesso a todos os campos retornados por esses tipos de dados compostos, eles precisam estar no formulário(função (x)). *, conforme descrito no manual do PostgreSQL.

Observe quefunção (x). *não vai funcionar, e é um pegadinho comum.


Problema do vizinho mais próximo no Postgis 2.0 usando o índice GIST (função)

Eu & # 8217m tentando usar a nova função Postgis 2.0 & lt- & gt (Geometria Distância Centróide) a fim de calcular, para cada linha da minha tabela (cosn1), a distância para o polígono mais próximo da mesma classe.

Eu estava tentando usar o seguinte código:

Mas então eu percebo o aviso:

Nota: O índice só entra em ação se uma das geometrias for uma constante (não em uma subconsulta / cte). por exemplo. & # 8216SRID = 3005POINT (1011102 450541) & # 8217 :: geometria em vez de a.geom

O que significa que o índice não será usado e a consulta levará quase o mesmo tempo que antes de usar:

Alguém pode me indicar uma solução alternativa que me permita melhorar o desempenho da minha consulta?

Uma resposta

Fazer alguns testes em minha máquina sugeriu que este operador & lt- & gt não está funcionando corretamente. Não tenho certeza se é um bug, mas relatou distância zero em geometrias não sobrepostas.

Eu tentei as otimizações de consulta SQL tradicionais justas. Como esses resultados inesperados com operador & lt- & gt, eu o substituo por st_centroid. Obteve resultados muito melhores em velocidade.

Espero que a semântica com st_overlaps continue a mesma. Pelo menos isso foi entendido pela documentação sobre & lt- & gt

Para outros tipos de geometria, a distância entre os centróides da caixa delimitadora de ponto flutuante é retornada.

5,5k polígonos ganharam velocidade de

5 segundos sem indexação espacial.

Eu vejo algumas pessoas usando DISTINCT ON para fazer agrupamento, mas não o group by existe para eliminar duplicatas.

Sua consulta com otimizações de SQL padrão sem o erro st_centroid introduzido


4.5. Linestrings¶

UMA linestring é um caminho entre locais. Tem a forma de uma série ordenada de dois ou mais pontos. Estradas e rios são normalmente representados como cadeias de linhas. Uma cadeia de linha é considerada fechado se começa e termina no mesmo ponto. É dito ser simples se ele não se cruzar ou se tocar (exceto em suas extremidades se estiver fechado). Uma cadeia de linha pode ser ambos fechado e simples.

A rede de ruas de Nova York (nyc_streets) foi carregada anteriormente no workshop. Este conjunto de dados contém detalhes como nome e tipo. Uma única rua do mundo real pode consistir em várias cadeias de linhas, cada uma representando um segmento de estrada com atributos diferentes.

A consulta SQL a seguir retornará a geometria associada a uma cadeia de linha (na coluna ST_AsText).

Algumas das funções espaciais específicas para trabalhar com cadeias de linhas são:

ST_Length (geometria) retorna o comprimento da cadeia de linha

ST_StartPoint (geometry) retorna a primeira coordenada como um ponto

ST_EndPoint (geometria) retorna a última coordenada como um ponto

ST_NPoints (geometria) retorna o número de coordenadas na cadeia de linha

Portanto, o comprimento de nossa cadeia de linha é:


Junte-se espacial com PostGIS

Vimos o conjunto de dados e verificamos o CRS do conjunto de dados. Tudo está pronto agora. Para unir os dois conjuntos de dados, podemos usar diferentes relações espaciais, incluindo ST_Within, ST_Contains, ST_Covers ou ST_Crosses. Neste exemplo, estamos usando ST_Within para descobrir qual ponto está dentro de qual polígono.

O resultado é esta tabela com listagens do Airbnb com uma coluna adicional indicando a qual código de área estatístico cada ponto pertence, conforme mostrado na tabela abaixo.

Ótimo! A junção espacial poderia ser seu resultado final se você quisesse descobrir apenas aonde cada ponto pertence. No entanto, podemos precisar resumir o resultado da junção espacial para descobrir insights sobre a distribuição do conjunto de dados.


Vamos nos familiarizar com a interface GUI do PgAdmin que usaremos neste tutorial. O seguinte GIF mostra a interface. Também estamos criando uma extensão PostGIS, que traz as funções espaciais que usaremos mais tarde.

Podemos simplesmente criar uma extensão PostGIS com o seguinte comando implementado na ferramenta PgAdmin Query.

Depois de executar este comando, temos muitas novas funções espaciais disponíveis à nossa disposição.

Vamos também criar um banco de dados usando PgAdmin4. As instruções GIF a seguir mostram como criar um novo banco de dados.


Polígonos

Os polígonos são construídos a partir de LineStrings fechadas. O polígono contém toda a área fechada e seu limite, enquanto a cadeia de linha fechada no limite é chamada de anel externo

No próximo exemplo, estamos calculando a área de cada edifício usando a função ST_Area (geom) no PostGIS.

Aqui estão alguns destaques das funções Polygon em PostGIS:

    , ST_Polygon, ST_PolygonFromText → Para criar uma geometria poligonal. → Retorna uma string de linha que representa o anel externo da geometria POLYGON. Retorne NULL se a geometria não for um polígono. Não funcionará com MULTIPOLYGON - Se a geometria for um polígono ou multipolígono retorna o número de anéis. - Retorna verdadeiro se a geometria A estiver completamente dentro da geometria B.

Seus primeiros passos com o tipo de dados Geografia

Os aplicativos geográficos estão em todos os lugares: GPS e sistemas de navegação por satélite, mapas, aplicativos para pegar um táxi, portais imobiliários, etc. Atrás de cada um deles está um banco de dados espacial que armazena dados geográficos e oferece suporte a consultas espaciais. Neste artigo, apresentaremos o PostGIS, o principal gerenciador de banco de dados espacial de código aberto.

PostGIS é uma extensão de banco de dados espacial para o banco de dados relacional PostgreSQL. Ele adiciona suporte para objetos geográficos, permitindo que consultas de localização sejam executadas em SQL.

PostGIS adiciona dois tipos de dados principais ao PostgreSQL: geografia e geometria. Ambos permitem o armazenamento de pontos em uma tabela, bem como outras formas mais complexas como linhas (uma linha é definida por dois pontos), linhas multiponto (definidas por N pontos), polígonos (definidos por uma linha multiponto fechada) e pontos com uma altitude específica (definida por uma terceira coordenada). Este extensor também oferece um conjunto de funções espaciais para cálculo de distância, cálculo de área, interseção e inclusão, entre muitos outros. Todos esses novos tipos de dados e funções podem ser usados ​​em combinação com dados relacionais regulares em SQL, aumentando o poder das consultas.

Neste artigo, cobriremos as primeiras etapas do uso do PostGIS, explorando recursos como o tipo de dados geográficos e algumas funções espaciais como distância, área e interseção. Para ilustrar à medida que avançamos, incluiremos alguns exemplos de SQL.

Vamos começar explicando o bloco de construção básico em um banco de dados espacial: um par de coordenadas que representa um único ponto na superfície da Terra.

O GEOGRAFIA Tipo de dados

O primeiro recurso do PostGIS que aprenderemos é o tipo de dados geográficos. Começaremos usando-o para representar um ponto definido por duas coordenadas: latitude e longitude. Como nosso exemplo neste artigo, usaremos a tabela obra de arte , que armazena uma lista das obras de arte mais importantes do mundo e onde estão sendo exibidas. Esta tabela terá o seguinte esquema:

Primeiro, inseriremos alguns dados relacionais para algumas obras-primas de arte. Observe que os campos de latitude e longitude ainda são números flutuantes e estão sendo armazenados como dados relacionais regulares, não espaciais.

Para preencher a coluna where_is, precisamos chamar o PostGIS ST_POINT função. Isso retorna um geografia ponto de dados, como vemos na seguinte instrução SQL:

Após esta atualização, temos nosso primeiro geografia pontos de dados no banco de dados. Se tentarmos ver seus valores com um SELECT, veremos apenas valores hexadecimais:

Temos dois itens a esclarecer aqui. A primeira é quando usamos o geografia tipo de dados, omitimos a menção à categoria. PostGIS assumiu que nos referíamos à categoria padrão, que é PONTO. No entanto - e como já mencionamos - podemos armazenar muitas categorias diferentes usando o geografia tipo de dados.

O segundo item está relacionado aos valores na coluna where_is. Esses valores são armazenados internamente no PostGIS usando o formato WKB (Well Known Binary), e não podemos entender prontamente o que eles representam neste formato. Precisamos de algum software para interpretar seu significado, por exemplo, se usarmos QGIS, podemos mostrar os quatro pontos geográficos em um mapa. Dê uma olhada nos resultados na seguinte captura de tela:

Texto conhecido (WKT)

O parágrafo anterior abre a porta para um conceito central no padrão de banco de dados espacial. Cada elemento espacial (como um ponto, linha ou polígono) pode ser expresso ou definido usando dois formatos diferentes:

Vamos dedicar um minuto aqui para falar sobre o formato de texto conhecido. Como vimos no exemplo anterior, cada elemento geográfico no PostGIS é representado internamente como uma string de dígitos hexadecimais no formato WKB. Obviamente, isso é muito difícil para os humanos entenderem. O WKT é uma maneira mais clara e amigável para representar qualquer geometria / geografia elemento. Alguns exemplos de WKTs são:

Além disso, uma função chamada ST_AsText () pode obter o WKT de qualquer geometria ou geografia elemento espacial, como podemos ver no exemplo a seguir:

Também existe uma função chamada ST_GeoFromText () . Esta é a função inversa, dado um formato WKT, ela retorna a geografia associada.

A função espacial mais simples: distância

Neste artigo, abordamos apenas os tipos de dados geográficos baseados na latitude e longitude. No entanto, PostGIS nos permite representar e armazenar outros elementos espaciais (como um ponto geográfico com base na latitude, longitude e altitude) e o geometria tipo de dados (que permite a representação de pontos espaciais de duas ou três coordenadas de forma simples).

A base para o PostGIS geometria tipo de dados é um plano. O caminho mais curto entre dois pontos em um plano é uma linha reta. Isso significa que cálculos de geometrias (áreas, distâncias, comprimentos, interseções, etc.) podem ser feitos usando matemática cartesiana e vetores de linha reta.

Por outro lado, a base para o PostGIS geografia tipo de dados é uma esfera. O caminho mais curto entre dois pontos na esfera é um grande arco de círculo. Isso significa que os cálculos geográficos (áreas, distâncias, comprimentos, interseções, etc.) devem ser baseados em uma esfera, usando matemática mais complicada. Além disso, a Terra não é uma esfera perfeita, então os cálculos devem considerar o mundo como uma forma esferoidal. Muito complicado! Vamos continuar usando PostGIS.

Vamos voltar ao distância função para pontos geográficos. A consulta SQL a seguir calcula a distância entre nossas quatro obras-primas, mostrando todas as combinações possíveis.

Por definição, os resultados de distância no PostGIS são expressos em metros. Quem preferir ver os resultados em pés deve converter de metros em pés, usando a seguinte expressão matemática simples:

Nós não apenas armazenamos pontos: linhas, polígonos, multipontos

O geografia tipo de dados tem um modificador que especifica que tipo de elemento iremos armazenar na coluna. Em nosso primeiro exemplo, não especificamos o modificador, portanto, por padrão, armazenamos um PONTO. No entanto, podemos armazenar LINHAS, POLÍGONOS e até mesmo formas espaciais complexas como MULTIPONTOS (uma matriz de pontos). Por exemplo, se quisermos armazenar um polígono para representar o perímetro de um museu, podemos criar a seguinte tabela:

Para criar um valor de polígono, usaremos a função PostGIS ST_GeoFromText. Como já mencionamos, isso retorna um valor de geometria quando recebe uma representação WKT. O WKT para um polígono tem a seguinte forma:

Observe que o primeiro ponto e o último ponto são iguais para definir um polígono, é obrigatório o uso de uma multilinha fechada. As próximas inserções de SQL definem registros de museu com seus perímetros:

Nota: Os perímetros não são estritamente reais, mas incluem a área do museu.

Calculando a área de formas complexas

Agora que temos uma tabela com um polígono definindo o perímetro de cada museu, podemos usar o ST_AREA função para obter a área de cada museu. Faremos isso usando uma consulta realmente simples.

O ponto interessante aqui é a simplicidade. Pense em como pode ser complexo calcular a área de uma forma irregular. Aqui estamos fazendo isso com uma boa consulta curta:

Nota: os perímetros do museu são estimativas, portanto os valores da área são aproximados.

Conhecendo as condições de associação

Se revisarmos o esquema das tabelas do museu e das obras de arte, não há chaves estrangeiras que permitem uma junção. Como relacionar uma obra de arte com o museu onde ela está sendo exibida? Existe alguma condição espacial para unir as duas tabelas? Vamos dar uma olhada na seguinte junção SQL:

Experimente você mesmo: um exercício para o leitor

Os bancos de dados espaciais têm elementos, funções e algoritmos mais complexos, mas os conceitos básicos que discutimos estão presentes por trás de cada elemento espacial. Um elemento não coberto aqui é a terceira dimensão de um ponto. Lembre-se de que os bancos de dados espaciais também nos permitem representar a altitude de um determinado ponto. Vamos imaginar um museu onde duas obras de arte estão nas mesmas coordenadas latitudinal e longitudinal, mas em andares diferentes.


é fundamental para eficiente

Normalização é a formalização do processo de design para tornar um banco de dados compatível com o conceito de Forma Normal.

Ele aborda várias maneiras pelas quais podemos procurar valores de dados repetidos em uma tabela.

Existem vários níveis da Forma Normal e cada nível requer que o nível anterior seja satisfeito.

O processo de normalização é baseado na coleta de uma lista exaustiva de todos os itens de dados a serem mantidos no banco de dados e no início do projeto com algumas tabelas & quotsuperset & quot.


Boas leituras

Você pode obter dados de um departamento em um arquivo de forma ou outro formato de dados geográficos. Se você deseja aproveitar o PostGIS, precisará colocar esses dados em nosso banco de dados PostGIS.

Você pode usar o QGIS usando o Database Manager descrito acima. Clicar em “Importar Camada / Arquivo” abrirá uma janela solicitando que você selecione o arquivo e algumas opções relacionadas a ele.

  • Selecione o esquema para o qual você está enviando no menu suspenso (você deve ter permissões de gravação para esse esquema)
  • Insira o que você deseja que o nome da tabela seja
  • Selecione a opção “Coluna de geometria” e defina o nome que deseja. Para nós da City, nomeamos colunas geométricas geom + SRID para o sistema de coordenadas em que os dados estão: geom2249.
  • Certifique-se de que os campos “Source SRID” e “Target SRID” estejam corretos.
  • Selecione “Converter nomes de campo em minúsculas”. Isso é apenas para cumprir nossa política de governança na cidade, que todos os nomes de campo usam letras minúsculas.
  • Selecione “Criar índice espacial”. Consulte a seção Indexação Espacial para obter mais referências.
  • Depois de clicar em “Ok”, pode ser necessário atualizar o esquema para que a nova tabela apareça.

Usando tabelas PostGIS existentes

ArcSDE pode usar com sucesso tabelas contendo colunas de geometria PostGIS criadas externamente por outros aplicativos ou usando SQL (também conhecido como tabelas de terceiros), desde que as tabelas atendam aos seguintes pré-requisitos:

    Cada mesa deve ser propriedade do usuário que está registrando a mesa.

Registro de tabelas de terceiros contendo colunas de geometria PostGIS

O comando de administração ArcSDE sdelayer & # 8211o registra uma tabela como uma classe de recurso. Isso significa que um registro é adicionado à tabela nas tabelas de sistema sde_layers, sde_table_registry e sde_geometry_columns. Os registros também são adicionados à tabela de sistema sde_column_registry para cada coluna da tabela.

A seguir está um exemplo de registro de uma tabela, propriedades, contendo geometrias de ponto (& # 8211e p) em uma coluna espacial, forma. A tabela tem uma coluna inteira, fid, para ser usada como uma coluna de identificador de recurso exclusivo (& # 8211C fid, SDE) mantida pela SDE.

Conforme indicado acima, sdelayer apenas adiciona a tabela às tabelas do sistema ArcSDE. Para poder usar a funcionalidade de geodatabase do ArcGIS Desktop, como topologia, controle de versão e redes, você também deve registrar a tabela com a geodatabase. Consulte Registrando tabelas a serem usadas pelo ArcGIS Desktop para obter mais informações sobre como registrar tabelas.

NOTA: Você não pode renomear tabelas espaciais armazenadas usando o tipo de geometria PostGIS. Isso se deve ao fato de que não há função PostGIS para atualizar o nome da tabela na tabela public.geometry_columns.


Assista o vídeo: PostgreSQL Tutorial: How to import Shapefile into PostGIS EN