Class: GeographicItem

Inherits:

ApplicationRecord

• Object
• ActiveRecord::Base
• ApplicationRecord
• GeographicItem

Includes:

Housekeeping::Timestamps, Housekeeping::Users, Shared::IsData, Shared::SharedAcrossProjects

Defined in:

app/models/geographic_item.rb

Overview

A GeographicItem is one and only one of [point, line_string, polygon, multi_point, multi_line_string, multi_polygon, geometry_collection] which describes a position, path, or area on the globe, generally associated with a geographic_area (through a geographic_area_geographic_item entry), and sometimes only with a georeference.

Key methods in this giant library

`#geo_object` - return a RGEO object representation

Direct Known Subclasses

GeometryCollection, LineString, MultiLineString, MultiPoint, MultiPolygon, Point, Polygon

Defined Under Namespace

Classes: GeometryCollection, LineString, MultiLineString, MultiPoint, MultiPolygon, Point, Polygon

Constant Summary

DATA_TYPES =

[
  :point,
  :line_string,
  :polygon,
  :multi_point,
  :multi_line_string,
  :multi_polygon,
  :geometry_collection
].freeze

GEOMETRY_SQL =

Arel::Nodes::Case.new(arel_table[:type])
.when('GeographicItem::MultiPolygon').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_polygon].as('geometry')]))
.when('GeographicItem::Point').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:point].as('geometry')]))
.when('GeographicItem::LineString').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:line_string].as('geometry')]))
.when('GeographicItem::Polygon').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:polygon].as('geometry')]))
.when('GeographicItem::MultiLineString').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_line_string].as('geometry')]))
.when('GeographicItem::MultiPoint').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_point].as('geometry')]))
.when('GeographicItem::GeometryCollection').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:geometry_collection].as('geometry')]))
.freeze

GEOGRAPHY_SQL =

"CASE geographic_items.type
WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon
WHEN 'GeographicItem::Point' THEN point
WHEN 'GeographicItem::LineString' THEN line_string
WHEN 'GeographicItem::Polygon' THEN polygon
WHEN 'GeographicItem::MultiLineString' THEN multi_line_string
WHEN 'GeographicItem::MultiPoint' THEN multi_point
WHEN 'GeographicItem::GeometryCollection' THEN geometry_collection
END".freeze

ANTI_MERIDIAN =

ANTI_MERIDIAN = '0X0102000020E61000000200000000000000008066400000000000405640000000000080664000000000004056C0'

'LINESTRING (180 89.0, 180 -89)'.freeze

Instance Attribute Summary

• #geometry ⇒ Hash^?

  An internal variable for use in super calls, holds a Hash in GeoJSON format (temporarily).

• #line_string ⇒ RGeo::Geographic::ProjectedLineStringImpl
• #multi_line_string ⇒ RGeo::Geographic::ProjectedMultiLineStringImpl
• #multi_point ⇒ RGeo::Geographic::ProjectedMultiPointImpl
• #multi_polygon ⇒ RGeo::Geographic::ProjectedMultiPolygonImpl
• #no_cached ⇒ Boolean

  When true cached values are not built.

• #point ⇒ RGeo::Geographic::ProjectedPointImpl
• #polygon ⇒ RGeo::Geographic::ProjectedPolygonImpl
• #shape ⇒ Boolean, RGeo object

  TODO: WHY! boolean not nil, or object Used to build geographic items from a shape [ of what class ] !?.

• #type ⇒ String

  Rails STI, determines the geography column as well.

Attributes included from Housekeeping::Users

#by

Class Method Summary

• .are_contained_in_item(column_name, *geographic_items) ⇒ Scope

  See are_contained_in_item_by_id.

• .are_contained_in_item_by_id(column_name, *geographic_item_ids) ⇒ Scope

  rubocop:disable Metrics/MethodLength If this scope is given an Array of GeographicItems as a second parameter, it will return the 'OR' of each of the objects against the table.

• .are_contained_in_wkt(column_name, geometry) ⇒ Scope

  a single WKT geometry is compared against column or columns (except geometry_collection) to find geographic_items which are contained in the WKT.

• .contained_by(*geographic_item_ids) ⇒ Scope

  (works via ST_ContainsProperly).

• .contained_by_where_sql(*geographic_item_ids) ⇒ String

  Note: Can not use GEOMETRY_SQL because geometry_collection is not supported in ST_ContainsProperly Note: !! If the target GeographicItem#id crosses the anti-meridian then you may/will get unexpected results.

• .contained_by_with_antimeridian_check(*ids) ⇒ Array

  TODO: test this.

• .contained_by_wkt_shifted_sql(wkt) ⇒ String

  Note: this routine is called when it is already known that the A argument crosses anti-meridian.

• .contained_by_wkt_sql(wkt) ⇒ String

  TODO: Remove the hard coded 4326 reference.

• .containing(*geographic_item_ids) ⇒ Scope

  The geographic items containing these collective geographic_item ids, not including self.

• .containing_point(rgeo_point) ⇒ Scope

  TODO: should be containing_wkt ?.

• .containing_sql(target_column_name = nil, geographic_item_id = nil, source_column_name = nil) ⇒ String

  A SQL fragment for ST_Contains() function, returns all geographic items which are contained in the item supplied.

• .containing_where_for_point_sql(rgeo_point) ⇒ String

  TODO: Remove the hard coded 4326 reference TODO: should this be wkt_point instead of rgeo_point?.

• .containing_where_sql(*geographic_item_ids) ⇒ String
• .containing_where_sql_geog(*geographic_item_ids) ⇒ String
• .crosses_anti_meridian?(wkt) ⇒ Boolean

  Whether or not the wtk intersects with the anti-meridian !! StrongParams security considerations.

• .crosses_anti_meridian_by_id?(*ids) ⇒ Boolean

  Whether or not any GeographicItem passed intersects the anti-meridian !! StrongParams security considerations This is our first line of defense against queries that define multiple shapes, one or more of which crosses the anti-meridian.

• .default_by_geographic_area_ids(geographic_area_ids = []) ⇒ GeographicItem::ActiveRecord_Relation
• .disjoint_from(column_name, *geographic_items) ⇒ Scope

  A SQL fragment for ST_DISJOINT, specifies all geographic_items that have data in column_name that are disjoint from the passed geographic_items.

• .distance_between(geographic_item_id1, geographic_item_id2) ⇒ Float
• .eval_for_type(type_name) ⇒ String

  If type.

• .gather_geographic_area_or_shape_data(geographic_area_ids, shape_in, search_object_class, project_id) ⇒ Scope

  TODO: * rename to reflect either/or and what is being returned.

• .gather_map_data(feature, search_object_class, project_id) ⇒ Scope

  TODO: likely not ussed any more.

• .geometry_for(geographic_item_id) ⇒ RGeo::Geographic object

  example, not used.

• .geometry_for_collection_sql(*geographic_item_ids) ⇒ String

  example, not used.

• .geometry_for_sql(geographic_item_id) ⇒ String

  example, not used.

• .geometry_sql(geographic_item_id = nil, source_column_name = nil) ⇒ String

  source_column_name, i.e.

• .geometry_sql2(*geographic_item_ids) ⇒ String

  geometry as 'geometry' for a single id.

• .intersecting(column_name, *geographic_items) ⇒ Scope
• .intersecting_radius_of_wkt_sql(wkt, distance) ⇒ String

  TODO: 3D is overkill here !! This is intersecting.

• .is_contained_by(column_name, *geographic_items) ⇒ Scope

  rubocop:disable Metrics/MethodLength containing the items the shape of which is contained in the geographic_item supplied.

• .is_contained_by_sql(column_name, geographic_item) ⇒ String

  rubocop:disable Metrics/MethodLength.

• .lat_long_sql(choice) ⇒ String

  A fragment returning either latitude or longitude columns.

• .not_including(geographic_items) ⇒ Scope
• .ordered_by_longest_distance_from(column_name, geographic_item) ⇒ Scope
• .ordered_by_shortest_distance_from(column_name, geographic_item) ⇒ Scope
• .point_inferred_geographic_name_hierarchy(point) ⇒ Hash

  As per #inferred_geographic_name_hierarchy but for Rgeo point.

• .reverse_containing_sql(target_column_name = nil, geographic_item_id = nil, source_column_name = nil) ⇒ String

  A SQL fragment for ST_Contains(), returns all geographic_items which contain the supplied geographic_item.

• .select_distance_with_geo_object(column_name, geographic_item) ⇒ String
• .select_geography_sql(geographic_item_id) ⇒ String

  A SQL select statement that returns the geography for the geographic_item with the specified id.

• .select_geometry_sql(geographic_item_id) ⇒ String

  A SQL select statement that returns the geometry for the geographic_item with the specified id.

• .single_geometry_sql(*geographic_item_ids) ⇒ String

  Returns one or more geographic items combined as a single geometry in column 'single'.

• .st_collect(geographic_item_scope) ⇒ Object
• .st_collect_sql(*geographic_item_ids) ⇒ String

  provided via ST_Collect).

• .st_multi(*geographic_item_ids) ⇒ Scope

  example, not used.

• .st_union(geographic_item_scope) ⇒ Object
• .where_distance_greater_than_zero(column_name, geographic_item) ⇒ Scope
• .with_area ⇒ Scope

  Adds an area_in_meters field, with meters.

• .with_collecting_event_through_georeferences ⇒ Scope

  This uses an Arel table approach, this is ultimately more decomposable if we need.

• .with_is_valid_geometry_column(geographic_item) ⇒ Scope

  Includes an 'is_valid' attribute (True/False) for the passed geographic_item.

• .with_latitude ⇒ Scope

  Include a 'latitude' column.

• .with_longitude ⇒ Scope

  Include a 'longitude' column.

• .within_radius_of_item(geographic_item_id, distance) ⇒ ActiveRecord::Relation

  !! should be distance, not radius?!.

• .within_radius_of_item_sql(geographic_item_id, distance) ⇒ String
• .within_radius_of_wkt_sql(wkt, distance) ⇒ String

  !! This is fully covering.

Instance Method Summary

• #area ⇒ Object

  return float, in meters, calculated, not from cached.

• #center_coords ⇒ Array

  The lat, long, as STRINGs for the centroid of this geographic item.

• #centroid ⇒ RGeo::Geographic::ProjectedPointImpl

  Representing the centroid of this geographic item.

• #containing_geographic_areas ⇒ Scope

  The Geographic Areas that contain (gis) this geographic item.

• #contains?(target_geo_object) ⇒ Boolean
• #far(target_geo_object, distance) ⇒ Boolean
• #geo_object ⇒ RGeo instance^?

  The Rgeo shape (See rubydoc.info/github/dazuma/rgeo/RGeo/Feature).

• #geo_object_type ⇒ Symbol

  according to the list of DATA_TYPES, or nil.

• #geo_type ⇒ Symbol protected

  Returns the attribute (column name) containing data nearly all methods should use #geo_object_type, not geo_type.

• #inferred_geographic_name_hierarchy ⇒ Hash

  finding the smallest area containing this GeographicItem, in the most accurate gazetteer and using it to return country/state/county.

• #intersects?(target_geo_object) ⇒ Boolean
• #line_string_to_a(line_string) ⇒ Array protected

  Of points in the line.

• #line_string_to_hash(line_string) ⇒ Hash protected

  Of points in the line.

• #multi_line_string_to_a(multi_line_string) ⇒ Array protected

  Of line_strings as arrays of points.

• #multi_line_string_to_hash(_multi_line_string) ⇒ Hash protected

  Of line_strings as hashes of points.

• #multi_point_to_a(multi_point) ⇒ Array protected

  Of points.

• #multi_point_to_hash(_multi_point) ⇒ Hash protected

  Of points.

• #multi_polygon_to_a(multi_polygon) ⇒ Array protected

  Of arrays of points in the polygons (exterior_ring ONLY).

• #multi_polygon_to_hash(_multi_polygon) ⇒ Hash protected

  Of hashes of points in the polygons (exterior_ring ONLY).

• #near(target_geo_object, distance) ⇒ Boolean
• #point_to_a(point) ⇒ Array protected

  Of a point Longitude |, Latitude -.

• #point_to_hash(point) ⇒ Hash protected

  Of a point.

• #polygon_to_a(polygon) ⇒ Array protected

  Of points in the polygon (exterior_ring ONLY).

• #polygon_to_hash(polygon) ⇒ Hash protected

  Of points in the polygon (exterior_ring ONLY).

• #quick_geographic_name_hierarchy ⇒ Hash

  This is a quick approach that works only when the geographic_item is linked explicitly to a GeographicArea.

• #rgeo_to_geo_json ⇒ GeoJSON hash

  Via Rgeo apparently necessary for GeometryCollection.

• #set_cached ⇒ Object private
• #set_type_if_geography_present ⇒ Boolean, String protected

  False if already set, or type to which it was set.

• #some_data_is_provided ⇒ Boolean protected

  Iff there is one and only one shape column set.

• #st_centroid ⇒ String

  A WKT POINT representing the centroid of the geographic item.

• #st_distance(geographic_item_id) ⇒ Double (also: #distance_to)

  Distance in meters (slower, more accurate).

• #st_distance_spheroid(geographic_item_id) ⇒ Double

  Distance in meters (faster, less accurate).

• #st_distance_to_geographic_item(geographic_item) ⇒ Double

  Like st_distance but works with changed and non persisted objects.

• #st_npoints ⇒ Integer

  The number of points in the geometry.

• #start_point ⇒ Array of latitude, longitude

  The lat, lon of the first point in the GeoItem, see subclass for #st_start_point.

• #to_geo_json ⇒ Hash

  In GeoJSON format Computed via “raw” PostGIS (much faster).

• #to_geo_json_feature ⇒ Hash

  The shape as a GeoJSON Feature with some item metadata.

• #to_geo_json_string ⇒ Object

  We don't need to serialize to/from JSON.

• #to_wkt ⇒ String
• #valid_geometry? ⇒ Boolean

  Whether stored shape is ST_IsValid.

• #within?(target_geo_object) ⇒ Boolean

Methods included from Shared::IsData

#errors_excepting, #full_error_messages_excepting, #identical, #is_community?, #is_destroyable?, #is_editable?, #is_in_use?, #is_in_users_projects?, #metamorphosize, #similar

Methods included from Housekeeping::Timestamps

#data_breakdown_for_chartkick_recent

Methods included from Housekeeping::Users

#set_created_by_id, #set_updated_by_id

Methods inherited from ApplicationRecord

transaction_with_retry

Instance Attribute Details

#geometry ⇒ Hash^?

An internal variable for use in super calls, holds a Hash in GeoJSON format (temporarily)

Returns:

• (Hash, nil)

# File 'app/models/geographic_item.rb', line 42

def geometry
  @geometry
end

#line_string ⇒ RGeo::Geographic::ProjectedLineStringImpl

Returns:

• (RGeo::Geographic::ProjectedLineStringImpl)

# File 'app/models/geographic_item.rb', line 34

class GeographicItem < ApplicationRecord
  include Housekeeping::Users
  include Housekeeping::Timestamps
  include Shared::IsData
  include Shared::SharedAcrossProjects

  # @return [Hash, nil]
  # An internal variable for use in super calls, holds a Hash in GeoJSON format (temporarily)
  attr_accessor :geometry

  # @return [Boolean, RGeo object]
  # @params value [Hash in GeoJSON format] ?!
  # TODO: WHY! boolean not nil, or object
  # Used to build geographic items from a shape [ of what class ] !?
  attr_accessor :shape

  # @return [Boolean]
  #   When true cached values are not built
  attr_accessor :no_cached

  DATA_TYPES = [
    :point,
    :line_string,
    :polygon,
    :multi_point,
    :multi_line_string,
    :multi_polygon,
    :geometry_collection
  ].freeze

  GEOMETRY_SQL = Arel::Nodes::Case.new(arel_table[:type])
    .when('GeographicItem::MultiPolygon').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_polygon].as('geometry')]))
    .when('GeographicItem::Point').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:point].as('geometry')]))
    .when('GeographicItem::LineString').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:line_string].as('geometry')]))
    .when('GeographicItem::Polygon').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:polygon].as('geometry')]))
    .when('GeographicItem::MultiLineString').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_line_string].as('geometry')]))
    .when('GeographicItem::MultiPoint').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_point].as('geometry')]))
    .when('GeographicItem::GeometryCollection').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:geometry_collection].as('geometry')]))
    .freeze

  GEOGRAPHY_SQL = "CASE geographic_items.type
    WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon
    WHEN 'GeographicItem::Point' THEN point
    WHEN 'GeographicItem::LineString' THEN line_string
    WHEN 'GeographicItem::Polygon' THEN polygon
    WHEN 'GeographicItem::MultiLineString' THEN multi_line_string
    WHEN 'GeographicItem::MultiPoint' THEN multi_point
    WHEN 'GeographicItem::GeometryCollection' THEN geometry_collection
    END".freeze

  # ANTI_MERIDIAN = '0X0102000020E61000000200000000000000008066400000000000405640000000000080664000000000004056C0'
  ANTI_MERIDIAN = 'LINESTRING (180 89.0, 180 -89)'.freeze

  has_many :geographic_areas_geographic_items, dependent: :destroy, inverse_of: :geographic_item
  has_many :geographic_areas, through: :geographic_areas_geographic_items
  has_many :asserted_distributions, through: :geographic_areas
  has_many :geographic_area_types, through: :geographic_areas
  has_many :parent_geographic_areas, through: :geographic_areas, source: :parent
  has_many :gadm_geographic_areas, class_name: 'GeographicArea', foreign_key: :gadm_geo_item_id
  has_many :ne_geographic_areas, class_name: 'GeographicArea', foreign_key: :ne_geo_item_id
  has_many :tdwg_geographic_areas, class_name: 'GeographicArea', foreign_key: :tdwg_geo_item_id
  has_many :georeferences, inverse_of: :geographic_item
  has_many :georeferences_through_error_geographic_item,
    class_name: 'Georeference', foreign_key: :error_geographic_item_id, inverse_of: :error_geographic_item
  has_many :collecting_events_through_georeferences, through: :georeferences, source: :collecting_event
  has_many :collecting_events_through_georeference_error_geographic_item,
    through: :georeferences_through_error_geographic_item, source: :collecting_event

  # TODO: THIS IS NOT GOOD
  before_validation :set_type_if_geography_present

  validate :some_data_is_provided
  validates :type, presence: true

  scope :include_collecting_event, -> { includes(:collecting_events_through_georeferences) }
  scope :geo_with_collecting_event, -> { joins(:collecting_events_through_georeferences) }
  scope :err_with_collecting_event, -> { joins(:georeferences_through_error_geographic_item) }

  after_save :set_cached, unless: Proc.new {|n| n.no_cached || errors.any? }

  class << self

    def st_union(geographic_item_scope)
      GeographicItem.select("ST_Union(#{GeographicItem::GEOMETRY_SQL.to_sql}) as collection")
        .where(id: geographic_item_scope.pluck(:id))
    end

    def st_collect(geographic_item_scope)
      GeographicItem.select("ST_Collect(#{GeographicItem::GEOMETRY_SQL.to_sql}) as collection")
        .where(id: geographic_item_scope.pluck(:id))
    end

    # @return [GeographicItem::ActiveRecord_Relation]
    # @params [Array] array of geographic area ids
    def default_by_geographic_area_ids(geographic_area_ids = [])
      GeographicItem.
        joins(:geographic_areas_geographic_items).
        merge(::GeographicAreasGeographicItem.default_geographic_item_data).
        where(geographic_areas_geographic_items: {geographic_area_id: geographic_area_ids})
    end

    # @param [String] wkt
    # @return [Boolean]
    #   whether or not the wtk intersects with the anti-meridian
    #   !! StrongParams security considerations
    def crosses_anti_meridian?(wkt)
      GeographicItem.find_by_sql(
        ['SELECT ST_Intersects(ST_GeogFromText(?), ST_GeogFromText(?)) as r;', wkt, ANTI_MERIDIAN]
      ).first.r
    end

    # @param [Integer] ids
    # @return [Boolean]
    #   whether or not any GeographicItem passed intersects the anti-meridian
    #   !! StrongParams security considerations
    #   This is our first line of defense against queries that define multiple shapes, one or
    #   more of which crosses the anti-meridian.  In this case the current TW strategy within the
    #   UI is to abandon the search, and prompt the user to refactor the query.
    def crosses_anti_meridian_by_id?(*ids)
      q1 = ["SELECT ST_Intersects((SELECT single_geometry FROM (#{GeographicItem.single_geometry_sql(*ids)}) as " \
            'left_intersect), ST_GeogFromText(?)) as r;', ANTI_MERIDIAN]
      _q2 = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT ST_Intersects((SELECT single_geometry FROM (?) as ' \
                                                          'left_intersect), ST_GeogFromText(?)) as r;', GeographicItem.single_geometry_sql(*ids), ANTI_MERIDIAN])
      GeographicItem.find_by_sql(q1).first.r
    end

    # TODO: * rename to reflect either/or and what is being returned
    # @param [Integer] geographic_area_ids
    # @param [String] shape_in in JSON (POINT, POLYGON, MULTIPOLYGON), usually from GoogleMaps
    # @param [String] search_object_class
    # @param [Integer] project_id for search_object_class
    # @return [Scope] of the requested search_object_type
    def gather_geographic_area_or_shape_data(geographic_area_ids, shape_in, search_object_class, project_id)
      if shape_in.blank?
        # get the shape from the geographic area, if possible
        finding = search_object_class.constantize
        target_geographic_item_ids = []
        if geographic_area_ids.blank?
          found = finding.none
        else
          # now use method from collection_object_filter_query
          geographic_area_ids.each do |gaid|
            target_geographic_item_ids.push(GeographicArea.joins(:geographic_items)
              .find(gaid)
              .default_geographic_item.id)
          end

          # TODO: There probably is a better way to do this, but for now...
          f1 = project_id.present? ? finding.with_project_id(project_id) : finding
          case search_object_class
          when /Collection/, /Collecting/
            found = f1.joins(:geographic_items)
              .where(GeographicItem.contained_by_where_sql(target_geographic_item_ids))
          when /Asserted/
            # TODO: Figure out how to see through this group of geographic_items to the ones which contain
            # geographic_items which are associated with geographic_areas (as #default_geographic_items)
            # which are associated with asserted_distributions
            found = f1.joins(:geographic_area).joins(:geographic_items)
              .where(GeographicItem.contained_by_where_sql(target_geographic_item_ids))
          else
          end
        end
      else
        found = gather_map_data(shape_in, search_object_class, project_id)
      end
      found
    end

    # TODO: likely not ussed any more
    #
    # @param [String] feature in JSON, looks like '{"type":"Feature","geometry":{"type":"Polygon",
    # "coordinates":[[[-40.078125,10.614539227964332],[-49.21875,-17.185577279306226],
    # [-23.203125,-15.837353550148276],[-40.078125,10.614539227964332]]]},"properties":{}}'
    # @param [String] search_object_class
    # @param [Integer, Nil] project_id for search_object_class
    # @return [Scope] of the requested search_object_type
    #   This function takes a feature, i.e. a string that is the result
    #   of drawing on a Google map, and submited as a form variable,
    #   and translates that to a scope for a provided search_object_class.
    #   e.g. Return all CollectionObjects in this drawn area
    #        Return all CollectionObjects in the radius around this point
    def gather_map_data(feature, search_object_class, project_id)
      finding = search_object_class.constantize
      g_feature = RGeo::GeoJSON.decode(feature, json_parser: :json)
      if g_feature.nil?
        finding.none
      else
        geometry = g_feature.geometry # isolate the WKT
        shape_type = geometry.geometry_type.to_s.downcase
        geometry = geometry.as_text
        radius = g_feature['radius']

        query = project_id.present? ?
          finding.with_project_id(project_id).joins(:geographic_items) :
          finding.joins(:geographic_items)

        case shape_type
        when 'point'
          query.where(GeographicItem.within_radius_of_wkt_sql(geometry, radius)) # was interesecting version.
        when 'polygon', 'multipolygon'
          query.where(GeographicItem.contained_by_wkt_sql(geometry))
        else
          query
        end
      end
    end

    #
    # SQL fragments
    #

    # @param [Integer, String]
    # @return [String]
    #   a SQL select statement that returns the *geometry* for the geographic_item with the specified id
    def select_geometry_sql(geographic_item_id)
      "SELECT #{GeographicItem::GEOMETRY_SQL.to_sql} from geographic_items where geographic_items.id = #{geographic_item_id}"
    end

    # @param [Integer, String]
    # @return [String]
    #   a SQL select statement that returns the geography for the geographic_item with the specified id
    def select_geography_sql(geographic_item_id)
      ActiveRecord::Base.send(:sanitize_sql_for_conditions, [
        "SELECT #{GeographicItem::GEOMETRY_SQL.to_sql} from geographic_items where geographic_items.id = ?",
        geographic_item_id])
    end

    # @param [Symbol] choice
    # @return [String]
    #   a fragment returning either latitude or longitude columns
    def lat_long_sql(choice)
      return nil unless [:latitude, :longitude].include?(choice)
      f = "'D.DDDDDD'" # TODO: probably a constant somewhere
      v = (choice == :latitude ? 1 : 2)
      "CASE type
      WHEN 'GeographicItem::GeometryCollection' THEN split_part(ST_AsLatLonText(ST_Centroid" \
      "(geometry_collection::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::LineString' THEN split_part(ST_AsLatLonText(ST_Centroid(line_string::geometry), " \
      "#{f}), ' ', #{v})
      WHEN 'GeographicItem::MultiPolygon' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_polygon::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::Point' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(point::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::Polygon' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(polygon::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::MultiLineString' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_line_string::geometry), #{f} ), ' ', #{v})
      WHEN 'GeographicItem::MultiPoint' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_point::geometry), #{f}), ' ', #{v})
    END as #{choice}"
    end

    # @param [Integer] geographic_item_id
    # @param [Integer] distance
    # @return [String]
    def within_radius_of_item_sql(geographic_item_id, distance)
      "ST_DWithin((#{GeographicItem::GEOGRAPHY_SQL}), (#{select_geography_sql(geographic_item_id)}), #{distance})"
    end

    # TODO: 3D is overkill here
    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [String]
    # !! This is intersecting
    def intersecting_radius_of_wkt_sql(wkt, distance)
      "ST_DWithin((#{GeographicItem::GEOGRAPHY_SQL}), ST_Transform( ST_GeomFromText('#{wkt}', " \
        "4326), 4326), #{distance})"
    end

    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [String]
    # !! This is fully covering
    def within_radius_of_wkt_sql(wkt, distance)
      "ST_Covers( ST_Buffer(ST_SetSRID( ST_GeomFromText('#{wkt}'), 4326)::geography, #{distance}), (#{GeographicItem::GEOGRAPHY_SQL}))"
    end

    # @param [String, Integer, String]
    # @return [String]
    #   a SQL fragment for ST_Contains() function, returns
    #   all geographic items which are contained in the item supplied
    def containing_sql(target_column_name = nil, geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil? || target_column_name.nil?
      "ST_Contains(#{target_column_name}::geometry, (#{geometry_sql(geographic_item_id, source_column_name)}))"
    end

    # @param [String, Integer, String]
    # @return [String]
    #   a SQL fragment for ST_Contains(), returns
    #   all geographic_items which contain the supplied geographic_item
    def reverse_containing_sql(target_column_name = nil, geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil? || target_column_name.nil?
      "ST_Contains((#{geometry_sql(geographic_item_id, source_column_name)}), #{target_column_name}::geometry)"
    end

    # @param [Integer, String]
    # @return [String]
    #   a SQL fragment that represents the geometry of the geographic item specified (which has data in the
    # source_column_name, i.e. geo_object_type)
    def geometry_sql(geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil?
      "select geom_alias_tbl.#{source_column_name}::geometry from geographic_items geom_alias_tbl " \
        "where geom_alias_tbl.id = #{geographic_item_id}"
    end

    # rubocop:disable Metrics/MethodLength
    # @param [String] column_name
    # @param [GeographicItem] geographic_item
    # @return [String] of SQL
    def is_contained_by_sql(column_name, geographic_item)
      geo_id = geographic_item.id
      geo_type = geographic_item.geo_object_type
      template = '(ST_Contains((select geographic_items.%s::geometry from geographic_items where ' \
        'geographic_items.id = %d), %s::geometry))'
      retval = []
      column_name.downcase!
      case column_name
      when 'any'
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            retval.push(template % [geo_type, geo_id, column])
          end
        }
      when 'any_poly', 'any_line'
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            if column.to_s.index(column_name.gsub('any_', ''))
              retval.push(template % [geo_type, geo_id, column])
            end
          end
        }
      else
        retval = template % [geo_type, geo_id, column_name]
      end
      retval = retval.join(' OR ') if retval.instance_of?(Array)
      retval
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #   a select query that returns a single geometry (column name 'single_geometry' for the collection of ids
    # provided via ST_Collect)
    def st_collect_sql(*geographic_item_ids)
      geographic_item_ids.flatten!
      ActiveRecord::Base.send(:sanitize_sql_for_conditions, [
        "SELECT ST_Collect(f.the_geom) AS single_geometry
     FROM (
        SELECT (ST_DUMP(#{GeographicItem::GEOMETRY_SQL.to_sql})).geom as the_geom
        FROM geographic_items
        WHERE id in (?))
      AS f", geographic_item_ids])
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #    returns one or more geographic items combined as a single geometry in column 'single'
    def single_geometry_sql(*geographic_item_ids)
      a = GeographicItem.st_collect_sql(geographic_item_ids)
      '(SELECT single.single_geometry FROM (' + a + ' ) AS single)'
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #   returns a single geometry "column" (paren wrapped) as "single" for multiple geographic item ids, or the
    # geometry as 'geometry' for a single id
    def geometry_sql2(*geographic_item_ids)
      geographic_item_ids.flatten! # *ALWAYS* reduce the pile to a single level of ids
      if geographic_item_ids.count == 1
        "(#{GeographicItem.geometry_for_sql(geographic_item_ids.first)})"
      else
        GeographicItem.single_geometry_sql(geographic_item_ids)
      end
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    def containing_where_sql(*geographic_item_ids)
      "ST_CoveredBy(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
     CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
       WHEN 'GeographicItem::Point' THEN point::geometry
       WHEN 'GeographicItem::LineString' THEN line_string::geometry
       WHEN 'GeographicItem::Polygon' THEN polygon::geometry
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
    END)"
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    def containing_where_sql_geog(*geographic_item_ids)
      "ST_CoveredBy(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
     CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geography
       WHEN 'GeographicItem::Point' THEN point::geography
       WHEN 'GeographicItem::LineString' THEN line_string::geography
       WHEN 'GeographicItem::Polygon' THEN polygon::geography
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geography
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geography
    END)"
    end

    # @param [Interger, Array of Integer] ids
    # @return [Array]
    #   If we detect that some query id has crossed the meridian, then loop through
    #   and "manually" build up a list of results.
    #   Should only be used if GeographicItem.crosses_anti_meridian_by_id? is true.
    #   Note that this does not return a Scope, so you can't chain it like contained_by?
    # TODO: test this
    def contained_by_with_antimeridian_check(*ids)
      ids.flatten! # make sure there is only one level of splat (*)
      results = []

      crossing_ids = []

      ids.each do |id|
        # push each which crosses
        crossing_ids.push(id) if GeographicItem.crosses_anti_meridian_by_id?(id)
      end

      non_crossing_ids = ids - crossing_ids
      results.push GeographicItem.contained_by(non_crossing_ids).to_a if non_crossing_ids.any?

      crossing_ids.each do |id|
        # [61666, 61661, 61659, 61654, 61639]
        q1 = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT ST_AsText((SELECT polygon FROM geographic_items ' \
                                                           'WHERE id = ?))', id])
        r = GeographicItem.where(
          # GeographicItem.contained_by_wkt_shifted_sql(GeographicItem.find(id).geo_object.to_s)
          GeographicItem.contained_by_wkt_shifted_sql(
            ApplicationRecord.connection.execute(q1).first['st_astext'])
        ).to_a
        results.push(r)
      end

      results.flatten.uniq
    end

    # @params [String] well known text
    # @return [String] the SQL fragment for the specific geometry type, shifted by longitude
    # Note: this routine is called when it is already known that the A argument crosses anti-meridian
    def contained_by_wkt_shifted_sql(wkt)
      "ST_Contains(ST_ShiftLongitude(ST_GeomFromText('#{wkt}', 4326)), (
        CASE geographic_items.type
           WHEN 'GeographicItem::MultiPolygon' THEN ST_ShiftLongitude(multi_polygon::geometry)
           WHEN 'GeographicItem::Point' THEN ST_ShiftLongitude(point::geometry)
           WHEN 'GeographicItem::LineString' THEN ST_ShiftLongitude(line_string::geometry)
           WHEN 'GeographicItem::Polygon' THEN ST_ShiftLongitude(polygon::geometry)
           WHEN 'GeographicItem::MultiLineString' THEN ST_ShiftLongitude(multi_line_string::geometry)
           WHEN 'GeographicItem::MultiPoint' THEN ST_ShiftLongitude(multi_point::geometry)
        END
        )
      )"
    end

    # TODO: Remove the hard coded 4326 reference
    # @params [String] wkt
    # @return [String] SQL fragment limiting geographics items to those in this WKT
    def contained_by_wkt_sql(wkt)
      if crosses_anti_meridian?(wkt)
        retval = contained_by_wkt_shifted_sql(wkt)
      else
        retval = "ST_Contains(ST_GeomFromText('#{wkt}', 4326), (
        CASE geographic_items.type
           WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
           WHEN 'GeographicItem::Point' THEN point::geometry
           WHEN 'GeographicItem::LineString' THEN line_string::geometry
           WHEN 'GeographicItem::Polygon' THEN polygon::geometry
           WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
           WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
        END
        )
      )"
      end
      retval
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String] sql for contained_by via ST_ContainsProperly
    # Note: Can not use GEOMETRY_SQL because geometry_collection is not supported in ST_ContainsProperly
    # Note: !! If the target GeographicItem#id crosses the anti-meridian then you may/will get unexpected results.
    def contained_by_where_sql(*geographic_item_ids)
      "ST_Contains(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
    CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
       WHEN 'GeographicItem::Point' THEN point::geometry
       WHEN 'GeographicItem::LineString' THEN line_string::geometry
       WHEN 'GeographicItem::Polygon' THEN polygon::geometry
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
    END)"
    end

    # @param [RGeo:Point] rgeo_point
    # @return [String] sql for containing via ST_CoveredBy
    # TODO: Remove the hard coded 4326 reference
    # TODO: should this be wkt_point instead of rgeo_point?
    def containing_where_for_point_sql(rgeo_point)
      "ST_CoveredBy(
      ST_GeomFromText('#{rgeo_point}', 4326),
      #{GeographicItem::GEOMETRY_SQL.to_sql}
     )"
    end

    # @param [Interger] geographic_item_id
    # @return [String] SQL for geometries
    # example, not used
    def geometry_for_sql(geographic_item_id)
      'SELECT ' + GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry FROM geographic_items WHERE id = ' \
        "#{geographic_item_id} LIMIT 1"
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String] SQL for geometries
    # example, not used
    def geometry_for_collection_sql(*geographic_item_ids)
      'SELECT ' + GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry FROM geographic_items WHERE id IN ' \
        "( #{geographic_item_ids.join(',')} )"
    end

    #
    # Scopes
    #

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [Scope]
    #    the geographic items containing these collective geographic_item ids, not including self
    def containing(*geographic_item_ids)
      where(GeographicItem.containing_where_sql(geographic_item_ids)).not_ids(*geographic_item_ids)
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [Scope]
    #    the geographic items contained by any of these geographic_item ids, not including self
    # (works via ST_ContainsProperly)
    def contained_by(*geographic_item_ids)
      where(GeographicItem.contained_by_where_sql(geographic_item_ids))
    end

    # @param [RGeo::Point] rgeo_point
    # @return [Scope]
    #    the geographic items containing this point
    # TODO: should be containing_wkt ?
    def containing_point(rgeo_point)
      where(GeographicItem.containing_where_for_point_sql(rgeo_point))
    end

    # @return [Scope]
    #   adds an area_in_meters field, with meters
    def with_area
      select("ST_Area(#{GeographicItem::GEOGRAPHY_SQL}, false) as area_in_meters")
    end

    # return [Scope]
    #   A scope that limits the result to those GeographicItems that have a collecting event
    #   through either the geographic_item or the error_geographic_item
    #
    # A raw SQL join approach for comparison
    #
    # GeographicItem.joins('LEFT JOIN georeferences g1 ON geographic_items.id = g1.geographic_item_id').
    #   joins('LEFT JOIN georeferences g2 ON geographic_items.id = g2.error_geographic_item_id').
    #   where("(g1.geographic_item_id IS NOT NULL OR g2.error_geographic_item_id IS NOT NULL)").uniq

    # @return [Scope] GeographicItem
    # This uses an Arel table approach, this is ultimately more decomposable if we need. Of use:
    #  http://danshultz.github.io/talks/mastering_activerecord_arel  <- best
    #  https://github.com/rails/arel
    #  http://stackoverflow.com/questions/4500629/use-arel-for-a-nested-set-join-query-and-convert-to-activerecordrelation
    #  http://rdoc.info/github/rails/arel/Arel/SelectManager
    #  http://stackoverflow.com/questions/7976358/activerecord-arel-or-condition
    #
    def with_collecting_event_through_georeferences
      geographic_items = GeographicItem.arel_table
      georeferences = Georeference.arel_table
      g1 = georeferences.alias('a')
      g2 = georeferences.alias('b')

      c = geographic_items.join(g1, Arel::Nodes::OuterJoin).on(geographic_items[:id].eq(g1[:geographic_item_id]))
        .join(g2, Arel::Nodes::OuterJoin).on(geographic_items[:id].eq(g2[:error_geographic_item_id]))

      GeographicItem.joins(# turn the Arel back into scope
                           c.join_sources # translate the Arel join to a join hash(?)
                          ).where(
                            g1[:id].not_eq(nil).or(g2[:id].not_eq(nil)) # returns a Arel::Nodes::Grouping
                          ).distinct
    end

    # @return [Scope] include a 'latitude' column
    def with_latitude
      select(lat_long_sql(:latitude))
    end

    # @return [Scope] include a 'longitude' column
    def with_longitude
      select(lat_long_sql(:longitude))
    end

    # @param [String, GeographicItems]
    # @return [Scope]
    def intersecting(column_name, *geographic_items)
      if column_name.downcase == 'any'
        pieces = []
        DATA_TYPES.each { |column|
          pieces.push(GeographicItem.intersecting(column.to_s, geographic_items).to_a)
        }

        # @TODO change 'id in (?)' to some other sql construct

        GeographicItem.where(id: pieces.flatten.map(&:id))
      else
        q = geographic_items.flatten.collect { |geographic_item|
          "ST_Intersects(#{column_name}, '#{geographic_item.geo_object}'    )" # seems like we want this: http://danshultz.github.io/talks/mastering_activerecord_arel/#/15/2
        }.join(' or ')

        where(q)
      end
    end

    # @param [GeographicItem#id] geographic_item_id
    # @param [Float] distance in meters ?!?!
    # @return [ActiveRecord::Relation]
    # !! should be distance, not radius?!
    def within_radius_of_item(geographic_item_id, distance)
      where(within_radius_of_item_sql(geographic_item_id, distance))
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    #   a SQL fragment for ST_DISJOINT, specifies all geographic_items that have data in column_name
    #   that are disjoint from the passed geographic_items
    def disjoint_from(column_name, *geographic_items)
      q = geographic_items.flatten.collect { |geographic_item|
        "ST_DISJOINT(#{column_name}::geometry, (#{geometry_sql(geographic_item.to_param,
                                                             geographic_item.geo_object_type)}))"
      }.join(' and ')

      where(q)
    end

    # @return [Scope]
    #   see are_contained_in_item_by_id
    # @param [String] column_name
    # @param [GeographicItem, Array] geographic_items
    def are_contained_in_item(column_name, *geographic_items)
      are_contained_in_item_by_id(column_name, geographic_items.flatten.map(&:id))
    end

    # rubocop:disable Metrics/MethodLength
    # @param [String] column_name to search
    # @param [GeographicItem] geographic_item_ids or array of geographic_item_ids to be tested.
    # @return [Scope] of GeographicItems
    #
    # If this scope is given an Array of GeographicItems as a second parameter,
    # it will return the 'OR' of each of the objects against the table.
    # SELECT COUNT(*) FROM "geographic_items"
    #        WHERE (ST_Contains(polygon::geometry, GeomFromEWKT('srid=4326;POINT (0.0 0.0 0.0)'))
    #               OR ST_Contains(polygon::geometry, GeomFromEWKT('srid=4326;POINT (-9.8 5.0 0.0)')))
    #
    def are_contained_in_item_by_id(column_name, *geographic_item_ids) # = containing
      geographic_item_ids.flatten! # in case there is a array of arrays, or multiple objects
      column_name.downcase!
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.are_contained_in_item_by_id(column.to_s, geographic_item_ids).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))
      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          if column.to_s.index(column_name.gsub('any_', ''))
            part.push(GeographicItem.are_contained_in_item_by_id("#{column}", geographic_item_ids).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))
      else
        q = geographic_item_ids.flatten.collect { |geographic_item_id|
          # discover the item types, and convert type to database type for 'multi_'
          b = GeographicItem.where(id: geographic_item_id)
            .pluck(:type)[0].split(':')[2].downcase.gsub('lti', 'lti_')
          # a = GeographicItem.find(geographic_item_id).geo_object_type
          GeographicItem.containing_sql(column_name, geographic_item_id, b)
        }.join(' or ')
        q = 'FALSE' if q.blank? # this will prevent the invocation of *ALL* of the GeographicItems, if there are
        # no GeographicItems in the request (see CollectingEvent.name_hash(types)).
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:enable Metrics/MethodLength

    # @param [String] column_name
    # @param [String] geometry of WKT
    # @return [Scope]
    # a single WKT geometry is compared against column or columns (except geometry_collection) to find geographic_items
    # which are contained in the WKT
    def are_contained_in_wkt(column_name, geometry)
      column_name.downcase!
      # column_name = 'point'
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.are_contained_in_wkt(column.to_s, geometry).pluck(:id).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten)
      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          if column.to_s.index(column_name.gsub('any_', ''))
            part.push(GeographicItem.are_contained_in_wkt("#{column}", geometry).pluck(:id).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten)
      else
        # column = points, geometry = square
        q = "ST_Contains(ST_GeomFromEWKT('srid=4326;#{geometry}'), #{column_name}::geometry)"
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:disable Metrics/MethodLength
    #    containing the items the shape of which is contained in the geographic_item[s] supplied.
    # @param column_name [String] can be any of DATA_TYPES, or 'any' to check against all types, 'any_poly' to check
    # against 'polygon' or 'multi_polygon', or 'any_line' to check against 'line_string' or 'multi_line_string'.
    #  CANNOT be 'geometry_collection'.
    # @param geographic_items [GeographicItem] Can be a single GeographicItem, or an array of GeographicItem.
    # @return [Scope]
    def is_contained_by(column_name, *geographic_items)
      column_name.downcase!
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.is_contained_by(column.to_s, geographic_items).to_a)
          end
        }
        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))

      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            if column.to_s.index(column_name.gsub('any_', ''))
              part.push(GeographicItem.is_contained_by(column.to_s, geographic_items).to_a)
            end
          end
        }
        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))

      else
        q = geographic_items.flatten.collect { |geographic_item|
          GeographicItem.reverse_containing_sql(column_name, geographic_item.to_param,
                                                geographic_item.geo_object_type)
        }.join(' or ')
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:enable Metrics/MethodLength

    # @param [String, GeographicItem]
    # @return [Scope]
    def ordered_by_shortest_distance_from(column_name, geographic_item)
      if true # check_geo_params(column_name, geographic_item)
        select_distance_with_geo_object(column_name, geographic_item)
          .where_distance_greater_than_zero(column_name, geographic_item).order('distance')
      else
        where('false')
      end
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    def ordered_by_longest_distance_from(column_name, geographic_item)
      if true # check_geo_params(column_name, geographic_item)
        q = select_distance_with_geo_object(column_name, geographic_item)
          .where_distance_greater_than_zero(column_name, geographic_item)
          .order('distance desc')
        q
      else
        where('false')
      end
    end

    # @param [String] column_name
    # @param [GeographicItem] geographic_item
    # @return [String]
    def select_distance_with_geo_object(column_name, geographic_item)
      select("*, ST_Distance(#{column_name}, GeomFromEWKT('srid=4326;#{geographic_item.geo_object}')) as distance")
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    def where_distance_greater_than_zero(column_name, geographic_item)
      where("#{column_name} is not null and ST_Distance(#{column_name}, " \
            "GeomFromEWKT('srid=4326;#{geographic_item.geo_object}')) > 0")
    end

    # @param [GeographicItem]
    # @return [Scope]
    def not_including(geographic_items)
      where.not(id: geographic_items)
    end

    # @return [Scope]
    #   includes an 'is_valid' attribute (True/False) for the passed geographic_item.  Uses St_IsValid.
    # @param [RGeo object] geographic_item
    def with_is_valid_geometry_column(geographic_item)
      where(id: geographic_item.id).select("ST_IsValid(ST_AsBinary(#{geographic_item.geo_object_type})) is_valid")
    end

    #
    # Other
    #

    # @param [Integer] geographic_item_id1
    # @param [Integer] geographic_item_id2
    # @return [Float]
    def distance_between(geographic_item_id1, geographic_item_id2)
      q1 = "ST_Distance(#{GeographicItem::GEOGRAPHY_SQL}, " \
        "(#{select_geography_sql(geographic_item_id2)})) as distance"
      _q2 = ActiveRecord::Base.send(
        :sanitize_sql_array, ['ST_Distance(?, (?)) as distance',
                              GeographicItem::GEOGRAPHY_SQL,
                              select_geography_sql(geographic_item_id2)])
      GeographicItem.where(id: geographic_item_id1).pluck(Arel.sql(q1)).first
    end

    # @param [RGeo::Point] point
    # @return [Hash]
    #   as per #inferred_geographic_name_hierarchy but for Rgeo point
    def point_inferred_geographic_name_hierarchy(point)
      GeographicItem.containing_point(point).order(cached_total_area: :ASC).limit(1).first&.inferred_geographic_name_hierarchy
    end

    # @param [String] type_name ('polygon', 'point', 'line' [, 'circle'])
    # @return [String] if type
    def eval_for_type(type_name)
      retval = 'GeographicItem'
      case type_name.upcase
      when 'POLYGON'
        retval += '::Polygon'
      when 'LINESTRING'
        retval += '::LineString'
      when 'POINT'
        retval += '::Point'
      when 'MULTIPOLYGON'
        retval += '::MultiPolygon'
      when 'MULTILINESTRING'
        retval += '::MultiLineString'
      when 'MULTIPOINT'
        retval += '::MultiPoint'
      else
        retval = nil
      end
      retval
    end

    # example, not used
    # @param [Integer] geographic_item_id
    # @return [RGeo::Geographic object]
    def geometry_for(geographic_item_id)
      GeographicItem.select(GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry').find(geographic_item_id)['geometry']
    end

    # example, not used
    # @param [Integer, Array] geographic_item_ids
    # @return [Scope]
    def st_multi(*geographic_item_ids)
      GeographicItem.find_by_sql(
        "SELECT ST_Multi(ST_Collect(g.the_geom)) AS singlegeom
     FROM (
        SELECT (ST_DUMP(#{GeographicItem::GEOMETRY_SQL.to_sql})).geom AS the_geom
        FROM geographic_items
        WHERE id IN (?))
      AS g;", geographic_item_ids.flatten
      )
    end
  end # class << self

  # @return [Hash]
  #    a geographic_name_classification or empty Hash
  # This is a quick approach that works only when
  # the geographic_item is linked explicitly to a GeographicArea.
  #
  # !! Note that it is not impossible for a GeographicItem to be linked
  # to > 1 GeographicArea, in that case we are assuming that all are
  # equally refined, this might not be the case in the future because
  # of how the GeographicArea gazetteer is indexed.
  def quick_geographic_name_hierarchy
    if a = geographic_areas.first
      a.geographic_name_classification
    else
      {}
    end
  end

  # @return [Hash]
  #   a geographic_name_classification (see GeographicArea) inferred by
  # finding the smallest area containing this GeographicItem, in the most accurate gazetteer
  # and using it to return country/state/county. See also the logic in
  # filling in missing levels in GeographicArea.
  def inferred_geographic_name_hierarchy
    if small_area = containing_geographic_areas
      .joins(:geographic_areas_geographic_items)
      .merge(GeographicAreasGeographicItem.ordered_by_data_origin)
      .ordered_by_area
      .limit(1)
      .first
    return small_area.geographic_name_classification
    else
      {}
    end
  end

  # @return [Scope]
  #   the Geographic Areas that contain (gis) this geographic item
  def containing_geographic_areas
    GeographicArea.joins(:geographic_items).includes(:geographic_area_type)
      .joins("JOIN (#{GeographicItem.containing(id).to_sql}) j on geographic_items.id = j.id")
  end

  # @return [Boolean]
  #   whether stored shape is ST_IsValid
  def valid_geometry?
    GeographicItem.with_is_valid_geometry_column(self).first['is_valid']
  end

  # @return [Array of latitude, longitude]
  #    the lat, lon of the first point in the GeoItem, see subclass for #st_start_point
  def start_point
    o = st_start_point
    [o.y, o.x]
  end

  # @return [Array]
  #   the lat, long, as STRINGs for the centroid of this geographic item
  def center_coords
    r = GeographicItem.find_by_sql(
      "Select split_part(ST_AsLatLonText(ST_Centroid(#{GeographicItem::GEOMETRY_SQL.to_sql}), " \
      "'D.DDDDDD'), ' ', 1) latitude, split_part(ST_AsLatLonText(ST_Centroid" \
      "(#{GeographicItem::GEOMETRY_SQL.to_sql}), 'D.DDDDDD'), ' ', 2) " \
      "longitude from geographic_items where id = #{id};")[0]

    [r.latitude, r.longitude]
  end

  # @return [RGeo::Geographic::ProjectedPointImpl]
  #    representing the centroid of this geographic item
  def centroid
    # Gis::FACTORY.point(*center_coords.reverse)
    return geo_object if type == 'GeographicItem::Point'
    return Gis::FACTORY.parse_wkt(self.st_centroid)
  end

  # @param [Integer] geographic_item_id
  # @return [Double] distance in meters (slower, more accurate)
  def st_distance(geographic_item_id) # geo_object
    q1 = "ST_Distance((#{GeographicItem.select_geography_sql(id)}), " \
      "(#{GeographicItem.select_geography_sql(geographic_item_id)})) as d"
    _q2 = ActiveRecord::Base.send(:sanitize_sql_array, ['ST_Distance((?),(?)) as d',
                                                        GeographicItem.select_geography_sql(self.id),
                                                        GeographicItem.select_geography_sql(geographic_item_id)])
    deg = GeographicItem.where(id: id).pluck(Arel.sql(q1)).first
    deg * Utilities::Geo::ONE_WEST
  end

  # @param [GeographicItem] geographic_item
  # @return [Double] distance in meters
  # Like st_distance but works with changed and non persisted objects
  def st_distance_to_geographic_item(geographic_item)
    unless !persisted? || changed?
      a = "(#{GeographicItem.select_geography_sql(id)})"
    else
      a = "ST_GeographyFromText('#{geo_object.to_s}')"
    end

    unless !geographic_item.persisted? || geographic_item.changed?
      b = "(#{GeographicItem.select_geography_sql(geographic_item.id)})"
    else
      b = "ST_GeographyFromText('#{geographic_item.geo_object.to_s}')"
    end

    ActiveRecord::Base.connection.select_value("SELECT ST_Distance(#{a}, #{b})")
  end

  alias_method :distance_to, :st_distance

  # @param [Integer] geographic_item_id
  # @return [Double] distance in meters (faster, less accurate)
  def st_distance_spheroid(geographic_item_id)
    q1 = "ST_DistanceSpheroid((#{GeographicItem.select_geometry_sql(id)})," \
      "(#{GeographicItem.select_geometry_sql(geographic_item_id)}),'#{Gis::SPHEROID}') as distance"
    _q2 = ActiveRecord::Base.send(:sanitize_sql_array,
                                  ['ST_DistanceSpheroid((?),(?),?) as distance',
                                   GeographicItem.select_geometry_sql(id),
                                   GeographicItem.select_geometry_sql(geographic_item_id),
                                   Gis::SPHEROID])
    # TODO: what is _q2?
    GeographicItem.where(id: id).pluck(Arel.sql(q1)).first
  end

  # @return [String]
  #   a WKT POINT representing the centroid of the geographic item
  def st_centroid
    GeographicItem.where(id: to_param).pluck(Arel.sql("ST_AsEWKT(ST_Centroid(#{GeographicItem::GEOMETRY_SQL.to_sql}))")).first.gsub(/SRID=\d*;/, '')
  end

  # @return [Integer]
  #   the number of points in the geometry
  def st_npoints
    GeographicItem.where(id: id).pluck(Arel.sql("ST_NPoints(#{GeographicItem::GEOMETRY_SQL.to_sql}) as npoints")).first
  end

  # @return [Symbol]
  #   the geo type (i.e. column like :point, :multipolygon).  References the first-found object,
  # according to the list of DATA_TYPES, or nil
  def geo_object_type
    if self.class.name == 'GeographicItem' # a proxy check for new records
      geo_type
    else
      self.class::SHAPE_COLUMN
    end
  end

  # !!TODO: migrate these to use native column calls

  # @return [RGeo instance, nil]
  #  the Rgeo shape (See http://rubydoc.info/github/dazuma/rgeo/RGeo/Feature)
  def geo_object
    begin
      if r = geo_object_type # rubocop:disable Lint/AssignmentInCondition
        send(r)
      else
        false
      end
    rescue RGeo::Error::InvalidGeometry
      return nil # TODO: we need to render proper error for this!
    end
  end

  # @param [geo_object]
  # @return [Boolean]
  def contains?(target_geo_object)
    return nil if target_geo_object.nil?
    self.geo_object.contains?(target_geo_object)
  end

  # @param [geo_object]
  # @return [Boolean]
  def within?(target_geo_object)
    self.geo_object.within?(target_geo_object)
  end

  # @param [geo_object]
  # @return [Boolean]
  def intersects?(target_geo_object)
    self.geo_object.intersects?(target_geo_object)
  end

  # @param [geo_object, Double]
  # @return [Boolean]
  def near(target_geo_object, distance)
    self.geo_object.buffer(distance).contains?(target_geo_object)
  end

  # @param [geo_object, Double]
  # @return [Boolean]
  def far(target_geo_object, distance)
    !near(target_geo_object, distance)
  end

  # @return [GeoJSON hash]
  #    via Rgeo apparently necessary for GeometryCollection
  def rgeo_to_geo_json
    RGeo::GeoJSON.encode(geo_object).to_json
  end

  # @return [Hash]
  #   in GeoJSON format
  #   Computed via "raw" PostGIS (much faster). This
  #   requires the geo_object_type and id.
  #
  def to_geo_json
    JSON.parse(
      GeographicItem.connection.select_one(
        "SELECT ST_AsGeoJSON(#{geo_object_type}::geometry) a " \
        "FROM geographic_items WHERE id=#{id};")['a'])
  end

  # We don't need to serialize to/from JSON
  def to_geo_json_string
    GeographicItem.connection.select_one(
      "SELECT ST_AsGeoJSON(#{geo_object_type}::geometry) a " \
      "FROM geographic_items WHERE id=#{id};")['a']
  end

  # @return [Hash]
  #   the shape as a GeoJSON Feature with some item metadata
  def to_geo_json_feature
    @geometry ||= to_geo_json
    {'type' => 'Feature',
     'geometry' => geometry,
     'properties' => {
       'geographic_item' => {
         'id' => id}
     }
    }
  end

  # '{"type":"Feature","geometry":{"type":"Point","coordinates":[2.5,4.0]},"properties":{"color":"red"}}'
  # '{"type":"Feature","geometry":{"type":"Polygon","coordinates":"[[[-125.29394388198853, 48.584480409793],
  # [-67.11035013198853, 45.09937589848195],[-80.64550638198853, 25.01924647619111],[-117.55956888198853,
  # 32.5591595028449],[-125.29394388198853, 48.584480409793]]]"},"properties":{}}'
  # @param [String] value
  # @return [Boolean, RGeo object]
  def shape=(value)
    unless value.blank?
      geom = RGeo::GeoJSON.decode(value, json_parser: :json)
      this_type = JSON.parse(value)['geometry']['type']

      # TODO: isn't this set automatically? Or perhaps the callback isn't hit in this approach?
      self.type = GeographicItem.eval_for_type(this_type) unless geom.nil?

      raise('GeographicItem.type not set.') if type.blank?

      object = nil

      begin
        object = Gis::FACTORY.parse_wkt(geom.geometry.to_s)
      rescue RGeo::Error::InvalidGeometry
        return false
      end

      write_attribute(this_type.underscore.to_sym, object)
      geom
    end
  end

  # @return [String]
  def to_wkt
    #  10k  #<Benchmark::Tms:0x00007fb0dfd30fd0 @label="", @real=25.237487000005785, @cstime=0.0, @cutime=0.0, @stime=1.1704609999999995, @utime=5.507929999999988, @total=6.678390999999987>
    #  GeographicItem.select("ST_AsText( #{GeographicItem::GEOMETRY_SQL.to_sql}) wkt").where(id: id).first.wkt

    # 10k <Benchmark::Tms:0x00007fb0e02f7540 @label="", @real=21.619827999995323, @cstime=0.0, @cutime=0.0, @stime=0.8850890000000007, @utime=3.2958549999999605, @total=4.180943999999961>
    if a =  ApplicationRecord.connection.execute( "SELECT ST_AsText( #{GeographicItem::GEOMETRY_SQL.to_sql} ) wkt from geographic_items where geographic_items.id = #{id}").first
      return a['wkt']
    else
      return nil
    end
  end

  # return float, in meters, calculated, not from cached
  def area
    GeographicItem.where(id: id).select("ST_Area(#{GeographicItem::GEOGRAPHY_SQL}, false) as area_in_meters").first['area_in_meters']
  end

  private

  def set_cached
    update_column(:cached_total_area, area)
  end

  protected

  # @return [Symbol]
  #   returns the attribute (column name) containing data
  #   nearly all methods should use #geo_object_type, not geo_type
  def geo_type
    DATA_TYPES.each { |item|
      return item if send(item)
    }
    nil
  end

  # @return [Boolean, String] false if already set, or type to which it was set
  def set_type_if_geography_present
    if type.blank?
      column = geo_type
      self.type = "GeographicItem::#{column.to_s.camelize}" if column
    end
  end

  # @param [RGeo::Point] point
  # @return [Array] of a point
  #   Longitude |, Latitude -
  def point_to_a(point)
    data = []
    data.push(point.x, point.y)
    data
  end

  # @param [RGeo::Point] point
  # @return [Hash] of a point
  def point_to_hash(point)
    {points: [point_to_a(point)]}
  end

  # @param [RGeo::MultiPoint] multi_point
  # @return [Array] of points
  def multi_point_to_a(multi_point)
    data = []
    multi_point.each { |point|
      data.push([point.x, point.y])
    }
    data
  end

  # @return [Hash] of points
  def multi_point_to_hash(_multi_point)
    # when we encounter a multi_point type, we only stick the points into the array, NOT it's identity as a group
    {points: multi_point_to_a(multi_point)}
  end

  # @param [Reo::LineString] line_string
  # @return [Array] of points in the line
  def line_string_to_a(line_string)
    data = []
    line_string.points.each { |point|
      data.push([point.x, point.y])
    }
    data
  end

  # @param [Reo::LineString] line_string
  # @return [Hash] of points in the line
  def line_string_to_hash(line_string)
    {lines: [line_string_to_a(line_string)]}
  end

  # @param [RGeo::Polygon] polygon
  # @return [Array] of points in the polygon (exterior_ring ONLY)
  def polygon_to_a(polygon)
    # TODO: handle other parts of the polygon; i.e., the interior_rings (if they exist)
    data = []
    polygon.exterior_ring.points.each { |point|
      data.push([point.x, point.y])
    }
    data
  end

  # @param [RGeo::Polygon] polygon
  # @return [Hash] of points in the polygon (exterior_ring ONLY)
  def polygon_to_hash(polygon)
    {polygons: [polygon_to_a(polygon)]}
  end

  # @return [Array] of line_strings as arrays of points
  # @param [RGeo::MultiLineString] multi_line_string
  def multi_line_string_to_a(multi_line_string)
    data = []
    multi_line_string.each { |line_string|
      line_data = []
      line_string.points.each { |point|
        line_data.push([point.x, point.y])
      }
      data.push(line_data)
    }
    data
  end

  # @return [Hash] of line_strings as hashes of points
  def multi_line_string_to_hash(_multi_line_string)
    {lines: to_a}
  end

  # @param [RGeo::MultiPolygon] multi_polygon
  # @return [Array] of arrays of points in the polygons (exterior_ring ONLY)
  def multi_polygon_to_a(multi_polygon)
    data = []
    multi_polygon.each { |polygon|
      polygon_data = []
      polygon.exterior_ring.points.each { |point|
        polygon_data.push([point.x, point.y])
      }
      data.push(polygon_data)
    }
    data
  end

  # @return [Hash] of hashes of points in the polygons (exterior_ring ONLY)
  def multi_polygon_to_hash(_multi_polygon)
    {polygons: to_a}
  end

  # @return [Boolean] iff there is one and only one shape column set
  def some_data_is_provided
    data = []

    DATA_TYPES.each do |item|
      data.push(item) unless send(item).blank?
    end

    case data.count
    when 0
      errors.add(:base, 'No shape provided or provided shape is invalid')
    when 1
      return true
    else
      data.each do |object|
        errors.add(object, 'More than one shape type provided')
      end
    end
    true
  end
end

#multi_line_string ⇒ RGeo::Geographic::ProjectedMultiLineStringImpl

Returns:

• (RGeo::Geographic::ProjectedMultiLineStringImpl)

# File 'app/models/geographic_item.rb', line 34

class GeographicItem < ApplicationRecord
  include Housekeeping::Users
  include Housekeeping::Timestamps
  include Shared::IsData
  include Shared::SharedAcrossProjects

  # @return [Hash, nil]
  # An internal variable for use in super calls, holds a Hash in GeoJSON format (temporarily)
  attr_accessor :geometry

  # @return [Boolean, RGeo object]
  # @params value [Hash in GeoJSON format] ?!
  # TODO: WHY! boolean not nil, or object
  # Used to build geographic items from a shape [ of what class ] !?
  attr_accessor :shape

  # @return [Boolean]
  #   When true cached values are not built
  attr_accessor :no_cached

  DATA_TYPES = [
    :point,
    :line_string,
    :polygon,
    :multi_point,
    :multi_line_string,
    :multi_polygon,
    :geometry_collection
  ].freeze

  GEOMETRY_SQL = Arel::Nodes::Case.new(arel_table[:type])
    .when('GeographicItem::MultiPolygon').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_polygon].as('geometry')]))
    .when('GeographicItem::Point').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:point].as('geometry')]))
    .when('GeographicItem::LineString').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:line_string].as('geometry')]))
    .when('GeographicItem::Polygon').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:polygon].as('geometry')]))
    .when('GeographicItem::MultiLineString').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_line_string].as('geometry')]))
    .when('GeographicItem::MultiPoint').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_point].as('geometry')]))
    .when('GeographicItem::GeometryCollection').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:geometry_collection].as('geometry')]))
    .freeze

  GEOGRAPHY_SQL = "CASE geographic_items.type
    WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon
    WHEN 'GeographicItem::Point' THEN point
    WHEN 'GeographicItem::LineString' THEN line_string
    WHEN 'GeographicItem::Polygon' THEN polygon
    WHEN 'GeographicItem::MultiLineString' THEN multi_line_string
    WHEN 'GeographicItem::MultiPoint' THEN multi_point
    WHEN 'GeographicItem::GeometryCollection' THEN geometry_collection
    END".freeze

  # ANTI_MERIDIAN = '0X0102000020E61000000200000000000000008066400000000000405640000000000080664000000000004056C0'
  ANTI_MERIDIAN = 'LINESTRING (180 89.0, 180 -89)'.freeze

  has_many :geographic_areas_geographic_items, dependent: :destroy, inverse_of: :geographic_item
  has_many :geographic_areas, through: :geographic_areas_geographic_items
  has_many :asserted_distributions, through: :geographic_areas
  has_many :geographic_area_types, through: :geographic_areas
  has_many :parent_geographic_areas, through: :geographic_areas, source: :parent
  has_many :gadm_geographic_areas, class_name: 'GeographicArea', foreign_key: :gadm_geo_item_id
  has_many :ne_geographic_areas, class_name: 'GeographicArea', foreign_key: :ne_geo_item_id
  has_many :tdwg_geographic_areas, class_name: 'GeographicArea', foreign_key: :tdwg_geo_item_id
  has_many :georeferences, inverse_of: :geographic_item
  has_many :georeferences_through_error_geographic_item,
    class_name: 'Georeference', foreign_key: :error_geographic_item_id, inverse_of: :error_geographic_item
  has_many :collecting_events_through_georeferences, through: :georeferences, source: :collecting_event
  has_many :collecting_events_through_georeference_error_geographic_item,
    through: :georeferences_through_error_geographic_item, source: :collecting_event

  # TODO: THIS IS NOT GOOD
  before_validation :set_type_if_geography_present

  validate :some_data_is_provided
  validates :type, presence: true

  scope :include_collecting_event, -> { includes(:collecting_events_through_georeferences) }
  scope :geo_with_collecting_event, -> { joins(:collecting_events_through_georeferences) }
  scope :err_with_collecting_event, -> { joins(:georeferences_through_error_geographic_item) }

  after_save :set_cached, unless: Proc.new {|n| n.no_cached || errors.any? }

  class << self

    def st_union(geographic_item_scope)
      GeographicItem.select("ST_Union(#{GeographicItem::GEOMETRY_SQL.to_sql}) as collection")
        .where(id: geographic_item_scope.pluck(:id))
    end

    def st_collect(geographic_item_scope)
      GeographicItem.select("ST_Collect(#{GeographicItem::GEOMETRY_SQL.to_sql}) as collection")
        .where(id: geographic_item_scope.pluck(:id))
    end

    # @return [GeographicItem::ActiveRecord_Relation]
    # @params [Array] array of geographic area ids
    def default_by_geographic_area_ids(geographic_area_ids = [])
      GeographicItem.
        joins(:geographic_areas_geographic_items).
        merge(::GeographicAreasGeographicItem.default_geographic_item_data).
        where(geographic_areas_geographic_items: {geographic_area_id: geographic_area_ids})
    end

    # @param [String] wkt
    # @return [Boolean]
    #   whether or not the wtk intersects with the anti-meridian
    #   !! StrongParams security considerations
    def crosses_anti_meridian?(wkt)
      GeographicItem.find_by_sql(
        ['SELECT ST_Intersects(ST_GeogFromText(?), ST_GeogFromText(?)) as r;', wkt, ANTI_MERIDIAN]
      ).first.r
    end

    # @param [Integer] ids
    # @return [Boolean]
    #   whether or not any GeographicItem passed intersects the anti-meridian
    #   !! StrongParams security considerations
    #   This is our first line of defense against queries that define multiple shapes, one or
    #   more of which crosses the anti-meridian.  In this case the current TW strategy within the
    #   UI is to abandon the search, and prompt the user to refactor the query.
    def crosses_anti_meridian_by_id?(*ids)
      q1 = ["SELECT ST_Intersects((SELECT single_geometry FROM (#{GeographicItem.single_geometry_sql(*ids)}) as " \
            'left_intersect), ST_GeogFromText(?)) as r;', ANTI_MERIDIAN]
      _q2 = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT ST_Intersects((SELECT single_geometry FROM (?) as ' \
                                                          'left_intersect), ST_GeogFromText(?)) as r;', GeographicItem.single_geometry_sql(*ids), ANTI_MERIDIAN])
      GeographicItem.find_by_sql(q1).first.r
    end

    # TODO: * rename to reflect either/or and what is being returned
    # @param [Integer] geographic_area_ids
    # @param [String] shape_in in JSON (POINT, POLYGON, MULTIPOLYGON), usually from GoogleMaps
    # @param [String] search_object_class
    # @param [Integer] project_id for search_object_class
    # @return [Scope] of the requested search_object_type
    def gather_geographic_area_or_shape_data(geographic_area_ids, shape_in, search_object_class, project_id)
      if shape_in.blank?
        # get the shape from the geographic area, if possible
        finding = search_object_class.constantize
        target_geographic_item_ids = []
        if geographic_area_ids.blank?
          found = finding.none
        else
          # now use method from collection_object_filter_query
          geographic_area_ids.each do |gaid|
            target_geographic_item_ids.push(GeographicArea.joins(:geographic_items)
              .find(gaid)
              .default_geographic_item.id)
          end

          # TODO: There probably is a better way to do this, but for now...
          f1 = project_id.present? ? finding.with_project_id(project_id) : finding
          case search_object_class
          when /Collection/, /Collecting/
            found = f1.joins(:geographic_items)
              .where(GeographicItem.contained_by_where_sql(target_geographic_item_ids))
          when /Asserted/
            # TODO: Figure out how to see through this group of geographic_items to the ones which contain
            # geographic_items which are associated with geographic_areas (as #default_geographic_items)
            # which are associated with asserted_distributions
            found = f1.joins(:geographic_area).joins(:geographic_items)
              .where(GeographicItem.contained_by_where_sql(target_geographic_item_ids))
          else
          end
        end
      else
        found = gather_map_data(shape_in, search_object_class, project_id)
      end
      found
    end

    # TODO: likely not ussed any more
    #
    # @param [String] feature in JSON, looks like '{"type":"Feature","geometry":{"type":"Polygon",
    # "coordinates":[[[-40.078125,10.614539227964332],[-49.21875,-17.185577279306226],
    # [-23.203125,-15.837353550148276],[-40.078125,10.614539227964332]]]},"properties":{}}'
    # @param [String] search_object_class
    # @param [Integer, Nil] project_id for search_object_class
    # @return [Scope] of the requested search_object_type
    #   This function takes a feature, i.e. a string that is the result
    #   of drawing on a Google map, and submited as a form variable,
    #   and translates that to a scope for a provided search_object_class.
    #   e.g. Return all CollectionObjects in this drawn area
    #        Return all CollectionObjects in the radius around this point
    def gather_map_data(feature, search_object_class, project_id)
      finding = search_object_class.constantize
      g_feature = RGeo::GeoJSON.decode(feature, json_parser: :json)
      if g_feature.nil?
        finding.none
      else
        geometry = g_feature.geometry # isolate the WKT
        shape_type = geometry.geometry_type.to_s.downcase
        geometry = geometry.as_text
        radius = g_feature['radius']

        query = project_id.present? ?
          finding.with_project_id(project_id).joins(:geographic_items) :
          finding.joins(:geographic_items)

        case shape_type
        when 'point'
          query.where(GeographicItem.within_radius_of_wkt_sql(geometry, radius)) # was interesecting version.
        when 'polygon', 'multipolygon'
          query.where(GeographicItem.contained_by_wkt_sql(geometry))
        else
          query
        end
      end
    end

    #
    # SQL fragments
    #

    # @param [Integer, String]
    # @return [String]
    #   a SQL select statement that returns the *geometry* for the geographic_item with the specified id
    def select_geometry_sql(geographic_item_id)
      "SELECT #{GeographicItem::GEOMETRY_SQL.to_sql} from geographic_items where geographic_items.id = #{geographic_item_id}"
    end

    # @param [Integer, String]
    # @return [String]
    #   a SQL select statement that returns the geography for the geographic_item with the specified id
    def select_geography_sql(geographic_item_id)
      ActiveRecord::Base.send(:sanitize_sql_for_conditions, [
        "SELECT #{GeographicItem::GEOMETRY_SQL.to_sql} from geographic_items where geographic_items.id = ?",
        geographic_item_id])
    end

    # @param [Symbol] choice
    # @return [String]
    #   a fragment returning either latitude or longitude columns
    def lat_long_sql(choice)
      return nil unless [:latitude, :longitude].include?(choice)
      f = "'D.DDDDDD'" # TODO: probably a constant somewhere
      v = (choice == :latitude ? 1 : 2)
      "CASE type
      WHEN 'GeographicItem::GeometryCollection' THEN split_part(ST_AsLatLonText(ST_Centroid" \
      "(geometry_collection::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::LineString' THEN split_part(ST_AsLatLonText(ST_Centroid(line_string::geometry), " \
      "#{f}), ' ', #{v})
      WHEN 'GeographicItem::MultiPolygon' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_polygon::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::Point' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(point::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::Polygon' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(polygon::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::MultiLineString' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_line_string::geometry), #{f} ), ' ', #{v})
      WHEN 'GeographicItem::MultiPoint' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_point::geometry), #{f}), ' ', #{v})
    END as #{choice}"
    end

    # @param [Integer] geographic_item_id
    # @param [Integer] distance
    # @return [String]
    def within_radius_of_item_sql(geographic_item_id, distance)
      "ST_DWithin((#{GeographicItem::GEOGRAPHY_SQL}), (#{select_geography_sql(geographic_item_id)}), #{distance})"
    end

    # TODO: 3D is overkill here
    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [String]
    # !! This is intersecting
    def intersecting_radius_of_wkt_sql(wkt, distance)
      "ST_DWithin((#{GeographicItem::GEOGRAPHY_SQL}), ST_Transform( ST_GeomFromText('#{wkt}', " \
        "4326), 4326), #{distance})"
    end

    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [String]
    # !! This is fully covering
    def within_radius_of_wkt_sql(wkt, distance)
      "ST_Covers( ST_Buffer(ST_SetSRID( ST_GeomFromText('#{wkt}'), 4326)::geography, #{distance}), (#{GeographicItem::GEOGRAPHY_SQL}))"
    end

    # @param [String, Integer, String]
    # @return [String]
    #   a SQL fragment for ST_Contains() function, returns
    #   all geographic items which are contained in the item supplied
    def containing_sql(target_column_name = nil, geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil? || target_column_name.nil?
      "ST_Contains(#{target_column_name}::geometry, (#{geometry_sql(geographic_item_id, source_column_name)}))"
    end

    # @param [String, Integer, String]
    # @return [String]
    #   a SQL fragment for ST_Contains(), returns
    #   all geographic_items which contain the supplied geographic_item
    def reverse_containing_sql(target_column_name = nil, geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil? || target_column_name.nil?
      "ST_Contains((#{geometry_sql(geographic_item_id, source_column_name)}), #{target_column_name}::geometry)"
    end

    # @param [Integer, String]
    # @return [String]
    #   a SQL fragment that represents the geometry of the geographic item specified (which has data in the
    # source_column_name, i.e. geo_object_type)
    def geometry_sql(geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil?
      "select geom_alias_tbl.#{source_column_name}::geometry from geographic_items geom_alias_tbl " \
        "where geom_alias_tbl.id = #{geographic_item_id}"
    end

    # rubocop:disable Metrics/MethodLength
    # @param [String] column_name
    # @param [GeographicItem] geographic_item
    # @return [String] of SQL
    def is_contained_by_sql(column_name, geographic_item)
      geo_id = geographic_item.id
      geo_type = geographic_item.geo_object_type
      template = '(ST_Contains((select geographic_items.%s::geometry from geographic_items where ' \
        'geographic_items.id = %d), %s::geometry))'
      retval = []
      column_name.downcase!
      case column_name
      when 'any'
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            retval.push(template % [geo_type, geo_id, column])
          end
        }
      when 'any_poly', 'any_line'
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            if column.to_s.index(column_name.gsub('any_', ''))
              retval.push(template % [geo_type, geo_id, column])
            end
          end
        }
      else
        retval = template % [geo_type, geo_id, column_name]
      end
      retval = retval.join(' OR ') if retval.instance_of?(Array)
      retval
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #   a select query that returns a single geometry (column name 'single_geometry' for the collection of ids
    # provided via ST_Collect)
    def st_collect_sql(*geographic_item_ids)
      geographic_item_ids.flatten!
      ActiveRecord::Base.send(:sanitize_sql_for_conditions, [
        "SELECT ST_Collect(f.the_geom) AS single_geometry
     FROM (
        SELECT (ST_DUMP(#{GeographicItem::GEOMETRY_SQL.to_sql})).geom as the_geom
        FROM geographic_items
        WHERE id in (?))
      AS f", geographic_item_ids])
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #    returns one or more geographic items combined as a single geometry in column 'single'
    def single_geometry_sql(*geographic_item_ids)
      a = GeographicItem.st_collect_sql(geographic_item_ids)
      '(SELECT single.single_geometry FROM (' + a + ' ) AS single)'
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #   returns a single geometry "column" (paren wrapped) as "single" for multiple geographic item ids, or the
    # geometry as 'geometry' for a single id
    def geometry_sql2(*geographic_item_ids)
      geographic_item_ids.flatten! # *ALWAYS* reduce the pile to a single level of ids
      if geographic_item_ids.count == 1
        "(#{GeographicItem.geometry_for_sql(geographic_item_ids.first)})"
      else
        GeographicItem.single_geometry_sql(geographic_item_ids)
      end
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    def containing_where_sql(*geographic_item_ids)
      "ST_CoveredBy(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
     CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
       WHEN 'GeographicItem::Point' THEN point::geometry
       WHEN 'GeographicItem::LineString' THEN line_string::geometry
       WHEN 'GeographicItem::Polygon' THEN polygon::geometry
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
    END)"
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    def containing_where_sql_geog(*geographic_item_ids)
      "ST_CoveredBy(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
     CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geography
       WHEN 'GeographicItem::Point' THEN point::geography
       WHEN 'GeographicItem::LineString' THEN line_string::geography
       WHEN 'GeographicItem::Polygon' THEN polygon::geography
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geography
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geography
    END)"
    end

    # @param [Interger, Array of Integer] ids
    # @return [Array]
    #   If we detect that some query id has crossed the meridian, then loop through
    #   and "manually" build up a list of results.
    #   Should only be used if GeographicItem.crosses_anti_meridian_by_id? is true.
    #   Note that this does not return a Scope, so you can't chain it like contained_by?
    # TODO: test this
    def contained_by_with_antimeridian_check(*ids)
      ids.flatten! # make sure there is only one level of splat (*)
      results = []

      crossing_ids = []

      ids.each do |id|
        # push each which crosses
        crossing_ids.push(id) if GeographicItem.crosses_anti_meridian_by_id?(id)
      end

      non_crossing_ids = ids - crossing_ids
      results.push GeographicItem.contained_by(non_crossing_ids).to_a if non_crossing_ids.any?

      crossing_ids.each do |id|
        # [61666, 61661, 61659, 61654, 61639]
        q1 = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT ST_AsText((SELECT polygon FROM geographic_items ' \
                                                           'WHERE id = ?))', id])
        r = GeographicItem.where(
          # GeographicItem.contained_by_wkt_shifted_sql(GeographicItem.find(id).geo_object.to_s)
          GeographicItem.contained_by_wkt_shifted_sql(
            ApplicationRecord.connection.execute(q1).first['st_astext'])
        ).to_a
        results.push(r)
      end

      results.flatten.uniq
    end

    # @params [String] well known text
    # @return [String] the SQL fragment for the specific geometry type, shifted by longitude
    # Note: this routine is called when it is already known that the A argument crosses anti-meridian
    def contained_by_wkt_shifted_sql(wkt)
      "ST_Contains(ST_ShiftLongitude(ST_GeomFromText('#{wkt}', 4326)), (
        CASE geographic_items.type
           WHEN 'GeographicItem::MultiPolygon' THEN ST_ShiftLongitude(multi_polygon::geometry)
           WHEN 'GeographicItem::Point' THEN ST_ShiftLongitude(point::geometry)
           WHEN 'GeographicItem::LineString' THEN ST_ShiftLongitude(line_string::geometry)
           WHEN 'GeographicItem::Polygon' THEN ST_ShiftLongitude(polygon::geometry)
           WHEN 'GeographicItem::MultiLineString' THEN ST_ShiftLongitude(multi_line_string::geometry)
           WHEN 'GeographicItem::MultiPoint' THEN ST_ShiftLongitude(multi_point::geometry)
        END
        )
      )"
    end

    # TODO: Remove the hard coded 4326 reference
    # @params [String] wkt
    # @return [String] SQL fragment limiting geographics items to those in this WKT
    def contained_by_wkt_sql(wkt)
      if crosses_anti_meridian?(wkt)
        retval = contained_by_wkt_shifted_sql(wkt)
      else
        retval = "ST_Contains(ST_GeomFromText('#{wkt}', 4326), (
        CASE geographic_items.type
           WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
           WHEN 'GeographicItem::Point' THEN point::geometry
           WHEN 'GeographicItem::LineString' THEN line_string::geometry
           WHEN 'GeographicItem::Polygon' THEN polygon::geometry
           WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
           WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
        END
        )
      )"
      end
      retval
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String] sql for contained_by via ST_ContainsProperly
    # Note: Can not use GEOMETRY_SQL because geometry_collection is not supported in ST_ContainsProperly
    # Note: !! If the target GeographicItem#id crosses the anti-meridian then you may/will get unexpected results.
    def contained_by_where_sql(*geographic_item_ids)
      "ST_Contains(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
    CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
       WHEN 'GeographicItem::Point' THEN point::geometry
       WHEN 'GeographicItem::LineString' THEN line_string::geometry
       WHEN 'GeographicItem::Polygon' THEN polygon::geometry
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
    END)"
    end

    # @param [RGeo:Point] rgeo_point
    # @return [String] sql for containing via ST_CoveredBy
    # TODO: Remove the hard coded 4326 reference
    # TODO: should this be wkt_point instead of rgeo_point?
    def containing_where_for_point_sql(rgeo_point)
      "ST_CoveredBy(
      ST_GeomFromText('#{rgeo_point}', 4326),
      #{GeographicItem::GEOMETRY_SQL.to_sql}
     )"
    end

    # @param [Interger] geographic_item_id
    # @return [String] SQL for geometries
    # example, not used
    def geometry_for_sql(geographic_item_id)
      'SELECT ' + GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry FROM geographic_items WHERE id = ' \
        "#{geographic_item_id} LIMIT 1"
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String] SQL for geometries
    # example, not used
    def geometry_for_collection_sql(*geographic_item_ids)
      'SELECT ' + GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry FROM geographic_items WHERE id IN ' \
        "( #{geographic_item_ids.join(',')} )"
    end

    #
    # Scopes
    #

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [Scope]
    #    the geographic items containing these collective geographic_item ids, not including self
    def containing(*geographic_item_ids)
      where(GeographicItem.containing_where_sql(geographic_item_ids)).not_ids(*geographic_item_ids)
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [Scope]
    #    the geographic items contained by any of these geographic_item ids, not including self
    # (works via ST_ContainsProperly)
    def contained_by(*geographic_item_ids)
      where(GeographicItem.contained_by_where_sql(geographic_item_ids))
    end

    # @param [RGeo::Point] rgeo_point
    # @return [Scope]
    #    the geographic items containing this point
    # TODO: should be containing_wkt ?
    def containing_point(rgeo_point)
      where(GeographicItem.containing_where_for_point_sql(rgeo_point))
    end

    # @return [Scope]
    #   adds an area_in_meters field, with meters
    def with_area
      select("ST_Area(#{GeographicItem::GEOGRAPHY_SQL}, false) as area_in_meters")
    end

    # return [Scope]
    #   A scope that limits the result to those GeographicItems that have a collecting event
    #   through either the geographic_item or the error_geographic_item
    #
    # A raw SQL join approach for comparison
    #
    # GeographicItem.joins('LEFT JOIN georeferences g1 ON geographic_items.id = g1.geographic_item_id').
    #   joins('LEFT JOIN georeferences g2 ON geographic_items.id = g2.error_geographic_item_id').
    #   where("(g1.geographic_item_id IS NOT NULL OR g2.error_geographic_item_id IS NOT NULL)").uniq

    # @return [Scope] GeographicItem
    # This uses an Arel table approach, this is ultimately more decomposable if we need. Of use:
    #  http://danshultz.github.io/talks/mastering_activerecord_arel  <- best
    #  https://github.com/rails/arel
    #  http://stackoverflow.com/questions/4500629/use-arel-for-a-nested-set-join-query-and-convert-to-activerecordrelation
    #  http://rdoc.info/github/rails/arel/Arel/SelectManager
    #  http://stackoverflow.com/questions/7976358/activerecord-arel-or-condition
    #
    def with_collecting_event_through_georeferences
      geographic_items = GeographicItem.arel_table
      georeferences = Georeference.arel_table
      g1 = georeferences.alias('a')
      g2 = georeferences.alias('b')

      c = geographic_items.join(g1, Arel::Nodes::OuterJoin).on(geographic_items[:id].eq(g1[:geographic_item_id]))
        .join(g2, Arel::Nodes::OuterJoin).on(geographic_items[:id].eq(g2[:error_geographic_item_id]))

      GeographicItem.joins(# turn the Arel back into scope
                           c.join_sources # translate the Arel join to a join hash(?)
                          ).where(
                            g1[:id].not_eq(nil).or(g2[:id].not_eq(nil)) # returns a Arel::Nodes::Grouping
                          ).distinct
    end

    # @return [Scope] include a 'latitude' column
    def with_latitude
      select(lat_long_sql(:latitude))
    end

    # @return [Scope] include a 'longitude' column
    def with_longitude
      select(lat_long_sql(:longitude))
    end

    # @param [String, GeographicItems]
    # @return [Scope]
    def intersecting(column_name, *geographic_items)
      if column_name.downcase == 'any'
        pieces = []
        DATA_TYPES.each { |column|
          pieces.push(GeographicItem.intersecting(column.to_s, geographic_items).to_a)
        }

        # @TODO change 'id in (?)' to some other sql construct

        GeographicItem.where(id: pieces.flatten.map(&:id))
      else
        q = geographic_items.flatten.collect { |geographic_item|
          "ST_Intersects(#{column_name}, '#{geographic_item.geo_object}'    )" # seems like we want this: http://danshultz.github.io/talks/mastering_activerecord_arel/#/15/2
        }.join(' or ')

        where(q)
      end
    end

    # @param [GeographicItem#id] geographic_item_id
    # @param [Float] distance in meters ?!?!
    # @return [ActiveRecord::Relation]
    # !! should be distance, not radius?!
    def within_radius_of_item(geographic_item_id, distance)
      where(within_radius_of_item_sql(geographic_item_id, distance))
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    #   a SQL fragment for ST_DISJOINT, specifies all geographic_items that have data in column_name
    #   that are disjoint from the passed geographic_items
    def disjoint_from(column_name, *geographic_items)
      q = geographic_items.flatten.collect { |geographic_item|
        "ST_DISJOINT(#{column_name}::geometry, (#{geometry_sql(geographic_item.to_param,
                                                             geographic_item.geo_object_type)}))"
      }.join(' and ')

      where(q)
    end

    # @return [Scope]
    #   see are_contained_in_item_by_id
    # @param [String] column_name
    # @param [GeographicItem, Array] geographic_items
    def are_contained_in_item(column_name, *geographic_items)
      are_contained_in_item_by_id(column_name, geographic_items.flatten.map(&:id))
    end

    # rubocop:disable Metrics/MethodLength
    # @param [String] column_name to search
    # @param [GeographicItem] geographic_item_ids or array of geographic_item_ids to be tested.
    # @return [Scope] of GeographicItems
    #
    # If this scope is given an Array of GeographicItems as a second parameter,
    # it will return the 'OR' of each of the objects against the table.
    # SELECT COUNT(*) FROM "geographic_items"
    #        WHERE (ST_Contains(polygon::geometry, GeomFromEWKT('srid=4326;POINT (0.0 0.0 0.0)'))
    #               OR ST_Contains(polygon::geometry, GeomFromEWKT('srid=4326;POINT (-9.8 5.0 0.0)')))
    #
    def are_contained_in_item_by_id(column_name, *geographic_item_ids) # = containing
      geographic_item_ids.flatten! # in case there is a array of arrays, or multiple objects
      column_name.downcase!
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.are_contained_in_item_by_id(column.to_s, geographic_item_ids).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))
      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          if column.to_s.index(column_name.gsub('any_', ''))
            part.push(GeographicItem.are_contained_in_item_by_id("#{column}", geographic_item_ids).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))
      else
        q = geographic_item_ids.flatten.collect { |geographic_item_id|
          # discover the item types, and convert type to database type for 'multi_'
          b = GeographicItem.where(id: geographic_item_id)
            .pluck(:type)[0].split(':')[2].downcase.gsub('lti', 'lti_')
          # a = GeographicItem.find(geographic_item_id).geo_object_type
          GeographicItem.containing_sql(column_name, geographic_item_id, b)
        }.join(' or ')
        q = 'FALSE' if q.blank? # this will prevent the invocation of *ALL* of the GeographicItems, if there are
        # no GeographicItems in the request (see CollectingEvent.name_hash(types)).
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:enable Metrics/MethodLength

    # @param [String] column_name
    # @param [String] geometry of WKT
    # @return [Scope]
    # a single WKT geometry is compared against column or columns (except geometry_collection) to find geographic_items
    # which are contained in the WKT
    def are_contained_in_wkt(column_name, geometry)
      column_name.downcase!
      # column_name = 'point'
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.are_contained_in_wkt(column.to_s, geometry).pluck(:id).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten)
      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          if column.to_s.index(column_name.gsub('any_', ''))
            part.push(GeographicItem.are_contained_in_wkt("#{column}", geometry).pluck(:id).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten)
      else
        # column = points, geometry = square
        q = "ST_Contains(ST_GeomFromEWKT('srid=4326;#{geometry}'), #{column_name}::geometry)"
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:disable Metrics/MethodLength
    #    containing the items the shape of which is contained in the geographic_item[s] supplied.
    # @param column_name [String] can be any of DATA_TYPES, or 'any' to check against all types, 'any_poly' to check
    # against 'polygon' or 'multi_polygon', or 'any_line' to check against 'line_string' or 'multi_line_string'.
    #  CANNOT be 'geometry_collection'.
    # @param geographic_items [GeographicItem] Can be a single GeographicItem, or an array of GeographicItem.
    # @return [Scope]
    def is_contained_by(column_name, *geographic_items)
      column_name.downcase!
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.is_contained_by(column.to_s, geographic_items).to_a)
          end
        }
        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))

      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            if column.to_s.index(column_name.gsub('any_', ''))
              part.push(GeographicItem.is_contained_by(column.to_s, geographic_items).to_a)
            end
          end
        }
        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))

      else
        q = geographic_items.flatten.collect { |geographic_item|
          GeographicItem.reverse_containing_sql(column_name, geographic_item.to_param,
                                                geographic_item.geo_object_type)
        }.join(' or ')
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:enable Metrics/MethodLength

    # @param [String, GeographicItem]
    # @return [Scope]
    def ordered_by_shortest_distance_from(column_name, geographic_item)
      if true # check_geo_params(column_name, geographic_item)
        select_distance_with_geo_object(column_name, geographic_item)
          .where_distance_greater_than_zero(column_name, geographic_item).order('distance')
      else
        where('false')
      end
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    def ordered_by_longest_distance_from(column_name, geographic_item)
      if true # check_geo_params(column_name, geographic_item)
        q = select_distance_with_geo_object(column_name, geographic_item)
          .where_distance_greater_than_zero(column_name, geographic_item)
          .order('distance desc')
        q
      else
        where('false')
      end
    end

    # @param [String] column_name
    # @param [GeographicItem] geographic_item
    # @return [String]
    def select_distance_with_geo_object(column_name, geographic_item)
      select("*, ST_Distance(#{column_name}, GeomFromEWKT('srid=4326;#{geographic_item.geo_object}')) as distance")
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    def where_distance_greater_than_zero(column_name, geographic_item)
      where("#{column_name} is not null and ST_Distance(#{column_name}, " \
            "GeomFromEWKT('srid=4326;#{geographic_item.geo_object}')) > 0")
    end

    # @param [GeographicItem]
    # @return [Scope]
    def not_including(geographic_items)
      where.not(id: geographic_items)
    end

    # @return [Scope]
    #   includes an 'is_valid' attribute (True/False) for the passed geographic_item.  Uses St_IsValid.
    # @param [RGeo object] geographic_item
    def with_is_valid_geometry_column(geographic_item)
      where(id: geographic_item.id).select("ST_IsValid(ST_AsBinary(#{geographic_item.geo_object_type})) is_valid")
    end

    #
    # Other
    #

    # @param [Integer] geographic_item_id1
    # @param [Integer] geographic_item_id2
    # @return [Float]
    def distance_between(geographic_item_id1, geographic_item_id2)
      q1 = "ST_Distance(#{GeographicItem::GEOGRAPHY_SQL}, " \
        "(#{select_geography_sql(geographic_item_id2)})) as distance"
      _q2 = ActiveRecord::Base.send(
        :sanitize_sql_array, ['ST_Distance(?, (?)) as distance',
                              GeographicItem::GEOGRAPHY_SQL,
                              select_geography_sql(geographic_item_id2)])
      GeographicItem.where(id: geographic_item_id1).pluck(Arel.sql(q1)).first
    end

    # @param [RGeo::Point] point
    # @return [Hash]
    #   as per #inferred_geographic_name_hierarchy but for Rgeo point
    def point_inferred_geographic_name_hierarchy(point)
      GeographicItem.containing_point(point).order(cached_total_area: :ASC).limit(1).first&.inferred_geographic_name_hierarchy
    end

    # @param [String] type_name ('polygon', 'point', 'line' [, 'circle'])
    # @return [String] if type
    def eval_for_type(type_name)
      retval = 'GeographicItem'
      case type_name.upcase
      when 'POLYGON'
        retval += '::Polygon'
      when 'LINESTRING'
        retval += '::LineString'
      when 'POINT'
        retval += '::Point'
      when 'MULTIPOLYGON'
        retval += '::MultiPolygon'
      when 'MULTILINESTRING'
        retval += '::MultiLineString'
      when 'MULTIPOINT'
        retval += '::MultiPoint'
      else
        retval = nil
      end
      retval
    end

    # example, not used
    # @param [Integer] geographic_item_id
    # @return [RGeo::Geographic object]
    def geometry_for(geographic_item_id)
      GeographicItem.select(GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry').find(geographic_item_id)['geometry']
    end

    # example, not used
    # @param [Integer, Array] geographic_item_ids
    # @return [Scope]
    def st_multi(*geographic_item_ids)
      GeographicItem.find_by_sql(
        "SELECT ST_Multi(ST_Collect(g.the_geom)) AS singlegeom
     FROM (
        SELECT (ST_DUMP(#{GeographicItem::GEOMETRY_SQL.to_sql})).geom AS the_geom
        FROM geographic_items
        WHERE id IN (?))
      AS g;", geographic_item_ids.flatten
      )
    end
  end # class << self

  # @return [Hash]
  #    a geographic_name_classification or empty Hash
  # This is a quick approach that works only when
  # the geographic_item is linked explicitly to a GeographicArea.
  #
  # !! Note that it is not impossible for a GeographicItem to be linked
  # to > 1 GeographicArea, in that case we are assuming that all are
  # equally refined, this might not be the case in the future because
  # of how the GeographicArea gazetteer is indexed.
  def quick_geographic_name_hierarchy
    if a = geographic_areas.first
      a.geographic_name_classification
    else
      {}
    end
  end

  # @return [Hash]
  #   a geographic_name_classification (see GeographicArea) inferred by
  # finding the smallest area containing this GeographicItem, in the most accurate gazetteer
  # and using it to return country/state/county. See also the logic in
  # filling in missing levels in GeographicArea.
  def inferred_geographic_name_hierarchy
    if small_area = containing_geographic_areas
      .joins(:geographic_areas_geographic_items)
      .merge(GeographicAreasGeographicItem.ordered_by_data_origin)
      .ordered_by_area
      .limit(1)
      .first
    return small_area.geographic_name_classification
    else
      {}
    end
  end

  # @return [Scope]
  #   the Geographic Areas that contain (gis) this geographic item
  def containing_geographic_areas
    GeographicArea.joins(:geographic_items).includes(:geographic_area_type)
      .joins("JOIN (#{GeographicItem.containing(id).to_sql}) j on geographic_items.id = j.id")
  end

  # @return [Boolean]
  #   whether stored shape is ST_IsValid
  def valid_geometry?
    GeographicItem.with_is_valid_geometry_column(self).first['is_valid']
  end

  # @return [Array of latitude, longitude]
  #    the lat, lon of the first point in the GeoItem, see subclass for #st_start_point
  def start_point
    o = st_start_point
    [o.y, o.x]
  end

  # @return [Array]
  #   the lat, long, as STRINGs for the centroid of this geographic item
  def center_coords
    r = GeographicItem.find_by_sql(
      "Select split_part(ST_AsLatLonText(ST_Centroid(#{GeographicItem::GEOMETRY_SQL.to_sql}), " \
      "'D.DDDDDD'), ' ', 1) latitude, split_part(ST_AsLatLonText(ST_Centroid" \
      "(#{GeographicItem::GEOMETRY_SQL.to_sql}), 'D.DDDDDD'), ' ', 2) " \
      "longitude from geographic_items where id = #{id};")[0]

    [r.latitude, r.longitude]
  end

  # @return [RGeo::Geographic::ProjectedPointImpl]
  #    representing the centroid of this geographic item
  def centroid
    # Gis::FACTORY.point(*center_coords.reverse)
    return geo_object if type == 'GeographicItem::Point'
    return Gis::FACTORY.parse_wkt(self.st_centroid)
  end

  # @param [Integer] geographic_item_id
  # @return [Double] distance in meters (slower, more accurate)
  def st_distance(geographic_item_id) # geo_object
    q1 = "ST_Distance((#{GeographicItem.select_geography_sql(id)}), " \
      "(#{GeographicItem.select_geography_sql(geographic_item_id)})) as d"
    _q2 = ActiveRecord::Base.send(:sanitize_sql_array, ['ST_Distance((?),(?)) as d',
                                                        GeographicItem.select_geography_sql(self.id),
                                                        GeographicItem.select_geography_sql(geographic_item_id)])
    deg = GeographicItem.where(id: id).pluck(Arel.sql(q1)).first
    deg * Utilities::Geo::ONE_WEST
  end

  # @param [GeographicItem] geographic_item
  # @return [Double] distance in meters
  # Like st_distance but works with changed and non persisted objects
  def st_distance_to_geographic_item(geographic_item)
    unless !persisted? || changed?
      a = "(#{GeographicItem.select_geography_sql(id)})"
    else
      a = "ST_GeographyFromText('#{geo_object.to_s}')"
    end

    unless !geographic_item.persisted? || geographic_item.changed?
      b = "(#{GeographicItem.select_geography_sql(geographic_item.id)})"
    else
      b = "ST_GeographyFromText('#{geographic_item.geo_object.to_s}')"
    end

    ActiveRecord::Base.connection.select_value("SELECT ST_Distance(#{a}, #{b})")
  end

  alias_method :distance_to, :st_distance

  # @param [Integer] geographic_item_id
  # @return [Double] distance in meters (faster, less accurate)
  def st_distance_spheroid(geographic_item_id)
    q1 = "ST_DistanceSpheroid((#{GeographicItem.select_geometry_sql(id)})," \
      "(#{GeographicItem.select_geometry_sql(geographic_item_id)}),'#{Gis::SPHEROID}') as distance"
    _q2 = ActiveRecord::Base.send(:sanitize_sql_array,
                                  ['ST_DistanceSpheroid((?),(?),?) as distance',
                                   GeographicItem.select_geometry_sql(id),
                                   GeographicItem.select_geometry_sql(geographic_item_id),
                                   Gis::SPHEROID])
    # TODO: what is _q2?
    GeographicItem.where(id: id).pluck(Arel.sql(q1)).first
  end

  # @return [String]
  #   a WKT POINT representing the centroid of the geographic item
  def st_centroid
    GeographicItem.where(id: to_param).pluck(Arel.sql("ST_AsEWKT(ST_Centroid(#{GeographicItem::GEOMETRY_SQL.to_sql}))")).first.gsub(/SRID=\d*;/, '')
  end

  # @return [Integer]
  #   the number of points in the geometry
  def st_npoints
    GeographicItem.where(id: id).pluck(Arel.sql("ST_NPoints(#{GeographicItem::GEOMETRY_SQL.to_sql}) as npoints")).first
  end

  # @return [Symbol]
  #   the geo type (i.e. column like :point, :multipolygon).  References the first-found object,
  # according to the list of DATA_TYPES, or nil
  def geo_object_type
    if self.class.name == 'GeographicItem' # a proxy check for new records
      geo_type
    else
      self.class::SHAPE_COLUMN
    end
  end

  # !!TODO: migrate these to use native column calls

  # @return [RGeo instance, nil]
  #  the Rgeo shape (See http://rubydoc.info/github/dazuma/rgeo/RGeo/Feature)
  def geo_object
    begin
      if r = geo_object_type # rubocop:disable Lint/AssignmentInCondition
        send(r)
      else
        false
      end
    rescue RGeo::Error::InvalidGeometry
      return nil # TODO: we need to render proper error for this!
    end
  end

  # @param [geo_object]
  # @return [Boolean]
  def contains?(target_geo_object)
    return nil if target_geo_object.nil?
    self.geo_object.contains?(target_geo_object)
  end

  # @param [geo_object]
  # @return [Boolean]
  def within?(target_geo_object)
    self.geo_object.within?(target_geo_object)
  end

  # @param [geo_object]
  # @return [Boolean]
  def intersects?(target_geo_object)
    self.geo_object.intersects?(target_geo_object)
  end

  # @param [geo_object, Double]
  # @return [Boolean]
  def near(target_geo_object, distance)
    self.geo_object.buffer(distance).contains?(target_geo_object)
  end

  # @param [geo_object, Double]
  # @return [Boolean]
  def far(target_geo_object, distance)
    !near(target_geo_object, distance)
  end

  # @return [GeoJSON hash]
  #    via Rgeo apparently necessary for GeometryCollection
  def rgeo_to_geo_json
    RGeo::GeoJSON.encode(geo_object).to_json
  end

  # @return [Hash]
  #   in GeoJSON format
  #   Computed via "raw" PostGIS (much faster). This
  #   requires the geo_object_type and id.
  #
  def to_geo_json
    JSON.parse(
      GeographicItem.connection.select_one(
        "SELECT ST_AsGeoJSON(#{geo_object_type}::geometry) a " \
        "FROM geographic_items WHERE id=#{id};")['a'])
  end

  # We don't need to serialize to/from JSON
  def to_geo_json_string
    GeographicItem.connection.select_one(
      "SELECT ST_AsGeoJSON(#{geo_object_type}::geometry) a " \
      "FROM geographic_items WHERE id=#{id};")['a']
  end

  # @return [Hash]
  #   the shape as a GeoJSON Feature with some item metadata
  def to_geo_json_feature
    @geometry ||= to_geo_json
    {'type' => 'Feature',
     'geometry' => geometry,
     'properties' => {
       'geographic_item' => {
         'id' => id}
     }
    }
  end

  # '{"type":"Feature","geometry":{"type":"Point","coordinates":[2.5,4.0]},"properties":{"color":"red"}}'
  # '{"type":"Feature","geometry":{"type":"Polygon","coordinates":"[[[-125.29394388198853, 48.584480409793],
  # [-67.11035013198853, 45.09937589848195],[-80.64550638198853, 25.01924647619111],[-117.55956888198853,
  # 32.5591595028449],[-125.29394388198853, 48.584480409793]]]"},"properties":{}}'
  # @param [String] value
  # @return [Boolean, RGeo object]
  def shape=(value)
    unless value.blank?
      geom = RGeo::GeoJSON.decode(value, json_parser: :json)
      this_type = JSON.parse(value)['geometry']['type']

      # TODO: isn't this set automatically? Or perhaps the callback isn't hit in this approach?
      self.type = GeographicItem.eval_for_type(this_type) unless geom.nil?

      raise('GeographicItem.type not set.') if type.blank?

      object = nil

      begin
        object = Gis::FACTORY.parse_wkt(geom.geometry.to_s)
      rescue RGeo::Error::InvalidGeometry
        return false
      end

      write_attribute(this_type.underscore.to_sym, object)
      geom
    end
  end

  # @return [String]
  def to_wkt
    #  10k  #<Benchmark::Tms:0x00007fb0dfd30fd0 @label="", @real=25.237487000005785, @cstime=0.0, @cutime=0.0, @stime=1.1704609999999995, @utime=5.507929999999988, @total=6.678390999999987>
    #  GeographicItem.select("ST_AsText( #{GeographicItem::GEOMETRY_SQL.to_sql}) wkt").where(id: id).first.wkt

    # 10k <Benchmark::Tms:0x00007fb0e02f7540 @label="", @real=21.619827999995323, @cstime=0.0, @cutime=0.0, @stime=0.8850890000000007, @utime=3.2958549999999605, @total=4.180943999999961>
    if a =  ApplicationRecord.connection.execute( "SELECT ST_AsText( #{GeographicItem::GEOMETRY_SQL.to_sql} ) wkt from geographic_items where geographic_items.id = #{id}").first
      return a['wkt']
    else
      return nil
    end
  end

  # return float, in meters, calculated, not from cached
  def area
    GeographicItem.where(id: id).select("ST_Area(#{GeographicItem::GEOGRAPHY_SQL}, false) as area_in_meters").first['area_in_meters']
  end

  private

  def set_cached
    update_column(:cached_total_area, area)
  end

  protected

  # @return [Symbol]
  #   returns the attribute (column name) containing data
  #   nearly all methods should use #geo_object_type, not geo_type
  def geo_type
    DATA_TYPES.each { |item|
      return item if send(item)
    }
    nil
  end

  # @return [Boolean, String] false if already set, or type to which it was set
  def set_type_if_geography_present
    if type.blank?
      column = geo_type
      self.type = "GeographicItem::#{column.to_s.camelize}" if column
    end
  end

  # @param [RGeo::Point] point
  # @return [Array] of a point
  #   Longitude |, Latitude -
  def point_to_a(point)
    data = []
    data.push(point.x, point.y)
    data
  end

  # @param [RGeo::Point] point
  # @return [Hash] of a point
  def point_to_hash(point)
    {points: [point_to_a(point)]}
  end

  # @param [RGeo::MultiPoint] multi_point
  # @return [Array] of points
  def multi_point_to_a(multi_point)
    data = []
    multi_point.each { |point|
      data.push([point.x, point.y])
    }
    data
  end

  # @return [Hash] of points
  def multi_point_to_hash(_multi_point)
    # when we encounter a multi_point type, we only stick the points into the array, NOT it's identity as a group
    {points: multi_point_to_a(multi_point)}
  end

  # @param [Reo::LineString] line_string
  # @return [Array] of points in the line
  def line_string_to_a(line_string)
    data = []
    line_string.points.each { |point|
      data.push([point.x, point.y])
    }
    data
  end

  # @param [Reo::LineString] line_string
  # @return [Hash] of points in the line
  def line_string_to_hash(line_string)
    {lines: [line_string_to_a(line_string)]}
  end

  # @param [RGeo::Polygon] polygon
  # @return [Array] of points in the polygon (exterior_ring ONLY)
  def polygon_to_a(polygon)
    # TODO: handle other parts of the polygon; i.e., the interior_rings (if they exist)
    data = []
    polygon.exterior_ring.points.each { |point|
      data.push([point.x, point.y])
    }
    data
  end

  # @param [RGeo::Polygon] polygon
  # @return [Hash] of points in the polygon (exterior_ring ONLY)
  def polygon_to_hash(polygon)
    {polygons: [polygon_to_a(polygon)]}
  end

  # @return [Array] of line_strings as arrays of points
  # @param [RGeo::MultiLineString] multi_line_string
  def multi_line_string_to_a(multi_line_string)
    data = []
    multi_line_string.each { |line_string|
      line_data = []
      line_string.points.each { |point|
        line_data.push([point.x, point.y])
      }
      data.push(line_data)
    }
    data
  end

  # @return [Hash] of line_strings as hashes of points
  def multi_line_string_to_hash(_multi_line_string)
    {lines: to_a}
  end

  # @param [RGeo::MultiPolygon] multi_polygon
  # @return [Array] of arrays of points in the polygons (exterior_ring ONLY)
  def multi_polygon_to_a(multi_polygon)
    data = []
    multi_polygon.each { |polygon|
      polygon_data = []
      polygon.exterior_ring.points.each { |point|
        polygon_data.push([point.x, point.y])
      }
      data.push(polygon_data)
    }
    data
  end

  # @return [Hash] of hashes of points in the polygons (exterior_ring ONLY)
  def multi_polygon_to_hash(_multi_polygon)
    {polygons: to_a}
  end

  # @return [Boolean] iff there is one and only one shape column set
  def some_data_is_provided
    data = []

    DATA_TYPES.each do |item|
      data.push(item) unless send(item).blank?
    end

    case data.count
    when 0
      errors.add(:base, 'No shape provided or provided shape is invalid')
    when 1
      return true
    else
      data.each do |object|
        errors.add(object, 'More than one shape type provided')
      end
    end
    true
  end
end

#multi_point ⇒ RGeo::Geographic::ProjectedMultiPointImpl

Returns:

• (RGeo::Geographic::ProjectedMultiPointImpl)

# File 'app/models/geographic_item.rb', line 34

class GeographicItem < ApplicationRecord
  include Housekeeping::Users
  include Housekeeping::Timestamps
  include Shared::IsData
  include Shared::SharedAcrossProjects

  # @return [Hash, nil]
  # An internal variable for use in super calls, holds a Hash in GeoJSON format (temporarily)
  attr_accessor :geometry

  # @return [Boolean, RGeo object]
  # @params value [Hash in GeoJSON format] ?!
  # TODO: WHY! boolean not nil, or object
  # Used to build geographic items from a shape [ of what class ] !?
  attr_accessor :shape

  # @return [Boolean]
  #   When true cached values are not built
  attr_accessor :no_cached

  DATA_TYPES = [
    :point,
    :line_string,
    :polygon,
    :multi_point,
    :multi_line_string,
    :multi_polygon,
    :geometry_collection
  ].freeze

  GEOMETRY_SQL = Arel::Nodes::Case.new(arel_table[:type])
    .when('GeographicItem::MultiPolygon').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_polygon].as('geometry')]))
    .when('GeographicItem::Point').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:point].as('geometry')]))
    .when('GeographicItem::LineString').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:line_string].as('geometry')]))
    .when('GeographicItem::Polygon').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:polygon].as('geometry')]))
    .when('GeographicItem::MultiLineString').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_line_string].as('geometry')]))
    .when('GeographicItem::MultiPoint').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_point].as('geometry')]))
    .when('GeographicItem::GeometryCollection').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:geometry_collection].as('geometry')]))
    .freeze

  GEOGRAPHY_SQL = "CASE geographic_items.type
    WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon
    WHEN 'GeographicItem::Point' THEN point
    WHEN 'GeographicItem::LineString' THEN line_string
    WHEN 'GeographicItem::Polygon' THEN polygon
    WHEN 'GeographicItem::MultiLineString' THEN multi_line_string
    WHEN 'GeographicItem::MultiPoint' THEN multi_point
    WHEN 'GeographicItem::GeometryCollection' THEN geometry_collection
    END".freeze

  # ANTI_MERIDIAN = '0X0102000020E61000000200000000000000008066400000000000405640000000000080664000000000004056C0'
  ANTI_MERIDIAN = 'LINESTRING (180 89.0, 180 -89)'.freeze

  has_many :geographic_areas_geographic_items, dependent: :destroy, inverse_of: :geographic_item
  has_many :geographic_areas, through: :geographic_areas_geographic_items
  has_many :asserted_distributions, through: :geographic_areas
  has_many :geographic_area_types, through: :geographic_areas
  has_many :parent_geographic_areas, through: :geographic_areas, source: :parent
  has_many :gadm_geographic_areas, class_name: 'GeographicArea', foreign_key: :gadm_geo_item_id
  has_many :ne_geographic_areas, class_name: 'GeographicArea', foreign_key: :ne_geo_item_id
  has_many :tdwg_geographic_areas, class_name: 'GeographicArea', foreign_key: :tdwg_geo_item_id
  has_many :georeferences, inverse_of: :geographic_item
  has_many :georeferences_through_error_geographic_item,
    class_name: 'Georeference', foreign_key: :error_geographic_item_id, inverse_of: :error_geographic_item
  has_many :collecting_events_through_georeferences, through: :georeferences, source: :collecting_event
  has_many :collecting_events_through_georeference_error_geographic_item,
    through: :georeferences_through_error_geographic_item, source: :collecting_event

  # TODO: THIS IS NOT GOOD
  before_validation :set_type_if_geography_present

  validate :some_data_is_provided
  validates :type, presence: true

  scope :include_collecting_event, -> { includes(:collecting_events_through_georeferences) }
  scope :geo_with_collecting_event, -> { joins(:collecting_events_through_georeferences) }
  scope :err_with_collecting_event, -> { joins(:georeferences_through_error_geographic_item) }

  after_save :set_cached, unless: Proc.new {|n| n.no_cached || errors.any? }

  class << self

    def st_union(geographic_item_scope)
      GeographicItem.select("ST_Union(#{GeographicItem::GEOMETRY_SQL.to_sql}) as collection")
        .where(id: geographic_item_scope.pluck(:id))
    end

    def st_collect(geographic_item_scope)
      GeographicItem.select("ST_Collect(#{GeographicItem::GEOMETRY_SQL.to_sql}) as collection")
        .where(id: geographic_item_scope.pluck(:id))
    end

    # @return [GeographicItem::ActiveRecord_Relation]
    # @params [Array] array of geographic area ids
    def default_by_geographic_area_ids(geographic_area_ids = [])
      GeographicItem.
        joins(:geographic_areas_geographic_items).
        merge(::GeographicAreasGeographicItem.default_geographic_item_data).
        where(geographic_areas_geographic_items: {geographic_area_id: geographic_area_ids})
    end

    # @param [String] wkt
    # @return [Boolean]
    #   whether or not the wtk intersects with the anti-meridian
    #   !! StrongParams security considerations
    def crosses_anti_meridian?(wkt)
      GeographicItem.find_by_sql(
        ['SELECT ST_Intersects(ST_GeogFromText(?), ST_GeogFromText(?)) as r;', wkt, ANTI_MERIDIAN]
      ).first.r
    end

    # @param [Integer] ids
    # @return [Boolean]
    #   whether or not any GeographicItem passed intersects the anti-meridian
    #   !! StrongParams security considerations
    #   This is our first line of defense against queries that define multiple shapes, one or
    #   more of which crosses the anti-meridian.  In this case the current TW strategy within the
    #   UI is to abandon the search, and prompt the user to refactor the query.
    def crosses_anti_meridian_by_id?(*ids)
      q1 = ["SELECT ST_Intersects((SELECT single_geometry FROM (#{GeographicItem.single_geometry_sql(*ids)}) as " \
            'left_intersect), ST_GeogFromText(?)) as r;', ANTI_MERIDIAN]
      _q2 = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT ST_Intersects((SELECT single_geometry FROM (?) as ' \
                                                          'left_intersect), ST_GeogFromText(?)) as r;', GeographicItem.single_geometry_sql(*ids), ANTI_MERIDIAN])
      GeographicItem.find_by_sql(q1).first.r
    end

    # TODO: * rename to reflect either/or and what is being returned
    # @param [Integer] geographic_area_ids
    # @param [String] shape_in in JSON (POINT, POLYGON, MULTIPOLYGON), usually from GoogleMaps
    # @param [String] search_object_class
    # @param [Integer] project_id for search_object_class
    # @return [Scope] of the requested search_object_type
    def gather_geographic_area_or_shape_data(geographic_area_ids, shape_in, search_object_class, project_id)
      if shape_in.blank?
        # get the shape from the geographic area, if possible
        finding = search_object_class.constantize
        target_geographic_item_ids = []
        if geographic_area_ids.blank?
          found = finding.none
        else
          # now use method from collection_object_filter_query
          geographic_area_ids.each do |gaid|
            target_geographic_item_ids.push(GeographicArea.joins(:geographic_items)
              .find(gaid)
              .default_geographic_item.id)
          end

          # TODO: There probably is a better way to do this, but for now...
          f1 = project_id.present? ? finding.with_project_id(project_id) : finding
          case search_object_class
          when /Collection/, /Collecting/
            found = f1.joins(:geographic_items)
              .where(GeographicItem.contained_by_where_sql(target_geographic_item_ids))
          when /Asserted/
            # TODO: Figure out how to see through this group of geographic_items to the ones which contain
            # geographic_items which are associated with geographic_areas (as #default_geographic_items)
            # which are associated with asserted_distributions
            found = f1.joins(:geographic_area).joins(:geographic_items)
              .where(GeographicItem.contained_by_where_sql(target_geographic_item_ids))
          else
          end
        end
      else
        found = gather_map_data(shape_in, search_object_class, project_id)
      end
      found
    end

    # TODO: likely not ussed any more
    #
    # @param [String] feature in JSON, looks like '{"type":"Feature","geometry":{"type":"Polygon",
    # "coordinates":[[[-40.078125,10.614539227964332],[-49.21875,-17.185577279306226],
    # [-23.203125,-15.837353550148276],[-40.078125,10.614539227964332]]]},"properties":{}}'
    # @param [String] search_object_class
    # @param [Integer, Nil] project_id for search_object_class
    # @return [Scope] of the requested search_object_type
    #   This function takes a feature, i.e. a string that is the result
    #   of drawing on a Google map, and submited as a form variable,
    #   and translates that to a scope for a provided search_object_class.
    #   e.g. Return all CollectionObjects in this drawn area
    #        Return all CollectionObjects in the radius around this point
    def gather_map_data(feature, search_object_class, project_id)
      finding = search_object_class.constantize
      g_feature = RGeo::GeoJSON.decode(feature, json_parser: :json)
      if g_feature.nil?
        finding.none
      else
        geometry = g_feature.geometry # isolate the WKT
        shape_type = geometry.geometry_type.to_s.downcase
        geometry = geometry.as_text
        radius = g_feature['radius']

        query = project_id.present? ?
          finding.with_project_id(project_id).joins(:geographic_items) :
          finding.joins(:geographic_items)

        case shape_type
        when 'point'
          query.where(GeographicItem.within_radius_of_wkt_sql(geometry, radius)) # was interesecting version.
        when 'polygon', 'multipolygon'
          query.where(GeographicItem.contained_by_wkt_sql(geometry))
        else
          query
        end
      end
    end

    #
    # SQL fragments
    #

    # @param [Integer, String]
    # @return [String]
    #   a SQL select statement that returns the *geometry* for the geographic_item with the specified id
    def select_geometry_sql(geographic_item_id)
      "SELECT #{GeographicItem::GEOMETRY_SQL.to_sql} from geographic_items where geographic_items.id = #{geographic_item_id}"
    end

    # @param [Integer, String]
    # @return [String]
    #   a SQL select statement that returns the geography for the geographic_item with the specified id
    def select_geography_sql(geographic_item_id)
      ActiveRecord::Base.send(:sanitize_sql_for_conditions, [
        "SELECT #{GeographicItem::GEOMETRY_SQL.to_sql} from geographic_items where geographic_items.id = ?",
        geographic_item_id])
    end

    # @param [Symbol] choice
    # @return [String]
    #   a fragment returning either latitude or longitude columns
    def lat_long_sql(choice)
      return nil unless [:latitude, :longitude].include?(choice)
      f = "'D.DDDDDD'" # TODO: probably a constant somewhere
      v = (choice == :latitude ? 1 : 2)
      "CASE type
      WHEN 'GeographicItem::GeometryCollection' THEN split_part(ST_AsLatLonText(ST_Centroid" \
      "(geometry_collection::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::LineString' THEN split_part(ST_AsLatLonText(ST_Centroid(line_string::geometry), " \
      "#{f}), ' ', #{v})
      WHEN 'GeographicItem::MultiPolygon' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_polygon::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::Point' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(point::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::Polygon' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(polygon::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::MultiLineString' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_line_string::geometry), #{f} ), ' ', #{v})
      WHEN 'GeographicItem::MultiPoint' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_point::geometry), #{f}), ' ', #{v})
    END as #{choice}"
    end

    # @param [Integer] geographic_item_id
    # @param [Integer] distance
    # @return [String]
    def within_radius_of_item_sql(geographic_item_id, distance)
      "ST_DWithin((#{GeographicItem::GEOGRAPHY_SQL}), (#{select_geography_sql(geographic_item_id)}), #{distance})"
    end

    # TODO: 3D is overkill here
    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [String]
    # !! This is intersecting
    def intersecting_radius_of_wkt_sql(wkt, distance)
      "ST_DWithin((#{GeographicItem::GEOGRAPHY_SQL}), ST_Transform( ST_GeomFromText('#{wkt}', " \
        "4326), 4326), #{distance})"
    end

    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [String]
    # !! This is fully covering
    def within_radius_of_wkt_sql(wkt, distance)
      "ST_Covers( ST_Buffer(ST_SetSRID( ST_GeomFromText('#{wkt}'), 4326)::geography, #{distance}), (#{GeographicItem::GEOGRAPHY_SQL}))"
    end

    # @param [String, Integer, String]
    # @return [String]
    #   a SQL fragment for ST_Contains() function, returns
    #   all geographic items which are contained in the item supplied
    def containing_sql(target_column_name = nil, geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil? || target_column_name.nil?
      "ST_Contains(#{target_column_name}::geometry, (#{geometry_sql(geographic_item_id, source_column_name)}))"
    end

    # @param [String, Integer, String]
    # @return [String]
    #   a SQL fragment for ST_Contains(), returns
    #   all geographic_items which contain the supplied geographic_item
    def reverse_containing_sql(target_column_name = nil, geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil? || target_column_name.nil?
      "ST_Contains((#{geometry_sql(geographic_item_id, source_column_name)}), #{target_column_name}::geometry)"
    end

    # @param [Integer, String]
    # @return [String]
    #   a SQL fragment that represents the geometry of the geographic item specified (which has data in the
    # source_column_name, i.e. geo_object_type)
    def geometry_sql(geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil?
      "select geom_alias_tbl.#{source_column_name}::geometry from geographic_items geom_alias_tbl " \
        "where geom_alias_tbl.id = #{geographic_item_id}"
    end

    # rubocop:disable Metrics/MethodLength
    # @param [String] column_name
    # @param [GeographicItem] geographic_item
    # @return [String] of SQL
    def is_contained_by_sql(column_name, geographic_item)
      geo_id = geographic_item.id
      geo_type = geographic_item.geo_object_type
      template = '(ST_Contains((select geographic_items.%s::geometry from geographic_items where ' \
        'geographic_items.id = %d), %s::geometry))'
      retval = []
      column_name.downcase!
      case column_name
      when 'any'
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            retval.push(template % [geo_type, geo_id, column])
          end
        }
      when 'any_poly', 'any_line'
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            if column.to_s.index(column_name.gsub('any_', ''))
              retval.push(template % [geo_type, geo_id, column])
            end
          end
        }
      else
        retval = template % [geo_type, geo_id, column_name]
      end
      retval = retval.join(' OR ') if retval.instance_of?(Array)
      retval
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #   a select query that returns a single geometry (column name 'single_geometry' for the collection of ids
    # provided via ST_Collect)
    def st_collect_sql(*geographic_item_ids)
      geographic_item_ids.flatten!
      ActiveRecord::Base.send(:sanitize_sql_for_conditions, [
        "SELECT ST_Collect(f.the_geom) AS single_geometry
     FROM (
        SELECT (ST_DUMP(#{GeographicItem::GEOMETRY_SQL.to_sql})).geom as the_geom
        FROM geographic_items
        WHERE id in (?))
      AS f", geographic_item_ids])
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #    returns one or more geographic items combined as a single geometry in column 'single'
    def single_geometry_sql(*geographic_item_ids)
      a = GeographicItem.st_collect_sql(geographic_item_ids)
      '(SELECT single.single_geometry FROM (' + a + ' ) AS single)'
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #   returns a single geometry "column" (paren wrapped) as "single" for multiple geographic item ids, or the
    # geometry as 'geometry' for a single id
    def geometry_sql2(*geographic_item_ids)
      geographic_item_ids.flatten! # *ALWAYS* reduce the pile to a single level of ids
      if geographic_item_ids.count == 1
        "(#{GeographicItem.geometry_for_sql(geographic_item_ids.first)})"
      else
        GeographicItem.single_geometry_sql(geographic_item_ids)
      end
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    def containing_where_sql(*geographic_item_ids)
      "ST_CoveredBy(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
     CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
       WHEN 'GeographicItem::Point' THEN point::geometry
       WHEN 'GeographicItem::LineString' THEN line_string::geometry
       WHEN 'GeographicItem::Polygon' THEN polygon::geometry
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
    END)"
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    def containing_where_sql_geog(*geographic_item_ids)
      "ST_CoveredBy(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
     CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geography
       WHEN 'GeographicItem::Point' THEN point::geography
       WHEN 'GeographicItem::LineString' THEN line_string::geography
       WHEN 'GeographicItem::Polygon' THEN polygon::geography
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geography
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geography
    END)"
    end

    # @param [Interger, Array of Integer] ids
    # @return [Array]
    #   If we detect that some query id has crossed the meridian, then loop through
    #   and "manually" build up a list of results.
    #   Should only be used if GeographicItem.crosses_anti_meridian_by_id? is true.
    #   Note that this does not return a Scope, so you can't chain it like contained_by?
    # TODO: test this
    def contained_by_with_antimeridian_check(*ids)
      ids.flatten! # make sure there is only one level of splat (*)
      results = []

      crossing_ids = []

      ids.each do |id|
        # push each which crosses
        crossing_ids.push(id) if GeographicItem.crosses_anti_meridian_by_id?(id)
      end

      non_crossing_ids = ids - crossing_ids
      results.push GeographicItem.contained_by(non_crossing_ids).to_a if non_crossing_ids.any?

      crossing_ids.each do |id|
        # [61666, 61661, 61659, 61654, 61639]
        q1 = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT ST_AsText((SELECT polygon FROM geographic_items ' \
                                                           'WHERE id = ?))', id])
        r = GeographicItem.where(
          # GeographicItem.contained_by_wkt_shifted_sql(GeographicItem.find(id).geo_object.to_s)
          GeographicItem.contained_by_wkt_shifted_sql(
            ApplicationRecord.connection.execute(q1).first['st_astext'])
        ).to_a
        results.push(r)
      end

      results.flatten.uniq
    end

    # @params [String] well known text
    # @return [String] the SQL fragment for the specific geometry type, shifted by longitude
    # Note: this routine is called when it is already known that the A argument crosses anti-meridian
    def contained_by_wkt_shifted_sql(wkt)
      "ST_Contains(ST_ShiftLongitude(ST_GeomFromText('#{wkt}', 4326)), (
        CASE geographic_items.type
           WHEN 'GeographicItem::MultiPolygon' THEN ST_ShiftLongitude(multi_polygon::geometry)
           WHEN 'GeographicItem::Point' THEN ST_ShiftLongitude(point::geometry)
           WHEN 'GeographicItem::LineString' THEN ST_ShiftLongitude(line_string::geometry)
           WHEN 'GeographicItem::Polygon' THEN ST_ShiftLongitude(polygon::geometry)
           WHEN 'GeographicItem::MultiLineString' THEN ST_ShiftLongitude(multi_line_string::geometry)
           WHEN 'GeographicItem::MultiPoint' THEN ST_ShiftLongitude(multi_point::geometry)
        END
        )
      )"
    end

    # TODO: Remove the hard coded 4326 reference
    # @params [String] wkt
    # @return [String] SQL fragment limiting geographics items to those in this WKT
    def contained_by_wkt_sql(wkt)
      if crosses_anti_meridian?(wkt)
        retval = contained_by_wkt_shifted_sql(wkt)
      else
        retval = "ST_Contains(ST_GeomFromText('#{wkt}', 4326), (
        CASE geographic_items.type
           WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
           WHEN 'GeographicItem::Point' THEN point::geometry
           WHEN 'GeographicItem::LineString' THEN line_string::geometry
           WHEN 'GeographicItem::Polygon' THEN polygon::geometry
           WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
           WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
        END
        )
      )"
      end
      retval
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String] sql for contained_by via ST_ContainsProperly
    # Note: Can not use GEOMETRY_SQL because geometry_collection is not supported in ST_ContainsProperly
    # Note: !! If the target GeographicItem#id crosses the anti-meridian then you may/will get unexpected results.
    def contained_by_where_sql(*geographic_item_ids)
      "ST_Contains(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
    CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
       WHEN 'GeographicItem::Point' THEN point::geometry
       WHEN 'GeographicItem::LineString' THEN line_string::geometry
       WHEN 'GeographicItem::Polygon' THEN polygon::geometry
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
    END)"
    end

    # @param [RGeo:Point] rgeo_point
    # @return [String] sql for containing via ST_CoveredBy
    # TODO: Remove the hard coded 4326 reference
    # TODO: should this be wkt_point instead of rgeo_point?
    def containing_where_for_point_sql(rgeo_point)
      "ST_CoveredBy(
      ST_GeomFromText('#{rgeo_point}', 4326),
      #{GeographicItem::GEOMETRY_SQL.to_sql}
     )"
    end

    # @param [Interger] geographic_item_id
    # @return [String] SQL for geometries
    # example, not used
    def geometry_for_sql(geographic_item_id)
      'SELECT ' + GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry FROM geographic_items WHERE id = ' \
        "#{geographic_item_id} LIMIT 1"
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String] SQL for geometries
    # example, not used
    def geometry_for_collection_sql(*geographic_item_ids)
      'SELECT ' + GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry FROM geographic_items WHERE id IN ' \
        "( #{geographic_item_ids.join(',')} )"
    end

    #
    # Scopes
    #

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [Scope]
    #    the geographic items containing these collective geographic_item ids, not including self
    def containing(*geographic_item_ids)
      where(GeographicItem.containing_where_sql(geographic_item_ids)).not_ids(*geographic_item_ids)
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [Scope]
    #    the geographic items contained by any of these geographic_item ids, not including self
    # (works via ST_ContainsProperly)
    def contained_by(*geographic_item_ids)
      where(GeographicItem.contained_by_where_sql(geographic_item_ids))
    end

    # @param [RGeo::Point] rgeo_point
    # @return [Scope]
    #    the geographic items containing this point
    # TODO: should be containing_wkt ?
    def containing_point(rgeo_point)
      where(GeographicItem.containing_where_for_point_sql(rgeo_point))
    end

    # @return [Scope]
    #   adds an area_in_meters field, with meters
    def with_area
      select("ST_Area(#{GeographicItem::GEOGRAPHY_SQL}, false) as area_in_meters")
    end

    # return [Scope]
    #   A scope that limits the result to those GeographicItems that have a collecting event
    #   through either the geographic_item or the error_geographic_item
    #
    # A raw SQL join approach for comparison
    #
    # GeographicItem.joins('LEFT JOIN georeferences g1 ON geographic_items.id = g1.geographic_item_id').
    #   joins('LEFT JOIN georeferences g2 ON geographic_items.id = g2.error_geographic_item_id').
    #   where("(g1.geographic_item_id IS NOT NULL OR g2.error_geographic_item_id IS NOT NULL)").uniq

    # @return [Scope] GeographicItem
    # This uses an Arel table approach, this is ultimately more decomposable if we need. Of use:
    #  http://danshultz.github.io/talks/mastering_activerecord_arel  <- best
    #  https://github.com/rails/arel
    #  http://stackoverflow.com/questions/4500629/use-arel-for-a-nested-set-join-query-and-convert-to-activerecordrelation
    #  http://rdoc.info/github/rails/arel/Arel/SelectManager
    #  http://stackoverflow.com/questions/7976358/activerecord-arel-or-condition
    #
    def with_collecting_event_through_georeferences
      geographic_items = GeographicItem.arel_table
      georeferences = Georeference.arel_table
      g1 = georeferences.alias('a')
      g2 = georeferences.alias('b')

      c = geographic_items.join(g1, Arel::Nodes::OuterJoin).on(geographic_items[:id].eq(g1[:geographic_item_id]))
        .join(g2, Arel::Nodes::OuterJoin).on(geographic_items[:id].eq(g2[:error_geographic_item_id]))

      GeographicItem.joins(# turn the Arel back into scope
                           c.join_sources # translate the Arel join to a join hash(?)
                          ).where(
                            g1[:id].not_eq(nil).or(g2[:id].not_eq(nil)) # returns a Arel::Nodes::Grouping
                          ).distinct
    end

    # @return [Scope] include a 'latitude' column
    def with_latitude
      select(lat_long_sql(:latitude))
    end

    # @return [Scope] include a 'longitude' column
    def with_longitude
      select(lat_long_sql(:longitude))
    end

    # @param [String, GeographicItems]
    # @return [Scope]
    def intersecting(column_name, *geographic_items)
      if column_name.downcase == 'any'
        pieces = []
        DATA_TYPES.each { |column|
          pieces.push(GeographicItem.intersecting(column.to_s, geographic_items).to_a)
        }

        # @TODO change 'id in (?)' to some other sql construct

        GeographicItem.where(id: pieces.flatten.map(&:id))
      else
        q = geographic_items.flatten.collect { |geographic_item|
          "ST_Intersects(#{column_name}, '#{geographic_item.geo_object}'    )" # seems like we want this: http://danshultz.github.io/talks/mastering_activerecord_arel/#/15/2
        }.join(' or ')

        where(q)
      end
    end

    # @param [GeographicItem#id] geographic_item_id
    # @param [Float] distance in meters ?!?!
    # @return [ActiveRecord::Relation]
    # !! should be distance, not radius?!
    def within_radius_of_item(geographic_item_id, distance)
      where(within_radius_of_item_sql(geographic_item_id, distance))
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    #   a SQL fragment for ST_DISJOINT, specifies all geographic_items that have data in column_name
    #   that are disjoint from the passed geographic_items
    def disjoint_from(column_name, *geographic_items)
      q = geographic_items.flatten.collect { |geographic_item|
        "ST_DISJOINT(#{column_name}::geometry, (#{geometry_sql(geographic_item.to_param,
                                                             geographic_item.geo_object_type)}))"
      }.join(' and ')

      where(q)
    end

    # @return [Scope]
    #   see are_contained_in_item_by_id
    # @param [String] column_name
    # @param [GeographicItem, Array] geographic_items
    def are_contained_in_item(column_name, *geographic_items)
      are_contained_in_item_by_id(column_name, geographic_items.flatten.map(&:id))
    end

    # rubocop:disable Metrics/MethodLength
    # @param [String] column_name to search
    # @param [GeographicItem] geographic_item_ids or array of geographic_item_ids to be tested.
    # @return [Scope] of GeographicItems
    #
    # If this scope is given an Array of GeographicItems as a second parameter,
    # it will return the 'OR' of each of the objects against the table.
    # SELECT COUNT(*) FROM "geographic_items"
    #        WHERE (ST_Contains(polygon::geometry, GeomFromEWKT('srid=4326;POINT (0.0 0.0 0.0)'))
    #               OR ST_Contains(polygon::geometry, GeomFromEWKT('srid=4326;POINT (-9.8 5.0 0.0)')))
    #
    def are_contained_in_item_by_id(column_name, *geographic_item_ids) # = containing
      geographic_item_ids.flatten! # in case there is a array of arrays, or multiple objects
      column_name.downcase!
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.are_contained_in_item_by_id(column.to_s, geographic_item_ids).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))
      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          if column.to_s.index(column_name.gsub('any_', ''))
            part.push(GeographicItem.are_contained_in_item_by_id("#{column}", geographic_item_ids).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))
      else
        q = geographic_item_ids.flatten.collect { |geographic_item_id|
          # discover the item types, and convert type to database type for 'multi_'
          b = GeographicItem.where(id: geographic_item_id)
            .pluck(:type)[0].split(':')[2].downcase.gsub('lti', 'lti_')
          # a = GeographicItem.find(geographic_item_id).geo_object_type
          GeographicItem.containing_sql(column_name, geographic_item_id, b)
        }.join(' or ')
        q = 'FALSE' if q.blank? # this will prevent the invocation of *ALL* of the GeographicItems, if there are
        # no GeographicItems in the request (see CollectingEvent.name_hash(types)).
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:enable Metrics/MethodLength

    # @param [String] column_name
    # @param [String] geometry of WKT
    # @return [Scope]
    # a single WKT geometry is compared against column or columns (except geometry_collection) to find geographic_items
    # which are contained in the WKT
    def are_contained_in_wkt(column_name, geometry)
      column_name.downcase!
      # column_name = 'point'
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.are_contained_in_wkt(column.to_s, geometry).pluck(:id).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten)
      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          if column.to_s.index(column_name.gsub('any_', ''))
            part.push(GeographicItem.are_contained_in_wkt("#{column}", geometry).pluck(:id).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten)
      else
        # column = points, geometry = square
        q = "ST_Contains(ST_GeomFromEWKT('srid=4326;#{geometry}'), #{column_name}::geometry)"
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:disable Metrics/MethodLength
    #    containing the items the shape of which is contained in the geographic_item[s] supplied.
    # @param column_name [String] can be any of DATA_TYPES, or 'any' to check against all types, 'any_poly' to check
    # against 'polygon' or 'multi_polygon', or 'any_line' to check against 'line_string' or 'multi_line_string'.
    #  CANNOT be 'geometry_collection'.
    # @param geographic_items [GeographicItem] Can be a single GeographicItem, or an array of GeographicItem.
    # @return [Scope]
    def is_contained_by(column_name, *geographic_items)
      column_name.downcase!
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.is_contained_by(column.to_s, geographic_items).to_a)
          end
        }
        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))

      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            if column.to_s.index(column_name.gsub('any_', ''))
              part.push(GeographicItem.is_contained_by(column.to_s, geographic_items).to_a)
            end
          end
        }
        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))

      else
        q = geographic_items.flatten.collect { |geographic_item|
          GeographicItem.reverse_containing_sql(column_name, geographic_item.to_param,
                                                geographic_item.geo_object_type)
        }.join(' or ')
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:enable Metrics/MethodLength

    # @param [String, GeographicItem]
    # @return [Scope]
    def ordered_by_shortest_distance_from(column_name, geographic_item)
      if true # check_geo_params(column_name, geographic_item)
        select_distance_with_geo_object(column_name, geographic_item)
          .where_distance_greater_than_zero(column_name, geographic_item).order('distance')
      else
        where('false')
      end
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    def ordered_by_longest_distance_from(column_name, geographic_item)
      if true # check_geo_params(column_name, geographic_item)
        q = select_distance_with_geo_object(column_name, geographic_item)
          .where_distance_greater_than_zero(column_name, geographic_item)
          .order('distance desc')
        q
      else
        where('false')
      end
    end

    # @param [String] column_name
    # @param [GeographicItem] geographic_item
    # @return [String]
    def select_distance_with_geo_object(column_name, geographic_item)
      select("*, ST_Distance(#{column_name}, GeomFromEWKT('srid=4326;#{geographic_item.geo_object}')) as distance")
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    def where_distance_greater_than_zero(column_name, geographic_item)
      where("#{column_name} is not null and ST_Distance(#{column_name}, " \
            "GeomFromEWKT('srid=4326;#{geographic_item.geo_object}')) > 0")
    end

    # @param [GeographicItem]
    # @return [Scope]
    def not_including(geographic_items)
      where.not(id: geographic_items)
    end

    # @return [Scope]
    #   includes an 'is_valid' attribute (True/False) for the passed geographic_item.  Uses St_IsValid.
    # @param [RGeo object] geographic_item
    def with_is_valid_geometry_column(geographic_item)
      where(id: geographic_item.id).select("ST_IsValid(ST_AsBinary(#{geographic_item.geo_object_type})) is_valid")
    end

    #
    # Other
    #

    # @param [Integer] geographic_item_id1
    # @param [Integer] geographic_item_id2
    # @return [Float]
    def distance_between(geographic_item_id1, geographic_item_id2)
      q1 = "ST_Distance(#{GeographicItem::GEOGRAPHY_SQL}, " \
        "(#{select_geography_sql(geographic_item_id2)})) as distance"
      _q2 = ActiveRecord::Base.send(
        :sanitize_sql_array, ['ST_Distance(?, (?)) as distance',
                              GeographicItem::GEOGRAPHY_SQL,
                              select_geography_sql(geographic_item_id2)])
      GeographicItem.where(id: geographic_item_id1).pluck(Arel.sql(q1)).first
    end

    # @param [RGeo::Point] point
    # @return [Hash]
    #   as per #inferred_geographic_name_hierarchy but for Rgeo point
    def point_inferred_geographic_name_hierarchy(point)
      GeographicItem.containing_point(point).order(cached_total_area: :ASC).limit(1).first&.inferred_geographic_name_hierarchy
    end

    # @param [String] type_name ('polygon', 'point', 'line' [, 'circle'])
    # @return [String] if type
    def eval_for_type(type_name)
      retval = 'GeographicItem'
      case type_name.upcase
      when 'POLYGON'
        retval += '::Polygon'
      when 'LINESTRING'
        retval += '::LineString'
      when 'POINT'
        retval += '::Point'
      when 'MULTIPOLYGON'
        retval += '::MultiPolygon'
      when 'MULTILINESTRING'
        retval += '::MultiLineString'
      when 'MULTIPOINT'
        retval += '::MultiPoint'
      else
        retval = nil
      end
      retval
    end

    # example, not used
    # @param [Integer] geographic_item_id
    # @return [RGeo::Geographic object]
    def geometry_for(geographic_item_id)
      GeographicItem.select(GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry').find(geographic_item_id)['geometry']
    end

    # example, not used
    # @param [Integer, Array] geographic_item_ids
    # @return [Scope]
    def st_multi(*geographic_item_ids)
      GeographicItem.find_by_sql(
        "SELECT ST_Multi(ST_Collect(g.the_geom)) AS singlegeom
     FROM (
        SELECT (ST_DUMP(#{GeographicItem::GEOMETRY_SQL.to_sql})).geom AS the_geom
        FROM geographic_items
        WHERE id IN (?))
      AS g;", geographic_item_ids.flatten
      )
    end
  end # class << self

  # @return [Hash]
  #    a geographic_name_classification or empty Hash
  # This is a quick approach that works only when
  # the geographic_item is linked explicitly to a GeographicArea.
  #
  # !! Note that it is not impossible for a GeographicItem to be linked
  # to > 1 GeographicArea, in that case we are assuming that all are
  # equally refined, this might not be the case in the future because
  # of how the GeographicArea gazetteer is indexed.
  def quick_geographic_name_hierarchy
    if a = geographic_areas.first
      a.geographic_name_classification
    else
      {}
    end
  end

  # @return [Hash]
  #   a geographic_name_classification (see GeographicArea) inferred by
  # finding the smallest area containing this GeographicItem, in the most accurate gazetteer
  # and using it to return country/state/county. See also the logic in
  # filling in missing levels in GeographicArea.
  def inferred_geographic_name_hierarchy
    if small_area = containing_geographic_areas
      .joins(:geographic_areas_geographic_items)
      .merge(GeographicAreasGeographicItem.ordered_by_data_origin)
      .ordered_by_area
      .limit(1)
      .first
    return small_area.geographic_name_classification
    else
      {}
    end
  end

  # @return [Scope]
  #   the Geographic Areas that contain (gis) this geographic item
  def containing_geographic_areas
    GeographicArea.joins(:geographic_items).includes(:geographic_area_type)
      .joins("JOIN (#{GeographicItem.containing(id).to_sql}) j on geographic_items.id = j.id")
  end

  # @return [Boolean]
  #   whether stored shape is ST_IsValid
  def valid_geometry?
    GeographicItem.with_is_valid_geometry_column(self).first['is_valid']
  end

  # @return [Array of latitude, longitude]
  #    the lat, lon of the first point in the GeoItem, see subclass for #st_start_point
  def start_point
    o = st_start_point
    [o.y, o.x]
  end

  # @return [Array]
  #   the lat, long, as STRINGs for the centroid of this geographic item
  def center_coords
    r = GeographicItem.find_by_sql(
      "Select split_part(ST_AsLatLonText(ST_Centroid(#{GeographicItem::GEOMETRY_SQL.to_sql}), " \
      "'D.DDDDDD'), ' ', 1) latitude, split_part(ST_AsLatLonText(ST_Centroid" \
      "(#{GeographicItem::GEOMETRY_SQL.to_sql}), 'D.DDDDDD'), ' ', 2) " \
      "longitude from geographic_items where id = #{id};")[0]

    [r.latitude, r.longitude]
  end

  # @return [RGeo::Geographic::ProjectedPointImpl]
  #    representing the centroid of this geographic item
  def centroid
    # Gis::FACTORY.point(*center_coords.reverse)
    return geo_object if type == 'GeographicItem::Point'
    return Gis::FACTORY.parse_wkt(self.st_centroid)
  end

  # @param [Integer] geographic_item_id
  # @return [Double] distance in meters (slower, more accurate)
  def st_distance(geographic_item_id) # geo_object
    q1 = "ST_Distance((#{GeographicItem.select_geography_sql(id)}), " \
      "(#{GeographicItem.select_geography_sql(geographic_item_id)})) as d"
    _q2 = ActiveRecord::Base.send(:sanitize_sql_array, ['ST_Distance((?),(?)) as d',
                                                        GeographicItem.select_geography_sql(self.id),
                                                        GeographicItem.select_geography_sql(geographic_item_id)])
    deg = GeographicItem.where(id: id).pluck(Arel.sql(q1)).first
    deg * Utilities::Geo::ONE_WEST
  end

  # @param [GeographicItem] geographic_item
  # @return [Double] distance in meters
  # Like st_distance but works with changed and non persisted objects
  def st_distance_to_geographic_item(geographic_item)
    unless !persisted? || changed?
      a = "(#{GeographicItem.select_geography_sql(id)})"
    else
      a = "ST_GeographyFromText('#{geo_object.to_s}')"
    end

    unless !geographic_item.persisted? || geographic_item.changed?
      b = "(#{GeographicItem.select_geography_sql(geographic_item.id)})"
    else
      b = "ST_GeographyFromText('#{geographic_item.geo_object.to_s}')"
    end

    ActiveRecord::Base.connection.select_value("SELECT ST_Distance(#{a}, #{b})")
  end

  alias_method :distance_to, :st_distance

  # @param [Integer] geographic_item_id
  # @return [Double] distance in meters (faster, less accurate)
  def st_distance_spheroid(geographic_item_id)
    q1 = "ST_DistanceSpheroid((#{GeographicItem.select_geometry_sql(id)})," \
      "(#{GeographicItem.select_geometry_sql(geographic_item_id)}),'#{Gis::SPHEROID}') as distance"
    _q2 = ActiveRecord::Base.send(:sanitize_sql_array,
                                  ['ST_DistanceSpheroid((?),(?),?) as distance',
                                   GeographicItem.select_geometry_sql(id),
                                   GeographicItem.select_geometry_sql(geographic_item_id),
                                   Gis::SPHEROID])
    # TODO: what is _q2?
    GeographicItem.where(id: id).pluck(Arel.sql(q1)).first
  end

  # @return [String]
  #   a WKT POINT representing the centroid of the geographic item
  def st_centroid
    GeographicItem.where(id: to_param).pluck(Arel.sql("ST_AsEWKT(ST_Centroid(#{GeographicItem::GEOMETRY_SQL.to_sql}))")).first.gsub(/SRID=\d*;/, '')
  end

  # @return [Integer]
  #   the number of points in the geometry
  def st_npoints
    GeographicItem.where(id: id).pluck(Arel.sql("ST_NPoints(#{GeographicItem::GEOMETRY_SQL.to_sql}) as npoints")).first
  end

  # @return [Symbol]
  #   the geo type (i.e. column like :point, :multipolygon).  References the first-found object,
  # according to the list of DATA_TYPES, or nil
  def geo_object_type
    if self.class.name == 'GeographicItem' # a proxy check for new records
      geo_type
    else
      self.class::SHAPE_COLUMN
    end
  end

  # !!TODO: migrate these to use native column calls

  # @return [RGeo instance, nil]
  #  the Rgeo shape (See http://rubydoc.info/github/dazuma/rgeo/RGeo/Feature)
  def geo_object
    begin
      if r = geo_object_type # rubocop:disable Lint/AssignmentInCondition
        send(r)
      else
        false
      end
    rescue RGeo::Error::InvalidGeometry
      return nil # TODO: we need to render proper error for this!
    end
  end

  # @param [geo_object]
  # @return [Boolean]
  def contains?(target_geo_object)
    return nil if target_geo_object.nil?
    self.geo_object.contains?(target_geo_object)
  end

  # @param [geo_object]
  # @return [Boolean]
  def within?(target_geo_object)
    self.geo_object.within?(target_geo_object)
  end

  # @param [geo_object]
  # @return [Boolean]
  def intersects?(target_geo_object)
    self.geo_object.intersects?(target_geo_object)
  end

  # @param [geo_object, Double]
  # @return [Boolean]
  def near(target_geo_object, distance)
    self.geo_object.buffer(distance).contains?(target_geo_object)
  end

  # @param [geo_object, Double]
  # @return [Boolean]
  def far(target_geo_object, distance)
    !near(target_geo_object, distance)
  end

  # @return [GeoJSON hash]
  #    via Rgeo apparently necessary for GeometryCollection
  def rgeo_to_geo_json
    RGeo::GeoJSON.encode(geo_object).to_json
  end

  # @return [Hash]
  #   in GeoJSON format
  #   Computed via "raw" PostGIS (much faster). This
  #   requires the geo_object_type and id.
  #
  def to_geo_json
    JSON.parse(
      GeographicItem.connection.select_one(
        "SELECT ST_AsGeoJSON(#{geo_object_type}::geometry) a " \
        "FROM geographic_items WHERE id=#{id};")['a'])
  end

  # We don't need to serialize to/from JSON
  def to_geo_json_string
    GeographicItem.connection.select_one(
      "SELECT ST_AsGeoJSON(#{geo_object_type}::geometry) a " \
      "FROM geographic_items WHERE id=#{id};")['a']
  end

  # @return [Hash]
  #   the shape as a GeoJSON Feature with some item metadata
  def to_geo_json_feature
    @geometry ||= to_geo_json
    {'type' => 'Feature',
     'geometry' => geometry,
     'properties' => {
       'geographic_item' => {
         'id' => id}
     }
    }
  end

  # '{"type":"Feature","geometry":{"type":"Point","coordinates":[2.5,4.0]},"properties":{"color":"red"}}'
  # '{"type":"Feature","geometry":{"type":"Polygon","coordinates":"[[[-125.29394388198853, 48.584480409793],
  # [-67.11035013198853, 45.09937589848195],[-80.64550638198853, 25.01924647619111],[-117.55956888198853,
  # 32.5591595028449],[-125.29394388198853, 48.584480409793]]]"},"properties":{}}'
  # @param [String] value
  # @return [Boolean, RGeo object]
  def shape=(value)
    unless value.blank?
      geom = RGeo::GeoJSON.decode(value, json_parser: :json)
      this_type = JSON.parse(value)['geometry']['type']

      # TODO: isn't this set automatically? Or perhaps the callback isn't hit in this approach?
      self.type = GeographicItem.eval_for_type(this_type) unless geom.nil?

      raise('GeographicItem.type not set.') if type.blank?

      object = nil

      begin
        object = Gis::FACTORY.parse_wkt(geom.geometry.to_s)
      rescue RGeo::Error::InvalidGeometry
        return false
      end

      write_attribute(this_type.underscore.to_sym, object)
      geom
    end
  end

  # @return [String]
  def to_wkt
    #  10k  #<Benchmark::Tms:0x00007fb0dfd30fd0 @label="", @real=25.237487000005785, @cstime=0.0, @cutime=0.0, @stime=1.1704609999999995, @utime=5.507929999999988, @total=6.678390999999987>
    #  GeographicItem.select("ST_AsText( #{GeographicItem::GEOMETRY_SQL.to_sql}) wkt").where(id: id).first.wkt

    # 10k <Benchmark::Tms:0x00007fb0e02f7540 @label="", @real=21.619827999995323, @cstime=0.0, @cutime=0.0, @stime=0.8850890000000007, @utime=3.2958549999999605, @total=4.180943999999961>
    if a =  ApplicationRecord.connection.execute( "SELECT ST_AsText( #{GeographicItem::GEOMETRY_SQL.to_sql} ) wkt from geographic_items where geographic_items.id = #{id}").first
      return a['wkt']
    else
      return nil
    end
  end

  # return float, in meters, calculated, not from cached
  def area
    GeographicItem.where(id: id).select("ST_Area(#{GeographicItem::GEOGRAPHY_SQL}, false) as area_in_meters").first['area_in_meters']
  end

  private

  def set_cached
    update_column(:cached_total_area, area)
  end

  protected

  # @return [Symbol]
  #   returns the attribute (column name) containing data
  #   nearly all methods should use #geo_object_type, not geo_type
  def geo_type
    DATA_TYPES.each { |item|
      return item if send(item)
    }
    nil
  end

  # @return [Boolean, String] false if already set, or type to which it was set
  def set_type_if_geography_present
    if type.blank?
      column = geo_type
      self.type = "GeographicItem::#{column.to_s.camelize}" if column
    end
  end

  # @param [RGeo::Point] point
  # @return [Array] of a point
  #   Longitude |, Latitude -
  def point_to_a(point)
    data = []
    data.push(point.x, point.y)
    data
  end

  # @param [RGeo::Point] point
  # @return [Hash] of a point
  def point_to_hash(point)
    {points: [point_to_a(point)]}
  end

  # @param [RGeo::MultiPoint] multi_point
  # @return [Array] of points
  def multi_point_to_a(multi_point)
    data = []
    multi_point.each { |point|
      data.push([point.x, point.y])
    }
    data
  end

  # @return [Hash] of points
  def multi_point_to_hash(_multi_point)
    # when we encounter a multi_point type, we only stick the points into the array, NOT it's identity as a group
    {points: multi_point_to_a(multi_point)}
  end

  # @param [Reo::LineString] line_string
  # @return [Array] of points in the line
  def line_string_to_a(line_string)
    data = []
    line_string.points.each { |point|
      data.push([point.x, point.y])
    }
    data
  end

  # @param [Reo::LineString] line_string
  # @return [Hash] of points in the line
  def line_string_to_hash(line_string)
    {lines: [line_string_to_a(line_string)]}
  end

  # @param [RGeo::Polygon] polygon
  # @return [Array] of points in the polygon (exterior_ring ONLY)
  def polygon_to_a(polygon)
    # TODO: handle other parts of the polygon; i.e., the interior_rings (if they exist)
    data = []
    polygon.exterior_ring.points.each { |point|
      data.push([point.x, point.y])
    }
    data
  end

  # @param [RGeo::Polygon] polygon
  # @return [Hash] of points in the polygon (exterior_ring ONLY)
  def polygon_to_hash(polygon)
    {polygons: [polygon_to_a(polygon)]}
  end

  # @return [Array] of line_strings as arrays of points
  # @param [RGeo::MultiLineString] multi_line_string
  def multi_line_string_to_a(multi_line_string)
    data = []
    multi_line_string.each { |line_string|
      line_data = []
      line_string.points.each { |point|
        line_data.push([point.x, point.y])
      }
      data.push(line_data)
    }
    data
  end

  # @return [Hash] of line_strings as hashes of points
  def multi_line_string_to_hash(_multi_line_string)
    {lines: to_a}
  end

  # @param [RGeo::MultiPolygon] multi_polygon
  # @return [Array] of arrays of points in the polygons (exterior_ring ONLY)
  def multi_polygon_to_a(multi_polygon)
    data = []
    multi_polygon.each { |polygon|
      polygon_data = []
      polygon.exterior_ring.points.each { |point|
        polygon_data.push([point.x, point.y])
      }
      data.push(polygon_data)
    }
    data
  end

  # @return [Hash] of hashes of points in the polygons (exterior_ring ONLY)
  def multi_polygon_to_hash(_multi_polygon)
    {polygons: to_a}
  end

  # @return [Boolean] iff there is one and only one shape column set
  def some_data_is_provided
    data = []

    DATA_TYPES.each do |item|
      data.push(item) unless send(item).blank?
    end

    case data.count
    when 0
      errors.add(:base, 'No shape provided or provided shape is invalid')
    when 1
      return true
    else
      data.each do |object|
        errors.add(object, 'More than one shape type provided')
      end
    end
    true
  end
end

#multi_polygon ⇒ RGeo::Geographic::ProjectedMultiPolygonImpl

Returns:

• (RGeo::Geographic::ProjectedMultiPolygonImpl)

# File 'app/models/geographic_item.rb', line 34

class GeographicItem < ApplicationRecord
  include Housekeeping::Users
  include Housekeeping::Timestamps
  include Shared::IsData
  include Shared::SharedAcrossProjects

  # @return [Hash, nil]
  # An internal variable for use in super calls, holds a Hash in GeoJSON format (temporarily)
  attr_accessor :geometry

  # @return [Boolean, RGeo object]
  # @params value [Hash in GeoJSON format] ?!
  # TODO: WHY! boolean not nil, or object
  # Used to build geographic items from a shape [ of what class ] !?
  attr_accessor :shape

  # @return [Boolean]
  #   When true cached values are not built
  attr_accessor :no_cached

  DATA_TYPES = [
    :point,
    :line_string,
    :polygon,
    :multi_point,
    :multi_line_string,
    :multi_polygon,
    :geometry_collection
  ].freeze

  GEOMETRY_SQL = Arel::Nodes::Case.new(arel_table[:type])
    .when('GeographicItem::MultiPolygon').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_polygon].as('geometry')]))
    .when('GeographicItem::Point').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:point].as('geometry')]))
    .when('GeographicItem::LineString').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:line_string].as('geometry')]))
    .when('GeographicItem::Polygon').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:polygon].as('geometry')]))
    .when('GeographicItem::MultiLineString').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_line_string].as('geometry')]))
    .when('GeographicItem::MultiPoint').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:multi_point].as('geometry')]))
    .when('GeographicItem::GeometryCollection').then(Arel::Nodes::NamedFunction.new("CAST", [arel_table[:geometry_collection].as('geometry')]))
    .freeze

  GEOGRAPHY_SQL = "CASE geographic_items.type
    WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon
    WHEN 'GeographicItem::Point' THEN point
    WHEN 'GeographicItem::LineString' THEN line_string
    WHEN 'GeographicItem::Polygon' THEN polygon
    WHEN 'GeographicItem::MultiLineString' THEN multi_line_string
    WHEN 'GeographicItem::MultiPoint' THEN multi_point
    WHEN 'GeographicItem::GeometryCollection' THEN geometry_collection
    END".freeze

  # ANTI_MERIDIAN = '0X0102000020E61000000200000000000000008066400000000000405640000000000080664000000000004056C0'
  ANTI_MERIDIAN = 'LINESTRING (180 89.0, 180 -89)'.freeze

  has_many :geographic_areas_geographic_items, dependent: :destroy, inverse_of: :geographic_item
  has_many :geographic_areas, through: :geographic_areas_geographic_items
  has_many :asserted_distributions, through: :geographic_areas
  has_many :geographic_area_types, through: :geographic_areas
  has_many :parent_geographic_areas, through: :geographic_areas, source: :parent
  has_many :gadm_geographic_areas, class_name: 'GeographicArea', foreign_key: :gadm_geo_item_id
  has_many :ne_geographic_areas, class_name: 'GeographicArea', foreign_key: :ne_geo_item_id
  has_many :tdwg_geographic_areas, class_name: 'GeographicArea', foreign_key: :tdwg_geo_item_id
  has_many :georeferences, inverse_of: :geographic_item
  has_many :georeferences_through_error_geographic_item,
    class_name: 'Georeference', foreign_key: :error_geographic_item_id, inverse_of: :error_geographic_item
  has_many :collecting_events_through_georeferences, through: :georeferences, source: :collecting_event
  has_many :collecting_events_through_georeference_error_geographic_item,
    through: :georeferences_through_error_geographic_item, source: :collecting_event

  # TODO: THIS IS NOT GOOD
  before_validation :set_type_if_geography_present

  validate :some_data_is_provided
  validates :type, presence: true

  scope :include_collecting_event, -> { includes(:collecting_events_through_georeferences) }
  scope :geo_with_collecting_event, -> { joins(:collecting_events_through_georeferences) }
  scope :err_with_collecting_event, -> { joins(:georeferences_through_error_geographic_item) }

  after_save :set_cached, unless: Proc.new {|n| n.no_cached || errors.any? }

  class << self

    def st_union(geographic_item_scope)
      GeographicItem.select("ST_Union(#{GeographicItem::GEOMETRY_SQL.to_sql}) as collection")
        .where(id: geographic_item_scope.pluck(:id))
    end

    def st_collect(geographic_item_scope)
      GeographicItem.select("ST_Collect(#{GeographicItem::GEOMETRY_SQL.to_sql}) as collection")
        .where(id: geographic_item_scope.pluck(:id))
    end

    # @return [GeographicItem::ActiveRecord_Relation]
    # @params [Array] array of geographic area ids
    def default_by_geographic_area_ids(geographic_area_ids = [])
      GeographicItem.
        joins(:geographic_areas_geographic_items).
        merge(::GeographicAreasGeographicItem.default_geographic_item_data).
        where(geographic_areas_geographic_items: {geographic_area_id: geographic_area_ids})
    end

    # @param [String] wkt
    # @return [Boolean]
    #   whether or not the wtk intersects with the anti-meridian
    #   !! StrongParams security considerations
    def crosses_anti_meridian?(wkt)
      GeographicItem.find_by_sql(
        ['SELECT ST_Intersects(ST_GeogFromText(?), ST_GeogFromText(?)) as r;', wkt, ANTI_MERIDIAN]
      ).first.r
    end

    # @param [Integer] ids
    # @return [Boolean]
    #   whether or not any GeographicItem passed intersects the anti-meridian
    #   !! StrongParams security considerations
    #   This is our first line of defense against queries that define multiple shapes, one or
    #   more of which crosses the anti-meridian.  In this case the current TW strategy within the
    #   UI is to abandon the search, and prompt the user to refactor the query.
    def crosses_anti_meridian_by_id?(*ids)
      q1 = ["SELECT ST_Intersects((SELECT single_geometry FROM (#{GeographicItem.single_geometry_sql(*ids)}) as " \
            'left_intersect), ST_GeogFromText(?)) as r;', ANTI_MERIDIAN]
      _q2 = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT ST_Intersects((SELECT single_geometry FROM (?) as ' \
                                                          'left_intersect), ST_GeogFromText(?)) as r;', GeographicItem.single_geometry_sql(*ids), ANTI_MERIDIAN])
      GeographicItem.find_by_sql(q1).first.r
    end

    # TODO: * rename to reflect either/or and what is being returned
    # @param [Integer] geographic_area_ids
    # @param [String] shape_in in JSON (POINT, POLYGON, MULTIPOLYGON), usually from GoogleMaps
    # @param [String] search_object_class
    # @param [Integer] project_id for search_object_class
    # @return [Scope] of the requested search_object_type
    def gather_geographic_area_or_shape_data(geographic_area_ids, shape_in, search_object_class, project_id)
      if shape_in.blank?
        # get the shape from the geographic area, if possible
        finding = search_object_class.constantize
        target_geographic_item_ids = []
        if geographic_area_ids.blank?
          found = finding.none
        else
          # now use method from collection_object_filter_query
          geographic_area_ids.each do |gaid|
            target_geographic_item_ids.push(GeographicArea.joins(:geographic_items)
              .find(gaid)
              .default_geographic_item.id)
          end

          # TODO: There probably is a better way to do this, but for now...
          f1 = project_id.present? ? finding.with_project_id(project_id) : finding
          case search_object_class
          when /Collection/, /Collecting/
            found = f1.joins(:geographic_items)
              .where(GeographicItem.contained_by_where_sql(target_geographic_item_ids))
          when /Asserted/
            # TODO: Figure out how to see through this group of geographic_items to the ones which contain
            # geographic_items which are associated with geographic_areas (as #default_geographic_items)
            # which are associated with asserted_distributions
            found = f1.joins(:geographic_area).joins(:geographic_items)
              .where(GeographicItem.contained_by_where_sql(target_geographic_item_ids))
          else
          end
        end
      else
        found = gather_map_data(shape_in, search_object_class, project_id)
      end
      found
    end

    # TODO: likely not ussed any more
    #
    # @param [String] feature in JSON, looks like '{"type":"Feature","geometry":{"type":"Polygon",
    # "coordinates":[[[-40.078125,10.614539227964332],[-49.21875,-17.185577279306226],
    # [-23.203125,-15.837353550148276],[-40.078125,10.614539227964332]]]},"properties":{}}'
    # @param [String] search_object_class
    # @param [Integer, Nil] project_id for search_object_class
    # @return [Scope] of the requested search_object_type
    #   This function takes a feature, i.e. a string that is the result
    #   of drawing on a Google map, and submited as a form variable,
    #   and translates that to a scope for a provided search_object_class.
    #   e.g. Return all CollectionObjects in this drawn area
    #        Return all CollectionObjects in the radius around this point
    def gather_map_data(feature, search_object_class, project_id)
      finding = search_object_class.constantize
      g_feature = RGeo::GeoJSON.decode(feature, json_parser: :json)
      if g_feature.nil?
        finding.none
      else
        geometry = g_feature.geometry # isolate the WKT
        shape_type = geometry.geometry_type.to_s.downcase
        geometry = geometry.as_text
        radius = g_feature['radius']

        query = project_id.present? ?
          finding.with_project_id(project_id).joins(:geographic_items) :
          finding.joins(:geographic_items)

        case shape_type
        when 'point'
          query.where(GeographicItem.within_radius_of_wkt_sql(geometry, radius)) # was interesecting version.
        when 'polygon', 'multipolygon'
          query.where(GeographicItem.contained_by_wkt_sql(geometry))
        else
          query
        end
      end
    end

    #
    # SQL fragments
    #

    # @param [Integer, String]
    # @return [String]
    #   a SQL select statement that returns the *geometry* for the geographic_item with the specified id
    def select_geometry_sql(geographic_item_id)
      "SELECT #{GeographicItem::GEOMETRY_SQL.to_sql} from geographic_items where geographic_items.id = #{geographic_item_id}"
    end

    # @param [Integer, String]
    # @return [String]
    #   a SQL select statement that returns the geography for the geographic_item with the specified id
    def select_geography_sql(geographic_item_id)
      ActiveRecord::Base.send(:sanitize_sql_for_conditions, [
        "SELECT #{GeographicItem::GEOMETRY_SQL.to_sql} from geographic_items where geographic_items.id = ?",
        geographic_item_id])
    end

    # @param [Symbol] choice
    # @return [String]
    #   a fragment returning either latitude or longitude columns
    def lat_long_sql(choice)
      return nil unless [:latitude, :longitude].include?(choice)
      f = "'D.DDDDDD'" # TODO: probably a constant somewhere
      v = (choice == :latitude ? 1 : 2)
      "CASE type
      WHEN 'GeographicItem::GeometryCollection' THEN split_part(ST_AsLatLonText(ST_Centroid" \
      "(geometry_collection::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::LineString' THEN split_part(ST_AsLatLonText(ST_Centroid(line_string::geometry), " \
      "#{f}), ' ', #{v})
      WHEN 'GeographicItem::MultiPolygon' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_polygon::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::Point' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(point::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::Polygon' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(polygon::geometry), #{f}), ' ', #{v})
      WHEN 'GeographicItem::MultiLineString' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_line_string::geometry), #{f} ), ' ', #{v})
      WHEN 'GeographicItem::MultiPoint' THEN split_part(ST_AsLatLonText(" \
      "ST_Centroid(multi_point::geometry), #{f}), ' ', #{v})
    END as #{choice}"
    end

    # @param [Integer] geographic_item_id
    # @param [Integer] distance
    # @return [String]
    def within_radius_of_item_sql(geographic_item_id, distance)
      "ST_DWithin((#{GeographicItem::GEOGRAPHY_SQL}), (#{select_geography_sql(geographic_item_id)}), #{distance})"
    end

    # TODO: 3D is overkill here
    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [String]
    # !! This is intersecting
    def intersecting_radius_of_wkt_sql(wkt, distance)
      "ST_DWithin((#{GeographicItem::GEOGRAPHY_SQL}), ST_Transform( ST_GeomFromText('#{wkt}', " \
        "4326), 4326), #{distance})"
    end

    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [String]
    # !! This is fully covering
    def within_radius_of_wkt_sql(wkt, distance)
      "ST_Covers( ST_Buffer(ST_SetSRID( ST_GeomFromText('#{wkt}'), 4326)::geography, #{distance}), (#{GeographicItem::GEOGRAPHY_SQL}))"
    end

    # @param [String, Integer, String]
    # @return [String]
    #   a SQL fragment for ST_Contains() function, returns
    #   all geographic items which are contained in the item supplied
    def containing_sql(target_column_name = nil, geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil? || target_column_name.nil?
      "ST_Contains(#{target_column_name}::geometry, (#{geometry_sql(geographic_item_id, source_column_name)}))"
    end

    # @param [String, Integer, String]
    # @return [String]
    #   a SQL fragment for ST_Contains(), returns
    #   all geographic_items which contain the supplied geographic_item
    def reverse_containing_sql(target_column_name = nil, geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil? || target_column_name.nil?
      "ST_Contains((#{geometry_sql(geographic_item_id, source_column_name)}), #{target_column_name}::geometry)"
    end

    # @param [Integer, String]
    # @return [String]
    #   a SQL fragment that represents the geometry of the geographic item specified (which has data in the
    # source_column_name, i.e. geo_object_type)
    def geometry_sql(geographic_item_id = nil, source_column_name = nil)
      return 'false' if geographic_item_id.nil? || source_column_name.nil?
      "select geom_alias_tbl.#{source_column_name}::geometry from geographic_items geom_alias_tbl " \
        "where geom_alias_tbl.id = #{geographic_item_id}"
    end

    # rubocop:disable Metrics/MethodLength
    # @param [String] column_name
    # @param [GeographicItem] geographic_item
    # @return [String] of SQL
    def is_contained_by_sql(column_name, geographic_item)
      geo_id = geographic_item.id
      geo_type = geographic_item.geo_object_type
      template = '(ST_Contains((select geographic_items.%s::geometry from geographic_items where ' \
        'geographic_items.id = %d), %s::geometry))'
      retval = []
      column_name.downcase!
      case column_name
      when 'any'
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            retval.push(template % [geo_type, geo_id, column])
          end
        }
      when 'any_poly', 'any_line'
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            if column.to_s.index(column_name.gsub('any_', ''))
              retval.push(template % [geo_type, geo_id, column])
            end
          end
        }
      else
        retval = template % [geo_type, geo_id, column_name]
      end
      retval = retval.join(' OR ') if retval.instance_of?(Array)
      retval
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #   a select query that returns a single geometry (column name 'single_geometry' for the collection of ids
    # provided via ST_Collect)
    def st_collect_sql(*geographic_item_ids)
      geographic_item_ids.flatten!
      ActiveRecord::Base.send(:sanitize_sql_for_conditions, [
        "SELECT ST_Collect(f.the_geom) AS single_geometry
     FROM (
        SELECT (ST_DUMP(#{GeographicItem::GEOMETRY_SQL.to_sql})).geom as the_geom
        FROM geographic_items
        WHERE id in (?))
      AS f", geographic_item_ids])
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #    returns one or more geographic items combined as a single geometry in column 'single'
    def single_geometry_sql(*geographic_item_ids)
      a = GeographicItem.st_collect_sql(geographic_item_ids)
      '(SELECT single.single_geometry FROM (' + a + ' ) AS single)'
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    #   returns a single geometry "column" (paren wrapped) as "single" for multiple geographic item ids, or the
    # geometry as 'geometry' for a single id
    def geometry_sql2(*geographic_item_ids)
      geographic_item_ids.flatten! # *ALWAYS* reduce the pile to a single level of ids
      if geographic_item_ids.count == 1
        "(#{GeographicItem.geometry_for_sql(geographic_item_ids.first)})"
      else
        GeographicItem.single_geometry_sql(geographic_item_ids)
      end
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    def containing_where_sql(*geographic_item_ids)
      "ST_CoveredBy(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
     CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
       WHEN 'GeographicItem::Point' THEN point::geometry
       WHEN 'GeographicItem::LineString' THEN line_string::geometry
       WHEN 'GeographicItem::Polygon' THEN polygon::geometry
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
    END)"
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String]
    def containing_where_sql_geog(*geographic_item_ids)
      "ST_CoveredBy(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
     CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geography
       WHEN 'GeographicItem::Point' THEN point::geography
       WHEN 'GeographicItem::LineString' THEN line_string::geography
       WHEN 'GeographicItem::Polygon' THEN polygon::geography
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geography
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geography
    END)"
    end

    # @param [Interger, Array of Integer] ids
    # @return [Array]
    #   If we detect that some query id has crossed the meridian, then loop through
    #   and "manually" build up a list of results.
    #   Should only be used if GeographicItem.crosses_anti_meridian_by_id? is true.
    #   Note that this does not return a Scope, so you can't chain it like contained_by?
    # TODO: test this
    def contained_by_with_antimeridian_check(*ids)
      ids.flatten! # make sure there is only one level of splat (*)
      results = []

      crossing_ids = []

      ids.each do |id|
        # push each which crosses
        crossing_ids.push(id) if GeographicItem.crosses_anti_meridian_by_id?(id)
      end

      non_crossing_ids = ids - crossing_ids
      results.push GeographicItem.contained_by(non_crossing_ids).to_a if non_crossing_ids.any?

      crossing_ids.each do |id|
        # [61666, 61661, 61659, 61654, 61639]
        q1 = ActiveRecord::Base.send(:sanitize_sql_array, ['SELECT ST_AsText((SELECT polygon FROM geographic_items ' \
                                                           'WHERE id = ?))', id])
        r = GeographicItem.where(
          # GeographicItem.contained_by_wkt_shifted_sql(GeographicItem.find(id).geo_object.to_s)
          GeographicItem.contained_by_wkt_shifted_sql(
            ApplicationRecord.connection.execute(q1).first['st_astext'])
        ).to_a
        results.push(r)
      end

      results.flatten.uniq
    end

    # @params [String] well known text
    # @return [String] the SQL fragment for the specific geometry type, shifted by longitude
    # Note: this routine is called when it is already known that the A argument crosses anti-meridian
    def contained_by_wkt_shifted_sql(wkt)
      "ST_Contains(ST_ShiftLongitude(ST_GeomFromText('#{wkt}', 4326)), (
        CASE geographic_items.type
           WHEN 'GeographicItem::MultiPolygon' THEN ST_ShiftLongitude(multi_polygon::geometry)
           WHEN 'GeographicItem::Point' THEN ST_ShiftLongitude(point::geometry)
           WHEN 'GeographicItem::LineString' THEN ST_ShiftLongitude(line_string::geometry)
           WHEN 'GeographicItem::Polygon' THEN ST_ShiftLongitude(polygon::geometry)
           WHEN 'GeographicItem::MultiLineString' THEN ST_ShiftLongitude(multi_line_string::geometry)
           WHEN 'GeographicItem::MultiPoint' THEN ST_ShiftLongitude(multi_point::geometry)
        END
        )
      )"
    end

    # TODO: Remove the hard coded 4326 reference
    # @params [String] wkt
    # @return [String] SQL fragment limiting geographics items to those in this WKT
    def contained_by_wkt_sql(wkt)
      if crosses_anti_meridian?(wkt)
        retval = contained_by_wkt_shifted_sql(wkt)
      else
        retval = "ST_Contains(ST_GeomFromText('#{wkt}', 4326), (
        CASE geographic_items.type
           WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
           WHEN 'GeographicItem::Point' THEN point::geometry
           WHEN 'GeographicItem::LineString' THEN line_string::geometry
           WHEN 'GeographicItem::Polygon' THEN polygon::geometry
           WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
           WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
        END
        )
      )"
      end
      retval
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String] sql for contained_by via ST_ContainsProperly
    # Note: Can not use GEOMETRY_SQL because geometry_collection is not supported in ST_ContainsProperly
    # Note: !! If the target GeographicItem#id crosses the anti-meridian then you may/will get unexpected results.
    def contained_by_where_sql(*geographic_item_ids)
      "ST_Contains(
    #{GeographicItem.geometry_sql2(*geographic_item_ids)},
    CASE geographic_items.type
       WHEN 'GeographicItem::MultiPolygon' THEN multi_polygon::geometry
       WHEN 'GeographicItem::Point' THEN point::geometry
       WHEN 'GeographicItem::LineString' THEN line_string::geometry
       WHEN 'GeographicItem::Polygon' THEN polygon::geometry
       WHEN 'GeographicItem::MultiLineString' THEN multi_line_string::geometry
       WHEN 'GeographicItem::MultiPoint' THEN multi_point::geometry
    END)"
    end

    # @param [RGeo:Point] rgeo_point
    # @return [String] sql for containing via ST_CoveredBy
    # TODO: Remove the hard coded 4326 reference
    # TODO: should this be wkt_point instead of rgeo_point?
    def containing_where_for_point_sql(rgeo_point)
      "ST_CoveredBy(
      ST_GeomFromText('#{rgeo_point}', 4326),
      #{GeographicItem::GEOMETRY_SQL.to_sql}
     )"
    end

    # @param [Interger] geographic_item_id
    # @return [String] SQL for geometries
    # example, not used
    def geometry_for_sql(geographic_item_id)
      'SELECT ' + GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry FROM geographic_items WHERE id = ' \
        "#{geographic_item_id} LIMIT 1"
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [String] SQL for geometries
    # example, not used
    def geometry_for_collection_sql(*geographic_item_ids)
      'SELECT ' + GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry FROM geographic_items WHERE id IN ' \
        "( #{geographic_item_ids.join(',')} )"
    end

    #
    # Scopes
    #

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [Scope]
    #    the geographic items containing these collective geographic_item ids, not including self
    def containing(*geographic_item_ids)
      where(GeographicItem.containing_where_sql(geographic_item_ids)).not_ids(*geographic_item_ids)
    end

    # @param [Interger, Array of Integer] geographic_item_ids
    # @return [Scope]
    #    the geographic items contained by any of these geographic_item ids, not including self
    # (works via ST_ContainsProperly)
    def contained_by(*geographic_item_ids)
      where(GeographicItem.contained_by_where_sql(geographic_item_ids))
    end

    # @param [RGeo::Point] rgeo_point
    # @return [Scope]
    #    the geographic items containing this point
    # TODO: should be containing_wkt ?
    def containing_point(rgeo_point)
      where(GeographicItem.containing_where_for_point_sql(rgeo_point))
    end

    # @return [Scope]
    #   adds an area_in_meters field, with meters
    def with_area
      select("ST_Area(#{GeographicItem::GEOGRAPHY_SQL}, false) as area_in_meters")
    end

    # return [Scope]
    #   A scope that limits the result to those GeographicItems that have a collecting event
    #   through either the geographic_item or the error_geographic_item
    #
    # A raw SQL join approach for comparison
    #
    # GeographicItem.joins('LEFT JOIN georeferences g1 ON geographic_items.id = g1.geographic_item_id').
    #   joins('LEFT JOIN georeferences g2 ON geographic_items.id = g2.error_geographic_item_id').
    #   where("(g1.geographic_item_id IS NOT NULL OR g2.error_geographic_item_id IS NOT NULL)").uniq

    # @return [Scope] GeographicItem
    # This uses an Arel table approach, this is ultimately more decomposable if we need. Of use:
    #  http://danshultz.github.io/talks/mastering_activerecord_arel  <- best
    #  https://github.com/rails/arel
    #  http://stackoverflow.com/questions/4500629/use-arel-for-a-nested-set-join-query-and-convert-to-activerecordrelation
    #  http://rdoc.info/github/rails/arel/Arel/SelectManager
    #  http://stackoverflow.com/questions/7976358/activerecord-arel-or-condition
    #
    def with_collecting_event_through_georeferences
      geographic_items = GeographicItem.arel_table
      georeferences = Georeference.arel_table
      g1 = georeferences.alias('a')
      g2 = georeferences.alias('b')

      c = geographic_items.join(g1, Arel::Nodes::OuterJoin).on(geographic_items[:id].eq(g1[:geographic_item_id]))
        .join(g2, Arel::Nodes::OuterJoin).on(geographic_items[:id].eq(g2[:error_geographic_item_id]))

      GeographicItem.joins(# turn the Arel back into scope
                           c.join_sources # translate the Arel join to a join hash(?)
                          ).where(
                            g1[:id].not_eq(nil).or(g2[:id].not_eq(nil)) # returns a Arel::Nodes::Grouping
                          ).distinct
    end

    # @return [Scope] include a 'latitude' column
    def with_latitude
      select(lat_long_sql(:latitude))
    end

    # @return [Scope] include a 'longitude' column
    def with_longitude
      select(lat_long_sql(:longitude))
    end

    # @param [String, GeographicItems]
    # @return [Scope]
    def intersecting(column_name, *geographic_items)
      if column_name.downcase == 'any'
        pieces = []
        DATA_TYPES.each { |column|
          pieces.push(GeographicItem.intersecting(column.to_s, geographic_items).to_a)
        }

        # @TODO change 'id in (?)' to some other sql construct

        GeographicItem.where(id: pieces.flatten.map(&:id))
      else
        q = geographic_items.flatten.collect { |geographic_item|
          "ST_Intersects(#{column_name}, '#{geographic_item.geo_object}'    )" # seems like we want this: http://danshultz.github.io/talks/mastering_activerecord_arel/#/15/2
        }.join(' or ')

        where(q)
      end
    end

    # @param [GeographicItem#id] geographic_item_id
    # @param [Float] distance in meters ?!?!
    # @return [ActiveRecord::Relation]
    # !! should be distance, not radius?!
    def within_radius_of_item(geographic_item_id, distance)
      where(within_radius_of_item_sql(geographic_item_id, distance))
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    #   a SQL fragment for ST_DISJOINT, specifies all geographic_items that have data in column_name
    #   that are disjoint from the passed geographic_items
    def disjoint_from(column_name, *geographic_items)
      q = geographic_items.flatten.collect { |geographic_item|
        "ST_DISJOINT(#{column_name}::geometry, (#{geometry_sql(geographic_item.to_param,
                                                             geographic_item.geo_object_type)}))"
      }.join(' and ')

      where(q)
    end

    # @return [Scope]
    #   see are_contained_in_item_by_id
    # @param [String] column_name
    # @param [GeographicItem, Array] geographic_items
    def are_contained_in_item(column_name, *geographic_items)
      are_contained_in_item_by_id(column_name, geographic_items.flatten.map(&:id))
    end

    # rubocop:disable Metrics/MethodLength
    # @param [String] column_name to search
    # @param [GeographicItem] geographic_item_ids or array of geographic_item_ids to be tested.
    # @return [Scope] of GeographicItems
    #
    # If this scope is given an Array of GeographicItems as a second parameter,
    # it will return the 'OR' of each of the objects against the table.
    # SELECT COUNT(*) FROM "geographic_items"
    #        WHERE (ST_Contains(polygon::geometry, GeomFromEWKT('srid=4326;POINT (0.0 0.0 0.0)'))
    #               OR ST_Contains(polygon::geometry, GeomFromEWKT('srid=4326;POINT (-9.8 5.0 0.0)')))
    #
    def are_contained_in_item_by_id(column_name, *geographic_item_ids) # = containing
      geographic_item_ids.flatten! # in case there is a array of arrays, or multiple objects
      column_name.downcase!
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.are_contained_in_item_by_id(column.to_s, geographic_item_ids).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))
      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          if column.to_s.index(column_name.gsub('any_', ''))
            part.push(GeographicItem.are_contained_in_item_by_id("#{column}", geographic_item_ids).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))
      else
        q = geographic_item_ids.flatten.collect { |geographic_item_id|
          # discover the item types, and convert type to database type for 'multi_'
          b = GeographicItem.where(id: geographic_item_id)
            .pluck(:type)[0].split(':')[2].downcase.gsub('lti', 'lti_')
          # a = GeographicItem.find(geographic_item_id).geo_object_type
          GeographicItem.containing_sql(column_name, geographic_item_id, b)
        }.join(' or ')
        q = 'FALSE' if q.blank? # this will prevent the invocation of *ALL* of the GeographicItems, if there are
        # no GeographicItems in the request (see CollectingEvent.name_hash(types)).
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:enable Metrics/MethodLength

    # @param [String] column_name
    # @param [String] geometry of WKT
    # @return [Scope]
    # a single WKT geometry is compared against column or columns (except geometry_collection) to find geographic_items
    # which are contained in the WKT
    def are_contained_in_wkt(column_name, geometry)
      column_name.downcase!
      # column_name = 'point'
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.are_contained_in_wkt(column.to_s, geometry).pluck(:id).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten)
      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          if column.to_s.index(column_name.gsub('any_', ''))
            part.push(GeographicItem.are_contained_in_wkt("#{column}", geometry).pluck(:id).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten)
      else
        # column = points, geometry = square
        q = "ST_Contains(ST_GeomFromEWKT('srid=4326;#{geometry}'), #{column_name}::geometry)"
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:disable Metrics/MethodLength
    #    containing the items the shape of which is contained in the geographic_item[s] supplied.
    # @param column_name [String] can be any of DATA_TYPES, or 'any' to check against all types, 'any_poly' to check
    # against 'polygon' or 'multi_polygon', or 'any_line' to check against 'line_string' or 'multi_line_string'.
    #  CANNOT be 'geometry_collection'.
    # @param geographic_items [GeographicItem] Can be a single GeographicItem, or an array of GeographicItem.
    # @return [Scope]
    def is_contained_by(column_name, *geographic_items)
      column_name.downcase!
      case column_name
      when 'any'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            part.push(GeographicItem.is_contained_by(column.to_s, geographic_items).to_a)
          end
        }
        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))

      when 'any_poly', 'any_line'
        part = []
        DATA_TYPES.each { |column|
          unless column == :geometry_collection
            if column.to_s.index(column_name.gsub('any_', ''))
              part.push(GeographicItem.is_contained_by(column.to_s, geographic_items).to_a)
            end
          end
        }
        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))

      else
        q = geographic_items.flatten.collect { |geographic_item|
          GeographicItem.reverse_containing_sql(column_name, geographic_item.to_param,
                                                geographic_item.geo_object_type)
        }.join(' or ')
        where(q) # .not_including(geographic_items)
      end
    end

    # rubocop:enable Metrics/MethodLength

    # @param [String, GeographicItem]
    # @return [Scope]
    def ordered_by_shortest_distance_from(column_name, geographic_item)
      if true # check_geo_params(column_name, geographic_item)
        select_distance_with_geo_object(column_name, geographic_item)
          .where_distance_greater_than_zero(column_name, geographic_item).order('distance')
      else
        where('false')
      end
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    def ordered_by_longest_distance_from(column_name, geographic_item)
      if true # check_geo_params(column_name, geographic_item)
        q = select_distance_with_geo_object(column_name, geographic_item)
          .where_distance_greater_than_zero(column_name, geographic_item)
          .order('distance desc')
        q
      else
        where('false')
      end
    end

    # @param [String] column_name
    # @param [GeographicItem] geographic_item
    # @return [String]
    def select_distance_with_geo_object(column_name, geographic_item)
      select("*, ST_Distance(#{column_name}, GeomFromEWKT('srid=4326;#{geographic_item.geo_object}')) as distance")
    end

    # @param [String, GeographicItem]
    # @return [Scope]
    def where_distance_greater_than_zero(column_name, geographic_item)
      where("#{column_name} is not null and ST_Distance(#{column_name}, " \
            "GeomFromEWKT('srid=4326;#{geographic_item.geo_object}')) > 0")
    end

    # @param [GeographicItem]
    # @return [Scope]
    def not_including(geographic_items)
      where.not(id: geographic_items)
    end

    # @return [Scope]
    #   includes an 'is_valid' attribute (True/False) for the passed geographic_item.  Uses St_IsValid.
    # @param [RGeo object] geographic_item
    def with_is_valid_geometry_column(geographic_item)
      where(id: geographic_item.id).select("ST_IsValid(ST_AsBinary(#{geographic_item.geo_object_type})) is_valid")
    end

    #
    # Other
    #

    # @param [Integer] geographic_item_id1
    # @param [Integer] geographic_item_id2
    # @return [Float]
    def distance_between(geographic_item_id1, geographic_item_id2)
      q1 = "ST_Distance(#{GeographicItem::GEOGRAPHY_SQL}, " \
        "(#{select_geography_sql(geographic_item_id2)})) as distance"
      _q2 = ActiveRecord::Base.send(
        :sanitize_sql_array, ['ST_Distance(?, (?)) as distance',
                              GeographicItem::GEOGRAPHY_SQL,
                              select_geography_sql(geographic_item_id2)])
      GeographicItem.where(id: geographic_item_id1).pluck(Arel.sql(q1)).first
    end

    # @param [RGeo::Point] point
    # @return [Hash]
    #   as per #inferred_geographic_name_hierarchy but for Rgeo point
    def point_inferred_geographic_name_hierarchy(point)
      GeographicItem.containing_point(point).order(cached_total_area: :ASC).limit(1).first&.inferred_geographic_name_hierarchy
    end

    # @param [String] type_name ('polygon', 'point', 'line' [, 'circle'])
    # @return [String] if type
    def eval_for_type(type_name)
      retval = 'GeographicItem'
      case type_name.upcase
      when 'POLYGON'
        retval += '::Polygon'
      when 'LINESTRING'
        retval += '::LineString'
      when 'POINT'
        retval += '::Point'
      when 'MULTIPOLYGON'
        retval += '::MultiPolygon'
      when 'MULTILINESTRING'
        retval += '::MultiLineString'
      when 'MULTIPOINT'
        retval += '::MultiPoint'
      else
        retval = nil
      end
      retval
    end

    # example, not used
    # @param [Integer] geographic_item_id
    # @return [RGeo::Geographic object]
    def geometry_for(geographic_item_id)
      GeographicItem.select(GeographicItem::GEOMETRY_SQL.to_sql + ' AS geometry').find(geographic_item_id)['geometry']
    end

    # example, not used
    # @param [Integer, Array] geographic_item_ids
    # @return [Scope]
    def st_multi(*geographic_item_ids)
      GeographicItem.find_by_sql(
        "SELECT ST_Multi(ST_Collect(g.the_geom)) AS singlegeom
     FROM (
        SELECT (ST_DUMP(#{GeographicItem::GEOMETRY_SQL.to_sql})).geom AS the_geom
        FROM geographic_items
        WHERE id IN (?))
      AS g;", geographic_item_ids.flatten
      )
    end
  end # class << self

  # @return [Hash]
  #    a geographic_name_classification or empty Hash
  # This is a quick approach that works only when
  # the geographic_item is linked explicitly to a GeographicArea.
  #
  # !! Note that it is not impossible for a GeographicItem to be linked
  # to > 1 GeographicArea, in that case we are assuming that all are
  # equally refined, this might not be the case in the future because
  # of how the GeographicArea gazetteer is indexed.
  def quick_geographic_name_hierarchy
    if a = geographic_areas.first
      a.geographic_name_classification
    else
      {}
    end
  end

  # @return [Hash]
  #   a geographic_name_classification (see GeographicArea) inferred by
  # finding the smallest area containing this GeographicItem, in the most accurate gazetteer
  # and using it to return country/state/county. See also the logic in
  # filling in missing levels in GeographicArea.
  def inferred_geographic_name_hierarchy
    if small_area = containing_geographic_areas
      .joins(:geographic_areas_geographic_items)
      .merge(GeographicAreasGeographicItem.ordered_by_data_origin)
      .ordered_by_area
      .limit(1)
      .first
    return small_area.geographic_name_classification
    else
      {}
    end
  end

  # @return [Scope]
  #   the Geographic Areas that contain (gis) this geographic item
  def containing_geographic_areas
    GeographicArea.joins(:geographic_items).includes(:geographic_area_type)
      .joins("JOIN (#{GeographicItem.containing(id).to_sql}) j on geographic_items.id = j.id")
  end

  # @return [Boolean]
  #   whether stored shape is ST_IsValid
  def valid_geometry?
    GeographicItem.with_is_valid_geometry_column(self)