Import data to CartoDB with Geobide

Publication of web mapping applications with hosted cloud platforms are part of a technology sector that is maturing rapidly. These platforms provide a highly reliable and scalable solutions for the implementation of web-level, with minimal support and infrastructure costs, and with more flexibility than expected.

This article focuses on import data to CartoDB using the GeoConverter tool that ships with the free suite Geobide. The exercise will serve us to demonstrate the flexibility that CartoDB offers to integrate easily into its architecture, and secondly, to instruct in the use of GeoConverter and his new "plugin" for reading and writing data in this repository.

Wikipedia says about CartoDB:

“CartoDB is a Software as a Service (SaaS) cloud computing platform that provides GIS and web mapping tools for display in a web browser. CartoDB users can use the company's free platform or deploy their own instance of the open source software. CartoDB is offered as freemium service, where accounts are free up to a certain size”

In other words, describes the typical architecture of geographic tools hosted in the cloud with a pricing policy based on the volume of data stored and a portfolio of technology capabilities to fit our own needs.

Geobide is a free suite that includes a set of desktop tools to transform and geoprocessing geographic data. Geobide supports most common CAD/GIS vector formats on the market (Shapefile, DGN, DXF/DWG, SDE, FGDB, PostGIS, LAS, ...).

The exercise will allow me to demonstrate the flexibility that CartoDB offers with its APIs and how easy it is going to be integrating our own tools in this cloud-based architecture.

Let's get to work!

Naturally, CartoDB offers its own data importer from different formats (Shapefile, CSV) but I will "practice" with its API to develop a new reader-writer of his data repository for Geobide. This new "plugin" is already available for free and you can download freely with all Geobide suite from its website. Here's a screenshot of GeoConverter with all supported formats including also the CartoDB dataset.

Of different APIs for developers that CartoDB offers, one of them seemed especially interesting for me because of this flexibility, it is the SQL API. According to the documentation, CartoDB saves the data, our own data uploaded to the cloud, in PostgreSQL/PostGIS databases and this API allows us to interact with them using SQL statements directly to hosted geographic layers.

Wow! That sounds great, we'll see if it's true :-). I have based the development of the new plugin on the use of this API using SQL statements as "CREATE TABLE", "SELECT", "INSERT"... in order to manage the layer in CartoDB. We can now use GeoConverter to better customize the data import process (aspect not too fancy in the tool of CartoDB) in areas such as the definition of the fields of geometries, or the definition of rules and expressions for customizing values of its attributes.

Left out of this article the technical details of plugin development for GeoConverter that would for any other article which document the use of the SDK for creating our own format managers or other functionalities.

Well, to practice we can download the layer of municipalities of my dear land Navarra. This layer is available from the IDE of Navarra (IDENA) and it is a Shapefile with 360 polygons with their specific attributes in the corresponding DBF file.

Needless to say that we could use as input in GeoConverter our own data layer, and saved in any of CAD/GIS formats supported, even now also CartoDB!

You can check this link to see the GeoConverter online help (Sorry, only in spanish).

Step 1: Select the input source.

Indicate at this point the format and data that are customizable in the source layer to load.

For a Shapefile, we simply indicate the file name.

Step 2: Define an alphanumeric and/or spatial filter to be applied to these source if we just load an extract of the entire layer.

Step 3: Set the output data settings.

In this exercise we define the output in "CartoDB" format, indicating the CartoDB user name and ApiKey settings where we will save the data.

The geometric and alphanumeric definition of the layer is automatically displayed by GeoConverter from the input layer, but we can change for example the attribute table to fit our preferences and it will be that we will see in the new CartoDB dataset.

By example, we will customize the attribute table, deleting a field definition loaded from the input layer, and add a field assigning its value based on an expression with data of the features (field values or geometric data such as area, length , ...). We can define the expression using a combination of attributes, standard mathematical operators, and utility functions that we need to calculate the final value of the new attribute.

Step 4, define the spatial transformation of the conversion.

CartoDB stores the geometry of each layer into two "the_geom" and "the_geom_webmercator" fields with the spatial reference systems "EPSG:4326" and "EPSG:3857" respectively. The second reference system is automatically managed by CartoDB so for us our layer is managed in "EPSG:4326".

Then indicate the transformation to apply to the source layer. The spatial reference system of my input layer is "EPSG:25830" and indicate the "EPSG:4326" as the target spatial reference system.

And that's all! Press the "Process" button and ready. We have our layer in the CartoDB repository for use in its great platform!

At this point I would like to discuss the good impression of CartoDB tools for a newcomer like me to this product, with a modern and intuitive interface. A "bonus" for this platform!

As a final conclusion of this article we tested the flexibility that CartoDB provides us with their APIs and we have applied it to develop a "plugin" into another external tool. It has helped us to discover the huge potential of these APIs in this "gis-cloud".

I have yet to improve the Geobide plugin for save geometries. Currently I insert each feature in a SQL "INSERT INTO ..." independent statement and as you would anticipate is very slow to import big data. The solution will be to modify the internal process basing it on the management of packages or files. This is my new task to investigate in these APIs. Fixed: Now the core manages the insert operations of geometries using a complex transaction wich groups them in chunks.