sábado, 12 de octubre de 2013

Exportar una tabla de Excel al Postgresql / Postgis

En este post, se va explicara una forma de exportar una tabla en Excel al Postgresql / Postgis usando una Macro (tendrá información geométrica que se visualizara en el Quantum GIS).

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:
http://www.connectionstrings.com/postgresql/


 La estructura que usualmente uso es la siguiente:

 Dim TXT as string

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.

 El driver PostgreSQL ODBC Driver(ANSI) es obtenido del “ODBC Data Source Administrator”


 















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 (***).

 (***) Esta columna vendría ser la “Columna clave” de la tabla a crear.

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


 Dónde:

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

 Luego con una sentencia en SQL del Postgis creamos un punto desde los datos de la columna “Long_84” y “Lat_84” con la referencia espacial “SRID” 4326 que es WGS84 en Latitud / Longitud y lo guardamos en la variable “Punto” (no está inicializada, pero automáticamente lo reconoce como un string)

Después en la variable TXT escribimos la sentencia SQL que nos permite insertar los datos a la tabla desde el Excel.

 A continuacion 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 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.
ª