application_controller.rb

class ApplicationController < ActionController::Base
  protect_from_forgery
  layout 'application'

  include AuthenticatedSystem
  
  before_filter :get_location_from_ip

  def show

  end
  
  protected
    def get_location_from_ip
      unless session[:location].present?
        begin
          session[:location] = Hostip.new.geo_location request.env[:REMOTE_ADDR]
        rescue RuntimeError => e
          session[:location] = {'lat' => '', 'long' => ''}
          logger.warn "Cannot get geo location from ip #{request.env[:REMOTE_ADDR]}. Error => #{e}"
        end
      end
    end
end

fusion_tables_client_wrapper.rb

class FusionTablesClientWrapper

  @@_reserved_words = [:order]
  
  class << self
    
    def table_name(table_name)
      @table_name = table_name
    end
    
    def field(field_name)
      return unless field_name
      @fields = [:ROWID] if @fields.nil?
      @fields.push( @@_reserved_words.include?(field_name) ? "'#{field_name}'" : field_name ) if @fields
    end

    def all(filters = '')
      return table.select(@fields.join(','), filters) if table && @fields
      []
    end

    def where(filters = '')
      all("WHERE #{filters}")
    end
    
    def create(params = nil)
      return if params.nil?
      
      fields = params.keys.map{ |key| "'#{key}'" }.join(', ')
      values = params.values.map{ |value| "'#{value}'" }.join(', ')
      sql    = "INSERT INTO #{table.id} (#{fields}) VALUES (#{values})"

      @client.sql_post(sql)
    end
    
    def update_attributes(params = nil, rowid = nil)
      return if params.nil? || rowid.nil?
      
      values = params.to_a.map { |pair| "'#{pair[0]}' = '#{pair[1]}'"}.join(', ')
      
      sql = "UPDATE #{table.id} SET #{values} WHERE ROWID='#{rowid}'"

      @client.sql_post(sql)
    end

    # Hack!! Since fusion tables client doesn't support aggregate querys, this 
    # method simulates the MAXIMUM aggregate function.
    def max(field)
      # All keys in results array are downcase
      field = field.to_s.downcase.to_sym

      # Discards all nil elements
      records = all.select { |x| x.present? && x[field].present? }
      # Orders by highest field
      records = records.sort { |p,n| n[field].to_i <=> p[field].to_i }
      records.first[field].to_i
    end

    def next(field)
      max(field) + 1
    end

    protected
      def client
        return @client if @client

        # Inits connection with Fusion Tables
        @client = GData::Client::FusionTables.new
        credentials = YAML::load_file("#{Rails.root}/config/credentials.yml")
        @client.clientlogin(credentials["ft_username"], credentials["ft_password"])
        @client
      end

      def client=(client)
        @client = client
      end

      def table
        @table ||= client.show_tables.select{|t| t.name == @table_name}.first if @table_name
      end

  end

end

identification.rb

class Identification < FusionTablesClientWrapper
  
  table_name 'Bioblitz2010Identifications'
  
  field :observationRowId
  field :scientificName
  field :identificationTime
  field :author
  field :application
  field :colId
  field :colLsid
  field :kingdom
  field :phylum
  field :class
  field :order
  field :family
  field :genus
  field :lat
  field :lon
  
end

identification_controller.rb

class Api::IdentificationsController < ApplicationController
  
  #Provide an identification
  def update

    identification_attributes = {
      :observationRowId   => params[:rowid],
      :scientificName     => params[:scientificName],
      :identificationTime => Time.now.strftime("%m-%d-%Y %H:%M:%S"),
      :author             => params[:username],
      :application        => 'Taxonomizer',
      :lat                => session[:location]['lat'],
      :lon                => session[:location]['long']
    }

    if (params[:id])
      taxonomy = resolve_taxonomy(params[:id])
      identification_attributes[:scientificName] = taxonomy[0]['s']
      identification_attributes[:colId]          = taxonomy[0]['id_col']
      identification_attributes[:colLsid]        = taxonomy[0]['lsid']
      identification_attributes[:kingdom]        = taxonomy[0]['k']
      identification_attributes[:phylum]         = taxonomy[0]['p']
      identification_attributes[:class]          = taxonomy[0]['c']
      identification_attributes[:order]          = taxonomy[0]['o']
      identification_attributes[:family]         = taxonomy[0]['f']
      identification_attributes[:genus]          = taxonomy[0]['g']
    end
    
    Identification.create(identification_attributes)
    
    occurrences = Occurrence.where("ROWID=#{params[:rowid]}")
    unless occurrences.blank?
      numIdentifications= occurrences.first[:numidentifications].to_i + 1
      Occurrence.update_attributes(:numidentifications => numidentifications)
    end
    
    respond_to do |format|
      format.json { render :json => 'ok'.to_json }
    end
  end

end

def resolve_taxonomy(id)
  conn = PGconn.connect( :dbname => 'col',:user=>'postgres' )
  result =conn.exec("select lsid,k,c,o,p,f,id_col,g,s from taxonomy where id = #{id}")
end

occurrence.rb

class Occurrence < FusionTablesClientWrapper

  table_name 'occurrences_clone'

  field :row_ID
  field :event_ID
  field :scientificName
  field :vernacular_name
  field :taxonConceptID
  field :occurrenceRemarks
  field :individualCount
  field :sex
  field :lifeStage
  field :reproductiveCondition
  field :behaviour
  field :associatedMedia
  field :associatedTaxa
  field :habitat
  field :trophicLevel
  field :verbatimLocality
  field :verbatimDepth
  field :latitude
  field :longitude
  field :coordinatePrecision
  field :observedBy
  field :recordedBy
  field :observedBy
  field :identifiedBy
  field :identificationReferences
  field :identificationRemarks
  field :identificationRequested
  field :dateTime
  field :kingdom
  field :phylum
  field :class
  field :order
  field :family
  field :genus
  field :col_ID
  field :zoomit_ID
  field :col_lsid
  field :numIdentifications

end