<commit_before># encoding: utf-8

module CartoDB
  module Importer2
    class Georeferencer
      LATITUDE_POSSIBLE_NAMES   = %w{ latitude lat latitudedecimal
        latitud lati decimallatitude decimallat }
      LONGITUDE_POSSIBLE_NAMES  = %w{ longitude lon lng 
        longitudedecimal longitud long decimallongitude decimallong }

      def initialize(db, table_name)
        @db         = db
        @table_name = table_name
      end #initialize

      def run
        latitude_column_name  = latitude_column_name_in(table_name)
        longitude_column_name = longitude_column_name_in(table_name)
        return self unless latitude_column_name && longitude_column_name

        create_the_geom_in(table_name)
        populate_the_geom_from_latlon(
          table_name, latitude_column_name, longitude_column_name
        )

        self
      end #run

      def populate_the_geom_from_latlon(table_name, latitude_column_name, 
      longitude_column_name)
        db.run(%Q{
          UPDATE "#{table_name}" 
          SET the_geom = ST_GeomFromText(
              'POINT(' || trim("#{longitude_column_name}") || ' ' ||
                trim("#{latitude_column_name}") || ')', 4326
          )
          WHERE trim(CAST("#{longitude_column_name}" AS text)) ~ 
            '^(([-+]?(([0-9]|[1-9][0-9]|1[0-7][0-9])(\.[0-9]+)?))|[-+]?180)$'
          AND trim(CAST("#{latitude_column_name}" AS text))  ~
            '^(([-+]?(([0-9]|[1-8][0-9])(\.[0-9]+)?))|[-+]?90)$'
        })
      end #populate_the_geom_from_latlon

      def create_the_geom_in(table_name)
        return false if column_exists_in?(table_name, 'the_geom')

        db.run(%Q{
          SELECT AddGeometryColumn(
            '#{table_name}','the_geom',4326,'POINT',2
          );
        })
      end #create_the_geom_in

      def column_exists_in?(table_name, column_name)
        columns_in(table_name).include?(column_name.to_sym)
      end #column_exists_in?

      def columns_in(table_name)
        db.schema(table_name, reload: true).map(&:first)
      end #columns_in

      def latitude_column_name_in(table_name)
        names = LATITUDE_POSSIBLE_NAMES.map { |name| "'#{name}'" }.join(',')
        find_column_in(table_name, names)
      end #latitude_column_name_in

      def longitude_column_name_in(table_name)
        names = LONGITUDE_POSSIBLE_NAMES.map { |name| "'#{name}'" }.join(',')
        find_column_in(table_name, names)
      end #longitude_column_name_in

      def find_column_in(table_name, possible_names)
        sample = db[%Q{
          SELECT column_name 
          FROM information_schema.columns
          WHERE table_name ='#{table_name}'
          AND lower(column_name) in (#{possible_names})
          LIMIT 1
        }].first

        !!sample && sample.fetch(:column_name, false)
      end #find_column_in

      private

      attr_reader :db, :table_name
    end # Georeferencer<commit_msg>[importer] Refactor Importer2::Georeferencer<commit_after># encoding: utf-8
require_relative './column'

module CartoDB
  module Importer2
    class Georeferencer
      LATITUDE_POSSIBLE_NAMES   = %w{ latitude lat latitudedecimal
        latitud lati decimallatitude decimallat }
      LONGITUDE_POSSIBLE_NAMES  = %w{ longitude lon lng 
        longitudedecimal longitud long decimallongitude decimallong }
      GEOMETRY_POSSIBLE_NAMES   = %w{ geometry the_geom }

      def initialize(db, table_name)
        @db         = db
        @table_name = table_name
      end #initialize

      def run
        create_the_geom_from_geometry_column || create_the_geom_from_latlon 
        self
      end #run

      def create_the_geom_from_latlon
        latitude_column_name  = latitude_column_name_in(table_name)
        longitude_column_name = longitude_column_name_in(table_name)

        return false unless latitude_column_name && longitude_column_name

        create_the_geom_in(table_name)
        populate_the_geom_from_latlon(
          table_name, latitude_column_name, longitude_column_name
        )
      end #create_the_geom_from_latlon

      def create_the_geom_from_geometry_column
        column = Column.new(db, table_name, geometry_column_in(table_name))
        column.geometrify
        column.rename_to(:the_geom) unless column_exists_in?(table_name, :the_geom)
        self
      rescue
        false
      end #create_the_geom_from_geometry_column

      def populate_the_geom_from_latlon(table_name, latitude_column_name, 
      longitude_column_name)
        db.run(%Q{
          UPDATE "#{table_name}" 
          SET the_geom = ST_GeomFromText(
              'POINT(' || trim("#{longitude_column_name}") || ' ' ||
                trim("#{latitude_column_name}") || ')', 4326
          )
          WHERE trim(CAST("#{longitude_column_name}" AS text)) ~ 
            '^(([-+]?(([0-9]|[1-9][0-9]|1[0-7][0-9])(\.[0-9]+)?))|[-+]?180)$'
          AND trim(CAST("#{latitude_column_name}" AS text))  ~
            '^(([-+]?(([0-9]|[1-8][0-9])(\.[0-9]+)?))|[-+]?90)$'
        })
      end #populate_the_geom_from_latlon

      def create_the_geom_in(table_name)
        return false if column_exists_in?(table_name, 'the_geom')

        db.run(%Q{
          SELECT AddGeometryColumn(
            '#{table_name}','the_geom',4326,'POINT',2
          );
        })
      end #create_the_geom_in

      def column_exists_in?(table_name, column_name)
        columns_in(table_name).include?(column_name.to_sym)
      end #column_exists_in?

      def columns_in(table_name)
        db.schema(table_name, reload: true).map(&:first)
      end #columns_in

      def latitude_column_name_in(table_name)
        names = LATITUDE_POSSIBLE_NAMES.map { |name| "'#{name}'" }.join(',')
        find_column_in(table_name, names)
      end #latitude_column_name_in

      def longitude_column_name_in(table_name)
        names = LONGITUDE_POSSIBLE_NAMES.map { |name| "'#{name}'" }.join(',')
        find_column_in(table_name, names)
      end #longitude_column_name_in

      def geometry_column_in(table_name)
        names = GEOMETRY_POSSIBLE_NAMES.map { |name| "'#{name}'" }.join(',')
        find_column_in(table_name, names)
      end #geometry_column_in

      def find_column_in(table_name, possible_names)
        sample = db[%Q{
          SELECT column_name 
          FROM information_schema.columns
          WHERE table_name ='#{table_name}'
          AND lower(column_name) in (#{possible_names})
          LIMIT 1
        }].first

        !!sample && sample.fetch(:column_name, false)
      end #find_column_in

      private

      attr_reader :db, :table_name
    end # Georeferencer
{'fraction_non_alphanumeric': 0.1126398548533414, 'fraction_numerical': 0.011641971575446023, 'mean_word_length': 2.730964467005076, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5778845', 'n_tokens_mistral': 2328, 'n_tokens_neox': 2234, 'n_words': 360}