Gerenciar dados espaciais com a extensão PostGIS

PostGIS é uma extensão do PostgreSQL para armazenar e gerenciar informações espaciais. Para saber mais sobre a extensão PostGIS, consulte Postgis.net.

Desde a versão 10.5, o PostgreSQL é compatível com a biblioteca libprotobuf 1.3.0 usada pelo PostGIS para trabalhar com dados de blocos vetoriais do Mapbox.

A configuração da extensão PostGIS exige privilégios rds_superuser. Recomendamos criar um usuário (perfil) para gerenciar a extensão PostGIS e os dados espaciais. A extensão PostGIS e seus componentes relacionados adicionam milhares de funções ao PostgreSQL. Considere criar a extensão PostGIS em seu próprio esquema se isso fizer sentido para o seu caso de uso. O exemplo a seguir mostra como instalar a extensão em seu próprio banco de dados, mas isso não é necessário.

Etapa 1: Criar um usuário (função) para gerenciar a extensão PostGIS

Primeiro, conecte-se a uma instância de banco de dados do RDS para PostgreSQL como um usuário que tem privilégios rds_superuser. Se você manteve o nome padrão ao configurar a instância, se conectará como postgres.

psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

Crie um perfil separado (usuário) para administrar a extensão PostGIS.

postgres=>  CREATE ROLE gis_admin LOGIN PASSWORD 'change_me';
CREATE ROLE

Conceda a esse perfil privilégios rds_superuser para permitir que ele instale a extensão.

postgres=> GRANT rds_superuser TO gis_admin;
GRANT

Crie um banco de dados a ser usado para seus artefatos PostGIS. Esta etapa é opcional. Como alternativa, você pode criar um esquema em seu banco de dados de usuário para as extensões PostGIS, mas isso também não é necessário.

postgres=> CREATE DATABASE lab_gis;
CREATE DATABASE

Conceda a gis_admin todos os privilégios no banco de dados lab_gis.

postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin;
GRANT

Saia da sessão e reconecte-se a uma instância de banco de dados do RDS para PostgreSQL como gis_admin.

postgres=> psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=gis_admin --password --dbname=lab_gis
Password for user gis_admin:...
lab_gis=> 

Continue a configurar a extensão conforme detalhado nas próximas etapas.

Etapa 2: Carregar as extensões PostGIS

A extensão PostGIS inclui várias extensões relacionadas que funcionam juntas para fornecer funcionalidade geoespacial. Dependendo do seu caso de uso, talvez você não precise de todas as extensões criadas nesta etapa.

Use instruções CREATE EXTENSION para carregar as extensões PostGIS.

CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION postgis_raster;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION

É possível verificar os resultados executando a consulta SQL mostrada no exemplo a seguir, que lista as extensões e seus proprietários.

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;
List of schemas
     Name     |   Owner
--------------+-----------
 public       | postgres
 tiger        | rdsadmin
 tiger_data   | rdsadmin
 topology     | rdsadmin
(4 rows) 

Etapa 3: Transferir a propriedade dos esquemas de extensão

Use as instruções ALTER SCHEMA para transferir a propriedade dos esquemas à função gis_admin.

ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin; 
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA

Se você quiser confirmar a alteração de propriedade, realize a consulta SQL a seguir. Ou é possível usar o metacomando \dn na linha de comando do psql.

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;

       List of schemas
     Name     |     Owner
--------------+---------------
 public       | postgres
 tiger        | gis_admin
 tiger_data   | gis_admin
 topology     | gis_admin
(4 rows) 

Etapa 4: Transferir a propriedade dos objetos PostGIS

Use a seguinte função para transferir a propriedade das tabelas do PostGIS ao perfil gis_admin. Execute a seguinte instrução no prompt psql para criar a função.

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION  

Depois, execute a consulta a seguir para executar a função exec que, por sua vez, executa as instruções e altera as permissões.

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')
  FROM (
    SELECT nspname, relname
    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
    WHERE nspname in ('tiger','topology') AND
    relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;

Etapa 5: Testar as extensões

Para evitar a necessidade de especificar o nome do esquema, adicione o esquema tiger ao seu caminho de pesquisa usando o seguinte comando.

SET search_path=public,tiger;
SET

Teste o esquema tiger usando a seguinte instrução SELECT.

SELECT address, streetname, streettypeabbrev, zip
 FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev |  zip
---------+------------+------------------+-------
       1 | Devonshire | Pl               | 02109
(1 row) 

Para saber mais sobre essa extensão, consulte Geocodificador Tiger na documentação do PostGIS.

Teste o acesso ao esquema topology usando a seguinte instrução SELECT. Isso chama a função createtopology para registrar um novo objeto de topologia (my_new_topo) com o identificador de referência espacial especificado (26986) e a tolerância padrão (0,5). Para saber mais, consulte CreateTopology na documentação do PostGIS.

SELECT topology.createtopology('my_new_topo',26986,0.5);
 createtopology
----------------
              1
(1 row) 

Etapa 6: Atualize a extensão PostGIS

Cada nova versão do PostgreSQL oferece suporte a uma ou mais versões da extensão PostGIS compatíveis com essa versão. A atualização do mecanismo PostgreSQL para uma nova versão não atualiza automaticamente a extensão PostGIS. Antes de atualizar o mecanismo PostgreSQL, faça upgrade do PostGIS para a versão mais recente disponível para a versão atual do PostgreSQL. Para obter detalhes, consulte Versões de extensão PostGIS.

Após a atualização do mecanismo PostgreSQL, faça upgrade da extensão PostGIS novamente, desta vez para a versão compatível com a versão recém-atualizada do mecanismo PostgreSQL. Para obter mais informações sobre como fazer upgrade do mecanismo PostgreSQL, consulte Como atualizar a versão principal do RDS para PostgreSQL.

Você pode verificar as atualizações de versão da extensão PostGIS disponíveis na sua instância de banco de dados do RDS para PostgreSQL a qualquer momento. Para fazer isso, execute o comando a seguir. Esta função está disponível com PostGIS 2.5.0 e versões posteriores.

SELECT postGIS_extensions_upgrade();

Se a sua aplicação não oferecer suporte à versão mais recente do PostGIS, você poderá instalar uma versão mais antiga do PostGIS que esteja disponível na sua versão principal conforme o exposto a seguir.

CREATE EXTENSION postgis VERSION "2.5.5";

Se quiser fazer upgrade para uma versão específica do PostGIS usando uma versão mais antiga, também poderá usar o comando a seguir.

ALTER EXTENSION postgis UPDATE TO "2.5.5";

Dependendo de sua versão atual antes do upgrade, talvez você precise usar essa função novamente. O resultado da primeira execução da função determina a necessidade de uma função de atualização adicional. Por exemplo, isso acontece em caso de upgrade do PostGIS 2 para o PostGIS 3. Para ter mais informações, consulte Upgrade do PostGIS 2 para o PostGIS 3.

Se você atualizou essa extensão para se preparar para uma atualização da versão principal do mecanismo PostgreSQL, poderá continuar com outras tarefas preliminares. Para obter mais informações, consulte Como atualizar a versão principal do RDS para PostgreSQL.

Versões de extensão PostGIS

Recomendamos que você instale as versões de todas as extensões, como PostGIS, conforme listado em “Extension versions for Amazon RDS para PostgreSQL” (Versões de extensões para o Amazon RDS para PostgreSQL) nas Notas de lançamento do Amazon RDS para PostgreSQL. Você pode conferir quais versões estão disponíveis na sua versão usando o comando a seguir.

SELECT * FROM pg_available_extension_versions WHERE name='postgis';

Informações sobre versões estão disponíveis nas seções a seguir das Notas de lançamento do Amazon RDS para PostgreSQL:

Upgrade do PostGIS 2 para o PostGIS 3

A partir da versão 3.0, a funcionalidade de rasterização do PostGIS é uma extensão separada, postgis_raster. Essa extensão tem seu próprio caminho de instalação e upgrade. Isso remove dezenas de funções, tipos de dados e outros artefatos necessários para o processamento de imagens rasterizadas da extensão postgis principal. Isso significa que, se o seu caso de uso não exigir processamento de rasterização, você não precisará instalar a extensão postgis_raster.

No exemplo de upgrade a seguir, o primeiro comando de upgrade extrai a funcionalidade de rasterização na extensão postgis_raster. Um segundo comando de upgrade é necessário para atualizar postgis_raster para a nova versão.

Depois que essa atualização for concluída, se você não planejar usar a funcionalidade de rasterização, poderá descartar a extensão da seguinte forma.

DROP EXTENSION postgis_raster;