Creating post offices network map in CartoDB - mofoyoda/geowiz GitHub Wiki

##Mission

Let's pretend we want to make a map of all post offices in Russia, displaying connections between them and add it to our wiki page here. Since we don't want to waste time on setting geospatial webstack to just make a simple map, CartoDB is generally a platform of choice for manipulating data in the cloud.
Is there any data we can use to achieve the task?
##Initial data Yes there is! Initial data can be grabbed from the Russian Post official site. It's being exposed to the wild in a form of dbf table, which isn't really bad, because it's a sign that data is well structured. So we shouldn't have to mess with regexes, formatting, etc. Unfortunately, CartoDB doesn't support DBF format for import, we have to convert it to CSV

So let's use a handy ogr2ogr utility to make a nice CSV file:

ogr2ogr -f CSV -lco SEPARATOR=SEMICOLON post.csv PIndx22.DBF

This will just translate our table into a 6MB CSV file, which we're going to import to CartoDB in the next step. Since a DBF table has text fields with cyrillic letters, ogr2ogr utility also helps us to re-encode it to UTF-8.

##Importing data After logging into CartoDB account we have a choice to work either with datasets or with maps. We'll choose datasets for now.

By choosing Create New

##Preparing location info for geocoding

Our table has location data, but it is stored in different columns. First we have to add an address column to our table. This is easily done in SQL pane of our dataset. From there we just have to enter an SQL query for creating a new text column.

ALTER TABLE post ADD COLUMN addr text;

Let's populate the column with formatted address info for each row

UPDATE post SET addr =
(
 CASE 
  WHEN "index" IS NOT NULL THEN "index"::text
  ELSE ''::text 
 END
) ||
(
 CASE 
  WHEN region IS NOT NULL AND region != ''::text THEN ', ' || region
  ELSE ''::text 
 END
) ||
(
 CASE 
  WHEN city_1 IS NOT NULL AND city_1 != ''::text THEN  ', ' || city_1
  ELSE ''::text 
 END
) ||
(
 CASE 
  WHEN city IS NOT NULL AND city != ''::text THEN ', ' || city
  ELSE ''::text 
 END
);

The reason we're using CASE statement for every concatenated column is that we want to add a separating comma to parts of an address, but don't want to separate empty values.