Administración de datos espaciales con la extensión PostGIS - Amazon Relational Database Service

Administración de datos espaciales con la extensión PostGIS

PostGIS es una extensión de PostgreSQL para almacenar y administrar información espacial. Para obtener más información sobre PostGIS, consulte PostGIS.net.

A partir de la versión 10.5, PostgreSQL admite la biblioteca libprotobuf 1.3.0 utilizada por PostGIS para trabajar con datos de teselas vectoriales de Mapbox.

La configuración de la extensión PostGIS requiere privilegios de rds_superuser. Le recomendamos que cree un usuario (rol) para administrar instalar la extensión PostGIS y los datos espaciales. La extensión PostGIS y sus componentes relacionados añaden miles de funciones a PostgreSQL. Considere la posibilidad de crear la extensión PostGIS en su propio esquema si eso tiene sentido para su caso de uso. En el ejemplo siguiente se muestra cómo instalar la extensión en su propia base de datos independiente, pero esto no es obligatorio.

Paso 1: cree un usuario (rol) para administrar la extensión PostGIS

En primer lugar, conéctese a una instancia de base de datos de RDS for PostgreSQL como usuario con privilegios rds_superuser. Si mantuvo el nombre predeterminado al configurar la instancia, conéctese como postgres:

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

Cree un rol independiente (usuario) para administrar la extensión PostGIS:

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

Conceda los privilegios rds_superuser de este rol para permitir que el rol instale la extensión:

postgres=> GRANT rds_superuser TO gis_admin; GRANT

Cree una base de datos para utilizarla para sus artefactos de PostGIS:

postgres=> CREATE DATABASE lab_gis; CREATE DATABASE

Conceda todos los privilegios gis_admin en la base de datos lab_gis.

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

Salga de la sesión y vuelva a conectarse a una instancia de base de datos de RDS for PostgreSQL como gis_admin:

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

Continúe configurando la extensión tal y como se detalla en los pasos siguientes.

Paso 2: cargue las extensiones PostGIS

Utilice instrucciones CREATE EXTENSION para cargar las extensiones de PostGIS.

CREATE EXTENSION postgis; CREATE EXTENSION CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION CREATE EXTENSION postgis_topology; CREATE EXTENSION

Para verificar los resultados, puede ejecutar la consulta SQL que se muestra en el siguiente ejemplo, que enumera las extensiones y sus propietarios.

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

Algunos casos de uso no requieren todas las extensiones creadas en este paso.

Paso 3: transfiera la propiedad de las extensiones.

Use las declaraciones de ALTER SCHEMA para transferir la propiedad de los esquemas al rol 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

Si desea confirmar el cambio de propiedad, ejecute la siguiente consulta SQL. O bien, puede utilizar el metacomando \dn de la línea de comandos 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)

Paso 4: transfiera la propiedad de los objetos de PostGIS

Use la siguiente función para transferir la propiedad de los objetos de PostGIS al rol gis_admin. Ejecute la siguiente declaración desde el símbolo del sistema psql para crear la función.

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

A continuación, ejecute la siguiente consulta para ejecutar la función exec que, a su vez, ejecuta las instrucciones y altera los permisos.

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;

Paso 5: pruebe las extensiones

Añada tiger a la ruta de búsqueda usando el siguiente comando.

SET search_path=public,tiger; SET

Pruebe tiger usando la siguiente declaración SELECT.

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

Pruebe topology usando la siguiente declaración SELECT.

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

Paso 6: actualice la extensión de PostGIS

Las distintas versiones de PostgreSQL admiten distintas versiones de la extensión PostGIS. Puede comprobar si hay disponible una actualización ejecutando el siguiente comando. Esta función está disponible con PostGIS 2.5.0 y versiones posteriores.

SELECT PostGIS_Extensions_Upgrade();

Dependiendo de la versión desde la que vaya a actualizar, es posible que esta función deba ejecutarse una segunda vez. El resultado de la primera ejecución de la función determina si se necesita una función de actualización adicional.

Si su aplicación no es compatible con la última versión de PostGIS, puede crear una versión anterior de PostGIS que esté disponible en su versión principal de la siguiente manera.

CREATE EXTENSION postgis VERSION "2.5.5"

Si desea actualizar a una VERSIÓN específica de PostGIS desde una versión anterior, también puede utilizar el siguiente comando.

ALTER EXTENSION postgis UPDATE TO "2.5.5"

Versiones de extensión PostGIS

Puede listar las versiones que están disponibles en su versión con el siguiente comando.

SELECT * FROM pg_available_extension_versions WHERE name='postgis';

También puede encontrar información sobre la versión en las siguientes secciones de las notas de la versión de Amazon RDS para PostgreSQL: