Geolocalizando tweets con Python. Tutorial 2.

En este post sigo con el tutorial anterior, en el que aprendíamos cómo recuperar tweets con un pequeño script en Python, y guardarlos en una base de datos Postgres.

Crear geometrías en PostGIS:

Como sabemos, Postgis es la extensión para dotar de componente espacial a las bases de datos de Postgres. Cómo no, si queréis realizar esta parte del tutorial, deberéis tenerla instalada. No voy a entrar en detalles de cómo hacerlo, pues hay mucha información en internet
Para crear la geometría vamos a utilizar los campos de coordenadas x_coord e y_coord. A partir de éstos crearemos un campo de geometrías, que será el encargado de mostrar los puntos en el mapa. Primero creamos el campo en sí alterando la tabla, y le pasamos los parámetros: El nombre del campo "the_geom", el tipo "geometría", el tpo de geometría "puntos" y el SRID, 4326 en este caso.



ALTER TABLE geo_tw ADD COLUMN the_geom geometry(POINT,4326);
Luego solo queda ejecutar la función de geometría, pasándole como parámetros los campos que contienen las coordenadas en texto:
UPDATE geo_tw SET the_geom = ST_SetSRID(ST_MakePoint(x_coord,y_coord),4326);
Y crear un índice espacial
CREATE INDEX idx_geotw_geom ON geo_tw USING GIST(the_geom);
Con esto ya disponemos de una tabla con geometría en Postgres, a la cuál podemos acceder conectándonos por medio de nuestro SIG favorito.
Tan sólo queda hacer un pequeño paso para optimizar la conexión con el SIG. Como se supone que el script sigue corriendo aunque nos conectemos, para evitar fallos en la conexión de QGIS con Postgres, he creado una vista, la cual guardará los tweets que queramos visualizar, y será la que utilizaremos para conectarnos. Para ello:
CREATE VIEW nombredelavista as SELECT * from geo_tw WHERE the_geom IS NOT NULL
Y ya tenemos la vista creada. Solo tendremos que conectarnos a dicha vista, y visualizar los resultados.
Aquí podéis ver un ejemplo de lo capturado: Aunque no lo parezca, hay 1733 puntos.
O aquí, con otros 1500, pero sólo en España:
Con esto me queda pendiente ralizar algún proceso de visualización de datos, ¡cada vez tengo más datos, y no sé muy bien para qué utilizarlos! Así, si alguien quiere, puedo realizar otro pequeño tutorial sobre cómo configurar Postgres para permitir conexiones externas. Otra cosa que me queda pendiente es realizar un Trigger para la base de datos, que permita hacer automáticamente la creaión de geometría cada vez que se inserta un registro nuevo en la base de datos. Si tenéis alguna petición, al final de este post podéis dejar vuestros comentarios.

13 comentarios:

  1. pablo tengo un problema a la hora de extraer la geolocalizacion de los tweets a tu manera

    ResponderEliminar
  2. Hola. Soy consciente de que este método es un poco antiguo, en cuanto tenga un poco de tiempo tengo que mejorarlo. Pero si me concretas el problema estaré encantado de ayudarte en lo que pueda.

    ResponderEliminar
  3. buenas pablo, solucione todo lo que me abrumaba, ahora tengo una base de datos mysql con las coordenadas X e Y que he recogido de los tweets escuchados y me gustaria mostrarlas en un mapa de cartoDB, puedes ayudarme, estoy francamente perdido.
    un saludo y gracias

    ResponderEliminar
  4. Hola. Pues a ver, si lo que quieres es importar tu bdd a CartoDB, basta con exportarla a algún formato que acepte, ya sea CSV, o si es espacial, SHP, geoJSSON, etc.
    Si trabajas con shp o geojson deberías crear las columnas de geometría a partir de las coordenadas, para esto, con mysql no sé muy bien cómo sería, pues nunca lo he usado, pero seguro que con las herramientas espaciales puedes hacerlo[1]
    Si no te aclaras con esto, tal vez la manera más sencilla sea, como digo, exportar a CSV, y a la hora de importar, CartoDB te reconocerá los campos X e Y como coordenadas y te los geolocalizará.

    No sé si con esto he respondido tu pregunta, si no, házmelo saber.
    Un saludo :)

    ResponderEliminar
  5. Entendido pablo, he exportado mi base de datos mysql a fichero csv sin problema, ahora la problematica es que al importar este fichero a cartoDB me reconoce erroneamente las columnas de coordenadas. El problema es que aparte de las columnas que vienen por defecto en cartoDB nos añade las nuestras propias de la BD de mysql entonces no coinciden las coordenadas de nuestra bd mysql referentes a la geolocalizacion (X e Y) con las coordenadas de dartoDB (the geom etc)
    ¿que puedo estar haciendo mal ahi? podrias ayudarme??
    agradezco encarecidamente su ayuda.

    ResponderEliminar
    Respuestas
    1. No debería darte error. Los pasos que yo he seguido, partiendo de un CSV con 3 campos: Id, X, Y son:
      Importar tabla en CartoDB
      Cuando esté importada verás sus columnas, y la columna Geom estará vacía (aún no ha creado la geometría, sólo tiene coordenadas)
      Vas a Vista de Mapa, arriba, y le dices que te la geocodifique, te mostrará un desplegable con las columnas que necesita para la longitud, que será tu X, y la latitud, que será tu Y.
      Una vez hecho esto, verás que la columna Geom, ya está actualizada, y podrás verlas en el mapa.
      Este proceso es el que explico en esta misma entrada con PostGIS, pero lo hace internamente CartoDB (UPDATE geo_tw SET the_geom = ST_SetSRID(ST_MakePoint(x_coord,y_coord),4326)

      Si aún así te da problemas, si quieres pásame al correo una muestra de tu tabla con algún registro, y puedo mirar si tiene algún problema.
      geograforever (arroba) gmail.com

      Eliminar
  6. Muchísimas gracias Pablo, nos has ayudado mucho, hemos conseguido todo perfectamente tal como queriamos, un saludo

    ResponderEliminar
  7. Hola pablo te escribo para hacerte una pregunta, quiero un listbox que recargue las bases de datos que tengo en cartodb, y todo esto desd un html-php, tienes idea de como poder hacer eso para rellenar mi listbox??
    muchas gracias

    ResponderEliminar
  8. Hola. Pues en el SQL API me parece que sólo hablan de operaciones de Insert, Delete, Select, etc de datos, pero no de bases de datos de un usuario.
    Si no encuentras nada ahí, igual en http://gis.stackexchange.com te pueden ayudar, si usas la etiqueta cartodb, tengo entendido que lo utilizan administradores como hilo de consultas.

    ResponderEliminar
  9. Hola Pablo. Una duda,
    Tu cuando estas viendo la tabla de cartodb hay un campo llamado the_geom y tu ahi asignas el campo que quieras para que te lo convierta, ya sean coordenadas, ciudades, etc.
    Sabes como se podria hacer esta asignación a traves de un sentencia SQL?
    Gracias por tu tiempo, un saludo

    ResponderEliminar
  10. Hola.
    No sé si te sigo. CartoDB en realidad es una base de datos postgis, por lo que tengo entendido que cualquier sentencia se puede hacer desde la consola de cartoDB.
    Por tanto, si creas un campo geometría
    ALTER TABLE tu_tabla ADD COLUMN the_geom2 GEOMETRY
    Puedes luego añadirle los datos:
    INSERT INTO tu_tabla(id, the_geom2) VALUES(2, ST_GeomFromText('POINT(-71.060316 48.432044)', 4326));
    O con un UPDATE para sacarlos de las columnas de coordenadas, etc.

    No sé si contesta eso a tu pregunta.
    Un saludo.

    ResponderEliminar
  11. No, te explico más detalladamente.
    Tu cuando tienes una tabla rellena, en el campo the_geom aparece como un botón al lado naranja donde pone GEO, y dandole tu seleccionas si quieres que los puntos te los pinte by long/lat, by city name, etc. Y una vez seleccionas el que quieres ya te pinta los puntos en el mapa.
    Mi pregunta es si esta accion se puede hacer a traves de SQL.
    Gracias por tddo, un saludo

    ResponderEliminar
  12. Vale. Pues depende de si tienes los datos.
    Si cuentas con las columnas de latitud y longitud, supongo que podrás hacerlo sin problemas:
    UPDATE tabla SET the_geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);

    Donde longitude y latitude son tus columnas.

    Ahora bien, si lo que quieres es hacerlo por nombre de ciudad, o de calle, no podrías hacerlo así, ya que CartoDB utiliza algún tipo de servicio para sacar las coordenadas de lo que le digas. (Ej. Si en tu tabla pone "Madrid" buscará cuál es la coordenada del centro de Madrid, y le asignará un punto.)
    Supongo que con calles y distritos lo hará similar. Nunca he utilizado ese servicio.

    Pero si dispones de los datos, sí que puedes hacerlo por SQL como he dicho arriba.

    ResponderEliminar