Crear visualizaciones en CartoDB mediante SQL y PostGIS

Visto que la gente está interesada en el tema de CartoDB, voy a coger una parte del anterior post, Ejemplos de peticiones SQL sobre tus capas subidas a CartoDB mediante Python, y hacer uno en exclusiva para la modificación de la visualización mediante SQL y PostGIS.

Explicar rápidamente que CartoDB consta de dos partes: Maps y Datasets. Los dataset hacen referencia a tablas de datos, y los mapas a visualizaciones de estas tablas. Una visualización puede mostrar varios dataset. El nombre que aparece en los dataset son los nombres de las tablas que necesitamos si queremos acceder a ellas mediante SQL, como expliqué en anteriores post.

Lo primero, tenemos que crear un mapa con los datos que queramos. Una vez entremos en nuestra cuenta de CartoDB, iremos a la sección de Maps. Aquí nos aparecerá un botón en verde «New map«. En esta pantalla podremos seleccionar los dataset que queremos que formen parte de la visualización, o subir nuevos dataset. Más adelante se pueden añadir más dataset a la visualización. Si estás interesado en utilizar los mismos datos que uso en este post, los puedes descargar de aquí.

Creamos una visualización con la capa de cities_europe_shp que hemos estado utilizando.

Para la visualización podéis jugar mucho con CartoDB, aquí un mapa rápido con las ciudades de las cuales tengo el nombre válido y las que no, sacado también del anterior post, y fácilmente representable al seleccionar un mapa por categorías basado en el campo valid_name:

El Wizard para crear representaciones sobre tu visualización es muy intuitivo y sencillo de utilizar, contiene diversos modos y puedes tocarlo todo lo que quieras ya que no hay posibilidad de que afecten a tus datos.

Pero lo que buscamos es la pestaña de SQL, que también aparece a la derecha. Si entramos a esta pestaña tenemos la oportunidad de trabajar con los datos que aparecen en la visualización. Por ejemplo, si queremos eliminar de la visualización los datos que son False en el campo valid_name, deberemos escribir:

SELECT * FROM cities_europe_shp WHERE valid_name=True

Vemos que nos elimina de la visualización los puntos correspondientes a valores no válidos:

Ahora, pasando a algo más complicado me he puesto a investigar y he comprobado que la parte de SQL la han ampliado desde CartoDB y ahora aparece en la Map Academy – SQL and PostGIS in CartoDB, así que ¡genial!, os muestro un par de ejemplos y si queréis formaros completamente ya sabéis donde ir. Distribuido en varias lecciones y con vídeos, totalmente recomendado. Además comentan que están pendientes de ampliar a una tercera lección. Genial CartoDB, genial.

He modificado una de las peticiones que aparecen en la Lesson 1, para que me haga un filtrado por ciudades que estén a menos de 1000 km de las coordenadas (40, 0) quedando así:

SELECT *
FROM cities_europe_shp ct
WHERE ST_Distance(the_geom::geography,
CDB_LatLng(40, 0)::geography
)/1000 < 1000

En esta petición lo que hace es un filtrado donde aparece el WHERE. Lo que hace es captar las coordenadas de los puntos the_geom y con la parte de ::geography lo que hace es proyectar este punto para poder trabajar con él. Con CDB_LatLng creamos una geometría de tipo punto, calculamos su distancia que nos la dará en metros, la pasamos a kilómetros y nos quedamos con las que están a menos de 1000km de ese punto. Todo esto lo podéis ver mejor explicado en la Lesson 1.

Con el siguiente código, basado en un ejemplo de la Lesson 2, podemos realizar una área de influencia sobre cada ciudad, en este caso la haremos de 50 kilómetros:

SELECT
  ST_Transform(
    ST_Buffer(
      the_geom::geography,
      50*1000
    )::geometry,
    3857
  ) As the_geom_webmercator,
  cartodb_id
FROM
  cities_europe_shp

He creado rápidamente una ruta la cual he introducido como otra capa en la visualización, entonces vamos a indicar que nos muestre solo las ciudades que entren en un rango de 100km esta ruta:

SELECT
	ce.cartodb_id,
	ce.the_geom_webmercator
FROM
	cities_europe_shp AS ce,
	europe_route AS er
WHERE
	ST_Intersects(
      ST_Buffer(
        er.the_geom_webmercator,
        100000),
      ce.the_geom_webmercator
      )

Lo hacemos calculando las ciudades que dan True en la intersección entre el buffer de la ruta, y la ciudad.

Y el ejemplo que quería hacer para terminar me parece demasiado complicado como para reexplicar, lo interesados tenéis un post del mismísimo Paul Ramsey explicandolo: Jets and Datelines. Yo solo voy a realizar un par de pasos solamente para que veáis un poco de PostGIS en acción, intentando evitar la parte complicada de SQL.

Primero hacer una línea que vaya desde una de nuestras entidades a un punto concreto de coordenadas (-30, 120).

SELECT 
cartodb_id,
ST_Transform(
  ST_Makeline(
    the_geom, 
    CDB_LatLng(-30,120)
  ),
  3857)
as the_geom_webmercator
FROM cities_europe_shp 
WHERE name='Cardiff'

Y ahora le vamos a dar la forma de la curva de círculo máximo:

SELECT 
cartodb_id,
ST_Transform(
  ST_Segmentize(
    ST_Makeline(
        the_geom, 
        CDB_LatLng(-30,120)
      )::geography,
    100000)::geometry,
  3857)
as the_geom_webmercator
FROM cities_europe_shp 
WHERE name='Cardiff'