Thursday, November 27, 2014

Deleting a Foreign Key Model Instance


Consider a scene where you have a django model with a Foreign Key reference to another Model Instance.  Now when you try and update the Foreign Key Instance , one need to remove the already existing instance. While trying to do that django also delete the model instance hosting the Foreign Key reference. So how to delete the Foreign Key Reference object without deleting the host instance.


The solution is to update the model with on_delete attribute  set as models.SET_NULL. Django by default assumes cascading behavior while deleting any model object.

Not knowing this costed me some precious time to figure this out.

Tuesday, November 18, 2014

How to get GIS coordinates from the address using Google Geo Location API

Problem : One of the requirement to enter data for client on boarding might be to enter the location data for the customer. The location data can then be used to generate specific insights based on client locations.The client form consists of typical form fields like - Name,Age,Email and Address. The Address as you might have seen is various applications is divided into 3-4 fields like

  • Address Line 1 
  • Locality 
  • City 
  • State 
  • Country
  • Zip Code
The problem now is to use this address to get latitude & longitude details of the address using the Google Map API. Once we have latitude & longitude data we can plot this point on map and make the analytics more interactive.


  • Parse the form to get the values out from the form field - Lets store them in variables as: addressLine,locality,city,state,country, zip
  • Create a location string by adding all the above variables separated by  "+" sign as:
       loc = addressLine+ '+' + locality + '+' + city + '+' + country + '+' + zip
       (above expression assumes that we have converted all the variable to string)
  • Declare a variable to point to geo location Google API and a dictionary to pass the encode location arguments
        GEOLOC_URL = ''
        GEO_ARGS = {}
        GEO_ARGS.update({'address': loc })
        Geolocation API needed 'sensor': "false"/ "true" attribute in earlier versions
        indicating the use of sensors to collect data
  • Appended the GEO_ARGS to GEOLOC_URL to create new URL
        url = GEOLOC_URL + '?' + urllib.urlencode(GEO_ARGS)
  • Once the url is encoded we now need to send the request for which we will import urllib and get the result back which is a dictionary with all the details
        result = json.load(urllib.urlopen(url))
        ( import urllib & json before calling the above line of code )
  • If everything went well one can check the status using result['status'] which will be OK
  • Now our Lat / Long Values be 
  • latitude =  result['results'][0]['geometry']['location']['lat']
  • longitude =  result['results'][0]['geometry']['location']['lng']
  • If the API is able to find the location accurately it returns only one results element and if the results are ambiguos it returns a list of probable elements so checking this might be important while parsing the JSON
  • The code below shows summarizes what we discussed above
Now we can store these values in our database to be retrieved later to be plotted on Google Maps

Sunday, November 2, 2014

Use C# .Net to Load & Analyze Excel Sheet Data

Problem : This is something that repeatedly keeps on coming and I am putting it for my reference here. This snippet is of good use for anyone wanting to do data analysis on Excel Data Sheet by LINQ or by SQL like queries.

Solution : Following things to be noted in the code below:

  1. On windows you need to provide path with double backward slash (\\)
  2. The file that you are trying to open for analysis - As the connection string changes for the xls and xlsx file
  3. The name of the worksheet should end with $ sign
  4. Now once you have data table loaded in the memory one can start using Rows and Columns set to analyze data via C# Code

Best  case will be to analyze Stock Market data  through C# code as most of the Open Data is available in csv / excel format , but I will take that up some other time.