En este
ejemplo, se intentara crear una tabla en el Postgresql / Postgis usando datos
de una tabla en Excel. Esta tabla contiene información de los departamentos que
está dividido el Perú y tendrá seis columnas, las cuales son:
Id: de tipo “numérico” y que es un
autonumerico.
Nombre: de tipo “string” y contiene los
nombres de cada departamento del Perú.
Poblacion: de tipo “numérico” y contiene
la población de cada departamento del Perú.
Creacion: de tipo “date”y contiene la
fecha de creación de cada departamento del Perú (*).
Long_84: de tipo “numérico” y contiene
la longitud de la capital de cada departamento (**).
Lat_84: de tipo “numérico” y contiene
la latitud de la capital de cada departamento (**).
(*) Es
la fecha de creación del departamento y no de la capital del departamento
(**) Las
coordenadas Long_84 y Lat_84 están en WGS84.
Para
ello, tenemos que tener estas cinco consideraciones:
A. Activar la referencia “Microsoft ActiveX Data
Objects 2.1 Library”, para que nos reconozca el objeto ADODB.recordset en la Macro.
B. Verificar que “Connection String” nos será útil para conectar el Postgresql / Postgis al Excel. Para eso pueden ver el “ODBC Data Source Administrator” y ver que Drivers para Postgrsql tienen instalados, o también pueden visitar estas páginas:
TXT = "Driver={PostgreSQL ODBC
Driver(ANSI)};Server=" & servidor & ";Port="
& port & ";Database=" & BD & ";Uid=" &
usuario & ";Pwd=" & clave & ";"
Dónde:
TXT,
es una variable de tipo string (texto) que sirve para guardar todo el
“Connection String”.
servidor, el servidor con que el
Postgresql trabaja (por lo general tiene el valor de “LOCALHOST”).
port, el
puerto con que el Postgresql trabaja (por lo general tiene el valor de “5432”).
BD, nombre
de la base de datos a que se quiere ingresar.
usuario, nombre del usuario con
acceso al Postgresql (por lo general es el usuario “postgres”)
clave, clave de ingreso a la base de datos.
C. La tabla que se creara en el Postgresql / Postgis tendrá la siguiente estructura de columnas:
Id: de tipo “smallint” y que es un
autonumerico.
Nombre: de tipo “character varying” y
contiene los nombres de cada departamento del Perú.
Poblacion: de tipo “numeric” y contiene la
población de cada departamento del Perú.
Creacion: de tipo “date” y contiene la
fecha de creación de cada departamento del Perú.
Long_84: de tipo “numeric” y contiene la
longitud de la capital de cada departamento.
Lat_84: de tipo “numeric” y contiene la
latitud de la capital de cada departamento.
The_geom: de tipo “geometry” y
contendrá cada punto de las capitales departamentales.
gid serial: de tipo “NOT NULL” y es
un autonumerico propio de las tablas de Postgresql / Postgis (***).
D.
Para crear la tabla, columnas y hacer la inserción de los datos, se usaran
comandos y sentencias SQL, los mismos que usa el Postgresql.
E.
No es tan necesario declarar una variable de tipo numérico o string en una
Macro de Excel.
El
código de la Macro para hacer este proceso es este:
Sub Excel_Postgres()
Dim MiRecordset As ADODB.Recordset
Dim TXT As String
Dim CamposEXCEL As String
'PARTE 01: Creacion de la tabla y las columnas
CamposEXCEL = " id smallint, nombre character varying(20), poblacion numeric(20,0), creacion date, long_84 numeric(20,16) , lat_84 numeric(20,16)"
TXT = "CREATE TABLE excel (" & CamposEXCEL & ", the_geom geometry, gid serial NOT NULL"
TXT = TXT & " ,CONSTRAINT " & Chr(34) & "excel_pkey" & Chr(34) & " PRIMARY KEY (gid), 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 (srid(the_geom) = 4326))"
Set MiRecordset = New ADODB.Recordset
Set MiRecordset = ConexionBaseDatos("localhost", "5432", "MI_BASE_DE_DATOS", "postgres", "MI_CLAVE", TXT) '(****) ver descripción de esta Función"
MiRecordset.Open
'PARTE 02: Inserción de los puntos a la tabla
For i = 1 To 25
Punto = "SRID=4326;POINT(" & Range("E" & i + 1).Value & " " & Range("F" & i + 1).Value & ")"
TXT = "INSERT INTO " & Chr(34) & "excel" & Chr(34) & " VALUES (" & Range("A" & i + 1).Value _
& ",'" & Range("B" & i + 1).Value & "'," & Range("C" & i + 1).Value & "," & "'" & Range("D" & i + 1).Value & "'" _
& "," & Range("E" & i + 1).Value & "," & Range("F" & i + 1).Value & ", '" & Punto & "', " & i & ")"
Set MiRecordset = ConexionBaseDatos("localhost", "5432", "MI_BASE_DE_DATOS", "postgres", "MI_CLAVE", TXT) '(****) ver descripción de esta Función"
MiRecordset.Open
Next
End Sub
Function ConexionBaseDatos(servidor As String, port As String, BD As String, usuario As String, clave As String, ByVal txtSQL As String) As ADODB.Recordset
Dim cn As ADODB.Connection
Dim MiRecordset As New ADODB.Recordset
Dim TXT As String
Set cn = New ADODB.Connection
TXT = "Driver={PostgreSQL ODBC Driver(ANSI)};Server=" & servidor & ";Port=" & port & ";Database=" & BD & ";Uid=" & usuario & ";Pwd=" & clave & ";"
cn.ConnectionString = TXT '
cn.Open (TXT)
Set MiRecordset = New ADODB.Recordset
MiRecordset.CursorType = adOpenStatic
MiRecordset.CursorLocation = adUseClient
MiRecordset.LockType = adLockOptimistic
MiRecordset.Source = txtSQL
MiRecordset.ActiveConnection = cn
Set ConexionBaseDatos = MiRecordset
End Function
Parte 01. En este punto, se crea la tabla y las
columnas en el Postgresql / Postgis.
Primero
en la variable “CamposEXCEL” (de tipo string) guardo la configuración de las
columnas de la tabla a crear y que están en el Excel en este momento (no están consideradas
por el momento ni la columna “the_geom” ni la columna “gid serial”).
En
la variable “TXT” (de tipo string) guardo la sentencia SQL que me permite crear
la tabla en el Postgresql, aquí estará los valores de la columna “CamposEXCEL”
y las columnas “the_geom” y “gid serial” (con la configuración de ambas
includas).
Luego
se sobrescribe la variable “TXT” con la configuración de la tabla, se asigna la
Columna Clave (“gid serial”), el tipo de Geometría (POINT) y la referencia
espacial “SRID” (4326 que es WGS84 en Latitud / Longitud).
Después
se inicializa el objeto recordset llamado “MiRecordset”
Luego
se guarda en el objeto “MiRecordset” se guarda el resultado de la Función
“ConexionBaseDatos” (****) ver descripción de esta Función.
Ejecutamos
(o abrimos) el recordset “MiRecordset” para crear la tabla.
Parte 02. En esta parte se realiza la
inserción de puntos a la tabla creada
Tenemos
una sentencia “For” inicializada en i=1 hasta i=25 (ya que son 25 registros a
ingresar).
Después
en la variable TXT escribimos la sentencia SQL que nos permite insertar los
datos a la tabla desde el Excel.
Ejecutamos
(o abrimos) el recordset “MiRecordset” para crear insertar cada registro, de
acuerdo como se va moviendo la sentencia “FOR”.
(****)
Función “ConexionBaseDatos”, esta función permite conectar a la base de datos
para que se ejecute una función SQL. Devuelve un objeto de tipo “ADODB.Recordset” y necesita:
servidor, el servidor con que el
Postgresql trabaja (por lo general tiene el valor de “LOCALHOST”).
port, el
puerto con que el Postgresql trabaja (por lo general tiene el valor de “5432”).
BD, nombre
de la base de datos a que se quiere ingresar.
usuario, nombre del usuario con
acceso al Postgresql (por lo general es el usuario “postgres”)
clave, clave de ingreso a la base de datos.
txtSQL,
que es la sentencia a ejecutar sobre la base de datos o tabla.
Luego
podemos ver la tabla creada en el Postgresql
Y
también podemos usar el Quantum GIS para visualizarlo espacialmente
Esperando que sea de utilidad, será hasta el otro mes.
