Excel functions

Microsoft Excel OR LibreOffice Calc are excellent tools for data cleaning. For small to moderate level tasks, I always use LibreOffice Calc but very time I use Calc functions, I have to search for functions as I cannot remember functions names or parameters. Thus this blog

Some interesting functions

1. Relplace/Remove unwanted characters

Ctrl+H is for find and replace, I use it all the time to remove special characters (end of line chearacter), spaces etc

2. Multiple data fields in one column.

Multiple fields in one column and I want them to be separated in multiple column

I have test “Alex 30years” in column A1 and as you can see these should be in two columns Name and age .I want any thing after first space to be in B1.

Now, will go to column B1 and paste the following

=MID( A1,FIND(" ",A1)+1,100)


  • FIND(“character”,cell_number) will find and return the location of given cheracter.

  • MID(cell_number) will extract substring from cell A1 and the substring will start from the location returned by FIND() function till 100th character (yes we can use lenth function instead of static number like 100)

3. sting concatenation

Have you seen a case where you have two or more columns and you want to generate a new column based on available columns. The colution is CONCATINATE() function. Here how it works

There are four columns with data


Now you want to column E to appear like “abc to z”. Click on cell/column D1 and use the following command

=CONCATENATE(A1,B1,C1," to ", D1)

I think its self explanatory :). This Example is not very interting so lets look at an interesting exmaple.

Suppose you have four pairs of latitudes/longitudes (a bounding box, see the image below) in columns and you want to make a csv which can be imported into qGIS in such a way that the columns becomes geometry.

We can generate a geometry column containing Well Known Text (WKT) by concatinating pairs of lat/long. Any GIS software that supports WKT will generate shapes based on this column.

and the code to generate wkt column is

=CONCATENATE("POLYGON ((",A2," ", B2,",",A2," ",C2,",",C2," ",D2,",",D2," ",B2,",",B2," ", A2,"))")

Save this exel sheet as csv and import in qGIS (by using layer – csv option). If every thing goes well, you will see a bolygon layer (like the one below)


Now if you have many rows, you can replicate the formula (by dragging it) on other columns.

Two interesting plugins for QGIS

QGIS has made its mark in FOSS4G and third party plugins make it more and more attractive. I will introduce two more plugins today which could also be useful  for you.


If you have many layers that require a similar style, you have to assign style for each layer one by one. Not any more, MultiQml can perform this tedious task for you.

First load all required layers. Second,  style any one layer and save the styling information as ‘QGIS Layer Style File’ below. You can use ‘Layer properties’ form to save styles (see below).

Screenshot from 2016-02-17 16:04:54

Start MultiQml from menu, perform the following

  1. Raster / Vector layer type
  2. Select all layers
  3. Apply style

Screenshot from 2016-02-17 16:09:32

The final operation will apply the same style (theme) to all the layers (in one go, saves a lot of time :)).

Table manager

In QGIS, if you want to change the attribute of shape file it is not possible directly. For example, you want to change the upper case character in field ‘Name’ to ‘name’, you cannot do that.

Now some one might think, why would I want to change the attributes from upper to lower characters. Well …. actually you don’t need to until you wan to upload this shape file to PostgreSQL/Postgis. If you have upper case character as attribute names (or as field names in database terms), you cannot access them (in a query) like normal attributes. For example a select on country shape file with upper character  (below) will return an error

select * from country where Name = ‘Sweden’;

one way to to correct the above query is to use quotes in field name

select * from country where “Name” = ‘Sweden’;

OR use ‘Table manager’ plugin to edit the attribute names. Start ‘Time Manager’ and it will pick the active layer and will show you all the attributes (below)

Screenshot from 2016-02-17 16:32:19

You can rename a attribute, change attribute order, clone an attribute, insert an attribute and delete an attribute.

puts, print and p in ruby

I’ve been trying to pick up Ruby and one of the things I was asking myself was the difference in the various ways you can output text.

  • puts: displays text and adds a newline character
  • print: displays text and doesn’t add a newline character (this means you must add newlines manually when using print)
  • p: while it’s similar to puts in that it adds a newline, it calls inspect instead of to_s This is why p can be more useful for debugging since it shows what class an object belongs to.

Screen Shot 2016-02-08 at 19.02.38

More references for those interested


Developing on OS X – Part 1

This multipart series will take you through how to setup a Python development environment on OS X Yosemite & El Capitan.  We will setup Python…and the various helpers I use for Django development.  We’ll also install a few other applications I make use of in my day-to-day coding activities.  For this first part, we’ll go through settings up the various helper applications we’ll end up using for our Python setup

Enabling Writing to NTFS

Install Homebrew and Homebrew Cask

For anyone new to OS X, homebrew is a package manager, and it enables you to install command-line applications and libraries that would require manual compilation.  While Homebrew Cask extends Homebrew.

To install homebrew, run this command on your terminal:

ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

To get Homebrew Cask run this command on your terminal:

brew tap caskroom/cask

We’re now ready to install osxfuse…El Capitan users will want to install the 3.x.x version (3.1.0 or higher would be best) to avoid System integrity Protection issues https://github.com/osxfuse/osxfuse/releases.  For Yosemite, brew will suffice

brew cask install osxfuse

The final step to give you write-access to NTFS will be to install ntfs3g.

brew install homebrew/fuse/ntfs-3g

If using El Capitan, System Integrity Protection’s fs role will first need to be disabled.  This is needed to enable writing to /sbin

  • reboot and hold CMD+R to go into recovery mode
  • Run this on a terminal: $ csrutil disable
  • reboot normally

Create a symlink to mount NTFS

sudo mv /sbin/mount_ntfs /sbin/mount_ntfs.original
sudo ln -s /usr/local/sbin/mount_ntfs /sbin/mount_ntfs

Remember to re-enable System Integrity Protection (and re-lock the system directories) if you’d previously disabled it (same steps as before), only run $ csrutil enable

After that you should now be able to read and write to your NTFS drives.


Xcode is an integrated development environment (IDE) containing a suite of software development tools developed by Apple for developing software for OS X and iOS.  While you don’t need the entire suite, you’ll want to install the command line tools.  Run this command then follow the instructions:

xcode-select --install


I always like having a good terminal at my disposal…iterm2 is my custom terminal of choice on OS X.  To get iTerm2, download and install it as a normal OS X pkg file

Version Control

It’s always recommended to have a VCS in place when you’re writing your code….this way you can always revert to a previously working version of your code if you break something along the line.  It’s a matter of personal preference which VCS you use, but I like git.  And for anyone new to VCS, this handy guide explains it all.

To install git:

brew install git


Next step is to configure git for your setup.  If you haven’t configured your SSH keys, that would be the first thing.  If you get stuck, this Github link will help

ssh-keygen -t rsa -C "<your-email-address>"
git config --global user.name "<your-name>"
git config --global user.email "<your-email-address>"


After this add your key to your Github account (same process for Bitbucket or any other code hosting site where you can upload your SSH keys).


  • You can never be too careful with your code so it’s always a good idea to enable two-factor authentication whenever possible.
  • For Android users, if you change phones frequently, instead of the Google Authenticator app, you can check out Authy…it’s slightly better since it has a clud backup feature, and if you’re a Chrome user, you can even register your browser as an MFA device

That’s it for the basics…in the next blog post we’ll get down and dirty with getting Python running

Time series animations using QGIS

For the past couple of months, I am working with vegetation indices (NDVI and EVI) time series data. Today, I decided to visualize some of the EVI data as an animation.

QGIS has an excellent plugin know as “TimeManager“. You can install “TimeManager” by selecting Plugins-> Manage and Install Plugins from QGIS menu. Search TimeManager in plugin window (like below)

Screenshot from 2015-12-17 15:22:50

After installation, you have to activate TimeManager plugin by clicking on it. A window (like below) will appear at the bottom of monitor screen.

Screenshot from 2015-12-17 15:21:24

Assuming that you have loaded and styled all the layers that will be used in animation, click the “setting” button on the TimeManager’s window. A new window will appear like the one below

Screenshot from 2015-12-17 16:05:45

Now add the layer, start and end time. In my case, I used “Add raster” option to add EVI layers, each layer had one second of visibility during animation. Following is an image of how my setting window looked like.

Screenshot from 2015-12-17 15:21:54

Finally, press “Export Video” button, a new window will appear like the one below.

Screenshot from 2015-12-17 15:22:14

Select the output folder and click on “Animation gif” radio button. If required, modify the “Animation frame Delay”, for my dataset, 100 millisec was too low thus I changed it to 1000 millisec.

The processing might take from few seconds to few minutes, in my case it two around 4 to 5 minutes (result below)


Some very good examples of TimeManager plugin are available on youtube, like the one below

and also this one.

GeoWebCache under GeoServer

Few months back I wrote about GeoWebCache in my post “GeoWebCache under GeoNode“. Today I noticed a strange beheaviour from GeoWebCache as some of my newly added layers were not being cached.

If GeoWebCache is working properly, when ever a layer is published in GeoServer, by default a cached layer is created with the same name (one can check this by looking in Tile Layers list under Tile Caching GeoServer menu (left side)). Plus a folder is created with similar name to hold tiles in main cache path (e.g. …./geowebcache/workspace_tree_layer). Now if some one views this layer, GeoWebCache will cache tiles generated by GeoServer (and on next request, these pre-generated tiles will be served, if you want to know more read “Tile Caching and Seeding – GeoWebCache” ). Tiles are stores in their respective folder in main cached path.

Now back to my problem, when I checked cached folder (on server), I found that individual cached folders for each newly  created layer were being generated. But all of these folders were empty (no tiles inside any of the folders). Hmmmmmmmmmmm

As my last resort, I started looking at GeoServer logs and found “no such style” error again and again (it must have been generated when I was seeding these specific layers). I looked at style descriptors (SLDs) and found that when ever I assign SLDs to a specific workspace (no matter if it is the same one of the layer to which this SLD belongs to), the SLD is not available to GeoWebCache. When I removed the workspace information, the GeoWebCache found the SLD and worked perfectly.

Although, I solved the issue but I do not understand why under the same workspace, GeoWebCacbe cannot find style information. I am using GeoServer 2.4 (under GeoNode 2.0).