Siguiendo el post de ayer sobre Subir capas a CartoDB mediante Python, ahora vamos a ver cómo podemos realizar peticiones de información a estas capas.
Lo más complicado es tener la instalación completada tal y como explico en el post anterior. Si llegaste a ejecutar el código de subir un fichero a CartoDB, no debería de aparecer ningún nuevo error al ejecutar estas peticiones.
Para realizar este código nos hemos basado en la información que aparece sobre la SQL API y sobre la librería cartodb-python.
La peticiones SQL que hacemos es muy sencilla, le pedimos a la base de datos que nos devuelva los datos de: osm_id y name, que corresponden a las 5 primeras ciudades ordenadas por osm_id.
from cartodb import CartoDBAPIKey, CartoDBException, FileImport
API_KEY ='apikey'
cartodb_domain = 'cuenta'
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
#Peticion SQL
petition = ('SELECT osm_id, name FROM cities_europe_shp ORDER BY osm_id LIMIT 5;')
catch = (cl.sql(petition))
for f in catch['rows']:
print f
Ocurre que no todas las ciudades tienen un nombre válido, aparecen algunas con nombre ‘???’.
{u'name': u'Gala?i', u'osm_id': u'100035698'}
{u'name': u'Leicester', u'osm_id': u'10021976'}
{u'name': u'???', u'osm_id': u'100890898'}
{u'name': u'???????', u'osm_id': u'101480194'}
{u'name': u'Lincoln', u'osm_id': u'10671639'}
Así que para limpiar datos vamos a ejecutar un código SQL que genere una nueva columna donde indique si el nombre de la ciudad contiene un’?’ o no.
Primero añadimos una columna nueva a nuestra tabla:
#Agregar columna
petition = ('ALTER TABLE cities_europe_shp ADD valid_name2 BOOLEAN;')
catch = (cl.sql(petition))
Antes de nada vamos a comprobar cuantos nombre contienen alguna ‘?’:
petition = ("""
SELECT count(*)
FROM cities_europe_shp
WHERE name
LIKE '%?%';
""")
catch = (cl.sql(petition))
for f in catch['rows']:
print f
Vemos que muestra que hay más de quinientos nombres incompletos:
{u'count': 564}
Esto puede ocurrir porque no nos preocupamos de esto al hacer la importación, y hay caracteres no soportados.
Ahora, le damos valor a la nueva columna según el campo name: si no contiene ningún carácter ‘?’, se le asignará el valor de True.
#Actualizar columna
petition = ("""
UPDATE cities_europe_shp
SET valid_name=True
WHERE name
NOT LIKE '%?%';
""")
catch = (cl.sql(petition))
for f in catch['rows']:
print f
Si vemos nuestro dataset en CartoDB veremos, algo por el estilo a esto, apareciendo True en la columna nueva creada:
Comprobamos que ahora podemos hacer la petición inicial pero solo con los nombres que sean válidos para nosotros:
#Peticion SQL sin ?
petition = ('SELECT osm_id, name FROM cities_europe_shp WHERE valid_name=True ORDER BY osm_id LIMIT 5;')
catch = (cl.sql(petition))
for f in catch['rows']:
print f
Y vemos que los que muestra es algo más parecido a lo que buscábamos:
{u'name': u'Leicester', u'osm_id': u'10021976'}
{u'name': u'Lincoln', u'osm_id': u'10671639'}
{u'name': u'London', u'osm_id': u'107775'}
{u'name': u'Marbella', u'osm_id': u'1081751552'}
{u'name': u'Guarda', u'osm_id': u'1084227094'}
Por supuesto, esta petición la podemos realizar directamente sin crear un campo nuevo quedando así:
petition = ("SELECT osm_id, name FROM cities_europe_shp WHERE name NOT LIKE '%?%' ORDER BY osm_id LIMIT 5;")
Con esto estamos actualizando, modificando y consultando directamente nuestros datos desde código. Y al ser desde código, con esto puedes crear procesos fácilmente automatizados.
Este es el código completo:
from cartodb import CartoDBAPIKey, CartoDBException, FileImport
API_KEY ='apikey'
cartodb_domain = 'cuenta'
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
#Peticion SQL
petition = ('SELECT osm_id, name FROM cities_europe_shp ORDER BY osm_id LIMIT 5;')
catch = (cl.sql(petition))
for f in catch['rows']:
print f
#Agregar columna
#petition = ('ALTER TABLE cities_europe_shp ADD valid_name2 BOOLEAN;')
#catch = (cl.sql(petition))
#Seleccionar columnas que cumplen el nombre con ?
petition = ("""
SELECT count(*)
FROM cities_europe_shp
WHERE name
LIKE '%?%';
""")
catch = (cl.sql(petition))
for f in catch['rows']:
print f
#Actualizar columna
petition = ("""
UPDATE cities_europe_shp
SET valid_name=True
WHERE name
NOT LIKE '%?%';
""")
catch = (cl.sql(petition))
for f in catch['rows']:
print f
#Peticion SQL sin ?
petition = ("SELECT osm_id, name FROM cities_europe_shp WHERE name NOT LIKE '%?%' ORDER BY osm_id LIMIT 5;")
catch = (cl.sql(petition))
for f in catch['rows']:
print f
Y se me olvidaba ya, también podemos por supuesto añadir nuevas entidades a nuestra capa, el código sql sería similar al siguiente:
#Insert info
rows = [
"(CDB_LatLng(10, 10), 'Ciudad 1',
"(CDB_LatLng(20, 12), 'Ciudad 2'",
"(CDB_LatLng(30, 14), 'Ciudad 3'"
]
petition = "INSERT INTO cities_europe_shp (the_geom, name) (VALUES %s)%s" % (','.join(rows),';')
catch = (cl.sql(petition))
Este código ha sido sacado del siguiente post en gis.stackexchange.
Vamos a ejecutar ahora código SQL directamente desde la visualización de CartoDB. Para ello abrimos nuestro dataset y nos vamos a: Map View. A la derecha veremos un botón donde aparece: ‘SQL’.
Lo interesante de esta petición SQL es que maneja los datos que se mostraran en la visualización. Al aplicar el QUERY, vemos como gran parte de las ciudades rusas desaparecen. Esto confirma la sospecha de que ha sido al convertir los caracteres el alfabeto ruso.
(Se poco sobre codificación, si alguien está interesado en aportar documentación donde se pudiera realizar esta importación desde OSM hasta fichero de texto, manteniendo correctamente todos los caracteres, seré el primero encantado de oirlo).
Así pues, con esto también hemos aprendido a modificar nuestra visualización con una petición SQL. Probablemente saque otro post dedicado en exclusiva a esta visualización mediante SQL.
Más post próximamente, y no olvidéis de comunicarme por comentarios o Twitter los temas en los que más estéis interesados.
másquesig 