Class: GeographicItem

Inherits:

ApplicationRecord

• Object
• ActiveRecord::Base
• ApplicationRecord
• GeographicItem

Includes:

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

Defined in:

app/models/geographic_item.rb

Overview

A GeographicItem describes a position, path, or area on the globe, generally associated with a geographic_area (through a geographic_area_geographic_item entry), a gazetteer, or a georeference.

Key methods in this giant library

‘#geo_object` - return a RGEO object representation

Constant Summary

SHAPE_TYPES =

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

ANTI_MERIDIAN =

ANTI_MERIDIAN = ‘0X0102000020E61000000200000000000000008066400000000000405640000000000080664000000000004056C0’

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

Instance Attribute Summary

• #cached_total_area ⇒ Numeric

  If polygon-based the value of the enclosed area in square meters.

• #geography ⇒ RGeo::Geographic::Geography

  Holds a shape of any geographic type.

• #no_cached ⇒ Boolean

  When true cached values are not built.

• #shape ⇒ Boolean, RGeo object

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

Attributes included from Housekeeping::Users

#by

Class Method Summary

• .anti_meridian_crossing_make_valid_sql(wkt) ⇒ Object

  Assumes wkt crosses the anti-meridian.

• .buffer_crosses_anti_meridian?(wkt, distance) ⇒ Boolean

  Whether or not the radius-buffer of wkt-point intersects the anti-meridian.

• .circle(center, radius, buffer_resolution = 8) ⇒ RGeo::Polygon

  A polygon approximation of the desired circle, in geographic coordinates.

• .covered_by_wkt_sql(wkt) ⇒ NamedFunction

  covered by this WKT.

• .crosses_anti_meridian?(wkt) ⇒ Boolean

  Whether or not the wkt intersects with the anti-meridian.

• .crosses_anti_meridian_by_id?(*ids) ⇒ Boolean

  Unused, kept for reference.

• .debug_draw(geographic_item_ids = []) ⇒ Object private

  Crude debuging helper, write the shapes to a png.

• .geography_as_geometry ⇒ Object
• .geography_cast(sql) ⇒ Object private
• .geometry_cast(sql) ⇒ Object private
• .intersecting(shape, *geographic_item_ids) ⇒ Object

  This is a geographic intersect, not geometric.

• .intersecting_radius_of_wkt_sql(wkt, distance) ⇒ NamedFunction

  !! This is computed in 2d.

• .items_as_one_geometry_sql(*geographic_item_ids) ⇒ SelectManager for one id, Arel::Nodes::SqlLiteral for more than one
• .lat_long_sql(choice) ⇒ Arel::Nodes::NamedFunction

  A fragment returning either latitude or longitude columns.

• .make_valid_non_anti_meridian_crossing_shape(wkt) ⇒ Object
• .not_including(geographic_items) ⇒ Scope
• .point_inferred_geographic_name_hierarchy(point) ⇒ Hash

  As per #inferred_geographic_name_hierarchy but for Rgeo point.

• .quote_string(s) ⇒ Object private
• .select_geography_sql(geographic_item_id) ⇒ SelectManager

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

• .select_geometry_sql(geographic_item_id) ⇒ SelectManager

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

• .select_value(named_function) ⇒ Object private
• .shape_column_sql(shape) ⇒ Arel::Nodes::Case private

  A Case statement that selects the geography column if that column is of type ‘shape`, otherwise NULL.

• .shape_is_type(shape) ⇒ Object private
• .single_geometry_sql(*geographic_item_ids) ⇒ Arel::Nodes::SqlLiteral

  Returns one or more geographic items combined as a single geometry in a column ‘single_geometry’.

• .split_along_anti_meridian(wkt, make_valid: false) ⇒ Object

  intersect the anti-meridian.

• .split_part(s, delimiter, n) ⇒ Object private

  From the docs: Splits string at occurrences of delimiter and returns the n’th field (counting from one), or when n is negative, returns the |n|‘th-from-last field.

• .st_area_sql(shape) ⇒ Object
• .st_as_geo_json_sql(shape) ⇒ Object
• .st_as_lat_lon_text_sql(point_shape, format = '') ⇒ Object
• .st_as_text_sql(shape) ⇒ Object
• .st_buffer_sql(shape, distance, num_seg_quarter_circle: 8) ⇒ Object
• .st_buffer_st_within_sql(geographic_item_id, distance, buffer = 0) ⇒ NamedFunction

  Shapes whose ‘buffer` is within `distance` of geographic_item.

• .st_centroid_sql(shape) ⇒ Object
• .st_collect_sql(shape) ⇒ Object
• .st_covered_by(shape, *geographic_items) ⇒ Scope

  rubocop:disable Metrics/MethodLength against all types, ‘any_poly’ to check against ‘polygon’ or ‘multi_polygon’, or ‘any_line’ to check against ‘line_string’ or ‘multi_line_string’.

• .st_covered_by_sql(shape1, shape2) ⇒ Object
• .st_covers(shape, *geographic_items) ⇒ Scope

  rubocop:disable Metrics/MethodLength against all types, ‘any_poly’ to check against ‘polygon’ or ‘multi_polygon’, or ‘any_line’ to check against ‘line_string’ or ‘multi_line_string’.

• .st_covers_sql(shape1, shape2) ⇒ Object
• .st_distance_sql(shape1, shape2) ⇒ Object
• .st_dump_sql(shape) ⇒ Object
• .st_dwithin_sql(shape1, shape2, distance) ⇒ Object

  True if the distance from shape1 to shape2 is less than ‘distance`.

• .st_geography_from_text_sql(wkt) ⇒ Object (also: st_geog_from_text_sql)
• .st_geometry_from_text_sql(wkt) ⇒ Object (also: st_geom_from_text_sql)
• .st_geometry_type(shape) ⇒ Object
• .st_intersection_sql(shape1, shape2) ⇒ Object

  !! Keep in mind that you may get different results depending on if the inputs are geographies or geometries.

• .st_intersects_sql(shape1, shape2) ⇒ Object

  # !! Keep in mind that you may get different results depending on if the inputs are geographies or geometries.

• .st_is_valid_reason_sql(shape) ⇒ Object
• .st_is_valid_sql(shape) ⇒ Object
• .st_make_valid_sql(shape) ⇒ Object

  Returns valid shapes unchanged.

• .st_minimum_bounding_radius_sql(shape) ⇒ Object
• .st_shift_longitude_sql(shape) ⇒ Object
• .st_union(geographic_item_scope) ⇒ Object

  DEPRECATED, moved to ::Queries::GeographicItem.

• .st_union_sql(shape) ⇒ Object

  Intended here to be used as an aggregate function.

• .subset_of_shifted_anti_meridian_shape_sql(shape) ⇒ Object

  Only called when shape crosses the anti-meridian !! shape must be pre-shifted to longitude-range (0, 360) !!.

• .subset_of_sql(shape) ⇒ Object

  True for those shapes that are subsets of shape.

• .subset_of_union_of_sql(*geographic_item_ids) ⇒ Object

  Note: !! If the target GeographicItem#id crosses the anti-meridian then you may/will get unexpected results.

• .superset_of_sql(shape) ⇒ Object

  True for those shapes that cover shape.

• .superset_of_union_of(*geographic_item_ids) ⇒ Scope

  does not include any of geographic_item_ids.

• .with_collecting_event_through_georeferences ⇒ Scope

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

• .within_radius_of_item(geographic_item_id, radius) ⇒ Object
• .within_radius_of_item_sql(geographic_item_id, radius) ⇒ Scope

  Of shapes within distance of (i.e. whose distance-buffer intersects) geographic_item_id.

• .within_radius_of_wkt_sql(wkt, distance) ⇒ NamedFunction

  Those items covered by the ‘distance`-buffer of wkt.

• .wkt_needs_longitude_translation(wkt) ⇒ Boolean private

  longitudes be in the interval (0, 360).

Instance Method Summary

• #align_winding ⇒ Object private
• #area ⇒ Float

  TODO: share with world.

• #center_coords ⇒ Array

  The lat, long, as STRINGs for the geometric centroid of this geographic item Meh- this: postgis.net/docs/en/ST_MinimumBoundingRadius.html.

• #centroid ⇒ RGeo::Geographic::ProjectedPointImpl

  Representing the geometric centroid of this geographic item.

• #contains?(target_geo_object) ⇒ Boolean
• #covering_geographic_areas ⇒ Scope

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

• #geo_object ⇒ RGeo instance^?

  The Rgeo shape.

• #geo_object_type ⇒ Symbol

  The specific type of geography: :point, :multi_polygon, etc.

• #geographic_name_hierarchy ⇒ Object
• #geography_is_multi_polygon? ⇒ Boolean private
• #geography_is_point? ⇒ Boolean private
• #geography_is_polygon? ⇒ Boolean private
• #inferred_geographic_name_hierarchy ⇒ Hash

  A geographic_name_classification (see GeographicArea) inferred by finding the smallest area covering this GeographicItem, in the most accurate gazetteer and using it to return country/state/county.

• #intersects?(target_geo_object) ⇒ Boolean
• #is_basic_donut? ⇒ Boolean

  !! Does not confirm that shapes are nested !!.

• #normalize_point_longitude ⇒ Object private
• #orientations ⇒ Object

  Convention is to store in PostGIS in CCW.

• #quick_geographic_name_hierarchy ⇒ Hash

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

• #radius ⇒ Object

  TODO: This is bad, while internal use of ONE_WEST_MEAN is consistent it is in-accurate given the vast differences of radius vs.

• #select_from_self(*named_function) ⇒ Object private
• #set_cached ⇒ Object private

  else render json: false end end.

• #some_data_is_provided ⇒ Object private
• #st_centroid ⇒ String

  A WKT POINT representing the geometry centroid of the geographic item.

• #st_distance_to_geographic_item(geographic_item) ⇒ Double

  Works with changed and non persisted objects.

• #st_is_valid ⇒ Object
• #st_is_valid_reason ⇒ Object
• #to_geo_json ⇒ Hash

  In GeoJSON format.

• #to_geo_json_feature ⇒ Hash

  The shape as a GeoJSON Feature with some item metadata.

• #to_wkt ⇒ String

  Wkt.

• #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 Shared::HasPapertrail

#attribute_updated, #attribute_updater, #detect_version

Methods included from Housekeeping::Users

#set_created_by_id, #set_updated_by_id

Methods inherited from ApplicationRecord

transaction_with_retry

Instance Attribute Details

#cached_total_area ⇒ Numeric

Returns if polygon-based the value of the enclosed area in square meters.

Returns:

• (Numeric) —

  if polygon-based the value of the enclosed area in square meters

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

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

  # @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

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

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

  # TODO Remove once `type` for GI STI has been deleted (it's currently there to
  # ease switching branches during development).
  self.inheritance_column = nil

  has_many :cached_map_items, inverse_of: :geographic_item

  has_many :geographic_areas_geographic_items, dependent: :destroy, inverse_of: :geographic_item
  has_many :geographic_areas, through: :geographic_areas_geographic_items
  has_many :geographic_area_types, through: :geographic_areas
  has_many :parent_geographic_areas, through: :geographic_areas, source: :parent

  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

  has_many :gazetteers, inverse_of: :geographic_item

  validate :some_data_is_provided

  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) }

  scope :points, -> { where(shape_is_type(:point)) }
  scope :multi_points, -> { where(shape_is_type(:multi_point)) }
  scope :line_strings, -> { where(shape_is_type(:line_string)) }
  scope :multi_line_strings, -> { where(shape_is_type(:multi_line_string)) }
  scope :polygons, -> { where(shape_is_type(:polygon)) }
  scope :multi_polygons, -> { where(shape_is_type(:multi_polygon)) }
  scope :geometry_collections, -> { where(shape_is_type(:geometry_collection)) }

  # Retrieving a geography point requires instantiating that point using our
  # Gis::FACTORY, which itself normalizes longitudes, so this is really just
  # ensuring that the normalized longitude is what we'll see stored in the
  # database as well.
  # TODO if/when needed: multipoints and points/multipoints inside
  # geometry_collections also need normalizing (Gis::FACTORY normalizes
  # longitudes of all other shapes).
  before_save :normalize_point_longitude

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

  class << self

    # DEPRECATED, moved to ::Queries::GeographicItem
    def st_union(geographic_item_scope)
      select('ST_Union(geography::geometry) as st_union')
        .where(id: geographic_item_scope.pluck(:id))
    end

    def st_covers_sql(shape1, shape2)
      Arel::Nodes::NamedFunction.new(
        'ST_Covers',
        [
          geometry_cast(shape1),
          geometry_cast(shape2)
        ]
      )
    end

    # True for those shapes that cover shape.
    def superset_of_sql(shape)
      st_covers_sql(
        geography_as_geometry,
        shape
      )
    end

    # @return [Scope] of items covering the union of geographic_item_ids;
    # does not include any of geographic_item_ids
    def superset_of_union_of(*geographic_item_ids)
      where(
        superset_of_sql(
          items_as_one_geometry_sql(*geographic_item_ids)
        )
      )
      .not_ids(*geographic_item_ids)
    end

    def st_covered_by_sql(shape1, shape2)
      Arel::Nodes::NamedFunction.new(
        'ST_CoveredBy',
        [
          geometry_cast(shape1),
          geometry_cast(shape2)
        ]
      )
    end

    # True for those shapes that are subsets of shape.
    def subset_of_sql(shape)
      st_covered_by_sql(
        geography_as_geometry,
        shape
      )
    end

    # Only called when shape crosses the anti-meridian
    # !! shape must be pre-shifted to longitude-range (0, 360) !!
    def subset_of_shifted_anti_meridian_shape_sql(shape)
      st_covered_by_sql(
        # All database geographic_items are (!! should be !!) stored in our
        # Gis::FACTORY-enforced longitude range (-180, 180), so always need to
        # be shifted in this case to the range (0, 360).
        st_shift_longitude_sql(geography_as_geometry),
        shape
      )
    end

    # Note: !! If the target GeographicItem#id crosses the anti-meridian then
    # you may/will get unexpected results.
    def subset_of_union_of_sql(*geographic_item_ids)
      subset_of_sql(
        items_as_one_geometry_sql(*geographic_item_ids)
      )
    end

    def st_distance_sql(shape1, shape2)
      Arel::Nodes::NamedFunction.new(
        'ST_Distance', [
          shape1,
          shape2
        ]
      )
    end

    def st_area_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_Area', [
          shape
        ]
      )
    end

    # Intended here to be used as an aggregate function
    def st_union_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_Union', [
          shape
        ]
      )
    end

    def st_dump_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_Dump', [
          shape
        ]
      )
    end

    def st_collect_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_Collect', [
          shape
        ]
      )
    end

    def st_is_valid_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_IsValid', [
          shape
        ]
      )
    end

    def st_is_valid_reason_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_IsValidReason', [
          shape
        ]
      )
    end

    def st_as_text_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_AsText', [
          shape
        ]
      )
    end

    def st_geometry_type(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_GeometryType', [
          geometry_cast(shape)
        ]
      )
    end

    def st_minimum_bounding_radius_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_MinimumBoundingRadius', [
          shape
        ]
      )
    end

    def st_as_geo_json_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_AsGeoJSON', [
          shape
        ]
      )
    end

    def st_geography_from_text_sql(wkt)
      wkt = quote_string(wkt)
      Arel::Nodes::NamedFunction.new(
        'ST_GeographyFromText', [
          Arel::Nodes.build_quoted(wkt),
        ]
      )
    end

    alias st_geog_from_text_sql st_geography_from_text_sql

    def st_geometry_from_text_sql(wkt)
      wkt = quote_string(wkt)
      Arel::Nodes::NamedFunction.new(
        'ST_GeometryFromText', [
          Arel::Nodes.build_quoted(wkt),
          Arel::Nodes.build_quoted(4326)
        ]
      )
    end

    alias st_geom_from_text_sql st_geometry_from_text_sql

    def st_centroid_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_Centroid', [
          shape
        ]
      )
    end

    def st_buffer_sql(shape, distance, num_seg_quarter_circle: 8)
      Arel::Nodes::NamedFunction.new(
        'ST_Buffer', [
          geography_cast(shape),
          Arel::Nodes.build_quoted(distance),
          Arel::Nodes.build_quoted(num_seg_quarter_circle)
        ]
      )
    end

    # # !! Keep in mind that you may get different results depending on if the
    # inputs are geographies or geometries.
    def st_intersects_sql(shape1, shape2)
      Arel::Nodes::NamedFunction.new(
        'ST_Intersects', [
          shape1,
          shape2
        ]
      )
    end

    # !! Keep in mind that you may get different results depending on if the
    # inputs are geographies or geometries.
    def st_intersection_sql(shape1, shape2)
      Arel::Nodes::NamedFunction.new(
        'ST_Intersection', [
          shape1,
          shape2
        ]
      )
    end

    def st_shift_longitude_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_ShiftLongitude', [
          geometry_cast(shape)
        ]
      )
    end

    # True if the distance from shape1 to shape2 is less than `distance`. This
    # is a geography dwithin, distance is in meters.
    def st_dwithin_sql(shape1, shape2, distance)
      Arel::Nodes::NamedFunction.new(
        'ST_DWithin', [
          geography_cast(shape1),
          geography_cast(shape2),
          Arel::Nodes.build_quoted(distance)
        ]
      )
    end

    def st_as_lat_lon_text_sql(point_shape, format = '')
      Arel::Nodes::NamedFunction.new(
        'ST_AsLatLonText', [
          geometry_cast(point_shape),
          Arel::Nodes.build_quoted(format)
        ]
      )
    end

    # Returns valid shapes unchanged.
    # !! This will give the wrong result on anti-meridian-crossing shapes stored
    # in Gis::FACTORY coordinates, use anti_meridian_crossing_make_valid
    # instead in that case.
    def st_make_valid_sql(shape)
      # TODO add params once we're on GEOS >= 3.10, they're not used until then
      #params = "method=structure keepcollapsed=false"
      Arel::Nodes::NamedFunction.new(
        'ST_MakeValid', [
          geometry_cast(shape)
          #Arel::Nodes.build_quoted(params)
        ]
      )
    end

    # Assumes wkt crosses the anti-meridian.
    # !! Note you must apply St_ShiftLongitude to the return value to have the
    # correct interpretation of the return value's relation to the
    # anti-meridian (cf. anti_meridian_spec.rb).
    def anti_meridian_crossing_make_valid_sql(wkt)
      st_make_valid_sql(
        st_shift_longitude_sql(
          st_geom_from_text_sql(
            wkt
          )
        )
      )
    end

    def make_valid_non_anti_meridian_crossing_shape(wkt)
      if crosses_anti_meridian?(wkt)
        split_along_anti_meridian(wkt, make_valid: true)
      else
        wkb = select_value(
          st_make_valid_sql(
            st_geom_from_text_sql(
              wkt
            )
          )
        )

        ::Gis::FACTORY.parse_wkb(wkb)
      end
    end

    # @param [String] wkt
    # @return RGeo shape for wkt expressed as a union of pieces none of which
    # intersect the anti-meridian. Slightly lossy (has to be), and may turn
    # polygon into multi-polygon, etc.
    # Assumes wkt intersects the anti-meridian.
    def split_along_anti_meridian(wkt, make_valid: false)
      wkt = quote_string(wkt)
      # Intended to be the exterior of a tiny buffer around the anti-meridian,
      # expressed as two sheets/near-hemispheres that meet at long=0=360.
      anti_meridian_exterior = 'MULTIPOLYGON(
        ((0 -89.999999, 179.999999 -89.999999, 179.999999 89.999999, 0 89.999999, 0 -89.999999)),
        ((180.000001 -89.999999, 360 -89.999999, 360 89.999999, 180.000001 89.999999, 180.000001 -89.999999))
      )'

      s = make_valid ?
        anti_meridian_crossing_make_valid_sql(wkt) :
        st_shift_longitude_sql(st_geom_from_text_sql(wkt))

      wkb = select_value(
        st_intersection_sql(
          s,
          st_geom_from_text_sql(anti_meridian_exterior)
        )
      )

      ::Gis::FACTORY.parse_wkb(wkb)
    end

    # @param [String] wkt
    # @return [Boolean]
    #   whether or not the wkt intersects with the anti-meridian
    def crosses_anti_meridian?(wkt)
      wkt = quote_string(wkt)
      select_value(
        st_intersects_sql(
          st_geography_from_text_sql(wkt),
          st_geography_from_text_sql(ANTI_MERIDIAN)
        )
      )
    end

    # @param [String] wkt
    # @param [Integer] buffer distance
    # @return [Boolean]
    #   whether or not the radius-buffer of wkt-point intersects the
    #   anti-meridian
    def buffer_crosses_anti_meridian?(wkt, distance)
      wkt = quote_string(wkt)
      select_value(
        st_intersects_sql(
          st_buffer_sql(
            st_geography_from_text_sql(wkt),
            distance
          ),
          st_geography_from_text_sql(ANTI_MERIDIAN)
        )
      )
    end

    # Unused, kept for reference
    # @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)
      q = "SELECT ST_Intersects((SELECT single_geometry FROM (#{GeographicItem.single_geometry_sql(*ids)}) as " \
            'left_intersect), ST_GeogFromText(?)) as r;', ANTI_MERIDIAN
      GeographicItem.find_by_sql(q).first.r
    end

    #
    # SQL fragments
    #

    # @param [Integer, String]
    # @return [SelectManager]
    #   a SQL select statement that returns the *geometry* for the
    #   geographic_item with the specified id
    def select_geometry_sql(geographic_item_id)
      arel_table
        .project(geography_as_geometry)
        .where(arel_table[:id].eq(geographic_item_id))
    end

    # @param [Integer, String]
    # @return [SelectManager]
    #   a SQL select statement that returns the geography for the
    #   geographic_item with the specified id
    def select_geography_sql(geographic_item_id)
      arel_table
        .project(arel_table[:geography])
        .where(arel_table[:id].eq(geographic_item_id))
    end

    # @param [Symbol] choice, either :latitude or :longitude
    # @return [Arel::Nodes::NamedFunction]
    #   a fragment returning either latitude or longitude columns
    def lat_long_sql(choice)
      return nil unless [:latitude, :longitude].include?(choice)
      f = 'D.DDDDDD'
      v = (choice == :latitude ? 1 : 2)

      split_part(
        st_as_lat_lon_text_sql(
          st_centroid_sql(geography_as_geometry),
          f
        ),
        ' ',
        v
      ).as(choice.to_s)
    end

    # @param [Integer] geographic_item_id
    # @param [Integer] radius in meters
    # @return [Scope] of shapes within distance of (i.e. whose
    #   distance-buffer intersects) geographic_item_id
    def within_radius_of_item_sql(geographic_item_id, radius)
      st_dwithin_sql(
        select_geography_sql(geographic_item_id),
        arel_table[:geography],
        radius
      )
    end

    def within_radius_of_item(geographic_item_id, radius)
      where(within_radius_of_item_sql(geographic_item_id, radius))
    end

    # @param [Integer] geographic_item_id
    # @param [Number] distance (in meters) (positive only?!)
    # @param [Number] buffer: distance in meters to grow/shrink the shapes
    #   checked against (negative allowed)
    # @return [NamedFunction] Shapes whose `buffer` is within `distance` of
    #   geographic_item
    def st_buffer_st_within_sql(geographic_item_id, distance, buffer = 0)
      # You can't always switch the buffer to the second argument, even when
      # distance is 0, without further assumptions (think of buffer being
      # large negative compared to geographic_item_id, but not another shape))
      st_dwithin_sql(
        st_buffer_sql(
          arel_table[:geography],
          buffer
        ),
        select_geography_sql(geographic_item_id),
        distance
      )
    end

    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [NamedFunction] Shapes whose distance to wkt is less than
    #   `distance`
    # !! This is computed in 2d
    def intersecting_radius_of_wkt_sql(wkt, distance)
      wkt = quote_string(wkt)
      st_dwithin_sql(
        st_geography_from_text_sql(wkt),
        arel_table[:geography],
        distance
      )
    end

    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [NamedFunction] Those items covered by the `distance`-buffer of
    #   wkt
    def within_radius_of_wkt_sql(wkt, distance)
      wkt = quote_string(wkt)

      if buffer_crosses_anti_meridian?(wkt, distance)
        subset_of_shifted_anti_meridian_shape_sql(
          # st_buffer_sql always has longitudes in (-180, 180) in this case, so
          # shift to (0, 360)
          st_shift_longitude_sql(
            st_buffer_sql(
              st_geography_from_text_sql(wkt),
              distance
            )
          )
        )
      else
        subset_of_sql(
          st_buffer_sql(
            st_geography_from_text_sql(wkt),
            distance
          )
        )
      end
    end

    # @param [Integer, Array of Integer] geographic_item_ids
    # @return [Arel::Nodes::SqlLiteral] returns one or more geographic items
    #   combined as a single geometry in a column 'single_geometry'
    def single_geometry_sql(*geographic_item_ids)
      geographic_item_ids.flatten!

      Arel.sql(
        "SELECT ST_Collect(f.the_geom) AS single_geometry
          FROM (
            SELECT (
              ST_DUMP(geography::geometry)
            ).geom AS the_geom
            FROM geographic_items
            WHERE id in (#{geographic_item_ids.join(',')})
          ) AS f"
      )
    end

    # @param [Integer, Array of Integer] geographic_item_ids
    # @return [SelectManager for one id, Arel::Nodes::SqlLiteral for more than
    #   one]
    def items_as_one_geometry_sql(*geographic_item_ids)
      geographic_item_ids.flatten! # *ALWAYS* reduce the pile to a single level of ids
      if geographic_item_ids.count == 1
        select_geometry_sql(geographic_item_ids.first)
      else
        single_geometry_sql(geographic_item_ids)
      end
    end

    # @params [String] wkt
    # @return [NamedFunction] SQL fragment limiting geographic items to those
    # covered by this WKT
    def covered_by_wkt_sql(wkt)
      wkt = quote_string(wkt)
      if crosses_anti_meridian?(wkt)
        translate_longitudes = wkt_needs_longitude_translation(wkt)
        wkt_geom = st_geom_from_text_sql(wkt)
        subset_of_shifted_anti_meridian_shape_sql(
          translate_longitudes ? st_shift_longitude_sql(wkt_geom) : wkt_geom
        )
      else
        subset_of_sql(
          st_geom_from_text_sql(wkt)
        )
      end
    end

    #
    # Scopes
    #

    # 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

    # This is a geographic intersect, not geometric
    def intersecting(shape, *geographic_item_ids)
      shape = shape.to_s.downcase
      if shape == 'any'
        pieces = []
        SHAPE_TYPES.each { |shape|
          pieces.push(
            intersecting(shape, geographic_item_ids).to_a
          )
        }

        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: pieces.flatten.map(&:id))
      else
        a = geographic_item_ids.flatten.collect { |geographic_item_id|
          # seems like we want this: http://danshultz.github.io/talks/mastering_activerecord_arel/#/15/2
          st_intersects_sql(
            shape_column_sql(shape),
            select_geography_sql(geographic_item_id)
          )
        }

        q = a.first
        if a.count > 1
          a[1..].each { |i| q = q.or(i) }
        end

        where(q)
      end
    end

    # rubocop:disable Metrics/MethodLength
    # @param [String] shape can be any of SHAPE_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'.
    # @param [GeographicItem] geographic_items or array of geographic_items
    #                         to be tested.
    # @return [Scope] of GeographicItems whose `shape` contains at least one
    #                 of geographic_items.
    #                 !! Returns geographic_item when geographic_item is of
    #                    type `shape`
    #
    # 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_Covers(polygon::geometry, GeomFromEWKT('srid=4326;POINT (0.0 0.0 0.0)'))
    #               OR ST_Covers(polygon::geometry, GeomFromEWKT('srid=4326;POINT (-9.8 5.0 0.0)')))
    #
    def st_covers(shape, *geographic_items)
      geographic_items.flatten! # in case there is a array of arrays, or multiple objects
      shape = shape.to_s.downcase
      case shape
      when 'any'
        part = []
        SHAPE_TYPES.each { |s|
          part.push(st_covers(s, geographic_items).to_a)
        }
        # TODO: change 'id in (?)' to some other sql construct
        where(id: part.flatten.map(&:id))

      when 'any_poly', 'any_line'
        part = []
        SHAPE_TYPES.each { |s|
          s = s.to_s
          if s.index(shape.gsub('any_', ''))
            part.push(st_covers(s, geographic_items).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        where(id: part.flatten.map(&:id))

      else
        a = geographic_items.flatten.collect { |geographic_item|
          st_covers_sql(
            shape_column_sql(shape),
            select_geometry_sql(geographic_item.id)
          )
        }

        q = a.first
        if a.count > 1
          a[1..].each { |i| q = q.or(i) }
        end

        # This will prevent the invocation of *ALL* of the GeographicItems
        # if there are no GeographicItems in the request (see
        # CollectingEvent.name_hash(types)).
        q = 'FALSE' if q.blank?
        where(q) # .not_including(geographic_items)
      end
    end
    # rubocop:enable Metrics/MethodLength

    # rubocop:disable Metrics/MethodLength
    # @param shape [String] can be any of SHAPE_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'.
    # @param geographic_items [GeographicItem] Can be a single
    # GeographicItem, or an array of GeographicItem.
    # @return [Scope] of all GeographicItems of the given `shape` covered by
    # one or more of geographic_items
    # !! Returns geographic_item when geographic_item is of type `shape`
    def st_covered_by(shape, *geographic_items)
      shape = shape.to_s.downcase
      case shape
      when 'any'
        part = []
        SHAPE_TYPES.each { |s|
          part.push(GeographicItem.st_covered_by(s, geographic_items).to_a)
        }
        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))

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

      else
        a = geographic_items.flatten.collect { |geographic_item|
          st_covered_by_sql(
            shape_column_sql(shape),
            select_geometry_sql(geographic_item.id)
          )
        }

        q = a.first
        if a.count > 1
          a[1..].each { |i| q = q.or(i) }
        end

        q = 'FALSE' if q.blank?
        where(q) # .not_including(geographic_items)
      end
    end
    # rubocop:enable Metrics/MethodLength

    # @param [RGeo::Point] center in lon/lat
    # @param [Integer] radius of the circle, in meters
    # @param [Integer] buffer_resolution: the number of sides of the polygon
    #                  approximation per quarter circle
    # @return [RGeo::Polygon] A polygon approximation of the desired circle, in
    #                         geographic coordinates
    def circle(center, radius, buffer_resolution = 8)
      circle_wkb = select_value(
        st_buffer_sql(
          st_geography_from_text_sql(
            "POINT (#{center.lon} #{center.lat})",
          ),
          radius,
          num_seg_quarter_circle: buffer_resolution
        )
      )

      make_valid_non_anti_meridian_crossing_shape(
        Gis::FACTORY.parse_wkb(circle_wkb).as_text
      )
    end

    #
    # Other
    #

    # @param [RGeo::Point] point
    # @return [Hash]
    #   as per #inferred_geographic_name_hierarchy but for Rgeo point
    def point_inferred_geographic_name_hierarchy(point)
      where(superset_of_sql(st_geom_from_text_sql(point.to_s)))
      .order(cached_total_area: :ASC)
      .first&.inferred_geographic_name_hierarchy
    end

    def geography_as_geometry
      Arel.sql('geography::geometry')
    end

    # @param [GeographicItem]
    # @return [Scope]
    def not_including(geographic_items)
      where.not(id: geographic_items)
    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
    geographic_areas.order(:id).each do |ga|
      h = ga.geographic_name_classification # not quick enough !!
      return h if h.present?
    end

    {}
  end

  # @return [Hash]
  #   a geographic_name_classification (see GeographicArea) inferred by
  #   finding the smallest area covering 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 = covering_geographic_areas
      .joins(:geographic_areas_geographic_items)
      .merge(GeographicAreasGeographicItem.ordered_by_data_origin)
      .ordered_by_area
      .first

      return small_area.geographic_name_classification
    end

    {}
  end

  def geographic_name_hierarchy
    a = quick_geographic_name_hierarchy # quick; almost never the case, UI not setup to do this
    return a if a.present?
    inferred_geographic_name_hierarchy # slow
  end

  # @return [Scope]
  #   the Geographic Areas that cover (gis) this geographic item
  def covering_geographic_areas
    GeographicArea
      .joins(:geographic_items)
      .includes(:geographic_area_type)
      .joins(
        "JOIN (#{GeographicItem.superset_of_union_of(id).to_sql}) AS j ON " \
        'geographic_items.id = j.id'
      )
  end

  # @param [GeographicItem] geographic_item
  # @return [Double] distance in meters
  # Works with changed and non persisted objects
  def st_distance_to_geographic_item(geographic_item)
    if persisted? && !changed?
      a = self.class.select_geography_sql(id)
    else
      a = self.class.st_geography_from_text_sql(geo_object.to_s)
    end

    if geographic_item.persisted? && !geographic_item.changed?
      b = self.class.select_geography_sql(geographic_item.id)
    else
      b = self.class.st_geography_from_text_sql(geographic_item.geo_object.to_s)
    end

    self.class.select_value(
      self.class.st_distance_sql(a, b)
    )
  end

  # @return [String]
  #   a WKT POINT representing the geometry centroid of the geographic item
  def st_centroid
    select_from_self(
      self.class.st_as_text_sql(
        self.class.st_centroid_sql(self.class.geography_as_geometry)
      )
    )['st_astext']
  end

  # @return [RGeo::Geographic::ProjectedPointImpl]
  #    representing the geometric centroid of this geographic item
  def centroid
    return geo_object if geo_object_type == :point

    Gis::FACTORY.parse_wkt(st_centroid)
  end

  # @return [Array]
  #   the lat, long, as STRINGs for the geometric centroid of this geographic
  #   item
  #   Meh- this: https://postgis.net/docs/en/ST_MinimumBoundingRadius.html
  def center_coords
    select_from_self(
      self.class.lat_long_sql(:latitude),
      self.class.lat_long_sql(:longitude)
    ).values
  end

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

  # @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

  # @return [Hash] in GeoJSON format
  def to_geo_json
    JSON.parse(
      select_from_self(
        self.class.st_as_geo_json_sql(self.class.arel_table[:geography])
      )['st_asgeojson']
    )
  end

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

  # @param value [String] geojson like:
  #   '{"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":{}}'
  #
  #  '{"type":"Point","coordinates":[2.5,4.0]},"properties":{"color":"red"}}'
  #
  def shape=(value)
    return if value.blank?

    begin
      geom = RGeo::GeoJSON.decode(value, json_parser: :json, geo_factory: Gis::FACTORY)
    rescue RGeo::Error::InvalidGeometry => e
      errors.add(:base, "invalid geometry: #{e}")
      return
    end

    object = nil

    s = geom.respond_to?(:geometry) ? geom.geometry.to_s : geom.to_s

    begin
      object = Gis::FACTORY.parse_wkt(s)
    rescue RGeo::Error::InvalidGeometry => e
      errors.add(:self, "Shape value is an Invalid Geometry: '#{e}'")
      return
    end

    write_attribute(:geography, object)
  end

  # @return [String] wkt
  def to_wkt
    select_from_self(
      self.class.st_as_text_sql(self.class.geography_as_geometry)
    )['st_astext']
  end

  # @return [Float] area in square meters, calculated
  # TODO: share with world
  def area
    select_from_self(
      self.class.st_area_sql(self.class.arel_table[:geography])
    )['st_area']
  end

  # TODO: This is bad, while internal use of ONE_WEST_MEAN is consistent it is in-accurate given the vast differences of radius vs. lat/long position.
  # When we strike the error-polygon from radius we should remove this
  #
  # Use case is returning the radius from a circle we calculated via buffer for error-polygon creation.
  def radius
    r = select_from_self(
      self.class.st_minimum_bounding_radius_sql(
        self.class.geography_as_geometry
      )
    )['st_minimumboundingradius'].split(',').last.chop.to_f

    (r * Utilities::Geo::ONE_WEST_MEAN).to_i
  end

  # Convention is to store in PostGIS in CCW
  # @return Array [Boolean]
  #   false - cw
  #   true - ccw (preferred), except see donuts
  def orientations
    if geography_is_multi_polygon?
      ApplicationRecord.connection.execute(
            "SELECT ST_IsPolygonCCW(a.geom) as is_ccw
              FROM ( SELECT b.id, (ST_Dump(p_geom)).geom AS geom
                  FROM (SELECT id, geography::geometry AS p_geom FROM geographic_items where id = #{id}) AS b
            ) AS a;").collect{|a| a['is_ccw']}
    elsif geography_is_polygon?
      ApplicationRecord.connection.execute(
        "SELECT ST_IsPolygonCCW(geography::geometry) as is_ccw \
        FROM geographic_items where  id = #{id};"
      ).collect { |a| a['is_ccw'] }
    else
      []
    end
  end

  # @return Boolean
  #   looks at all orientations
  #   if they follow the pattern [true, false, ... <all false>] then `true`, else `false`
  # !! Does not confirm that shapes are nested !!
  def is_basic_donut?
    a = orientations
    b = a.shift
    return false unless b

    a.uniq! == [false]
  end

  def st_is_valid
    select_from_self(
      self.class.st_is_valid_sql(
        self.class.geography_as_geometry
      )
    )['st_isvalid']
  end

  def st_is_valid_reason
    select_from_self(
      self.class.st_is_valid_reason_sql(
        self.class.geography_as_geometry
      )
    )['st_isvalidreason']
  end

  # @return [Symbol]
  #   the specific type of geography: :point, :multi_polygon, etc.
  def geo_object_type
    return geography.geometry_type.type_name.underscore.to_sym if geography

    nil
  end

  # @return [RGeo instance, nil]
  #  the Rgeo shape
  def geo_object
    geography
  end

  private

  # @param [String or Symbol] shape, the kind of shape you want, e.g. :polygon
  # @return [Arel::Nodes::Case]
  #   A Case statement that selects the geography column if that column is of
  #   type `shape`, otherwise NULL
  def self.shape_column_sql(shape)
    st_shape = 'ST_' + shape.to_s.camelize

    Arel::Nodes::Case.new(st_geometry_type(arel_table[:geography]))
      .when(st_shape.to_sym).then(arel_table[:geography])
      .else(Arel.sql('NULL'))
  end

  def self.shape_is_type(shape)
    st_shape = 'ST_' + shape.to_s.camelize

    Arel::Nodes::Case.new(st_geometry_type(arel_table[:geography]))
      .when(st_shape.to_sym).then(Arel.sql('TRUE'))
      .else(Arel.sql('FALSE'))
  end

  def geography_is_point?
    geo_object_type == :point
  end

  def geography_is_polygon?
    geo_object_type == :polygon
  end

  def geography_is_multi_polygon?
    geo_object_type == :multi_polygon
  end

  def select_from_self(*named_function)
    # This is faster than GeographicItem.select(...)
    ActiveRecord::Base.connection.execute(
      self.class.arel_table
        .project(*named_function)
        .where(self.class.arel_table[:id].eq(id))
        .to_sql
    ).to_a.first
  end

  def self.select_value(named_function)
    # This is faster than select(...)
    ActiveRecord::Base.connection.select_value(
      'SELECT ' +
      named_function.to_sql
    )
  end

  def align_winding
    if orientations.flatten.include?(false)
      if (geography_is_polygon? || geography_is_multi_polygon?)
        ApplicationRecord.connection.execute(
          "UPDATE geographic_items SET geography = ST_ForcePolygonCCW(geography::geometry)
            WHERE id = #{self.id};"
          )
      end
    end
  end

  # Crude debuging helper, write the shapes
  # to a png
  def self.debug_draw(geographic_item_ids = [])
    return false if geographic_item_ids.empty?
    sql = "SELECT ST_AsPNG(
        ST_AsRaster(
          (SELECT ST_Union(geography::geometry) from geographic_items where id IN (" + geographic_item_ids.join(',') + ")), 1920, 1080
      )
      ) png;"

    # ST_Buffer( multi_polygon::geometry, 0, 'join=bevel'),
    #     1920,
    #     1080)


    result = ActiveRecord::Base.connection.execute(sql).first['png']
    r = ActiveRecord::Base.connection.unescape_bytea(result)

    prefix = if geographic_item_ids.size > 10
               'multiple'
              else
                geographic_item_ids.join('_')
              end

    n = prefix + '_debug.draw.png'

    # Open the file in binary write mode ("wb")
    File.open(n, 'wb') do |file|
      # Write the binary data to the file
      file.write(r)
    end
  end

  # def png

  #   if ids = Otu.joins(:cached_map_items).first.cached_map_items.pluck(:geographic_item_id)

  #     sql = "SELECT ST_AsPNG(
  #    ST_AsRaster(
  #        ST_Buffer( multi_polygon::geometry, 0, 'join=bevel'),
  #            1024,
  #            768)
  #     ) png
  #        from geographic_items where id IN (" + ids.join(',') + ');'

  #     # hack, not the best way to unpack result
  #     result = ActiveRecord::Base.connection.execute(sql).first['png']
  #     r = ActiveRecord::Base.connection.unescape_bytea(result)

  #     send_data r, filename: 'foo.png', type: 'imnage/png'

  #   else
  #     render json: {foo: false}
  #   end
  # end

  def set_cached
    update_column(:cached_total_area, area)
  end

  def some_data_is_provided
    errors.add(:base, 'No shape provided or provided shape is invalid') if
      geography.nil?
  end

  def normalize_point_longitude
    return if !geography_is_point?

    if geography.x < -180.0 || geography.x > 180.0
      new_lon = geography.x % 360.0
      new_lon = new_lon - 360.0 if new_lon > 180.0
      self.geography = Gis::FACTORY.point(new_lon, geography.y)
    end
  end

  # @return [Boolean] true if wkt needs to be longitude-translated to make its
  # longitudes be in the interval (0, 360).
  # This is a bit of a hack: whether or not to shift wkt depends on whether or
  # not its longitudes are already in the range (0,360), which indicates to rgeo
  # that hemisphere-crossing lines cross the anti-meridian, not the meridian.
  # (We make the assumption that there aren't coordinates in both (180, 360) and
  # (-180, 0), which is actually possible with hand-entered wkt, and not easy
  # to deal with.)
  # TODO: find a more canonical/rgeo way to do this?
  # TODO: support other wkt shape types as needed. MultiPolygon covers all
  # polygon inputs from leaflet, the main case.
  def self.wkt_needs_longitude_translation(wkt)
    # Use a cartesian factory that doesn't automagically normalize its
    # longitude inputs, as Gis::FACTORY does.
    s = RGeo::Cartesian.simple_factory.parse_wkt(wkt)

    translate_longitudes = true
    # Currently this is intended to support Leaflet polygons.
    if (s.geometry_type.type_name == 'MultiPolygon' && s.count == 1)
      translate_longitudes =
        s[0].exterior_ring.points.map(&:x).any? { |l| l < 0 }
    end

    translate_longitudes
  end

  def self.geography_cast(sql)
    # Arel::Nodes::NamedFunction.new('CAST', [sql.as('geography')])
    Arel.sql(
      Arel::Nodes::Grouping.new(sql).to_sql + '::geography'
    )
  end

  def self.geometry_cast(sql)
    # specs fail using:
    # Arel::Nodes::NamedFunction.new('CAST', [sql.as('geometry')])
    Arel.sql(
      Arel::Nodes::Grouping.new(sql).to_sql + '::geometry'
    )
  end

  def self.quote_string(s)
    ActiveRecord::Base.connection.quote_string(s)
  end

  # From the docs: Splits string at occurrences of delimiter and returns the
  # n'th field (counting from one), or when n is negative, returns the
  # |n|'th-from-last field.
  # !! postgresql-specific
  def self.split_part(s, delimiter, n)
    Arel::Nodes::NamedFunction.new(
        'split_part',
        [
          Arel::Nodes.build_quoted(s),
          Arel::Nodes.build_quoted(delimiter),
          Arel::Nodes.build_quoted(n)
        ]
      )
  end

end

#geography ⇒ RGeo::Geographic::Geography

Holds a shape of any geographic type.

Returns:

• (RGeo::Geographic::Geography)

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

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

  # @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

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

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

  # TODO Remove once `type` for GI STI has been deleted (it's currently there to
  # ease switching branches during development).
  self.inheritance_column = nil

  has_many :cached_map_items, inverse_of: :geographic_item

  has_many :geographic_areas_geographic_items, dependent: :destroy, inverse_of: :geographic_item
  has_many :geographic_areas, through: :geographic_areas_geographic_items
  has_many :geographic_area_types, through: :geographic_areas
  has_many :parent_geographic_areas, through: :geographic_areas, source: :parent

  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

  has_many :gazetteers, inverse_of: :geographic_item

  validate :some_data_is_provided

  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) }

  scope :points, -> { where(shape_is_type(:point)) }
  scope :multi_points, -> { where(shape_is_type(:multi_point)) }
  scope :line_strings, -> { where(shape_is_type(:line_string)) }
  scope :multi_line_strings, -> { where(shape_is_type(:multi_line_string)) }
  scope :polygons, -> { where(shape_is_type(:polygon)) }
  scope :multi_polygons, -> { where(shape_is_type(:multi_polygon)) }
  scope :geometry_collections, -> { where(shape_is_type(:geometry_collection)) }

  # Retrieving a geography point requires instantiating that point using our
  # Gis::FACTORY, which itself normalizes longitudes, so this is really just
  # ensuring that the normalized longitude is what we'll see stored in the
  # database as well.
  # TODO if/when needed: multipoints and points/multipoints inside
  # geometry_collections also need normalizing (Gis::FACTORY normalizes
  # longitudes of all other shapes).
  before_save :normalize_point_longitude

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

  class << self

    # DEPRECATED, moved to ::Queries::GeographicItem
    def st_union(geographic_item_scope)
      select('ST_Union(geography::geometry) as st_union')
        .where(id: geographic_item_scope.pluck(:id))
    end

    def st_covers_sql(shape1, shape2)
      Arel::Nodes::NamedFunction.new(
        'ST_Covers',
        [
          geometry_cast(shape1),
          geometry_cast(shape2)
        ]
      )
    end

    # True for those shapes that cover shape.
    def superset_of_sql(shape)
      st_covers_sql(
        geography_as_geometry,
        shape
      )
    end

    # @return [Scope] of items covering the union of geographic_item_ids;
    # does not include any of geographic_item_ids
    def superset_of_union_of(*geographic_item_ids)
      where(
        superset_of_sql(
          items_as_one_geometry_sql(*geographic_item_ids)
        )
      )
      .not_ids(*geographic_item_ids)
    end

    def st_covered_by_sql(shape1, shape2)
      Arel::Nodes::NamedFunction.new(
        'ST_CoveredBy',
        [
          geometry_cast(shape1),
          geometry_cast(shape2)
        ]
      )
    end

    # True for those shapes that are subsets of shape.
    def subset_of_sql(shape)
      st_covered_by_sql(
        geography_as_geometry,
        shape
      )
    end

    # Only called when shape crosses the anti-meridian
    # !! shape must be pre-shifted to longitude-range (0, 360) !!
    def subset_of_shifted_anti_meridian_shape_sql(shape)
      st_covered_by_sql(
        # All database geographic_items are (!! should be !!) stored in our
        # Gis::FACTORY-enforced longitude range (-180, 180), so always need to
        # be shifted in this case to the range (0, 360).
        st_shift_longitude_sql(geography_as_geometry),
        shape
      )
    end

    # Note: !! If the target GeographicItem#id crosses the anti-meridian then
    # you may/will get unexpected results.
    def subset_of_union_of_sql(*geographic_item_ids)
      subset_of_sql(
        items_as_one_geometry_sql(*geographic_item_ids)
      )
    end

    def st_distance_sql(shape1, shape2)
      Arel::Nodes::NamedFunction.new(
        'ST_Distance', [
          shape1,
          shape2
        ]
      )
    end

    def st_area_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_Area', [
          shape
        ]
      )
    end

    # Intended here to be used as an aggregate function
    def st_union_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_Union', [
          shape
        ]
      )
    end

    def st_dump_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_Dump', [
          shape
        ]
      )
    end

    def st_collect_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_Collect', [
          shape
        ]
      )
    end

    def st_is_valid_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_IsValid', [
          shape
        ]
      )
    end

    def st_is_valid_reason_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_IsValidReason', [
          shape
        ]
      )
    end

    def st_as_text_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_AsText', [
          shape
        ]
      )
    end

    def st_geometry_type(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_GeometryType', [
          geometry_cast(shape)
        ]
      )
    end

    def st_minimum_bounding_radius_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_MinimumBoundingRadius', [
          shape
        ]
      )
    end

    def st_as_geo_json_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_AsGeoJSON', [
          shape
        ]
      )
    end

    def st_geography_from_text_sql(wkt)
      wkt = quote_string(wkt)
      Arel::Nodes::NamedFunction.new(
        'ST_GeographyFromText', [
          Arel::Nodes.build_quoted(wkt),
        ]
      )
    end

    alias st_geog_from_text_sql st_geography_from_text_sql

    def st_geometry_from_text_sql(wkt)
      wkt = quote_string(wkt)
      Arel::Nodes::NamedFunction.new(
        'ST_GeometryFromText', [
          Arel::Nodes.build_quoted(wkt),
          Arel::Nodes.build_quoted(4326)
        ]
      )
    end

    alias st_geom_from_text_sql st_geometry_from_text_sql

    def st_centroid_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_Centroid', [
          shape
        ]
      )
    end

    def st_buffer_sql(shape, distance, num_seg_quarter_circle: 8)
      Arel::Nodes::NamedFunction.new(
        'ST_Buffer', [
          geography_cast(shape),
          Arel::Nodes.build_quoted(distance),
          Arel::Nodes.build_quoted(num_seg_quarter_circle)
        ]
      )
    end

    # # !! Keep in mind that you may get different results depending on if the
    # inputs are geographies or geometries.
    def st_intersects_sql(shape1, shape2)
      Arel::Nodes::NamedFunction.new(
        'ST_Intersects', [
          shape1,
          shape2
        ]
      )
    end

    # !! Keep in mind that you may get different results depending on if the
    # inputs are geographies or geometries.
    def st_intersection_sql(shape1, shape2)
      Arel::Nodes::NamedFunction.new(
        'ST_Intersection', [
          shape1,
          shape2
        ]
      )
    end

    def st_shift_longitude_sql(shape)
      Arel::Nodes::NamedFunction.new(
        'ST_ShiftLongitude', [
          geometry_cast(shape)
        ]
      )
    end

    # True if the distance from shape1 to shape2 is less than `distance`. This
    # is a geography dwithin, distance is in meters.
    def st_dwithin_sql(shape1, shape2, distance)
      Arel::Nodes::NamedFunction.new(
        'ST_DWithin', [
          geography_cast(shape1),
          geography_cast(shape2),
          Arel::Nodes.build_quoted(distance)
        ]
      )
    end

    def st_as_lat_lon_text_sql(point_shape, format = '')
      Arel::Nodes::NamedFunction.new(
        'ST_AsLatLonText', [
          geometry_cast(point_shape),
          Arel::Nodes.build_quoted(format)
        ]
      )
    end

    # Returns valid shapes unchanged.
    # !! This will give the wrong result on anti-meridian-crossing shapes stored
    # in Gis::FACTORY coordinates, use anti_meridian_crossing_make_valid
    # instead in that case.
    def st_make_valid_sql(shape)
      # TODO add params once we're on GEOS >= 3.10, they're not used until then
      #params = "method=structure keepcollapsed=false"
      Arel::Nodes::NamedFunction.new(
        'ST_MakeValid', [
          geometry_cast(shape)
          #Arel::Nodes.build_quoted(params)
        ]
      )
    end

    # Assumes wkt crosses the anti-meridian.
    # !! Note you must apply St_ShiftLongitude to the return value to have the
    # correct interpretation of the return value's relation to the
    # anti-meridian (cf. anti_meridian_spec.rb).
    def anti_meridian_crossing_make_valid_sql(wkt)
      st_make_valid_sql(
        st_shift_longitude_sql(
          st_geom_from_text_sql(
            wkt
          )
        )
      )
    end

    def make_valid_non_anti_meridian_crossing_shape(wkt)
      if crosses_anti_meridian?(wkt)
        split_along_anti_meridian(wkt, make_valid: true)
      else
        wkb = select_value(
          st_make_valid_sql(
            st_geom_from_text_sql(
              wkt
            )
          )
        )

        ::Gis::FACTORY.parse_wkb(wkb)
      end
    end

    # @param [String] wkt
    # @return RGeo shape for wkt expressed as a union of pieces none of which
    # intersect the anti-meridian. Slightly lossy (has to be), and may turn
    # polygon into multi-polygon, etc.
    # Assumes wkt intersects the anti-meridian.
    def split_along_anti_meridian(wkt, make_valid: false)
      wkt = quote_string(wkt)
      # Intended to be the exterior of a tiny buffer around the anti-meridian,
      # expressed as two sheets/near-hemispheres that meet at long=0=360.
      anti_meridian_exterior = 'MULTIPOLYGON(
        ((0 -89.999999, 179.999999 -89.999999, 179.999999 89.999999, 0 89.999999, 0 -89.999999)),
        ((180.000001 -89.999999, 360 -89.999999, 360 89.999999, 180.000001 89.999999, 180.000001 -89.999999))
      )'

      s = make_valid ?
        anti_meridian_crossing_make_valid_sql(wkt) :
        st_shift_longitude_sql(st_geom_from_text_sql(wkt))

      wkb = select_value(
        st_intersection_sql(
          s,
          st_geom_from_text_sql(anti_meridian_exterior)
        )
      )

      ::Gis::FACTORY.parse_wkb(wkb)
    end

    # @param [String] wkt
    # @return [Boolean]
    #   whether or not the wkt intersects with the anti-meridian
    def crosses_anti_meridian?(wkt)
      wkt = quote_string(wkt)
      select_value(
        st_intersects_sql(
          st_geography_from_text_sql(wkt),
          st_geography_from_text_sql(ANTI_MERIDIAN)
        )
      )
    end

    # @param [String] wkt
    # @param [Integer] buffer distance
    # @return [Boolean]
    #   whether or not the radius-buffer of wkt-point intersects the
    #   anti-meridian
    def buffer_crosses_anti_meridian?(wkt, distance)
      wkt = quote_string(wkt)
      select_value(
        st_intersects_sql(
          st_buffer_sql(
            st_geography_from_text_sql(wkt),
            distance
          ),
          st_geography_from_text_sql(ANTI_MERIDIAN)
        )
      )
    end

    # Unused, kept for reference
    # @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)
      q = "SELECT ST_Intersects((SELECT single_geometry FROM (#{GeographicItem.single_geometry_sql(*ids)}) as " \
            'left_intersect), ST_GeogFromText(?)) as r;', ANTI_MERIDIAN
      GeographicItem.find_by_sql(q).first.r
    end

    #
    # SQL fragments
    #

    # @param [Integer, String]
    # @return [SelectManager]
    #   a SQL select statement that returns the *geometry* for the
    #   geographic_item with the specified id
    def select_geometry_sql(geographic_item_id)
      arel_table
        .project(geography_as_geometry)
        .where(arel_table[:id].eq(geographic_item_id))
    end

    # @param [Integer, String]
    # @return [SelectManager]
    #   a SQL select statement that returns the geography for the
    #   geographic_item with the specified id
    def select_geography_sql(geographic_item_id)
      arel_table
        .project(arel_table[:geography])
        .where(arel_table[:id].eq(geographic_item_id))
    end

    # @param [Symbol] choice, either :latitude or :longitude
    # @return [Arel::Nodes::NamedFunction]
    #   a fragment returning either latitude or longitude columns
    def lat_long_sql(choice)
      return nil unless [:latitude, :longitude].include?(choice)
      f = 'D.DDDDDD'
      v = (choice == :latitude ? 1 : 2)

      split_part(
        st_as_lat_lon_text_sql(
          st_centroid_sql(geography_as_geometry),
          f
        ),
        ' ',
        v
      ).as(choice.to_s)
    end

    # @param [Integer] geographic_item_id
    # @param [Integer] radius in meters
    # @return [Scope] of shapes within distance of (i.e. whose
    #   distance-buffer intersects) geographic_item_id
    def within_radius_of_item_sql(geographic_item_id, radius)
      st_dwithin_sql(
        select_geography_sql(geographic_item_id),
        arel_table[:geography],
        radius
      )
    end

    def within_radius_of_item(geographic_item_id, radius)
      where(within_radius_of_item_sql(geographic_item_id, radius))
    end

    # @param [Integer] geographic_item_id
    # @param [Number] distance (in meters) (positive only?!)
    # @param [Number] buffer: distance in meters to grow/shrink the shapes
    #   checked against (negative allowed)
    # @return [NamedFunction] Shapes whose `buffer` is within `distance` of
    #   geographic_item
    def st_buffer_st_within_sql(geographic_item_id, distance, buffer = 0)
      # You can't always switch the buffer to the second argument, even when
      # distance is 0, without further assumptions (think of buffer being
      # large negative compared to geographic_item_id, but not another shape))
      st_dwithin_sql(
        st_buffer_sql(
          arel_table[:geography],
          buffer
        ),
        select_geography_sql(geographic_item_id),
        distance
      )
    end

    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [NamedFunction] Shapes whose distance to wkt is less than
    #   `distance`
    # !! This is computed in 2d
    def intersecting_radius_of_wkt_sql(wkt, distance)
      wkt = quote_string(wkt)
      st_dwithin_sql(
        st_geography_from_text_sql(wkt),
        arel_table[:geography],
        distance
      )
    end

    # @param [String] wkt
    # @param [Integer] distance (meters)
    # @return [NamedFunction] Those items covered by the `distance`-buffer of
    #   wkt
    def within_radius_of_wkt_sql(wkt, distance)
      wkt = quote_string(wkt)

      if buffer_crosses_anti_meridian?(wkt, distance)
        subset_of_shifted_anti_meridian_shape_sql(
          # st_buffer_sql always has longitudes in (-180, 180) in this case, so
          # shift to (0, 360)
          st_shift_longitude_sql(
            st_buffer_sql(
              st_geography_from_text_sql(wkt),
              distance
            )
          )
        )
      else
        subset_of_sql(
          st_buffer_sql(
            st_geography_from_text_sql(wkt),
            distance
          )
        )
      end
    end

    # @param [Integer, Array of Integer] geographic_item_ids
    # @return [Arel::Nodes::SqlLiteral] returns one or more geographic items
    #   combined as a single geometry in a column 'single_geometry'
    def single_geometry_sql(*geographic_item_ids)
      geographic_item_ids.flatten!

      Arel.sql(
        "SELECT ST_Collect(f.the_geom) AS single_geometry
          FROM (
            SELECT (
              ST_DUMP(geography::geometry)
            ).geom AS the_geom
            FROM geographic_items
            WHERE id in (#{geographic_item_ids.join(',')})
          ) AS f"
      )
    end

    # @param [Integer, Array of Integer] geographic_item_ids
    # @return [SelectManager for one id, Arel::Nodes::SqlLiteral for more than
    #   one]
    def items_as_one_geometry_sql(*geographic_item_ids)
      geographic_item_ids.flatten! # *ALWAYS* reduce the pile to a single level of ids
      if geographic_item_ids.count == 1
        select_geometry_sql(geographic_item_ids.first)
      else
        single_geometry_sql(geographic_item_ids)
      end
    end

    # @params [String] wkt
    # @return [NamedFunction] SQL fragment limiting geographic items to those
    # covered by this WKT
    def covered_by_wkt_sql(wkt)
      wkt = quote_string(wkt)
      if crosses_anti_meridian?(wkt)
        translate_longitudes = wkt_needs_longitude_translation(wkt)
        wkt_geom = st_geom_from_text_sql(wkt)
        subset_of_shifted_anti_meridian_shape_sql(
          translate_longitudes ? st_shift_longitude_sql(wkt_geom) : wkt_geom
        )
      else
        subset_of_sql(
          st_geom_from_text_sql(wkt)
        )
      end
    end

    #
    # Scopes
    #

    # 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

    # This is a geographic intersect, not geometric
    def intersecting(shape, *geographic_item_ids)
      shape = shape.to_s.downcase
      if shape == 'any'
        pieces = []
        SHAPE_TYPES.each { |shape|
          pieces.push(
            intersecting(shape, geographic_item_ids).to_a
          )
        }

        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: pieces.flatten.map(&:id))
      else
        a = geographic_item_ids.flatten.collect { |geographic_item_id|
          # seems like we want this: http://danshultz.github.io/talks/mastering_activerecord_arel/#/15/2
          st_intersects_sql(
            shape_column_sql(shape),
            select_geography_sql(geographic_item_id)
          )
        }

        q = a.first
        if a.count > 1
          a[1..].each { |i| q = q.or(i) }
        end

        where(q)
      end
    end

    # rubocop:disable Metrics/MethodLength
    # @param [String] shape can be any of SHAPE_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'.
    # @param [GeographicItem] geographic_items or array of geographic_items
    #                         to be tested.
    # @return [Scope] of GeographicItems whose `shape` contains at least one
    #                 of geographic_items.
    #                 !! Returns geographic_item when geographic_item is of
    #                    type `shape`
    #
    # 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_Covers(polygon::geometry, GeomFromEWKT('srid=4326;POINT (0.0 0.0 0.0)'))
    #               OR ST_Covers(polygon::geometry, GeomFromEWKT('srid=4326;POINT (-9.8 5.0 0.0)')))
    #
    def st_covers(shape, *geographic_items)
      geographic_items.flatten! # in case there is a array of arrays, or multiple objects
      shape = shape.to_s.downcase
      case shape
      when 'any'
        part = []
        SHAPE_TYPES.each { |s|
          part.push(st_covers(s, geographic_items).to_a)
        }
        # TODO: change 'id in (?)' to some other sql construct
        where(id: part.flatten.map(&:id))

      when 'any_poly', 'any_line'
        part = []
        SHAPE_TYPES.each { |s|
          s = s.to_s
          if s.index(shape.gsub('any_', ''))
            part.push(st_covers(s, geographic_items).to_a)
          end
        }
        # TODO: change 'id in (?)' to some other sql construct
        where(id: part.flatten.map(&:id))

      else
        a = geographic_items.flatten.collect { |geographic_item|
          st_covers_sql(
            shape_column_sql(shape),
            select_geometry_sql(geographic_item.id)
          )
        }

        q = a.first
        if a.count > 1
          a[1..].each { |i| q = q.or(i) }
        end

        # This will prevent the invocation of *ALL* of the GeographicItems
        # if there are no GeographicItems in the request (see
        # CollectingEvent.name_hash(types)).
        q = 'FALSE' if q.blank?
        where(q) # .not_including(geographic_items)
      end
    end
    # rubocop:enable Metrics/MethodLength

    # rubocop:disable Metrics/MethodLength
    # @param shape [String] can be any of SHAPE_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'.
    # @param geographic_items [GeographicItem] Can be a single
    # GeographicItem, or an array of GeographicItem.
    # @return [Scope] of all GeographicItems of the given `shape` covered by
    # one or more of geographic_items
    # !! Returns geographic_item when geographic_item is of type `shape`
    def st_covered_by(shape, *geographic_items)
      shape = shape.to_s.downcase
      case shape
      when 'any'
        part = []
        SHAPE_TYPES.each { |s|
          part.push(GeographicItem.st_covered_by(s, geographic_items).to_a)
        }
        # @TODO change 'id in (?)' to some other sql construct
        GeographicItem.where(id: part.flatten.map(&:id))

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

      else
        a = geographic_items.flatten.collect { |geographic_item|
          st_covered_by_sql(
            shape_column_sql(shape),
            select_geometry_sql(geographic_item.id)
          )
        }

        q = a.first
        if a.count > 1
          a[1..].each { |i| q = q.or(i) }
        end

        q = 'FALSE' if q.blank?
        where(q) # .not_including(geographic_items)
      end
    end
    # rubocop:enable Metrics/MethodLength

    # @param [RGeo::Point] center in lon/lat
    # @param [Integer] radius of the circle, in meters
    # @param [Integer] buffer_resolution: the number of sides of the polygon
    #                  approximation per quarter circle
    # @return [RGeo::Polygon] A polygon approximation of the desired circle, in
    #                         geographic coordinates
    def circle(center, radius, buffer_resolution = 8)
      circle_wkb = select_value(
        st_buffer_sql(
          st_geography_from_text_sql(
            "POINT (#{center.lon} #{center.lat})",
          ),
          radius,
          num_seg_quarter_circle: buffer_resolution
        )
      )

      make_valid_non_anti_meridian_crossing_shape(
        Gis::FACTORY.parse_wkb(circle_wkb).as_text
      )
    end

    #
    # Other
    #

    # @param [RGeo::Point] point
    # @return [Hash]
    #   as per #inferred_geographic_name_hierarchy but for Rgeo point
    def point_inferred_geographic_name_hierarchy(point)
      where(superset_of_sql(st_geom_from_text_sql(point.to_s)))
      .order(cached_total_area: :ASC)
      .first&.inferred_geographic_name_hierarchy
    end

    def geography_as_geometry
      Arel.sql('geography::geometry')
    end

    # @param [GeographicItem]
    # @return [Scope]
    def not_including(geographic_items)
      where.not(id: geographic_items)
    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
    geographic_areas.order(:id).each do |ga|
      h = ga.geographic_name_classification # not quick enough !!
      return h if h.present?
    end

    {}
  end

  # @return [Hash]
  #   a geographic_name_classification (see GeographicArea) inferred by
  #   finding the smallest area covering 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 = covering_geographic_areas
      .joins(:geographic_areas_geographic_items)
      .merge(GeographicAreasGeographicItem.ordered_by_data_origin)
      .ordered_by_area
      .first

      return small_area.geographic_name_classification
    end

    {}
  end

  def geographic_name_hierarchy
    a = quick_geographic_name_hierarchy # quick; almost never the case, UI not setup to do this
    return a if a.present?
    inferred_geographic_name_hierarchy # slow
  end

  # @return [Scope]
  #   the Geographic Areas that cover (gis) this geographic item
  def covering_geographic_areas
    GeographicArea
      .joins(:geographic_items)
      .includes(:geographic_area_type)
      .joins(
        "JOIN (#{GeographicItem.superset_of_union_of(id).to_sql}) AS j ON " \
        'geographic_items.id = j.id'
      )
  end

  # @param [GeographicItem] geographic_item
  # @return [Double] distance in meters
  # Works with changed and non persisted objects
  def st_distance_to_geographic_item(geographic_item)
    if persisted? && !changed?
      a = self.class.select_geography_sql(id)
    else
      a = self.class.st_geography_from_text_sql(geo_object.to_s)
    end

    if geographic_item.persisted? && !geographic_item.changed?
      b = self.class.select_geography_sql(geographic_item.id)
    else
      b = self.class.st_geography_from_text_sql(geographic_item.geo_object.to_s)
    end

    self.class.select_value(
      self.class.st_distance_sql(a, b)
    )
  end

  # @return [String]
  #   a WKT POINT representing the geometry centroid of the geographic item
  def st_centroid
    select_from_self(
      self.class.st_as_text_sql(
        self.class.st_centroid_sql(self.class.geography_as_geometry)
      )
    )['st_astext']
  end

  # @return [RGeo::Geographic::ProjectedPointImpl]
  #    representing the geometric centroid of this geographic item
  def centroid
    return geo_object if geo_object_type == :point

    Gis::FACTORY.parse_wkt(st_centroid)
  end

  # @return [Array]
  #   the lat, long, as STRINGs for the geometric centroid of this geographic
  #   item
  #   Meh- this: https://postgis.net/docs/en/ST_MinimumBoundingRadius.html
  def center_coords
    select_from_self(
      self.class.lat_long_sql(:latitude),
      self.class.lat_long_sql(:longitude)
    ).values
  end

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

  # @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

  # @return [Hash] in GeoJSON format
  def to_geo_json
    JSON.parse(
      select_from_self(
        self.class.st_as_geo_json_sql(self.class.arel_table[:geography])
      )['st_asgeojson']
    )
  end

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

  # @param value [String] geojson like:
  #   '{"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":{}}'
  #
  #  '{"type":"Point","coordinates":[2.5,4.0]},"properties":{"color":"red"}}'
  #
  def shape=(value)
    return if value.blank?

    begin
      geom = RGeo::GeoJSON.decode(value, json_parser: :json, geo_factory: Gis::FACTORY)
    rescue RGeo::Error::InvalidGeometry => e
      errors.add(:base, "invalid geometry: #{e}")
      return
    end

    object = nil

    s = geom.respond_to?(:geometry) ? geom.geometry.to_s : geom.to_s

    begin
      object = Gis::FACTORY.parse_wkt(s)
    rescue RGeo::Error::InvalidGeometry => e
      errors.add(:self, "Shape value is an Invalid Geometry: '#{e}'")
      return
    end

    write_attribute(:geography, object)
  end

  # @return [String] wkt
  def to_wkt
    select_from_self(
      self.class.st_as_text_sql(self.class.geography_as_geometry)
    )['st_astext']
  end

  # @return [Float] area in square meters, calculated
  # TODO: share with world
  def area
    select_from_self(
      self.class.st_area_sql(self.class.arel_table[:geography])
    )['st_area']
  end

  # TODO: This is bad, while internal use of ONE_WEST_MEAN is consistent it is in-accurate given the vast differences of radius vs. lat/long position.
  # When we strike the error-polygon from radius we should remove this
  #
  # Use case is returning the radius from a circle we calculated via buffer for error-polygon creation.
  def radius
    r = select_from_self(
      self.class.st_minimum_bounding_radius_sql(
        self.class.geography_as_geometry
      )
    )['st_minimumboundingradius'].split(',').last.chop.to_f

    (r * Utilities::Geo::ONE_WEST_MEAN).to_i
  end

  # Convention is to store in PostGIS in CCW
  # @return Array [Boolean]
  #   false - cw
  #   true - ccw (preferred), except see donuts
  def orientations
    if geography_is_multi_polygon?
      ApplicationRecord.connection.execute(
            "SELECT ST_IsPolygonCCW(a.geom) as is_ccw
              FROM ( SELECT b.id, (ST_Dump(p_geom)).geom AS geom
                  FROM (SELECT id, geography::geometry AS p_geom FROM geographic_items where id = #{id}) AS b
            ) AS a;").collect{|a| a['is_ccw']}
    elsif geography_is_polygon?
      ApplicationRecord.connection.execute(
        "SELECT ST_IsPolygonCCW(geography::geometry) as is_ccw \
        FROM geographic_items where  id = #{id};"
      ).collect { |a| a['is_ccw'] }
    else
      []
    end
  end

  # @return Boolean
  #   looks at all orientations
  #   if they follow the pattern [true, false, ... <all false>] then `true`, else `false`
  # !! Does not confirm that shapes are nested !!
  def is_basic_donut?
    a = orientations
    b = a.shift
    return false unless b

    a.uniq! == [false]
  end

  def st_is_valid
    select_from_self(
      self.class.st_is_valid_sql(
        self.class.geography_as_geometry
      )
    )['st_isvalid']
  end

  def st_is_valid_reason
    select_from_self(
      self.class.st_is_valid_reason_sql(
        self.class.geography_as_geometry
      )
    )['st_isvalidreason']
  end

  # @return [Symbol]
  #   the specific type of geography: :point, :multi_polygon, etc.
  def geo_object_type
    return geography.geometry_type.type_name.underscore.to_sym if geography

    nil
  end

  # @return [RGeo instance, nil]
  #  the Rgeo shape
  def geo_object
    geography
  end

  private

  # @param [String or Symbol] shape, the kind of shape you want, e.g. :polygon
  # @return [Arel::Nodes::Case]
  #   A Case statement that selects the geography column if that column is of
  #   type `shape`, otherwise NULL
  def self.shape_column_sql(shape)
    st_shape = 'ST_' + shape.to_s.camelize

    Arel::Nodes::Case.new(st_geometry_type(arel_table[:geography]))
      .when(st_shape.to_sym).then(arel_table[:geography])
      .else(Arel.sql('NULL'))
  end

  def self.shape_is_type(shape)
    st_shape = 'ST_' + shape.to_s.camelize

    Arel::Nodes::Case.new(st_geometry_type(arel_table[:geography]))
      .when(st_shape.to_sym).then(Arel.sql('TRUE'))
      .else(Arel.sql('FALSE'))
  end

  def geography_is_point?
    geo_object_type == :point
  end

  def geography_is_polygon?
    geo_object_type == :polygon
  end

  def geography_is_multi_polygon?
    geo_object_type == :multi_polygon
  end

  def select_from_self(*named_function)
    # This is faster than GeographicItem.select(...)
    ActiveRecord::Base.connection.execute(
      self.class.arel_table
        .project(*named_function)
        .where(self.class.arel_table[:id].eq(id))
        .to_sql
    ).to_a.first
  end

  def self.select_value(named_function)
    # This is faster than select(...)
    ActiveRecord::Base.connection.select_value(
      'SELECT ' +
      named_function.to_sql
    )
  end

  def align_winding
    if orientations.flatten.include?(false)
      if (geography_is_polygon? || geography_is_multi_polygon?)
        ApplicationRecord.connection.execute(
          "UPDATE geographic_items SET geography = ST_ForcePolygonCCW(geography::geometry)
            WHERE id = #{self.id};"
          )
      end
    end
  end

  # Crude debuging helper, write the shapes
  # to a png
  def self.debug_draw(geographic_item_ids = [])
    return false if geographic_item_ids.empty?
    sql = "SELECT ST_AsPNG(
        ST_AsRaster(
          (SELECT ST_Union(geography::geometry) from geographic_items where id IN (" + geographic_item_ids.join(',') + ")), 1920, 1080
      )
      ) png;"

    # ST_Buffer( multi_polygon::geometry, 0, 'join=bevel'),
    #     1920,
    #     1080)


    result = ActiveRecord::Base.connection.execute(sql).first['png']
    r = ActiveRecord::Base.connection.unescape_bytea(result)

    prefix = if geographic_item_ids.size > 10
               'multiple'
              else
                geographic_item_ids.join('_')
              end

    n = prefix + '_debug.draw.png'

    # Open the file in binary write mode ("wb")
    File.open(n, 'wb') do |file|
      # Write the binary data to the file
      file.write(r)
    end
  end

  # def png

  #   if ids = Otu.joins(:cached_map_items).first.cached_map_items.pluck(:geographic_item_id)

  #     sql = "SELECT ST_AsPNG(
  #    ST_AsRaster(
  #        ST_Buffer( multi_polygon::geometry, 0, 'join=bevel'),
  #            1024,
  #            768)
  #     ) png
  #        from geographic_items where id IN (" + ids.join(',') + ');'

  #     # hack, not the best way to unpack result
  #     result = ActiveRecord::Base.connection.execute(sql).first['png']
  #     r = ActiveRecord::Base.connection.unescape_bytea(result)

  #     send_data r, filename: 'foo.png', type: 'imnage/png'

  #   else
  #     render json: {foo: false}
  #   end
  # end

  def set_cached
    update_column(:cached_total_area, area)
  end

  def some_data_is_provided
    errors.add(:base, 'No shape provided or provided shape is invalid') if
      geography.nil?
  end

  def normalize_point_longitude
    return if !geography_is_point?

    if geography.x < -180.0 || geography.x > 180.0
      new_lon = geography.x % 360.0
      new_lon = new_lon - 360.0 if new_lon > 180.0
      self.geography = Gis::FACTORY.point(new_lon, geography.y)
    end
  end

  # @return [Boolean] true if wkt needs to be longitude-translated to make its
  # longitudes be in the interval (0, 360).
  # This is a bit of a hack: whether or not to shift wkt depends on whether or
  # not its longitudes are already in the range (0,360), which indicates to rgeo
  # that hemisphere-crossing lines cross the anti-meridian, not the meridian.
  # (We make the assumption that there aren't coordinates in both (180, 360) and
  # (-180, 0), which is actually possible with hand-entered wkt, and not easy
  # to deal with.)
  # TODO: find a more canonical/rgeo way to do this?
  # TODO: support other wkt shape types as needed. MultiPolygon covers all
  # polygon inputs from leaflet, the main case.
  def self.wkt_needs_longitude_translation(wkt)
    # Use a cartesian factory that doesn't automagically normalize its
    # longitude inputs, as Gis::FACTORY does.
    s = RGeo::Cartesian.simple_factory.parse_wkt(wkt)

    translate_longitudes = true
    # Currently this is intended to support Leaflet polygons.
    if (s.geometry_type.type_name == 'MultiPolygon' && s.count == 1)
      translate_longitudes =
        s[0].exterior_ring.points.map(&:x).any? { |l| l < 0 }
    end

    translate_longitudes
  end

  def self.geography_cast(sql)
    # Arel::Nodes::NamedFunction.new('CAST', [sql.as('geography')])
    Arel.sql(
      Arel::Nodes::Grouping.new(sql).to_sql + '::geography'
    )
  end

  def self.geometry_cast(sql)
    # specs fail using:
    # Arel::Nodes::NamedFunction.new('CAST', [sql.as('geometry')])
    Arel.sql(
      Arel::Nodes::Grouping.new(sql).to_sql + '::geometry'
    )
  end

  def self.quote_string(s)
    ActiveRecord::Base.connection.quote_string(s)
  end

  # From the docs: Splits string at occurrences of delimiter and returns the
  # n'th field (counting from one), or when n is negative, returns the
  # |n|'th-from-last field.
  # !! postgresql-specific
  def self.split_part(s, delimiter, n)
    Arel::Nodes::NamedFunction.new(
        'split_part',
        [
          Arel::Nodes.build_quoted(s),
          Arel::Nodes.build_quoted(delimiter),
          Arel::Nodes.build_quoted(n)
        ]
      )
  end

end

#no_cached ⇒ Boolean

Returns When true cached values are not built.

Returns:

• (Boolean) —

  When true cached values are not built

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

def no_cached
  @no_cached
end

#shape ⇒ Boolean, RGeo object

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

Returns:

• (Boolean, RGeo object)

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

def shape
  @shape
end

Class Method Details

.anti_meridian_crossing_make_valid_sql(wkt) ⇒ Object

Assumes wkt crosses the anti-meridian. !! Note you must apply St_ShiftLongitude to the return value to have the correct interpretation of the return value’s relation to the anti-meridian (cf. anti_meridian_spec.rb).

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

def anti_meridian_crossing_make_valid_sql(wkt)
  st_make_valid_sql(
    st_shift_longitude_sql(
      st_geom_from_text_sql(
        wkt
      )
    )
  )
end

.buffer_crosses_anti_meridian?(wkt, distance) ⇒ Boolean

Returns whether or not the radius-buffer of wkt-point intersects the anti-meridian.

Parameters:

• wkt (String)
• buffer (Integer) —

  distance

Returns:

• (Boolean) —

  whether or not the radius-buffer of wkt-point intersects the anti-meridian

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

def buffer_crosses_anti_meridian?(wkt, distance)
  wkt = quote_string(wkt)
  select_value(
    st_intersects_sql(
      st_buffer_sql(
        st_geography_from_text_sql(wkt),
        distance
      ),
      st_geography_from_text_sql(ANTI_MERIDIAN)
    )
  )
end

.circle(center, radius, buffer_resolution = 8) ⇒ RGeo::Polygon

Returns A polygon approximation of the desired circle, in geographic coordinates.

Parameters:

• center (RGeo::Point) —

  in lon/lat

• radius (Integer) —

  of the circle, in meters

• buffer_resolution: (Integer) —

  the number of sides of the polygon approximation per quarter circle

Returns:

• (RGeo::Polygon) —

  A polygon approximation of the desired circle, in geographic coordinates

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

def circle(center, radius, buffer_resolution = 8)
  circle_wkb = select_value(
    st_buffer_sql(
      st_geography_from_text_sql(
        "POINT (#{center.lon} #{center.lat})",
      ),
      radius,
      num_seg_quarter_circle: buffer_resolution
    )
  )

  make_valid_non_anti_meridian_crossing_shape(
    Gis::FACTORY.parse_wkb(circle_wkb).as_text
  )
end

.covered_by_wkt_sql(wkt) ⇒ NamedFunction

covered by this WKT

Returns:

• (NamedFunction) —

  SQL fragment limiting geographic items to those

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

def covered_by_wkt_sql(wkt)
  wkt = quote_string(wkt)
  if crosses_anti_meridian?(wkt)
    translate_longitudes = wkt_needs_longitude_translation(wkt)
    wkt_geom = st_geom_from_text_sql(wkt)
    subset_of_shifted_anti_meridian_shape_sql(
      translate_longitudes ? st_shift_longitude_sql(wkt_geom) : wkt_geom
    )
  else
    subset_of_sql(
      st_geom_from_text_sql(wkt)
    )
  end
end

.crosses_anti_meridian?(wkt) ⇒ Boolean

Returns whether or not the wkt intersects with the anti-meridian.

Parameters:

• wkt (String)

Returns:

• (Boolean) —

  whether or not the wkt intersects with the anti-meridian

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

def crosses_anti_meridian?(wkt)
  wkt = quote_string(wkt)
  select_value(
    st_intersects_sql(
      st_geography_from_text_sql(wkt),
      st_geography_from_text_sql(ANTI_MERIDIAN)
    )
  )
end

.crosses_anti_meridian_by_id?(*ids) ⇒ Boolean

Unused, kept for reference

Parameters:

• ids (Integer)

Returns:

• (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.

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

def crosses_anti_meridian_by_id?(*ids)
  q = "SELECT ST_Intersects((SELECT single_geometry FROM (#{GeographicItem.single_geometry_sql(*ids)}) as " \
        'left_intersect), ST_GeogFromText(?)) as r;', ANTI_MERIDIAN
  GeographicItem.find_by_sql(q).first.r
end

.debug_draw(geographic_item_ids = []) ⇒ Object (private)

Crude debuging helper, write the shapes to a png

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

def self.debug_draw(geographic_item_ids = [])
  return false if geographic_item_ids.empty?
  sql = "SELECT ST_AsPNG(
      ST_AsRaster(
        (SELECT ST_Union(geography::geometry) from geographic_items where id IN (" + geographic_item_ids.join(',') + ")), 1920, 1080
    )
    ) png;"

  # ST_Buffer( multi_polygon::geometry, 0, 'join=bevel'),
  #     1920,
  #     1080)


  result = ActiveRecord::Base.connection.execute(sql).first['png']
  r = ActiveRecord::Base.connection.unescape_bytea(result)

  prefix = if geographic_item_ids.size > 10
             'multiple'
            else
              geographic_item_ids.join('_')
            end

  n = prefix + '_debug.draw.png'

  # Open the file in binary write mode ("wb")
  File.open(n, 'wb') do |file|
    # Write the binary data to the file
    file.write(r)
  end
end

.geography_as_geometry ⇒ Object

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

def geography_as_geometry
  Arel.sql('geography::geometry')
end

.geography_cast(sql) ⇒ Object (private)

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

def self.geography_cast(sql)
  # Arel::Nodes::NamedFunction.new('CAST', [sql.as('geography')])
  Arel.sql(
    Arel::Nodes::Grouping.new(sql).to_sql + '::geography'
  )
end

.geometry_cast(sql) ⇒ Object (private)

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

def self.geometry_cast(sql)
  # specs fail using:
  # Arel::Nodes::NamedFunction.new('CAST', [sql.as('geometry')])
  Arel.sql(
    Arel::Nodes::Grouping.new(sql).to_sql + '::geometry'
  )
end

.intersecting(shape, *geographic_item_ids) ⇒ Object

This is a geographic intersect, not geometric

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

def intersecting(shape, *geographic_item_ids)
  shape = shape.to_s.downcase
  if shape == 'any'
    pieces = []
    SHAPE_TYPES.each { |shape|
      pieces.push(
        intersecting(shape, geographic_item_ids).to_a
      )
    }

    # @TODO change 'id in (?)' to some other sql construct
    GeographicItem.where(id: pieces.flatten.map(&:id))
  else
    a = geographic_item_ids.flatten.collect { |geographic_item_id|
      # seems like we want this: http://danshultz.github.io/talks/mastering_activerecord_arel/#/15/2
      st_intersects_sql(
        shape_column_sql(shape),
        select_geography_sql(geographic_item_id)
      )
    }

    q = a.first
    if a.count > 1
      a[1..].each { |i| q = q.or(i) }
    end

    where(q)
  end
end

.intersecting_radius_of_wkt_sql(wkt, distance) ⇒ NamedFunction

!! This is computed in 2d

Parameters:

• wkt (String)
• distance (Integer) —

  (meters)

Returns:

• (NamedFunction) —

  Shapes whose distance to wkt is less than ‘distance`

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

def intersecting_radius_of_wkt_sql(wkt, distance)
  wkt = quote_string(wkt)
  st_dwithin_sql(
    st_geography_from_text_sql(wkt),
    arel_table[:geography],
    distance
  )
end

.items_as_one_geometry_sql(*geographic_item_ids) ⇒ SelectManager for one id, Arel::Nodes::SqlLiteral for more than one

Parameters:

• geographic_item_ids (Integer, Array of Integer)

Returns:

• (SelectManager for one id, Arel::Nodes::SqlLiteral for more than one)

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

def items_as_one_geometry_sql(*geographic_item_ids)
  geographic_item_ids.flatten! # *ALWAYS* reduce the pile to a single level of ids
  if geographic_item_ids.count == 1
    select_geometry_sql(geographic_item_ids.first)
  else
    single_geometry_sql(geographic_item_ids)
  end
end

.lat_long_sql(choice) ⇒ Arel::Nodes::NamedFunction

Returns a fragment returning either latitude or longitude columns.

Parameters:

• choice, (Symbol) —

  either :latitude or :longitude

Returns:

• (Arel::Nodes::NamedFunction) —

  a fragment returning either latitude or longitude columns

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

def lat_long_sql(choice)
  return nil unless [:latitude, :longitude].include?(choice)
  f = 'D.DDDDDD'
  v = (choice == :latitude ? 1 : 2)

  split_part(
    st_as_lat_lon_text_sql(
      st_centroid_sql(geography_as_geometry),
      f
    ),
    ' ',
    v
  ).as(choice.to_s)
end

.make_valid_non_anti_meridian_crossing_shape(wkt) ⇒ Object

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

def make_valid_non_anti_meridian_crossing_shape(wkt)
  if crosses_anti_meridian?(wkt)
    split_along_anti_meridian(wkt, make_valid: true)
  else
    wkb = select_value(
      st_make_valid_sql(
        st_geom_from_text_sql(
          wkt
        )
      )
    )

    ::Gis::FACTORY.parse_wkb(wkb)
  end
end

.not_including(geographic_items) ⇒ Scope

Parameters:

• (GeographicItem)

Returns:

• (Scope)

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

def not_including(geographic_items)
  where.not(id: geographic_items)
end

.point_inferred_geographic_name_hierarchy(point) ⇒ Hash

Returns as per #inferred_geographic_name_hierarchy but for Rgeo point.

Parameters:

• point (RGeo::Point)

Returns:

• (Hash) —

  as per #inferred_geographic_name_hierarchy but for Rgeo point

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

def point_inferred_geographic_name_hierarchy(point)
  where(superset_of_sql(st_geom_from_text_sql(point.to_s)))
  .order(cached_total_area: :ASC)
  .first&.inferred_geographic_name_hierarchy
end

.quote_string(s) ⇒ Object (private)

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

def self.quote_string(s)
  ActiveRecord::Base.connection.quote_string(s)
end

.select_geography_sql(geographic_item_id) ⇒ SelectManager

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

Parameters:

• (Integer, String)

Returns:

• (SelectManager) —

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

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

def select_geography_sql(geographic_item_id)
  arel_table
    .project(arel_table[:geography])
    .where(arel_table[:id].eq(geographic_item_id))
end

.select_geometry_sql(geographic_item_id) ⇒ SelectManager

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

Parameters:

• (Integer, String)

Returns:

• (SelectManager) —

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

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

def select_geometry_sql(geographic_item_id)
  arel_table
    .project(geography_as_geometry)
    .where(arel_table[:id].eq(geographic_item_id))
end

.select_value(named_function) ⇒ Object (private)

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

def self.select_value(named_function)
  # This is faster than select(...)
  ActiveRecord::Base.connection.select_value(
    'SELECT ' +
    named_function.to_sql
  )
end

.shape_column_sql(shape) ⇒ Arel::Nodes::Case (private)

Returns A Case statement that selects the geography column if that column is of type ‘shape`, otherwise NULL.

Parameters:

• shape, (String or Symbol) —

  the kind of shape you want, e.g. :polygon

Returns:

• (Arel::Nodes::Case) —

  A Case statement that selects the geography column if that column is of type ‘shape`, otherwise NULL

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

def self.shape_column_sql(shape)
  st_shape = 'ST_' + shape.to_s.camelize

  Arel::Nodes::Case.new(st_geometry_type(arel_table[:geography]))
    .when(st_shape.to_sym).then(arel_table[:geography])
    .else(Arel.sql('NULL'))
end

.shape_is_type(shape) ⇒ Object (private)

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

def self.shape_is_type(shape)
  st_shape = 'ST_' + shape.to_s.camelize

  Arel::Nodes::Case.new(st_geometry_type(arel_table[:geography]))
    .when(st_shape.to_sym).then(Arel.sql('TRUE'))
    .else(Arel.sql('FALSE'))
end

.single_geometry_sql(*geographic_item_ids) ⇒ Arel::Nodes::SqlLiteral

Returns one or more geographic items combined as a single geometry in a column ‘single_geometry’

Parameters:

• geographic_item_ids (Integer, Array of Integer)

Returns:

• (Arel::Nodes::SqlLiteral) —

  returns one or more geographic items combined as a single geometry in a column ‘single_geometry’

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

def single_geometry_sql(*geographic_item_ids)
  geographic_item_ids.flatten!

  Arel.sql(
    "SELECT ST_Collect(f.the_geom) AS single_geometry
      FROM (
        SELECT (
          ST_DUMP(geography::geometry)
        ).geom AS the_geom
        FROM geographic_items
        WHERE id in (#{geographic_item_ids.join(',')})
      ) AS f"
  )
end

.split_along_anti_meridian(wkt, make_valid: false) ⇒ Object

intersect the anti-meridian. Slightly lossy (has to be), and may turn polygon into multi-polygon, etc. Assumes wkt intersects the anti-meridian.

Parameters:

• wkt (String)

Returns:

• RGeo shape for wkt expressed as a union of pieces none of which

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

def split_along_anti_meridian(wkt, make_valid: false)
  wkt = quote_string(wkt)
  # Intended to be the exterior of a tiny buffer around the anti-meridian,
  # expressed as two sheets/near-hemispheres that meet at long=0=360.
  anti_meridian_exterior = 'MULTIPOLYGON(
    ((0 -89.999999, 179.999999 -89.999999, 179.999999 89.999999, 0 89.999999, 0 -89.999999)),
    ((180.000001 -89.999999, 360 -89.999999, 360 89.999999, 180.000001 89.999999, 180.000001 -89.999999))
  )'

  s = make_valid ?
    anti_meridian_crossing_make_valid_sql(wkt) :
    st_shift_longitude_sql(st_geom_from_text_sql(wkt))

  wkb = select_value(
    st_intersection_sql(
      s,
      st_geom_from_text_sql(anti_meridian_exterior)
    )
  )

  ::Gis::FACTORY.parse_wkb(wkb)
end

.split_part(s, delimiter, n) ⇒ Object (private)

From the docs: Splits string at occurrences of delimiter and returns the n’th field (counting from one), or when n is negative, returns the |n|‘th-from-last field. !! postgresql-specific

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

def self.split_part(s, delimiter, n)
  Arel::Nodes::NamedFunction.new(
      'split_part',
      [
        Arel::Nodes.build_quoted(s),
        Arel::Nodes.build_quoted(delimiter),
        Arel::Nodes.build_quoted(n)
      ]
    )
end

.st_area_sql(shape) ⇒ Object

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

def st_area_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_Area', [
      shape
    ]
  )
end

.st_as_geo_json_sql(shape) ⇒ Object

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

def st_as_geo_json_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_AsGeoJSON', [
      shape
    ]
  )
end

.st_as_lat_lon_text_sql(point_shape, format = '') ⇒ Object

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

def st_as_lat_lon_text_sql(point_shape, format = '')
  Arel::Nodes::NamedFunction.new(
    'ST_AsLatLonText', [
      geometry_cast(point_shape),
      Arel::Nodes.build_quoted(format)
    ]
  )
end

.st_as_text_sql(shape) ⇒ Object

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

def st_as_text_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_AsText', [
      shape
    ]
  )
end

.st_buffer_sql(shape, distance, num_seg_quarter_circle: 8) ⇒ Object

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

def st_buffer_sql(shape, distance, num_seg_quarter_circle: 8)
  Arel::Nodes::NamedFunction.new(
    'ST_Buffer', [
      geography_cast(shape),
      Arel::Nodes.build_quoted(distance),
      Arel::Nodes.build_quoted(num_seg_quarter_circle)
    ]
  )
end

.st_buffer_st_within_sql(geographic_item_id, distance, buffer = 0) ⇒ NamedFunction

Returns Shapes whose ‘buffer` is within `distance` of geographic_item.

Parameters:

• geographic_item_id (Integer)
• distance (Number) —

  (in meters) (positive only?!)

• buffer: (Number) —

  distance in meters to grow/shrink the shapes checked against (negative allowed)

Returns:

• (NamedFunction) —

  Shapes whose ‘buffer` is within `distance` of geographic_item

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

def st_buffer_st_within_sql(geographic_item_id, distance, buffer = 0)
  # You can't always switch the buffer to the second argument, even when
  # distance is 0, without further assumptions (think of buffer being
  # large negative compared to geographic_item_id, but not another shape))
  st_dwithin_sql(
    st_buffer_sql(
      arel_table[:geography],
      buffer
    ),
    select_geography_sql(geographic_item_id),
    distance
  )
end

.st_centroid_sql(shape) ⇒ Object

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

def st_centroid_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_Centroid', [
      shape
    ]
  )
end

.st_collect_sql(shape) ⇒ Object

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

def st_collect_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_Collect', [
      shape
    ]
  )
end

.st_covered_by(shape, *geographic_items) ⇒ Scope

rubocop:disable Metrics/MethodLength against all types, ‘any_poly’ to check against ‘polygon’ or ‘multi_polygon’, or ‘any_line’ to check against ‘line_string’ or ‘multi_line_string’. GeographicItem, or an array of GeographicItem. one or more of geographic_items !! Returns geographic_item when geographic_item is of type ‘shape`

Parameters:

• shape (String) —

  can be any of SHAPE_TYPES, or ‘any’ to check

• geographic_items (GeographicItem) —

  Can be a single

Returns:

• (Scope) —

  of all GeographicItems of the given ‘shape` covered by

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

def st_covered_by(shape, *geographic_items)
  shape = shape.to_s.downcase
  case shape
  when 'any'
    part = []
    SHAPE_TYPES.each { |s|
      part.push(GeographicItem.st_covered_by(s, geographic_items).to_a)
    }
    # @TODO change 'id in (?)' to some other sql construct
    GeographicItem.where(id: part.flatten.map(&:id))

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

  else
    a = geographic_items.flatten.collect { |geographic_item|
      st_covered_by_sql(
        shape_column_sql(shape),
        select_geometry_sql(geographic_item.id)
      )
    }

    q = a.first
    if a.count > 1
      a[1..].each { |i| q = q.or(i) }
    end

    q = 'FALSE' if q.blank?
    where(q) # .not_including(geographic_items)
  end
end

.st_covered_by_sql(shape1, shape2) ⇒ Object

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

def st_covered_by_sql(shape1, shape2)
  Arel::Nodes::NamedFunction.new(
    'ST_CoveredBy',
    [
      geometry_cast(shape1),
      geometry_cast(shape2)
    ]
  )
end

.st_covers(shape, *geographic_items) ⇒ Scope

rubocop:disable Metrics/MethodLength against all types, ‘any_poly’ to check against ‘polygon’ or ‘multi_polygon’, or ‘any_line’ to check against ‘line_string’ or ‘multi_line_string’. 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_Covers(polygon::geometry, GeomFromEWKT('srid=4326;POINT (0.0 0.0 0.0)'))
       OR ST_Covers(polygon::geometry, GeomFromEWKT('srid=4326;POINT (-9.8 5.0 0.0)')))

Parameters:

• shape (String) —

  can be any of SHAPE_TYPES, or ‘any’ to check

• geographic_items (GeographicItem) —

  or array of geographic_items to be tested.

Returns:

• (Scope) —

  of GeographicItems whose ‘shape` contains at least one of geographic_items. !! Returns geographic_item when geographic_item is of

  type `shape`
  

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

def st_covers(shape, *geographic_items)
  geographic_items.flatten! # in case there is a array of arrays, or multiple objects
  shape = shape.to_s.downcase
  case shape
  when 'any'
    part = []
    SHAPE_TYPES.each { |s|
      part.push(st_covers(s, geographic_items).to_a)
    }
    # TODO: change 'id in (?)' to some other sql construct
    where(id: part.flatten.map(&:id))

  when 'any_poly', 'any_line'
    part = []
    SHAPE_TYPES.each { |s|
      s = s.to_s
      if s.index(shape.gsub('any_', ''))
        part.push(st_covers(s, geographic_items).to_a)
      end
    }
    # TODO: change 'id in (?)' to some other sql construct
    where(id: part.flatten.map(&:id))

  else
    a = geographic_items.flatten.collect { |geographic_item|
      st_covers_sql(
        shape_column_sql(shape),
        select_geometry_sql(geographic_item.id)
      )
    }

    q = a.first
    if a.count > 1
      a[1..].each { |i| q = q.or(i) }
    end

    # This will prevent the invocation of *ALL* of the GeographicItems
    # if there are no GeographicItems in the request (see
    # CollectingEvent.name_hash(types)).
    q = 'FALSE' if q.blank?
    where(q) # .not_including(geographic_items)
  end
end

.st_covers_sql(shape1, shape2) ⇒ Object

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

def st_covers_sql(shape1, shape2)
  Arel::Nodes::NamedFunction.new(
    'ST_Covers',
    [
      geometry_cast(shape1),
      geometry_cast(shape2)
    ]
  )
end

.st_distance_sql(shape1, shape2) ⇒ Object

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

def st_distance_sql(shape1, shape2)
  Arel::Nodes::NamedFunction.new(
    'ST_Distance', [
      shape1,
      shape2
    ]
  )
end

.st_dump_sql(shape) ⇒ Object

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

def st_dump_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_Dump', [
      shape
    ]
  )
end

.st_dwithin_sql(shape1, shape2, distance) ⇒ Object

True if the distance from shape1 to shape2 is less than ‘distance`. This is a geography dwithin, distance is in meters.

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

def st_dwithin_sql(shape1, shape2, distance)
  Arel::Nodes::NamedFunction.new(
    'ST_DWithin', [
      geography_cast(shape1),
      geography_cast(shape2),
      Arel::Nodes.build_quoted(distance)
    ]
  )
end

.st_geography_from_text_sql(wkt) ⇒ Object Also known as: st_geog_from_text_sql

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

def st_geography_from_text_sql(wkt)
  wkt = quote_string(wkt)
  Arel::Nodes::NamedFunction.new(
    'ST_GeographyFromText', [
      Arel::Nodes.build_quoted(wkt),
    ]
  )
end

.st_geometry_from_text_sql(wkt) ⇒ Object Also known as: st_geom_from_text_sql

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

def st_geometry_from_text_sql(wkt)
  wkt = quote_string(wkt)
  Arel::Nodes::NamedFunction.new(
    'ST_GeometryFromText', [
      Arel::Nodes.build_quoted(wkt),
      Arel::Nodes.build_quoted(4326)
    ]
  )
end

.st_geometry_type(shape) ⇒ Object

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

def st_geometry_type(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_GeometryType', [
      geometry_cast(shape)
    ]
  )
end

.st_intersection_sql(shape1, shape2) ⇒ Object

!! Keep in mind that you may get different results depending on if the inputs are geographies or geometries.

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

def st_intersection_sql(shape1, shape2)
  Arel::Nodes::NamedFunction.new(
    'ST_Intersection', [
      shape1,
      shape2
    ]
  )
end

.st_intersects_sql(shape1, shape2) ⇒ Object

# !! Keep in mind that you may get different results depending on if the inputs are geographies or geometries.

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

def st_intersects_sql(shape1, shape2)
  Arel::Nodes::NamedFunction.new(
    'ST_Intersects', [
      shape1,
      shape2
    ]
  )
end

.st_is_valid_reason_sql(shape) ⇒ Object

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

def st_is_valid_reason_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_IsValidReason', [
      shape
    ]
  )
end

.st_is_valid_sql(shape) ⇒ Object

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

def st_is_valid_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_IsValid', [
      shape
    ]
  )
end

.st_make_valid_sql(shape) ⇒ Object

Returns valid shapes unchanged. !! This will give the wrong result on anti-meridian-crossing shapes stored in Gis::FACTORY coordinates, use anti_meridian_crossing_make_valid instead in that case.

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

def st_make_valid_sql(shape)
  # TODO add params once we're on GEOS >= 3.10, they're not used until then
  #params = "method=structure keepcollapsed=false"
  Arel::Nodes::NamedFunction.new(
    'ST_MakeValid', [
      geometry_cast(shape)
      #Arel::Nodes.build_quoted(params)
    ]
  )
end

.st_minimum_bounding_radius_sql(shape) ⇒ Object

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

def st_minimum_bounding_radius_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_MinimumBoundingRadius', [
      shape
    ]
  )
end

.st_shift_longitude_sql(shape) ⇒ Object

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

def st_shift_longitude_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_ShiftLongitude', [
      geometry_cast(shape)
    ]
  )
end

.st_union(geographic_item_scope) ⇒ Object

DEPRECATED, moved to ::Queries::GeographicItem

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

def st_union(geographic_item_scope)
  select('ST_Union(geography::geometry) as st_union')
    .where(id: geographic_item_scope.pluck(:id))
end

.st_union_sql(shape) ⇒ Object

Intended here to be used as an aggregate function

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

def st_union_sql(shape)
  Arel::Nodes::NamedFunction.new(
    'ST_Union', [
      shape
    ]
  )
end

.subset_of_shifted_anti_meridian_shape_sql(shape) ⇒ Object

Only called when shape crosses the anti-meridian !! shape must be pre-shifted to longitude-range (0, 360) !!

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

def subset_of_shifted_anti_meridian_shape_sql(shape)
  st_covered_by_sql(
    # All database geographic_items are (!! should be !!) stored in our
    # Gis::FACTORY-enforced longitude range (-180, 180), so always need to
    # be shifted in this case to the range (0, 360).
    st_shift_longitude_sql(geography_as_geometry),
    shape
  )
end

.subset_of_sql(shape) ⇒ Object

True for those shapes that are subsets of shape.

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

def subset_of_sql(shape)
  st_covered_by_sql(
    geography_as_geometry,
    shape
  )
end

.subset_of_union_of_sql(*geographic_item_ids) ⇒ Object

Note: !! If the target GeographicItem#id crosses the anti-meridian then you may/will get unexpected results.

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

def subset_of_union_of_sql(*geographic_item_ids)
  subset_of_sql(
    items_as_one_geometry_sql(*geographic_item_ids)
  )
end

.superset_of_sql(shape) ⇒ Object

True for those shapes that cover shape.

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

def superset_of_sql(shape)
  st_covers_sql(
    geography_as_geometry,
    shape
  )
end

.superset_of_union_of(*geographic_item_ids) ⇒ Scope

does not include any of geographic_item_ids

Returns:

• (Scope) —

  of items covering the union of geographic_item_ids;

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

def superset_of_union_of(*geographic_item_ids)
  where(
    superset_of_sql(
      items_as_one_geometry_sql(*geographic_item_ids)
    )
  )
  .not_ids(*geographic_item_ids)
end

.with_collecting_event_through_georeferences ⇒ Scope

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

Returns:

• (Scope) —

  GeographicItem

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

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

.within_radius_of_item(geographic_item_id, radius) ⇒ Object

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

def within_radius_of_item(geographic_item_id, radius)
  where(within_radius_of_item_sql(geographic_item_id, radius))
end

.within_radius_of_item_sql(geographic_item_id, radius) ⇒ Scope

Returns of shapes within distance of (i.e. whose distance-buffer intersects) geographic_item_id.

Parameters:

• geographic_item_id (Integer)
• radius (Integer) —

  in meters

Returns:

• (Scope) —

  of shapes within distance of (i.e. whose distance-buffer intersects) geographic_item_id

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

def within_radius_of_item_sql(geographic_item_id, radius)
  st_dwithin_sql(
    select_geography_sql(geographic_item_id),
    arel_table[:geography],
    radius
  )
end

.within_radius_of_wkt_sql(wkt, distance) ⇒ NamedFunction

Returns Those items covered by the ‘distance`-buffer of wkt.

Parameters:

• wkt (String)
• distance (Integer) —

  (meters)

Returns:

• (NamedFunction) —

  Those items covered by the ‘distance`-buffer of wkt

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

def within_radius_of_wkt_sql(wkt, distance)
  wkt = quote_string(wkt)

  if buffer_crosses_anti_meridian?(wkt, distance)
    subset_of_shifted_anti_meridian_shape_sql(
      # st_buffer_sql always has longitudes in (-180, 180) in this case, so
      # shift to (0, 360)
      st_shift_longitude_sql(
        st_buffer_sql(
          st_geography_from_text_sql(wkt),
          distance
        )
      )
    )
  else
    subset_of_sql(
      st_buffer_sql(
        st_geography_from_text_sql(wkt),
        distance
      )
    )
  end
end

.wkt_needs_longitude_translation(wkt) ⇒ Boolean (private)

longitudes be in the interval (0, 360). This is a bit of a hack: whether or not to shift wkt depends on whether or not its longitudes are already in the range (0,360), which indicates to rgeo that hemisphere-crossing lines cross the anti-meridian, not the meridian. (We make the assumption that there aren’t coordinates in both (180, 360) and (-180, 0), which is actually possible with hand-entered wkt, and not easy to deal with.) TODO: find a more canonical/rgeo way to do this? TODO: support other wkt shape types as needed. MultiPolygon covers all polygon inputs from leaflet, the main case.

Returns:

• (Boolean) —

  true if wkt needs to be longitude-translated to make its

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

def self.wkt_needs_longitude_translation(wkt)
  # Use a cartesian factory that doesn't automagically normalize its
  # longitude inputs, as Gis::FACTORY does.
  s = RGeo::Cartesian.simple_factory.parse_wkt(wkt)

  translate_longitudes = true
  # Currently this is intended to support Leaflet polygons.
  if (s.geometry_type.type_name == 'MultiPolygon' && s.count == 1)
    translate_longitudes =
      s[0].exterior_ring.points.map(&:x).any? { |l| l < 0 }
  end

  translate_longitudes
end

Instance Method Details

#align_winding ⇒ Object (private)

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

def align_winding
  if orientations.flatten.include?(false)
    if (geography_is_polygon? || geography_is_multi_polygon?)
      ApplicationRecord.connection.execute(
        "UPDATE geographic_items SET geography = ST_ForcePolygonCCW(geography::geometry)
          WHERE id = #{self.id};"
        )
    end
  end
end

#area ⇒ Float

TODO: share with world

Returns:

• (Float) —

  area in square meters, calculated

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

def area
  select_from_self(
    self.class.st_area_sql(self.class.arel_table[:geography])
  )['st_area']
end

#center_coords ⇒ Array

Returns the lat, long, as STRINGs for the geometric centroid of this geographic item Meh- this: postgis.net/docs/en/ST_MinimumBoundingRadius.html.

Returns:

• (Array) —

  the lat, long, as STRINGs for the geometric centroid of this geographic item Meh- this: postgis.net/docs/en/ST_MinimumBoundingRadius.html

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

def center_coords
  select_from_self(
    self.class.lat_long_sql(:latitude),
    self.class.lat_long_sql(:longitude)
  ).values
end

#centroid ⇒ RGeo::Geographic::ProjectedPointImpl

Returns representing the geometric centroid of this geographic item.

Returns:

• (RGeo::Geographic::ProjectedPointImpl) —

  representing the geometric centroid of this geographic item

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

def centroid
  return geo_object if geo_object_type == :point

  Gis::FACTORY.parse_wkt(st_centroid)
end

#contains?(target_geo_object) ⇒ Boolean

Parameters:

• (geo_object)

Returns:

• (Boolean)

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

def contains?(target_geo_object)
  return nil if target_geo_object.nil?
  self.geo_object.contains?(target_geo_object)
end

#covering_geographic_areas ⇒ Scope

Returns the Geographic Areas that cover (gis) this geographic item.

Returns:

• (Scope) —

  the Geographic Areas that cover (gis) this geographic item

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

def covering_geographic_areas
  GeographicArea
    .joins(:geographic_items)
    .includes(:geographic_area_type)
    .joins(
      "JOIN (#{GeographicItem.superset_of_union_of(id).to_sql}) AS j ON " \
      'geographic_items.id = j.id'
    )
end

#geo_object ⇒ RGeo instance^?

Returns the Rgeo shape.

Returns:

• (RGeo instance, nil) —

  the Rgeo shape

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

def geo_object
  geography
end

#geo_object_type ⇒ Symbol

Returns the specific type of geography: :point, :multi_polygon, etc.

Returns:

• (Symbol) —

  the specific type of geography: :point, :multi_polygon, etc.

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

def geo_object_type
  return geography.geometry_type.type_name.underscore.to_sym if geography

  nil
end

#geographic_name_hierarchy ⇒ Object

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

def geographic_name_hierarchy
  a = quick_geographic_name_hierarchy # quick; almost never the case, UI not setup to do this
  return a if a.present?
  inferred_geographic_name_hierarchy # slow
end

#geography_is_multi_polygon? ⇒ Boolean (private)

Returns:

• (Boolean)

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

def geography_is_multi_polygon?
  geo_object_type == :multi_polygon
end

#geography_is_point? ⇒ Boolean (private)

Returns:

• (Boolean)

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

def geography_is_point?
  geo_object_type == :point
end

#geography_is_polygon? ⇒ Boolean (private)

Returns:

• (Boolean)

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

def geography_is_polygon?
  geo_object_type == :polygon
end

#inferred_geographic_name_hierarchy ⇒ Hash

Returns a geographic_name_classification (see GeographicArea) inferred by finding the smallest area covering 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.

Returns:

• (Hash) —

  a geographic_name_classification (see GeographicArea) inferred by finding the smallest area covering 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.

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

def inferred_geographic_name_hierarchy
  if small_area = covering_geographic_areas
    .joins(:geographic_areas_geographic_items)
    .merge(GeographicAreasGeographicItem.ordered_by_data_origin)
    .ordered_by_area
    .first

    return small_area.geographic_name_classification
  end

  {}
end

#intersects?(target_geo_object) ⇒ Boolean

Parameters:

• (geo_object)

Returns:

• (Boolean)

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

def intersects?(target_geo_object)
  self.geo_object.intersects?(target_geo_object)
end

#is_basic_donut? ⇒ Boolean

!! Does not confirm that shapes are nested !!

Returns:

• (Boolean) —

  Boolean looks at all orientations if they follow the pattern [true, false, … <all false>] then ‘true`, else `false`

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

def is_basic_donut?
  a = orientations
  b = a.shift
  return false unless b

  a.uniq! == [false]
end

#normalize_point_longitude ⇒ Object (private)

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

def normalize_point_longitude
  return if !geography_is_point?

  if geography.x < -180.0 || geography.x > 180.0
    new_lon = geography.x % 360.0
    new_lon = new_lon - 360.0 if new_lon > 180.0
    self.geography = Gis::FACTORY.point(new_lon, geography.y)
  end
end

#orientations ⇒ Object

Convention is to store in PostGIS in CCW

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

def orientations
  if geography_is_multi_polygon?
    ApplicationRecord.connection.execute(
          "SELECT ST_IsPolygonCCW(a.geom) as is_ccw
            FROM ( SELECT b.id, (ST_Dump(p_geom)).geom AS geom
                FROM (SELECT id, geography::geometry AS p_geom FROM geographic_items where id = #{id}) AS b
          ) AS a;").collect{|a| a['is_ccw']}
  elsif geography_is_polygon?
    ApplicationRecord.connection.execute(
      "SELECT ST_IsPolygonCCW(geography::geometry) as is_ccw \
      FROM geographic_items where  id = #{id};"
    ).collect { |a| a['is_ccw'] }
  else
    []
  end
end

#quick_geographic_name_hierarchy ⇒ 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.

Returns:

• (Hash) —

  a geographic_name_classification or empty Hash

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

def quick_geographic_name_hierarchy
  geographic_areas.order(:id).each do |ga|
    h = ga.geographic_name_classification # not quick enough !!
    return h if h.present?
  end

  {}
end

#radius ⇒ Object

TODO: This is bad, while internal use of ONE_WEST_MEAN is consistent it is in-accurate given the vast differences of radius vs. lat/long position. When we strike the error-polygon from radius we should remove this

Use case is returning the radius from a circle we calculated via buffer for error-polygon creation.

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

def radius
  r = select_from_self(
    self.class.st_minimum_bounding_radius_sql(
      self.class.geography_as_geometry
    )
  )['st_minimumboundingradius'].split(',').last.chop.to_f

  (r * Utilities::Geo::ONE_WEST_MEAN).to_i
end

#select_from_self(*named_function) ⇒ Object (private)

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

def select_from_self(*named_function)
  # This is faster than GeographicItem.select(...)
  ActiveRecord::Base.connection.execute(
    self.class.arel_table
      .project(*named_function)
      .where(self.class.arel_table[:id].eq(id))
      .to_sql
  ).to_a.first
end

#set_cached ⇒ Object (private)

else

  render json: {foo: false}
end

end

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

def set_cached
  update_column(:cached_total_area, area)
end

#some_data_is_provided ⇒ Object (private)

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

def some_data_is_provided
  errors.add(:base, 'No shape provided or provided shape is invalid') if
    geography.nil?
end

#st_centroid ⇒ String

Returns a WKT POINT representing the geometry centroid of the geographic item.

Returns:

• (String) —

  a WKT POINT representing the geometry centroid of the geographic item

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

def st_centroid
  select_from_self(
    self.class.st_as_text_sql(
      self.class.st_centroid_sql(self.class.geography_as_geometry)
    )
  )['st_astext']
end

#st_distance_to_geographic_item(geographic_item) ⇒ Double

Works with changed and non persisted objects

Parameters:

• geographic_item (GeographicItem)

Returns:

• (Double) —

  distance in meters

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

def st_distance_to_geographic_item(geographic_item)
  if persisted? && !changed?
    a = self.class.select_geography_sql(id)
  else
    a = self.class.st_geography_from_text_sql(geo_object.to_s)
  end

  if geographic_item.persisted? && !geographic_item.changed?
    b = self.class.select_geography_sql(geographic_item.id)
  else
    b = self.class.st_geography_from_text_sql(geographic_item.geo_object.to_s)
  end

  self.class.select_value(
    self.class.st_distance_sql(a, b)
  )
end

#st_is_valid ⇒ Object

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

def st_is_valid
  select_from_self(
    self.class.st_is_valid_sql(
      self.class.geography_as_geometry
    )
  )['st_isvalid']
end

#st_is_valid_reason ⇒ Object

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

def st_is_valid_reason
  select_from_self(
    self.class.st_is_valid_reason_sql(
      self.class.geography_as_geometry
    )
  )['st_isvalidreason']
end

#to_geo_json ⇒ Hash

Returns in GeoJSON format.

Returns:

• (Hash) —

  in GeoJSON format

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

def to_geo_json
  JSON.parse(
    select_from_self(
      self.class.st_as_geo_json_sql(self.class.arel_table[:geography])
    )['st_asgeojson']
  )
end

#to_geo_json_feature ⇒ Hash

Returns the shape as a GeoJSON Feature with some item metadata.

Returns:

• (Hash) —

  the shape as a GeoJSON Feature with some item metadata

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

def to_geo_json_feature
  {
    'type' => 'Feature',
    'geometry' => to_geo_json,
    'properties' => {
      'geographic_item' => {
        'id' => id
      }
    }
  }
end

#to_wkt ⇒ String

Returns wkt.

Returns:

• (String) —

  wkt

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

def to_wkt
  select_from_self(
    self.class.st_as_text_sql(self.class.geography_as_geometry)
  )['st_astext']
end

#within?(target_geo_object) ⇒ Boolean

Parameters:

• (geo_object)

Returns:

• (Boolean)

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

def within?(target_geo_object)
  self.geo_object.within?(target_geo_object)
end