Postgis

De WikiSalud
Saltar a: navegación, buscar

Contenido

Introducción

Postgis es la extensión espacial o geográfica de Postgres y añade la capacidad de ejecutar consultas SQL a objetos geográficos. El sitio oficial es Postgis.net
Una base de datos común tiene números, texto y fechas, la base de datos espacial contiene este tipo geográfico, en el caso de Postgis, el tipo se llama geometry. El tipo geometry almacena las coordenadas del objeto espacial ya sea que sea un punto , línea o polígono.
A continuación se muestra el esquema de una tabla de establecimiento, el cual incluye el tipo "geometry".

CREATE TABLE establecimiento
(
  gid INTEGER NOT NULL DEFAULT NEXTVAL('establecimiento_gid_seq'::regclass),
  idmaestros INTEGER,
  nombre CHARACTER VARYING(254),
  codmuni INTEGER,
  tipo INTEGER,
  activo BOOLEAN,
  interv INTEGER,
  fecha_georef DATE,
  cabezared BOOLEAN,
  notas CHARACTER VARYING(254),
  inaug CHARACTER VARYING(10),
  cambio INTEGER,
  anio2009 CHARACTER VARYING(1),
  actual CHARACTER VARYING(1),
  direccion CHARACTER VARYING(254),
  the_geom geometry,
  id INTEGER,
  nombreantiguo CHARACTER VARYING(254),
  tipoanterior INTEGER,
  CONSTRAINT estab_pk PRIMARY KEY (gid),
  CONSTRAINT codmun_fk FOREIGN KEY (codmuni)
      REFERENCES public.municipios_wgs84 (mign_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
)
WITH (
  OIDS=FALSE
);

Cuando se consultan los datos con algún administrador de bases de datos se observa que el campo geográfico está codificado así:

Postgis1.jpeg

Además de almacenar el tipo de datos geográfico, Postgis permite crear índices espaciales y realizar funciones espaciales. Al momento de crear una base de datos geográfica, se crean automáticamente 2 tablas de metadatos, las cuales le dan seguimiento a los datos geográficos:

  • geometry_columns
  • spatial_ref_sys

Tabla geometry_columns (versiones anteriores a la 2.0)

La tabla geometry_columns guarda un registro por cada tabla que contiene la capacidad geográfica e indica para cada tabla principalmente:

  • nombre de la tabla
  • esquema al que pertenece
  • nombre de la columna que contiene el tipo geográfico
  • Sistema de referencia espacial (SRID, por sus siglas en inglés)
  • tipo de geometría (punto, línea, polígono, etc.)

A continuación se indica el esquema de la tabla geometry_columns:

CREATE TABLE geometry_columns
(
  f_table_catalog CHARACTER VARYING(256) NOT NULL,
  f_table_schema CHARACTER VARYING(256) NOT NULL,
  f_table_name CHARACTER VARYING(256) NOT NULL,
  f_geometry_column CHARACTER VARYING(256) NOT NULL,
  coord_dimension INTEGER NOT NULL,
  srid INTEGER NOT NULL,
  TYPE CHARACTER VARYING(30) NOT NULL,
  CONSTRAINT geometry_columns_pk PRIMARY KEY (f_table_catalog , f_table_schema , f_table_name , f_geometry_column )
)

Un ejemplo de la tabla geometry_columns con datos es como el que se indica en la figura: Postgis6.jpeg

En este caso, se tiene 4 tablas con capacidad geográfica: agisdependencias, albergues, casa_salud_final y centro_nutricion_final. El nombre de la columna que contiene las coordenadas se llama the_geom para todas las tablas, todas están en el sistema de coordenadas WGS84 y pertenecen al esquema 'public'. Sin embargo, no todas son del mismo tipo geográfico: 3 tablas son de tipo punto y 1 es de tipo multipolígono. Para los fines que se está usando esta base de datos es suficiente modelar a los albergues, las casas de salud y los centros de nutrición como un punto. En el caso de las áreas geográficas de influencia de un UCSF, se refiere a un área cerrada por lo que se representa como un polígono.

 A partir de la versión 2.0 ya no se usa la tabla geometry_columns sino que hay una vista geometry_columns que obtiene datos directamente de las tablas.

Tabla spatial_ref_sys

Esta tabla almacena un listado de códigos de los sistemas de coordenadas más comúnmente utilizados a nivel mundial. Estos códigos son los que se especifican en la columna srid de la tabla geometry_columns.

A continuación se muestra el esquema de la tabla spatial_ref_sys:

CREATE TABLE spatial_ref_sys
(
  srid INTEGER NOT NULL,
  auth_name CHARACTER VARYING(256),
  auth_srid INTEGER,
  srtext CHARACTER VARYING(2048),
  proj4text CHARACTER VARYING(2048),
  CONSTRAINT spatial_ref_sys_pkey PRIMARY KEY (srid )

Como se indica en el esquema, la tabla almacena el código del sistema de coordenadas, las siglas del sistema de codificación, los parámetros de cada sistema de coordenadas (proyección, elipsoide, datum y otros parámetros necesarios) en dos tipo de formato.

La figura a continuación muestra parte del contenido de la tabla spatial_ref_sys, en total son aproximadamente 3749 registros.

Postgis7.jpeg

Funciones espaciales

Postgis ofrece una serie de funciones para manipular los datos geográficos, en el siguiente vínculo http://postgis.net/docs/manual-1.5/reference.html está el listado de la versión 1.5 de Postgis.

Algunos ejemplos de funciones:

  • El siguiente ejemplo utiliza las funciones ST_X y ST_Y para obtener las coordenadas de latitud y longitud del hospital nacional Rosales:
SELECT ST_X(the_geom) AS lon, ST_Y(the_geom) AS lat
  FROM hospitales_final
WHERE name = 'HOSPITAL NACIONAL ROSALES';

La salida del SQL anterior es:

Postgis8.jpeg

  • En este ejemplo se utiliza la función ST_AsText para obtener las coordenadas de los puntos que forman la línea que modela la quebrada Hierba buena.
SELECT 
    ST_AsText(the_geom)
FROM 
  rios_gm
WHERE 
  "NOMBRE" = 'Qda. de La Hierba Buena';

La salida del query anterior es: Postgis9.jpeg

  • La función NPoints devuelve la cantidad de puntos que tiene la línea que modela a la quebrada Hierba buena:
SELECT 
    ST_NumPoints(the_geom)
FROM 
  rios_gm
WHERE 
  "NOMBRE" = 'Qda. de La Hierba Buena';

La salida del query anterior es:
Postgis10.jpeg

  • La función ST_AsKML devuelve las coordenadas en formato KML.

El ejemplo a continuación devuelve las coordenadas del hospital nacional rosales en formato KML.

SELECT 
    ST_AsKML(the_geom)
FROM 
  hospitales_final
WHERE 
  "name" = 'HOSPITAL NACIONAL ROSALES';

La salida del query anterior es:
Postgis11.jpeg

  • La función ST_AsGeoJSON devuelve las coordenadas en formato JSON.

El ejemplo a continuación devuelve las coordenadas del hospital nacional rosales en formato JSON.

SELECT 
    ST_AsGeoJSON(the_geom)
FROM 
  hospitales_final
WHERE 
  "name" = 'HOSPITAL NACIONAL ROSALES';

La salida del query anterior es:
Postgis13.jpeg

  • La función ST_NRings devuelve la cantidad e anillos o huecos que tiene el polígono.

En el siguiente ejemplo se obtiene la cantidad de áreas geográficas de influencia (AGI) que tienen más de un hueco en su área.

SELECT 
    COUNT(*)
FROM 
  agisdependencias
WHERE 
  ST_NRings(the_geom) > 1;

La salida del query anterior es:
Postgis12.jpeg

Además, Postgis permite:

  • relacionar más de un objeto espacial y efectuar operaciones de intersección, unión, diferencia, análisis de proximidad, etc.

Por ejemplo, si se quiere saber qué hospitales están en el municipio de Nueva San Salvador se puede usar la función de intersección así:

En primer lugar se obtiene la geometría del municipio Nueva San Salvador así:

SELECT 
    the_geom
FROM 
  municipios_wgs84
WHERE 
  "nombre" = 'NUEVA SAN SALVADOR';

El resultado es:

"0106000020E610000001000000010300000001000000AE000000AB1F29C8125256C0661797DB577A2B40FCE2C54BF05156C0EA43117DB0792B40CB529CE5E25156C05233CB5389792B40CD4C18C6D95156C0F00F421D88792B4048BC7825C75156C0E7143B02B5792B409337EF21B45156C0254A12E40E7A2B404D5F31808D5156C08067E8E6F17A2B4071BCEA8D705156C096C872606F7B2B4089E0E41B575156C08FFB97D89B7B2B40F6748CEF345156C036FCAE16D97B2B40E70BF956265156C0183DDFAC1B7C2B402A19EE89FD5056C0081E47AE267D2B40B0BF772AF15056C003BE24240C7C2B400BDAE680F95056C0FCCFF1B0457B2B4063EAF1F9085156C0848967F9DC792B40DF3D72E00B5156C0DEBDA1D9FC772B40206D5516215156C041C6F811B9762B40E7F533173C5156C02F9C77E86C752B40D71433765A5156C01923F1C272742B4094CF0928605156C0D3E775D4EC732B40D4F7540D635156C025A3B4DBDB722B403665B8C2615156C07268A02B5F722B40C508E1AA605156C0F7E74A7F20722B403D606AE75D5156C017684A5363712B40576E5D4D5E5156C0496169C925712B408DA5D81C5F5156C0E6A4714DA9702B405F17F8277A5156C0E98C1FED386F2B40BB807BEA9A5156C0FCE61597FF6D2B40ADCCF22F9A5156C0F162BBEF9D6C2B407E3D51A4AB5156C0A97EE766456C2B40D43034ADB95156C0AD079722BF6B2B4060E912C3BB5156C0DAE2E253706B2B409369F657B55156C030D9D9E1D26A2B40800465C89C5156C0C00E3BAADF692B408011EE0DE15156C0B6FD32A1D5672B402699D01DF65156C037D27DA707672B4014DCE404F75156C01BCCEA993F662B4075A30E6FDD5156C09F0E863B9C642B40A20157BDB65156C0C8C932EFA4622B40F16A6954875156C095C0516A1B612B40F3E100E46D5156C029DA49EC67602B40161995DB205156C0FF4BCDA23A5E2B40B09A0535085156C05C67CFB1705D2B40E6611F76F15056C0D3392185925C2B40CF4622CDD95056C015576D64AB5B2B401426C211D25056C0ADD61A55885A2B409EE7276EE65056C0C0DDA49B46582B4014ABCE76E65056C0D39F2977C4572B403BF19B0EE65056C0E9DA741581572B40FA7BB014E65056C0E8AB565FB7562B403132DD9AE85056C0B634FE8D7E562B408B27B212EE5056C06B7BF35C03562B40C3D06CCD055156C0E06D9C0E75552B400B84ADBF3A5156C0A3B8B95ACF542B402EF9BF2AAA5156C0F27E5C2760532B4006E9BD41C85156C01FA9DF8BF8522B409415CDEACB5156C020C03AD6A5522B406238FDD1B05156C0C68930C694502B40AC83FD9DB25156C09A7EBD30874E2B406A09F0D8B85156C0B768A441144D2B403B3AC416D85156C018C423F644492B408F3BDEB2E15156C0CCB366EF1A482B40304881B3E85156C030C150C772472B408607429BEC5156C0F9AC70111B472B406D60C21BF85156C04FC6F17E0E462B40CC6533D8FA5156C07FD23334B5452B40CC2F9513005256C014B5C4520A452B403BE240FEFC5156C0EE90F3CC1A442B400AAC4992F55156C0B5CEA729B0432B403FECD92FD25156C0426F1A3C41422B40CBFFBC1BCE5156C070D3628784402B40203EA56ACD5156C067761F751E402B40B0A0EC0FCE5156C03051596EEC3F2B40C52C2B1DD05156C09A56FB3DBF3F2B40851466E1E65156C0AD518F83E63E2B4068DC03E8365256C0CB7F1285043E2B40A38621356D5256C05D3D2B28143E2B407504E9E8AD5256C0F5E7BA05253E2B40C083F8C5C75256C0BE5C4FE6FE3D2B4029CC298DEE5256C075E2C3D6EF3C2B402A92C337085356C011E30CFF323B2B40E3F99638285356C0A8E7D4F94B372B40494DEBE7315356C008F4B484E2352B40E1758561055456C09325C7041C232B40DEB3F180175456C08E606D6B88202B404859DD420A5456C06B143018821E2B403DA1A53ACA5356C0FD21E95E511C2B40D7F98797B85356C00EAFA4B1AF192B40A8D875B7B95356C0BA1F2A184E182B404F4F069BC85356C06CD1111C55162B40E4F20BCDE45356C030CE25B1D2142B401AA427BC0E5456C02CDC3F9EB5132B4014EA1C19A75456C006B6AAA12B142B40277BEF5FB95456C049BDC3E224142B408048678ACB5456C088099BCF12142B4048481B91DD5456C093C79B42F2132B40BF09ADC6145556C0DD9A2EE392132B40D6B76910275556C0F6082DCF7C132B404FA17E36395556C00EAC16CD75132B40EF936A614B5556C029894DC16E132B407D12DC75BC5556C04BA47408E9132B400390A988B35556C032F32116AB162B40C6459F30C35556C0C2C8A51A60182B40E21000ABE25556C032542F2E421B2B406550DCAFBD5556C0D76736E6191F2B40A646F463A25556C03A02490A76212B40C961B45D985556C0F9CF6AA30A242B4061E2FDB1A45556C080740D6649252B40534993B2BA5556C04464A3C6F1282B4064C8977FA95556C0ADE7B4F14C2C2B407A9DE791985556C032D42284C52E2B40C34295E2835556C09C33476B32322B40FD157FBD785556C0BBDA8B98A2342B4092D583645B5556C06EA1EBEF24362B40639B0D710A5556C045A7ACA91B3A2B4052AB6045035556C07230B3068C3B2B40B78728F23B5556C02716AAAF853E2B40F864B0206A5556C0E1C4CC77AF3E2B406B22A22C835556C03FB397C1EF3F2B40C09024778F5556C0EAA6B1C352412B40C888FE8C895556C02E9635F491452B4032112BD8DC5556C00647B873F04C2B403CE1B70A025656C0B6C407A012502B405DA7531FF75556C06A90477EC4512B4061083C2BF45556C0250E0C0DE4532B40913CDF39E15556C0C1D3758B5E552B40E59B6B3AB05556C0483E7F7BD5562B40FFC6D4F3755556C03EE18D7866582B4023BDF725D15556C0FDA88B0FD5582B406D0790291B5656C041681B9DB9582B40562EDF15205656C00FEE7658BC572B40828F4783375656C0288BC53DB8562B40DF877609515656C0A46B92AE69562B40B891246FBE5656C0FBFEA929CF572B400C8825C5E75656C02F2DF5C9BF582B4033E29605F95656C0361D44570A592B404D337EAD0D5756C0B63D23B98B592B40CBFF7E49055756C09C4A8A8D885A2B403D3C4B85015756C0202432F5855B2B40EA532483115756C0B23427032B5C2B40C41DF1402D5756C072E391502E5C2B4074D90C53525756C0B0588528EA5B2B4069B695EA795756C0CD822FEF9E5B2B40606F992E8C5756C0694AFCA1BA5B2B408F17425C595956C0C3BB217ACD632B405534767A965856C003C46CD603662B40BF64E780385856C0234BC6DF1A672B40631D89C4065856C023F5432E30672B4051F00C13DB5756C00537569863662B4063863D22B65756C0AB01F7E831662B40613B47D0865756C0DA289CD5EC652B40BF7F00F04A5756C0BDE8AD511E652B40067331FA1E5756C09933D94F34652B40F11B136DEF5656C0068863ADB6652B40097E185FC95656C036BA348E60652B400F5474DDB45656C0C515EC4860642B40D4242A7CA95656C0376F80D6C4632B400C859CB57D5656C07AA8A6B740632B40883DEE5C735656C0EB95B42512632B40D98B29E33D5656C06A34628D36602B40488401E7155656C0A7F9FBE9A25E2B400D6FA0EBD65556C0787F4BD2A85C2B40205F9877BD5556C05C7DDD231A5D2B40F491ADF29B5556C08E6FA59B905E2B40FB4B13018B5556C0ACDEDD8D355F2B4044D83724225556C008134018E3612B4025BC6870FF5456C0B4C06417F1612B403AF6B6DCE35456C085C82CCD65612B40F13886ADCC5456C05ECF846699612B40CC94F260AD5456C0CDB26E30DE612B40DB8831178D5456C095EB17E29A612B4031DD38196E5456C0E9021EDCE1602B403C8A92B8505256C0D99C3AF148782B40AB1F29C8125256C0661797DB577A2B40"

En segundo lugar, se consulta "¿qué hospitales intersectan (o están dentro de) con el municipio de Nueva San Salvador?

Para responder la pregunta se usa la siguiente consulta:

SELECT 
    name
FROM 
  hospitales_final
WHERE 
  ST_Intersects(
  the_geom,
  '0106000020E610000001000000010300000001000000AE000000AB1F29C8125256C0661797DB577A2B40FCE2C54BF05156C0EA43117DB0792B40CB529CE5E25156C05233CB5389792B40CD4C18C6D95156C0F00F421D88792B4048BC7825C75156C0E7143B02B5792B409337EF21B45156C0254A12E40E7A2B404D5F31808D5156C08067E8E6F17A2B4071BCEA8D705156C096C872606F7B2B4089E0E41B575156C08FFB97D89B7B2B40F6748CEF345156C036FCAE16D97B2B40E70BF956265156C0183DDFAC1B7C2B402A19EE89FD5056C0081E47AE267D2B40B0BF772AF15056C003BE24240C7C2B400BDAE680F95056C0FCCFF1B0457B2B4063EAF1F9085156C0848967F9DC792B40DF3D72E00B5156C0DEBDA1D9FC772B40206D5516215156C041C6F811B9762B40E7F533173C5156C02F9C77E86C752B40D71433765A5156C01923F1C272742B4094CF0928605156C0D3E775D4EC732B40D4F7540D635156C025A3B4DBDB722B403665B8C2615156C07268A02B5F722B40C508E1AA605156C0F7E74A7F20722B403D606AE75D5156C017684A5363712B40576E5D4D5E5156C0496169C925712B408DA5D81C5F5156C0E6A4714DA9702B405F17F8277A5156C0E98C1FED386F2B40BB807BEA9A5156C0FCE61597FF6D2B40ADCCF22F9A5156C0F162BBEF9D6C2B407E3D51A4AB5156C0A97EE766456C2B40D43034ADB95156C0AD079722BF6B2B4060E912C3BB5156C0DAE2E253706B2B409369F657B55156C030D9D9E1D26A2B40800465C89C5156C0C00E3BAADF692B408011EE0DE15156C0B6FD32A1D5672B402699D01DF65156C037D27DA707672B4014DCE404F75156C01BCCEA993F662B4075A30E6FDD5156C09F0E863B9C642B40A20157BDB65156C0C8C932EFA4622B40F16A6954875156C095C0516A1B612B40F3E100E46D5156C029DA49EC67602B40161995DB205156C0FF4BCDA23A5E2B40B09A0535085156C05C67CFB1705D2B40E6611F76F15056C0D3392185925C2B40CF4622CDD95056C015576D64AB5B2B401426C211D25056C0ADD61A55885A2B409EE7276EE65056C0C0DDA49B46582B4014ABCE76E65056C0D39F2977C4572B403BF19B0EE65056C0E9DA741581572B40FA7BB014E65056C0E8AB565FB7562B403132DD9AE85056C0B634FE8D7E562B408B27B212EE5056C06B7BF35C03562B40C3D06CCD055156C0E06D9C0E75552B400B84ADBF3A5156C0A3B8B95ACF542B402EF9BF2AAA5156C0F27E5C2760532B4006E9BD41C85156C01FA9DF8BF8522B409415CDEACB5156C020C03AD6A5522B406238FDD1B05156C0C68930C694502B40AC83FD9DB25156C09A7EBD30874E2B406A09F0D8B85156C0B768A441144D2B403B3AC416D85156C018C423F644492B408F3BDEB2E15156C0CCB366EF1A482B40304881B3E85156C030C150C772472B408607429BEC5156C0F9AC70111B472B406D60C21BF85156C04FC6F17E0E462B40CC6533D8FA5156C07FD23334B5452B40CC2F9513005256C014B5C4520A452B403BE240FEFC5156C0EE90F3CC1A442B400AAC4992F55156C0B5CEA729B0432B403FECD92FD25156C0426F1A3C41422B40CBFFBC1BCE5156C070D3628784402B40203EA56ACD5156C067761F751E402B40B0A0EC0FCE5156C03051596EEC3F2B40C52C2B1DD05156C09A56FB3DBF3F2B40851466E1E65156C0AD518F83E63E2B4068DC03E8365256C0CB7F1285043E2B40A38621356D5256C05D3D2B28143E2B407504E9E8AD5256C0F5E7BA05253E2B40C083F8C5C75256C0BE5C4FE6FE3D2B4029CC298DEE5256C075E2C3D6EF3C2B402A92C337085356C011E30CFF323B2B40E3F99638285356C0A8E7D4F94B372B40494DEBE7315356C008F4B484E2352B40E1758561055456C09325C7041C232B40DEB3F180175456C08E606D6B88202B404859DD420A5456C06B143018821E2B403DA1A53ACA5356C0FD21E95E511C2B40D7F98797B85356C00EAFA4B1AF192B40A8D875B7B95356C0BA1F2A184E182B404F4F069BC85356C06CD1111C55162B40E4F20BCDE45356C030CE25B1D2142B401AA427BC0E5456C02CDC3F9EB5132B4014EA1C19A75456C006B6AAA12B142B40277BEF5FB95456C049BDC3E224142B408048678ACB5456C088099BCF12142B4048481B91DD5456C093C79B42F2132B40BF09ADC6145556C0DD9A2EE392132B40D6B76910275556C0F6082DCF7C132B404FA17E36395556C00EAC16CD75132B40EF936A614B5556C029894DC16E132B407D12DC75BC5556C04BA47408E9132B400390A988B35556C032F32116AB162B40C6459F30C35556C0C2C8A51A60182B40E21000ABE25556C032542F2E421B2B406550DCAFBD5556C0D76736E6191F2B40A646F463A25556C03A02490A76212B40C961B45D985556C0F9CF6AA30A242B4061E2FDB1A45556C080740D6649252B40534993B2BA5556C04464A3C6F1282B4064C8977FA95556C0ADE7B4F14C2C2B407A9DE791985556C032D42284C52E2B40C34295E2835556C09C33476B32322B40FD157FBD785556C0BBDA8B98A2342B4092D583645B5556C06EA1EBEF24362B40639B0D710A5556C045A7ACA91B3A2B4052AB6045035556C07230B3068C3B2B40B78728F23B5556C02716AAAF853E2B40F864B0206A5556C0E1C4CC77AF3E2B406B22A22C835556C03FB397C1EF3F2B40C09024778F5556C0EAA6B1C352412B40C888FE8C895556C02E9635F491452B4032112BD8DC5556C00647B873F04C2B403CE1B70A025656C0B6C407A012502B405DA7531FF75556C06A90477EC4512B4061083C2BF45556C0250E0C0DE4532B40913CDF39E15556C0C1D3758B5E552B40E59B6B3AB05556C0483E7F7BD5562B40FFC6D4F3755556C03EE18D7866582B4023BDF725D15556C0FDA88B0FD5582B406D0790291B5656C041681B9DB9582B40562EDF15205656C00FEE7658BC572B40828F4783375656C0288BC53DB8562B40DF877609515656C0A46B92AE69562B40B891246FBE5656C0FBFEA929CF572B400C8825C5E75656C02F2DF5C9BF582B4033E29605F95656C0361D44570A592B404D337EAD0D5756C0B63D23B98B592B40CBFF7E49055756C09C4A8A8D885A2B403D3C4B85015756C0202432F5855B2B40EA532483115756C0B23427032B5C2B40C41DF1402D5756C072E391502E5C2B4074D90C53525756C0B0588528EA5B2B4069B695EA795756C0CD822FEF9E5B2B40606F992E8C5756C0694AFCA1BA5B2B408F17425C595956C0C3BB217ACD632B405534767A965856C003C46CD603662B40BF64E780385856C0234BC6DF1A672B40631D89C4065856C023F5432E30672B4051F00C13DB5756C00537569863662B4063863D22B65756C0AB01F7E831662B40613B47D0865756C0DA289CD5EC652B40BF7F00F04A5756C0BDE8AD511E652B40067331FA1E5756C09933D94F34652B40F11B136DEF5656C0068863ADB6652B40097E185FC95656C036BA348E60652B400F5474DDB45656C0C515EC4860642B40D4242A7CA95656C0376F80D6C4632B400C859CB57D5656C07AA8A6B740632B40883DEE5C735656C0EB95B42512632B40D98B29E33D5656C06A34628D36602B40488401E7155656C0A7F9FBE9A25E2B400D6FA0EBD65556C0787F4BD2A85C2B40205F9877BD5556C05C7DDD231A5D2B40F491ADF29B5556C08E6FA59B905E2B40FB4B13018B5556C0ACDEDD8D355F2B4044D83724225556C008134018E3612B4025BC6870FF5456C0B4C06417F1612B403AF6B6DCE35456C085C82CCD65612B40F13886ADCC5456C05ECF846699612B40CC94F260AD5456C0CDB26E30DE612B40DB8831178D5456C095EB17E29A612B4031DD38196E5456C0E9021EDCE1602B403C8A92B8505256C0D99C3AF148782B40AB1F29C8125256C0661797DB577A2B40'
);

y se obtiene el siguiente resultado:
Postgis14.jpeg

Otro ejemplo de intersección sería si se quisiera conocer ¿qué establecimientos de salud están activos y se encuentran ubicados geográficamente en el municipio de Ahuachapán y a qué distancia quedan del Hospital de Ahuachapán (ordenados del más próximo al más lejano)?

Para esto se escribe el siguiente SQL:

SELECT
  ST_Intersection(r.the_geom, m.the_geom) AS intersection_geom,
  ST_Distance(r.the_geom, (SELECT the_geom FROM establecimiento WHERE nombre LIKE 'Hospital Nacional Gral Dr. Francisco Menéndez Ahuachapán' ))*110.54 AS rd_orig_length,
  r.*
FROM
  establecimiento AS r,
  municipios_wgs84 AS m
WHERE  m.nombre = 'AHUACHAPAN' AND ST_Intersects(r.the_geom, m.the_geom) AND r.activo=TRUE
ORDER BY rd_orig_length;

El resultado de este query es:
Postgis intersect.png
En la imagen anterior, la columna intersection_geom son las geometrías de los establecimientos activos que están geográficamente dentro del municipio de Ahuachapán. La columna rd_orig_lenght contiene la distancia del establecimiento al Hospital Nacional de Ahuachapán en kilómetros. El valor resultante de la función ST_Distance se ha multiplicado por 110.54 ya que se ha considerado que 1 grado decimal equivale a ese valor.

Un ejemplo de actualización de coordenadas de un lugar en base a coordenadas geográficas (latitud y longitud):

UPDATE ctl_establecimiento
SET the_geom = ST_SetSRID(ST_MakePoint(-89.2146194444,13.8893416667),4326)
WHERE idmaestros=622;
  • crear índices espaciales
  • crear join espaciales
  • proyectar datos

Requerimientos

Instalar postgresql y configurar el archivo pg_hba.conf como lo indica la guía PostgreSQL

Instalación

Debian Squeeze:

Con la aplicación Aptitude instalar posgresql-8.4-postgis o en consola escribir como usuario root:

aptitude install postgresql-8.4-postgis

Debian Wheezy:

aptitude install postgresql-9.1-postgis

Debian Jessie:

aptitude install postgresql-9.4-postgis-2.1

Crear nueva base de datos geográfica

1- Entrar como root en la máquina local

2- Cambiar a usuario postgres y ubicarse en el home de postgres (/var/lib/postgresql/):

su postgres

3-crear el usuario nombreusuario de la base de datos geográfica como superusuario

createuser -P nombreusuario

donde:

P: Si se especifica, se solicitará una contraseña para el nuevo usuario

4 – Crear la nueva base de datos geográfica vacía con su respectivo usuario:

Se crea una base de datos nueva 'nuevabd' y a continuación se le extienden las capacidades geográficas de la siguiente manera:

createdb nuevabd -O nombreusuario
createlang plpgsql nuevabd 
psql -d nuevabd -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
psql -d nuevabd -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql

El comando createlang, permite a la nueva base de datos comprender el lenguaje PL/pgSQL.

El archivo postgis.sql permite cargar el objeto PostGIS y las definiciones de función en la nueva base de datos.

El archivo spatial_ref_sys.sql permite poblar la tabla spatial_ref_sys con los identificadores de los distintos sistemas de coordenadas.

La ruta de los script sql podría variar según la versión de postgresql, es necesario verificarla con la función locate.

Nota: Es recomendable utilizar una plantilla para crear bases de datos geográficas postgis. Para esto, creamos una base de datos nueva como en el paso 6 y sustituimos el nombre
'nuevabd' por template_postgis. Esto permite simplificar la tarea de crear bases de datos geográficas ya que únicamente le asignamos la plantilla cada vez.

Para crear una base de datos geográfica con plantilla de postgis, escribimos la siguiente línea:

createdb -T  template_postgis nuevabd -O nombreusuario

Copia de la base de datos geográfica

1- Conectarse al servidor remoto donde está la base de datos geográfica con el comando ssh

2- Cambiar a usuario root

3- Cambiar a usuario postgres y ubicarse en el home de postgres (/var/lib/):

su postgres

4- Si la base de datos tiene un tamaño grande, crear la copia de la base de datos en un archivo comprimido, donde nombrebd es el nombre de la base de datos geográfica

pg_dump nombrebd | gzip -c > nombrebd.dump.out.gz

sino, hacer la copia así:

pg_dump -U usuario nombrebd -f nombrebd.sql

Recuperación de la base de datos geográfica

1- Entrar como root en la máquina local

2- Cambiar a usuario postgres y ubicarse en el home de postgres (/var/lib/postgresql):

su postgres

3-Copiar desde el servidor la copia de la base de datos geográfica al home de postgres de la máquina local:

scp administrador@servidor:/var/lib/postgresql/nombrebd.dump.out.gz .

4-crear el usuario nombreusuario de la base de datos geográfica como superusuario

createuser -P nombreusuario

donde:

P: Si se especifica, se solicitará una contraseña para el nuevo usuario

5-Si la base de datos está comprimida, descomprimir la copia de la base de datos geográfica así:

gunzip nombrebd.dump.out.gz

Con esto se obtiene la copia de la base de datos geográfica: nombrebd.dump.out

6 – Crear la nueva base de datos geográfica vacía con su respectivo usuario:

Se crea una base de datos nueva 'nuevabd' con su respectivo usuario:

createdb nuevabd -O nombreusuario

7- Restaurar la base de datos geográfica en la máquina local:

psql -U nombreusuario -d nuevabd
\i nombrebd.dump.out

Conversión de formatos geográficos

  • Si se requiere exportar un archivo tipo shapefile a la base de datos:
shp2pgsql -s 4326 shapefilename.shp tablename | psql -d dbname


donde:
4326: un ejemplo del código del sistema de coordenadas
shapefilename.shp: archivo shape que se va a exportar a la base de datos postgis
tablename: nombre de la nueva tabla
dbname: nombre de base de datos postgis

  • Para exportar una tabla de la base de datos postgis a un archivo de tipo shapefile
pgsql2shp  -g the_geom -h localhost -P mypassword  -p 5432 -u myusername mydbname mytablename


donde:
the_geom: es la columna geográfica.
localhost: ejemplo de host
mypassword: contraseña
myusername: usuario
mydbname: nombre de base de datos postgis mytablename: tabla geográfica que se va a convertir a shape file

  • Para exportar un query de la base de datos a un archivo de tipo shapefile
pgsql2shp -f filename.shp -g the_geom -h localhost -P mypassword  -p 5432 -u myusername mydbname "select * from departamentos where  dpto='SAN SALVADOR'"

donde:
filename: es el archivo shape file de salida
the_geom: es la columna geográfica
localhost: ejemplo de host
mypassword: contraseña
myusername: usuario
mydbname: nombre de base de datos postgis

Migración de postgresql-8.4-postgis a postgresql-9.1-postgis

Para migrar una base de datos postgresql-8.4-postgis a postgresql-9.1-postgis es necesario seguir los siguientes pasos:

  • Con el programa Aptitude desinstalar los paquetes vinculados a la versión de postgres-8.4
  • Eliminar manualmente las carpetas de postgres-8.4, por ej. /var/lib/postgresql/8.4/ y demás carpetas de esta versión.
  • Configurar los archivos pg_hba.conf y postgres.conf así:

pg_hba.conf

nano /etc/postgresql/9.1/main/pg_hba.conf

ir al final del archivo y verificar que las líneas sean así:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

postgresql.conf

nano /etc/postgresql/9.1/main/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
 
# - Connection Settings -
 
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
Nota 1: Es posible que debido al conflicto de versiones, el valor de port sea 5433, sin embargo es necesario cambiarlo a 5432
Nota 2: Si se va a permitir acceso a una ip en específico, se debe de agregar la línea''' siguiente en el archivo pg_hba.conf:
host    nombrebd       nombreusuario          numeroip/24         md5

donde nombrebd es el nombre de la base de datos a la que va a accesar la ip, nombreusuario el nombre usuario es el nombre del usuario asignado y numeroip la ip que va a tener acceso.


Con la configuración lista, reiniciar el servicio de PostgreSQL

/etc/init.d/postgresql restart
Si se está conectando a una base de datos postgis con UMN Mapserver, es necesario colocar en la etiqueta CONNECTION del archivo map el parámetro
de dbname sin comillas simples ya que no es compatible este formato con la versión 9.1 de Postgres.

Migración de postgresql-9.1-postgis-1.5 a postgresql-9.4-postgis-2.1

En primer lugar hay que crear la base de datos nueva en 9.4 vacía y luego entrar a la base de datos y ejecutar:

CREATE EXTENSION postgis;
psql -d nuevabd -f /usr/share/postgresql/9.4/contrib/postgis-2.1/legacy.sql
CREATE EXTENSION postgis_topology;

Postgis 2.1 y UMN Mapserver

La versión 2.1 de Postgis tiene algunos cambios en nombres de funciones que podrían generar error en las consultas hechas con el servidor de mapas Mapserver. Sin embargo, este problema se soluciona ejecutando la siguiente sentencia como usuario postgres en consola:

psql -d basededatos -f /usr/share/postgresql/[número de versión de postgres]/contrib/postgis-2.1/legacy_minimal.sql

Fuentes:

http://postgis.refractions.net
http://gis.stackexchange.com
http://www.postgresql.org
http://opengeo.org

Herramientas personales
Espacios de nombres

Variantes
Acciones
Navegación
Herramientas