Class: CachedMapItem

Inherits:
ApplicationRecord show all
Includes:
Housekeeping::Projects, Housekeeping::Timestamps, Shared::IsData
Defined in:
app/models/cached_map_item.rb

Overview

A CachedMapItem is a summary of data from Georeferences and AssertedDistributions for mapping/visualization purposes.

All data are ‘cached` sensu TaxonWorks, i.e. derived from underlying data elsewhere. The intent is not to preserve the origin of the data, but rather provide a tool to summarize in (at present) a simple visualization.

Direct Known Subclasses

WebLevel1

Defined Under Namespace

Classes: WebLevel1

Instance Attribute Summary collapse

Class Method Summary collapse

Methods included from Shared::IsData

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

Methods included from Housekeeping::Timestamps

#data_breakdown_for_chartkick_recent

Methods included from Housekeeping::Projects

#annotates_community_object?, #is_community?, #set_project_id

Methods inherited from ApplicationRecord

transaction_with_retry

Instance Attribute Details

#geographic_item_id ⇒ GeographicItem#id

Returns , the id of GeographicItem, required.

Returns:



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'app/models/cached_map_item.rb', line 44

class CachedMapItem < ApplicationRecord
  include Housekeeping::Projects
  include Housekeeping::Timestamps
  include Shared::IsData

  belongs_to :otu, inverse_of: :cached_map_items
  belongs_to :geographic_item, inverse_of: :cached_map_items

  validates_uniqueness_of :otu_id, scope: [:type, :geographic_item_id]
  validates_presence_of :otu, :geographic_item, :type

  # @return Hash
  #   {country:, state:, county: }
  #  Check to see if a record is already present rather than-recalculate spatially
  #  CONSIDER: Use Reddis store to cache these results and look them up from there.
  #
  def self.cached_map_name_hierarchy(geographic_item_id)
    h = CachedMapItem
      .select('level0_geographic_name country, level1_geographic_name state, level2_geographic_name county')
      .where('level0_geographic_name IS NOT NULL OR level1_geographic_name IS NOT NULL OR level2_geographic_name IS NOT NULL')
      .find_by(geographic_item_id:) # finds first
      &.attributes
      &.compact!

    return h.symbolize_keys if h.present?

    GeographicItem.find(geographic_item_id).quick_geographic_name_hierarchy
  end

  def self.cached_map_geographic_items_by_otu(otu_scope = nil)
    return GeographicItem.none if otu_scope.nil?

    s = 'WITH otu_scope AS (' + otu_scope.all.to_sql + ') ' +
      ::GeographicItem
      .joins('JOIN cached_maps on cached_maps.geographic_item_id = geographic_items.id')
      .joins( 'JOIN otu_scope as otu_scope1 on otu_scope1.id = cached_maps.otu_id').to_sql

    ::GeographicItem.from('(' + s + ') as geographic_items').distinct
  end

  # @return Array
  def self.types_by_data_origin(data_origin = [])
    types = []
    data_origin.each do |o|
      CachedMapItem.descendants.each do |d|
        types.push d.name if d::SOURCE_GAZETEERS.include?(o)
      end
    end

    types.uniq!
    types
  end

  # Check CachedMapItemTranslation for previous translations and use
  #   that if possible
  def self.translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
    a = CachedMapItemTranslation.where(
      cached_map_type:,
      geographic_item_id:
    ).pluck(:translated_geographic_item_id)
      .uniq # Just in case we duplicate the index, hopefully not needed

    (a.presence || [])
  end

  # @return [Array]
  #   Return the geographic_item_id if it is already of the requested origin
  def self.translate_by_data_origin(geographic_item_id, data_origin)
    if ::GeographicAreasGeographicItem.where(
        geographic_item_id:,
        data_origin:
    ).any?
      return [geographic_item_id]
    else
      []
    end
  end

  # @return [Array]
  # Return the geographic_item_id if it is already used in a CachedMapItem of the right type
  #   !! Probably redundant with translate_by_data_origin !!
  def self.translate_by_cached_map_usage(geographic_item_id, cached_map_type)
    if ::CachedMapItem.where(geographic_item_id:, type: cached_map_type).any?
      return [geographic_item_id]
    else
      []
    end
  end

  def self.translate_by_alternate_shape(geographic_item_id, data_origin)
    # GeographicItem is an alternate shape to a GeographicArea that *also* has a target gazeteer type
    if a = GeographicItem.find(geographic_item_id)
      .geographic_areas
      .joins(:geographic_items)
      .where(geographic_areas: { data_origin: })
      .order(cached_total_area: :ASC) # smallest first
      .first
      &.id
      return [a]
    else
      []
    end
  end

  # Given a  set of target shapes, return those that intersect with the provided shape
  #
  # @param geographic_item_id [id]
  #   the shape we are translating *from*
  #
  # @param data_origin
  #    defines the shapes we are translating to
  #
  # #param buffer [nil, Decimal] in meters
  #    shrink, (or grow) the shape we are translating from
  #    Typical use is to shrink, so that differences in spatial resolution
  #    are minimized (low res shapes intersect with high res in undesireable ways)
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer)
    return [] if geographic_item_id.blank?

    # !! Assumes all GeographicArea shapes were loaded to multi_polygon
    # (pre-adapts us to a single geometry field type), however be
    # aware of this assumption

    # This is a fast first pass, pure intersection
    a = GeographicItem
      .joins(:geographic_areas_geographic_items)
      .where(geographic_areas_geographic_items: { data_origin: })
      .where( "ST_Intersects( multi_polygon, ( select #{ GeographicItem::GEOGRAPHY_SQL } from geographic_items where geographic_items.id = #{geographic_item_id}) )" )
      .pluck(:id)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    return GeographicItem
      .where(id: a)
      .where( GeographicItem.st_buffer_st_within(geographic_item_id, 0.0, buffer) )
      .pluck(:id)
  end

  # @return [Array]
  # @param origin_type
  #   'AssertedDistribution' or 'Georeference'
  #
  # @param data_origin Array, String
  #   like `ne_states` or ['ne_states, 'ne_countries']
  #
  # @param buffer [nil, Decimal]
  #   shr,ink (or grow) the size of the target shape, in meters
  #   Typical use, do not apply for Georeferences, apply -10km for AssertedDistributions
  #
  def self.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil)
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if origin_type == 'AssertedDistribution'
      a = translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
      return a if a.present?

      a = translate_by_data_origin(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_alternate_shape(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_cached_map_usage(geographic_item_id, cached_map_type)
      return a if a.present?

      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(geographic_item_id, data_origin, b)
  end

  def self.dynamic_buffer(geographic_item_id)
    v = GeographicItem.select(:id, :cached_total_area).find(geographic_item_id).cached_total_area
    return 0 if v.nil?
    case Math.log10(v).to_i
    when 0..2 # 3786
      0.0
    when 3..6 # Perhaps no GeographicAreas hit here
      -100.0
    when 7 # e.g. Monaco 122
      -1000
    when 8
      -2000 # e.g. Calhoun Co. 27490
    when 9
      -4000 # 3786 Cooma-Monaro
    when 10..12
      -12000.0  # e.g. Brazil 33794; Canada 37 !! Seems to be a sweet spot, remainder untested
    else #(max is 13)
      -20000.0 # Antarctic, 10
    end
  end

  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type)
    # return nil unless source_object.persisted?
    o = source_object

    h = {
      origin_object: o,
      cached_map_type:,
      otu_id: [],
      geographic_item_id: [],
      origin_geographic_item_id: nil
    }

    geographic_item_id = nil
    name_hierarchy = nil
    otu_id = nil

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      geographic_item_id = o.geographic_area.default_geographic_item_id
      otu_id = [o.otu_id]
    when 'Georeference'
      geographic_item_id = o.geographic_item_id
      otu_id = o.otus.joins('LEFT JOIN taxon_determinations td on otus.id = td.otu_id').where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    # Some AssertedDistribution don't have shapes
    if geographic_item_id
      h[:origin_geographic_item_id] = geographic_item_id

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id,
        base_class_name,
        cached_map_type.safe_constantize::SOURCE_GAZETEERS
      )

      if h[:geographic_item_id].blank?
        h[:geographic_item_id] = [geographic_item_id]
        h[:untranslated] = true
      end
    end

    h[:otu_id] = otu_id
    h
  end


  # Create breadth-first CachedMapItems
  #   Only applicable to Georeferences.
  #
  # @params batch_stubs [Hash]
  # {
  #  map_type: ,
  #  geographic_item_id: []
  #  otu_id: [ [otu_id, :project_id], ... [] ],
  #  georeference_id: [ [geoference_id, :project_id] ],
  # }
  #
  #
  def self.batch_create_georeference_cached_map_items(batch_stubs)
    map_type = batch_stubs[:map_type]
    j = batch_stubs[:geographic_item_id]
    k = batch_stubs[:otu_id]

    j.each do |geographic_item_id|
      k.each do |otu_id|
        otu_id = otu_id.first
        project_id = otu_id.second

        begin
          a = CachedMapItem.find_or_initialize_by(
            type: map_type,
            otu_id:,
            geographic_item_id:,
            project_id:,
          )

          if a.persisted?
            a.increment!(:reference_count)
          else
            a.reference_count = 1
            a.save!
          end

        rescue ActiveRecord::RecordInvalid => e
          logger.debug e
        rescue PG::UniqueViolation
          logger.debug 'pg unique violation'
        end
      end
    end

    # Register the Georeferences
    registrations = []

    batch_stubs[:georeference_id].each do |georeference_id, project_id|
      registrations.push({
        cached_map_register_object_type: 'Georeference',
        cached_map_register_object_id: georeference_id,
        project_id:,
        created_at: Time.current,
        updated_at: Time.current,
      })
    end

    begin
      CachedMapRegister.insert_all(registrations) if registrations.present?
    rescue
      puts '!! Failed to register Georeferences in batch_create_georeference_cached_map_items.'
    end

    true
  end

end

#is_absent ⇒ Boolean?

Returns if True then the corresponding AssertedDistributions have is_absent true.

Returns:

  • (Boolean, nil) —

    if True then the corresponding AssertedDistributions have is_absent true



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'app/models/cached_map_item.rb', line 44

class CachedMapItem < ApplicationRecord
  include Housekeeping::Projects
  include Housekeeping::Timestamps
  include Shared::IsData

  belongs_to :otu, inverse_of: :cached_map_items
  belongs_to :geographic_item, inverse_of: :cached_map_items

  validates_uniqueness_of :otu_id, scope: [:type, :geographic_item_id]
  validates_presence_of :otu, :geographic_item, :type

  # @return Hash
  #   {country:, state:, county: }
  #  Check to see if a record is already present rather than-recalculate spatially
  #  CONSIDER: Use Reddis store to cache these results and look them up from there.
  #
  def self.cached_map_name_hierarchy(geographic_item_id)
    h = CachedMapItem
      .select('level0_geographic_name country, level1_geographic_name state, level2_geographic_name county')
      .where('level0_geographic_name IS NOT NULL OR level1_geographic_name IS NOT NULL OR level2_geographic_name IS NOT NULL')
      .find_by(geographic_item_id:) # finds first
      &.attributes
      &.compact!

    return h.symbolize_keys if h.present?

    GeographicItem.find(geographic_item_id).quick_geographic_name_hierarchy
  end

  def self.cached_map_geographic_items_by_otu(otu_scope = nil)
    return GeographicItem.none if otu_scope.nil?

    s = 'WITH otu_scope AS (' + otu_scope.all.to_sql + ') ' +
      ::GeographicItem
      .joins('JOIN cached_maps on cached_maps.geographic_item_id = geographic_items.id')
      .joins( 'JOIN otu_scope as otu_scope1 on otu_scope1.id = cached_maps.otu_id').to_sql

    ::GeographicItem.from('(' + s + ') as geographic_items').distinct
  end

  # @return Array
  def self.types_by_data_origin(data_origin = [])
    types = []
    data_origin.each do |o|
      CachedMapItem.descendants.each do |d|
        types.push d.name if d::SOURCE_GAZETEERS.include?(o)
      end
    end

    types.uniq!
    types
  end

  # Check CachedMapItemTranslation for previous translations and use
  #   that if possible
  def self.translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
    a = CachedMapItemTranslation.where(
      cached_map_type:,
      geographic_item_id:
    ).pluck(:translated_geographic_item_id)
      .uniq # Just in case we duplicate the index, hopefully not needed

    (a.presence || [])
  end

  # @return [Array]
  #   Return the geographic_item_id if it is already of the requested origin
  def self.translate_by_data_origin(geographic_item_id, data_origin)
    if ::GeographicAreasGeographicItem.where(
        geographic_item_id:,
        data_origin:
    ).any?
      return [geographic_item_id]
    else
      []
    end
  end

  # @return [Array]
  # Return the geographic_item_id if it is already used in a CachedMapItem of the right type
  #   !! Probably redundant with translate_by_data_origin !!
  def self.translate_by_cached_map_usage(geographic_item_id, cached_map_type)
    if ::CachedMapItem.where(geographic_item_id:, type: cached_map_type).any?
      return [geographic_item_id]
    else
      []
    end
  end

  def self.translate_by_alternate_shape(geographic_item_id, data_origin)
    # GeographicItem is an alternate shape to a GeographicArea that *also* has a target gazeteer type
    if a = GeographicItem.find(geographic_item_id)
      .geographic_areas
      .joins(:geographic_items)
      .where(geographic_areas: { data_origin: })
      .order(cached_total_area: :ASC) # smallest first
      .first
      &.id
      return [a]
    else
      []
    end
  end

  # Given a  set of target shapes, return those that intersect with the provided shape
  #
  # @param geographic_item_id [id]
  #   the shape we are translating *from*
  #
  # @param data_origin
  #    defines the shapes we are translating to
  #
  # #param buffer [nil, Decimal] in meters
  #    shrink, (or grow) the shape we are translating from
  #    Typical use is to shrink, so that differences in spatial resolution
  #    are minimized (low res shapes intersect with high res in undesireable ways)
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer)
    return [] if geographic_item_id.blank?

    # !! Assumes all GeographicArea shapes were loaded to multi_polygon
    # (pre-adapts us to a single geometry field type), however be
    # aware of this assumption

    # This is a fast first pass, pure intersection
    a = GeographicItem
      .joins(:geographic_areas_geographic_items)
      .where(geographic_areas_geographic_items: { data_origin: })
      .where( "ST_Intersects( multi_polygon, ( select #{ GeographicItem::GEOGRAPHY_SQL } from geographic_items where geographic_items.id = #{geographic_item_id}) )" )
      .pluck(:id)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    return GeographicItem
      .where(id: a)
      .where( GeographicItem.st_buffer_st_within(geographic_item_id, 0.0, buffer) )
      .pluck(:id)
  end

  # @return [Array]
  # @param origin_type
  #   'AssertedDistribution' or 'Georeference'
  #
  # @param data_origin Array, String
  #   like `ne_states` or ['ne_states, 'ne_countries']
  #
  # @param buffer [nil, Decimal]
  #   shr,ink (or grow) the size of the target shape, in meters
  #   Typical use, do not apply for Georeferences, apply -10km for AssertedDistributions
  #
  def self.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil)
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if origin_type == 'AssertedDistribution'
      a = translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
      return a if a.present?

      a = translate_by_data_origin(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_alternate_shape(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_cached_map_usage(geographic_item_id, cached_map_type)
      return a if a.present?

      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(geographic_item_id, data_origin, b)
  end

  def self.dynamic_buffer(geographic_item_id)
    v = GeographicItem.select(:id, :cached_total_area).find(geographic_item_id).cached_total_area
    return 0 if v.nil?
    case Math.log10(v).to_i
    when 0..2 # 3786
      0.0
    when 3..6 # Perhaps no GeographicAreas hit here
      -100.0
    when 7 # e.g. Monaco 122
      -1000
    when 8
      -2000 # e.g. Calhoun Co. 27490
    when 9
      -4000 # 3786 Cooma-Monaro
    when 10..12
      -12000.0  # e.g. Brazil 33794; Canada 37 !! Seems to be a sweet spot, remainder untested
    else #(max is 13)
      -20000.0 # Antarctic, 10
    end
  end

  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type)
    # return nil unless source_object.persisted?
    o = source_object

    h = {
      origin_object: o,
      cached_map_type:,
      otu_id: [],
      geographic_item_id: [],
      origin_geographic_item_id: nil
    }

    geographic_item_id = nil
    name_hierarchy = nil
    otu_id = nil

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      geographic_item_id = o.geographic_area.default_geographic_item_id
      otu_id = [o.otu_id]
    when 'Georeference'
      geographic_item_id = o.geographic_item_id
      otu_id = o.otus.joins('LEFT JOIN taxon_determinations td on otus.id = td.otu_id').where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    # Some AssertedDistribution don't have shapes
    if geographic_item_id
      h[:origin_geographic_item_id] = geographic_item_id

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id,
        base_class_name,
        cached_map_type.safe_constantize::SOURCE_GAZETEERS
      )

      if h[:geographic_item_id].blank?
        h[:geographic_item_id] = [geographic_item_id]
        h[:untranslated] = true
      end
    end

    h[:otu_id] = otu_id
    h
  end


  # Create breadth-first CachedMapItems
  #   Only applicable to Georeferences.
  #
  # @params batch_stubs [Hash]
  # {
  #  map_type: ,
  #  geographic_item_id: []
  #  otu_id: [ [otu_id, :project_id], ... [] ],
  #  georeference_id: [ [geoference_id, :project_id] ],
  # }
  #
  #
  def self.batch_create_georeference_cached_map_items(batch_stubs)
    map_type = batch_stubs[:map_type]
    j = batch_stubs[:geographic_item_id]
    k = batch_stubs[:otu_id]

    j.each do |geographic_item_id|
      k.each do |otu_id|
        otu_id = otu_id.first
        project_id = otu_id.second

        begin
          a = CachedMapItem.find_or_initialize_by(
            type: map_type,
            otu_id:,
            geographic_item_id:,
            project_id:,
          )

          if a.persisted?
            a.increment!(:reference_count)
          else
            a.reference_count = 1
            a.save!
          end

        rescue ActiveRecord::RecordInvalid => e
          logger.debug e
        rescue PG::UniqueViolation
          logger.debug 'pg unique violation'
        end
      end
    end

    # Register the Georeferences
    registrations = []

    batch_stubs[:georeference_id].each do |georeference_id, project_id|
      registrations.push({
        cached_map_register_object_type: 'Georeference',
        cached_map_register_object_id: georeference_id,
        project_id:,
        created_at: Time.current,
        updated_at: Time.current,
      })
    end

    begin
      CachedMapRegister.insert_all(registrations) if registrations.present?
    rescue
      puts '!! Failed to register Georeferences in batch_create_georeference_cached_map_items.'
    end

    true
  end

end

#level0_geographic_name ⇒ String?

Returns the level 0 name.

Returns:

  • (String, nil) —

    the level 0 name



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'app/models/cached_map_item.rb', line 44

class CachedMapItem < ApplicationRecord
  include Housekeeping::Projects
  include Housekeeping::Timestamps
  include Shared::IsData

  belongs_to :otu, inverse_of: :cached_map_items
  belongs_to :geographic_item, inverse_of: :cached_map_items

  validates_uniqueness_of :otu_id, scope: [:type, :geographic_item_id]
  validates_presence_of :otu, :geographic_item, :type

  # @return Hash
  #   {country:, state:, county: }
  #  Check to see if a record is already present rather than-recalculate spatially
  #  CONSIDER: Use Reddis store to cache these results and look them up from there.
  #
  def self.cached_map_name_hierarchy(geographic_item_id)
    h = CachedMapItem
      .select('level0_geographic_name country, level1_geographic_name state, level2_geographic_name county')
      .where('level0_geographic_name IS NOT NULL OR level1_geographic_name IS NOT NULL OR level2_geographic_name IS NOT NULL')
      .find_by(geographic_item_id:) # finds first
      &.attributes
      &.compact!

    return h.symbolize_keys if h.present?

    GeographicItem.find(geographic_item_id).quick_geographic_name_hierarchy
  end

  def self.cached_map_geographic_items_by_otu(otu_scope = nil)
    return GeographicItem.none if otu_scope.nil?

    s = 'WITH otu_scope AS (' + otu_scope.all.to_sql + ') ' +
      ::GeographicItem
      .joins('JOIN cached_maps on cached_maps.geographic_item_id = geographic_items.id')
      .joins( 'JOIN otu_scope as otu_scope1 on otu_scope1.id = cached_maps.otu_id').to_sql

    ::GeographicItem.from('(' + s + ') as geographic_items').distinct
  end

  # @return Array
  def self.types_by_data_origin(data_origin = [])
    types = []
    data_origin.each do |o|
      CachedMapItem.descendants.each do |d|
        types.push d.name if d::SOURCE_GAZETEERS.include?(o)
      end
    end

    types.uniq!
    types
  end

  # Check CachedMapItemTranslation for previous translations and use
  #   that if possible
  def self.translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
    a = CachedMapItemTranslation.where(
      cached_map_type:,
      geographic_item_id:
    ).pluck(:translated_geographic_item_id)
      .uniq # Just in case we duplicate the index, hopefully not needed

    (a.presence || [])
  end

  # @return [Array]
  #   Return the geographic_item_id if it is already of the requested origin
  def self.translate_by_data_origin(geographic_item_id, data_origin)
    if ::GeographicAreasGeographicItem.where(
        geographic_item_id:,
        data_origin:
    ).any?
      return [geographic_item_id]
    else
      []
    end
  end

  # @return [Array]
  # Return the geographic_item_id if it is already used in a CachedMapItem of the right type
  #   !! Probably redundant with translate_by_data_origin !!
  def self.translate_by_cached_map_usage(geographic_item_id, cached_map_type)
    if ::CachedMapItem.where(geographic_item_id:, type: cached_map_type).any?
      return [geographic_item_id]
    else
      []
    end
  end

  def self.translate_by_alternate_shape(geographic_item_id, data_origin)
    # GeographicItem is an alternate shape to a GeographicArea that *also* has a target gazeteer type
    if a = GeographicItem.find(geographic_item_id)
      .geographic_areas
      .joins(:geographic_items)
      .where(geographic_areas: { data_origin: })
      .order(cached_total_area: :ASC) # smallest first
      .first
      &.id
      return [a]
    else
      []
    end
  end

  # Given a  set of target shapes, return those that intersect with the provided shape
  #
  # @param geographic_item_id [id]
  #   the shape we are translating *from*
  #
  # @param data_origin
  #    defines the shapes we are translating to
  #
  # #param buffer [nil, Decimal] in meters
  #    shrink, (or grow) the shape we are translating from
  #    Typical use is to shrink, so that differences in spatial resolution
  #    are minimized (low res shapes intersect with high res in undesireable ways)
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer)
    return [] if geographic_item_id.blank?

    # !! Assumes all GeographicArea shapes were loaded to multi_polygon
    # (pre-adapts us to a single geometry field type), however be
    # aware of this assumption

    # This is a fast first pass, pure intersection
    a = GeographicItem
      .joins(:geographic_areas_geographic_items)
      .where(geographic_areas_geographic_items: { data_origin: })
      .where( "ST_Intersects( multi_polygon, ( select #{ GeographicItem::GEOGRAPHY_SQL } from geographic_items where geographic_items.id = #{geographic_item_id}) )" )
      .pluck(:id)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    return GeographicItem
      .where(id: a)
      .where( GeographicItem.st_buffer_st_within(geographic_item_id, 0.0, buffer) )
      .pluck(:id)
  end

  # @return [Array]
  # @param origin_type
  #   'AssertedDistribution' or 'Georeference'
  #
  # @param data_origin Array, String
  #   like `ne_states` or ['ne_states, 'ne_countries']
  #
  # @param buffer [nil, Decimal]
  #   shr,ink (or grow) the size of the target shape, in meters
  #   Typical use, do not apply for Georeferences, apply -10km for AssertedDistributions
  #
  def self.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil)
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if origin_type == 'AssertedDistribution'
      a = translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
      return a if a.present?

      a = translate_by_data_origin(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_alternate_shape(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_cached_map_usage(geographic_item_id, cached_map_type)
      return a if a.present?

      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(geographic_item_id, data_origin, b)
  end

  def self.dynamic_buffer(geographic_item_id)
    v = GeographicItem.select(:id, :cached_total_area).find(geographic_item_id).cached_total_area
    return 0 if v.nil?
    case Math.log10(v).to_i
    when 0..2 # 3786
      0.0
    when 3..6 # Perhaps no GeographicAreas hit here
      -100.0
    when 7 # e.g. Monaco 122
      -1000
    when 8
      -2000 # e.g. Calhoun Co. 27490
    when 9
      -4000 # 3786 Cooma-Monaro
    when 10..12
      -12000.0  # e.g. Brazil 33794; Canada 37 !! Seems to be a sweet spot, remainder untested
    else #(max is 13)
      -20000.0 # Antarctic, 10
    end
  end

  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type)
    # return nil unless source_object.persisted?
    o = source_object

    h = {
      origin_object: o,
      cached_map_type:,
      otu_id: [],
      geographic_item_id: [],
      origin_geographic_item_id: nil
    }

    geographic_item_id = nil
    name_hierarchy = nil
    otu_id = nil

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      geographic_item_id = o.geographic_area.default_geographic_item_id
      otu_id = [o.otu_id]
    when 'Georeference'
      geographic_item_id = o.geographic_item_id
      otu_id = o.otus.joins('LEFT JOIN taxon_determinations td on otus.id = td.otu_id').where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    # Some AssertedDistribution don't have shapes
    if geographic_item_id
      h[:origin_geographic_item_id] = geographic_item_id

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id,
        base_class_name,
        cached_map_type.safe_constantize::SOURCE_GAZETEERS
      )

      if h[:geographic_item_id].blank?
        h[:geographic_item_id] = [geographic_item_id]
        h[:untranslated] = true
      end
    end

    h[:otu_id] = otu_id
    h
  end


  # Create breadth-first CachedMapItems
  #   Only applicable to Georeferences.
  #
  # @params batch_stubs [Hash]
  # {
  #  map_type: ,
  #  geographic_item_id: []
  #  otu_id: [ [otu_id, :project_id], ... [] ],
  #  georeference_id: [ [geoference_id, :project_id] ],
  # }
  #
  #
  def self.batch_create_georeference_cached_map_items(batch_stubs)
    map_type = batch_stubs[:map_type]
    j = batch_stubs[:geographic_item_id]
    k = batch_stubs[:otu_id]

    j.each do |geographic_item_id|
      k.each do |otu_id|
        otu_id = otu_id.first
        project_id = otu_id.second

        begin
          a = CachedMapItem.find_or_initialize_by(
            type: map_type,
            otu_id:,
            geographic_item_id:,
            project_id:,
          )

          if a.persisted?
            a.increment!(:reference_count)
          else
            a.reference_count = 1
            a.save!
          end

        rescue ActiveRecord::RecordInvalid => e
          logger.debug e
        rescue PG::UniqueViolation
          logger.debug 'pg unique violation'
        end
      end
    end

    # Register the Georeferences
    registrations = []

    batch_stubs[:georeference_id].each do |georeference_id, project_id|
      registrations.push({
        cached_map_register_object_type: 'Georeference',
        cached_map_register_object_id: georeference_id,
        project_id:,
        created_at: Time.current,
        updated_at: Time.current,
      })
    end

    begin
      CachedMapRegister.insert_all(registrations) if registrations.present?
    rescue
      puts '!! Failed to register Georeferences in batch_create_georeference_cached_map_items.'
    end

    true
  end

end

#level1_geographic_name ⇒ String?

Returns the level 1 name.

Returns:

  • (String, nil) —

    the level 1 name



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'app/models/cached_map_item.rb', line 44

class CachedMapItem < ApplicationRecord
  include Housekeeping::Projects
  include Housekeeping::Timestamps
  include Shared::IsData

  belongs_to :otu, inverse_of: :cached_map_items
  belongs_to :geographic_item, inverse_of: :cached_map_items

  validates_uniqueness_of :otu_id, scope: [:type, :geographic_item_id]
  validates_presence_of :otu, :geographic_item, :type

  # @return Hash
  #   {country:, state:, county: }
  #  Check to see if a record is already present rather than-recalculate spatially
  #  CONSIDER: Use Reddis store to cache these results and look them up from there.
  #
  def self.cached_map_name_hierarchy(geographic_item_id)
    h = CachedMapItem
      .select('level0_geographic_name country, level1_geographic_name state, level2_geographic_name county')
      .where('level0_geographic_name IS NOT NULL OR level1_geographic_name IS NOT NULL OR level2_geographic_name IS NOT NULL')
      .find_by(geographic_item_id:) # finds first
      &.attributes
      &.compact!

    return h.symbolize_keys if h.present?

    GeographicItem.find(geographic_item_id).quick_geographic_name_hierarchy
  end

  def self.cached_map_geographic_items_by_otu(otu_scope = nil)
    return GeographicItem.none if otu_scope.nil?

    s = 'WITH otu_scope AS (' + otu_scope.all.to_sql + ') ' +
      ::GeographicItem
      .joins('JOIN cached_maps on cached_maps.geographic_item_id = geographic_items.id')
      .joins( 'JOIN otu_scope as otu_scope1 on otu_scope1.id = cached_maps.otu_id').to_sql

    ::GeographicItem.from('(' + s + ') as geographic_items').distinct
  end

  # @return Array
  def self.types_by_data_origin(data_origin = [])
    types = []
    data_origin.each do |o|
      CachedMapItem.descendants.each do |d|
        types.push d.name if d::SOURCE_GAZETEERS.include?(o)
      end
    end

    types.uniq!
    types
  end

  # Check CachedMapItemTranslation for previous translations and use
  #   that if possible
  def self.translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
    a = CachedMapItemTranslation.where(
      cached_map_type:,
      geographic_item_id:
    ).pluck(:translated_geographic_item_id)
      .uniq # Just in case we duplicate the index, hopefully not needed

    (a.presence || [])
  end

  # @return [Array]
  #   Return the geographic_item_id if it is already of the requested origin
  def self.translate_by_data_origin(geographic_item_id, data_origin)
    if ::GeographicAreasGeographicItem.where(
        geographic_item_id:,
        data_origin:
    ).any?
      return [geographic_item_id]
    else
      []
    end
  end

  # @return [Array]
  # Return the geographic_item_id if it is already used in a CachedMapItem of the right type
  #   !! Probably redundant with translate_by_data_origin !!
  def self.translate_by_cached_map_usage(geographic_item_id, cached_map_type)
    if ::CachedMapItem.where(geographic_item_id:, type: cached_map_type).any?
      return [geographic_item_id]
    else
      []
    end
  end

  def self.translate_by_alternate_shape(geographic_item_id, data_origin)
    # GeographicItem is an alternate shape to a GeographicArea that *also* has a target gazeteer type
    if a = GeographicItem.find(geographic_item_id)
      .geographic_areas
      .joins(:geographic_items)
      .where(geographic_areas: { data_origin: })
      .order(cached_total_area: :ASC) # smallest first
      .first
      &.id
      return [a]
    else
      []
    end
  end

  # Given a  set of target shapes, return those that intersect with the provided shape
  #
  # @param geographic_item_id [id]
  #   the shape we are translating *from*
  #
  # @param data_origin
  #    defines the shapes we are translating to
  #
  # #param buffer [nil, Decimal] in meters
  #    shrink, (or grow) the shape we are translating from
  #    Typical use is to shrink, so that differences in spatial resolution
  #    are minimized (low res shapes intersect with high res in undesireable ways)
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer)
    return [] if geographic_item_id.blank?

    # !! Assumes all GeographicArea shapes were loaded to multi_polygon
    # (pre-adapts us to a single geometry field type), however be
    # aware of this assumption

    # This is a fast first pass, pure intersection
    a = GeographicItem
      .joins(:geographic_areas_geographic_items)
      .where(geographic_areas_geographic_items: { data_origin: })
      .where( "ST_Intersects( multi_polygon, ( select #{ GeographicItem::GEOGRAPHY_SQL } from geographic_items where geographic_items.id = #{geographic_item_id}) )" )
      .pluck(:id)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    return GeographicItem
      .where(id: a)
      .where( GeographicItem.st_buffer_st_within(geographic_item_id, 0.0, buffer) )
      .pluck(:id)
  end

  # @return [Array]
  # @param origin_type
  #   'AssertedDistribution' or 'Georeference'
  #
  # @param data_origin Array, String
  #   like `ne_states` or ['ne_states, 'ne_countries']
  #
  # @param buffer [nil, Decimal]
  #   shr,ink (or grow) the size of the target shape, in meters
  #   Typical use, do not apply for Georeferences, apply -10km for AssertedDistributions
  #
  def self.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil)
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if origin_type == 'AssertedDistribution'
      a = translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
      return a if a.present?

      a = translate_by_data_origin(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_alternate_shape(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_cached_map_usage(geographic_item_id, cached_map_type)
      return a if a.present?

      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(geographic_item_id, data_origin, b)
  end

  def self.dynamic_buffer(geographic_item_id)
    v = GeographicItem.select(:id, :cached_total_area).find(geographic_item_id).cached_total_area
    return 0 if v.nil?
    case Math.log10(v).to_i
    when 0..2 # 3786
      0.0
    when 3..6 # Perhaps no GeographicAreas hit here
      -100.0
    when 7 # e.g. Monaco 122
      -1000
    when 8
      -2000 # e.g. Calhoun Co. 27490
    when 9
      -4000 # 3786 Cooma-Monaro
    when 10..12
      -12000.0  # e.g. Brazil 33794; Canada 37 !! Seems to be a sweet spot, remainder untested
    else #(max is 13)
      -20000.0 # Antarctic, 10
    end
  end

  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type)
    # return nil unless source_object.persisted?
    o = source_object

    h = {
      origin_object: o,
      cached_map_type:,
      otu_id: [],
      geographic_item_id: [],
      origin_geographic_item_id: nil
    }

    geographic_item_id = nil
    name_hierarchy = nil
    otu_id = nil

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      geographic_item_id = o.geographic_area.default_geographic_item_id
      otu_id = [o.otu_id]
    when 'Georeference'
      geographic_item_id = o.geographic_item_id
      otu_id = o.otus.joins('LEFT JOIN taxon_determinations td on otus.id = td.otu_id').where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    # Some AssertedDistribution don't have shapes
    if geographic_item_id
      h[:origin_geographic_item_id] = geographic_item_id

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id,
        base_class_name,
        cached_map_type.safe_constantize::SOURCE_GAZETEERS
      )

      if h[:geographic_item_id].blank?
        h[:geographic_item_id] = [geographic_item_id]
        h[:untranslated] = true
      end
    end

    h[:otu_id] = otu_id
    h
  end


  # Create breadth-first CachedMapItems
  #   Only applicable to Georeferences.
  #
  # @params batch_stubs [Hash]
  # {
  #  map_type: ,
  #  geographic_item_id: []
  #  otu_id: [ [otu_id, :project_id], ... [] ],
  #  georeference_id: [ [geoference_id, :project_id] ],
  # }
  #
  #
  def self.batch_create_georeference_cached_map_items(batch_stubs)
    map_type = batch_stubs[:map_type]
    j = batch_stubs[:geographic_item_id]
    k = batch_stubs[:otu_id]

    j.each do |geographic_item_id|
      k.each do |otu_id|
        otu_id = otu_id.first
        project_id = otu_id.second

        begin
          a = CachedMapItem.find_or_initialize_by(
            type: map_type,
            otu_id:,
            geographic_item_id:,
            project_id:,
          )

          if a.persisted?
            a.increment!(:reference_count)
          else
            a.reference_count = 1
            a.save!
          end

        rescue ActiveRecord::RecordInvalid => e
          logger.debug e
        rescue PG::UniqueViolation
          logger.debug 'pg unique violation'
        end
      end
    end

    # Register the Georeferences
    registrations = []

    batch_stubs[:georeference_id].each do |georeference_id, project_id|
      registrations.push({
        cached_map_register_object_type: 'Georeference',
        cached_map_register_object_id: georeference_id,
        project_id:,
        created_at: Time.current,
        updated_at: Time.current,
      })
    end

    begin
      CachedMapRegister.insert_all(registrations) if registrations.present?
    rescue
      puts '!! Failed to register Georeferences in batch_create_georeference_cached_map_items.'
    end

    true
  end

end

#level2_geographic_name ⇒ String?

Returns the level 2 name. !! Not presently used. !!.

Returns:

  • (String, nil) —

    the level 2 name. !! Not presently used. !!



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'app/models/cached_map_item.rb', line 44

class CachedMapItem < ApplicationRecord
  include Housekeeping::Projects
  include Housekeeping::Timestamps
  include Shared::IsData

  belongs_to :otu, inverse_of: :cached_map_items
  belongs_to :geographic_item, inverse_of: :cached_map_items

  validates_uniqueness_of :otu_id, scope: [:type, :geographic_item_id]
  validates_presence_of :otu, :geographic_item, :type

  # @return Hash
  #   {country:, state:, county: }
  #  Check to see if a record is already present rather than-recalculate spatially
  #  CONSIDER: Use Reddis store to cache these results and look them up from there.
  #
  def self.cached_map_name_hierarchy(geographic_item_id)
    h = CachedMapItem
      .select('level0_geographic_name country, level1_geographic_name state, level2_geographic_name county')
      .where('level0_geographic_name IS NOT NULL OR level1_geographic_name IS NOT NULL OR level2_geographic_name IS NOT NULL')
      .find_by(geographic_item_id:) # finds first
      &.attributes
      &.compact!

    return h.symbolize_keys if h.present?

    GeographicItem.find(geographic_item_id).quick_geographic_name_hierarchy
  end

  def self.cached_map_geographic_items_by_otu(otu_scope = nil)
    return GeographicItem.none if otu_scope.nil?

    s = 'WITH otu_scope AS (' + otu_scope.all.to_sql + ') ' +
      ::GeographicItem
      .joins('JOIN cached_maps on cached_maps.geographic_item_id = geographic_items.id')
      .joins( 'JOIN otu_scope as otu_scope1 on otu_scope1.id = cached_maps.otu_id').to_sql

    ::GeographicItem.from('(' + s + ') as geographic_items').distinct
  end

  # @return Array
  def self.types_by_data_origin(data_origin = [])
    types = []
    data_origin.each do |o|
      CachedMapItem.descendants.each do |d|
        types.push d.name if d::SOURCE_GAZETEERS.include?(o)
      end
    end

    types.uniq!
    types
  end

  # Check CachedMapItemTranslation for previous translations and use
  #   that if possible
  def self.translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
    a = CachedMapItemTranslation.where(
      cached_map_type:,
      geographic_item_id:
    ).pluck(:translated_geographic_item_id)
      .uniq # Just in case we duplicate the index, hopefully not needed

    (a.presence || [])
  end

  # @return [Array]
  #   Return the geographic_item_id if it is already of the requested origin
  def self.translate_by_data_origin(geographic_item_id, data_origin)
    if ::GeographicAreasGeographicItem.where(
        geographic_item_id:,
        data_origin:
    ).any?
      return [geographic_item_id]
    else
      []
    end
  end

  # @return [Array]
  # Return the geographic_item_id if it is already used in a CachedMapItem of the right type
  #   !! Probably redundant with translate_by_data_origin !!
  def self.translate_by_cached_map_usage(geographic_item_id, cached_map_type)
    if ::CachedMapItem.where(geographic_item_id:, type: cached_map_type).any?
      return [geographic_item_id]
    else
      []
    end
  end

  def self.translate_by_alternate_shape(geographic_item_id, data_origin)
    # GeographicItem is an alternate shape to a GeographicArea that *also* has a target gazeteer type
    if a = GeographicItem.find(geographic_item_id)
      .geographic_areas
      .joins(:geographic_items)
      .where(geographic_areas: { data_origin: })
      .order(cached_total_area: :ASC) # smallest first
      .first
      &.id
      return [a]
    else
      []
    end
  end

  # Given a  set of target shapes, return those that intersect with the provided shape
  #
  # @param geographic_item_id [id]
  #   the shape we are translating *from*
  #
  # @param data_origin
  #    defines the shapes we are translating to
  #
  # #param buffer [nil, Decimal] in meters
  #    shrink, (or grow) the shape we are translating from
  #    Typical use is to shrink, so that differences in spatial resolution
  #    are minimized (low res shapes intersect with high res in undesireable ways)
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer)
    return [] if geographic_item_id.blank?

    # !! Assumes all GeographicArea shapes were loaded to multi_polygon
    # (pre-adapts us to a single geometry field type), however be
    # aware of this assumption

    # This is a fast first pass, pure intersection
    a = GeographicItem
      .joins(:geographic_areas_geographic_items)
      .where(geographic_areas_geographic_items: { data_origin: })
      .where( "ST_Intersects( multi_polygon, ( select #{ GeographicItem::GEOGRAPHY_SQL } from geographic_items where geographic_items.id = #{geographic_item_id}) )" )
      .pluck(:id)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    return GeographicItem
      .where(id: a)
      .where( GeographicItem.st_buffer_st_within(geographic_item_id, 0.0, buffer) )
      .pluck(:id)
  end

  # @return [Array]
  # @param origin_type
  #   'AssertedDistribution' or 'Georeference'
  #
  # @param data_origin Array, String
  #   like `ne_states` or ['ne_states, 'ne_countries']
  #
  # @param buffer [nil, Decimal]
  #   shr,ink (or grow) the size of the target shape, in meters
  #   Typical use, do not apply for Georeferences, apply -10km for AssertedDistributions
  #
  def self.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil)
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if origin_type == 'AssertedDistribution'
      a = translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
      return a if a.present?

      a = translate_by_data_origin(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_alternate_shape(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_cached_map_usage(geographic_item_id, cached_map_type)
      return a if a.present?

      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(geographic_item_id, data_origin, b)
  end

  def self.dynamic_buffer(geographic_item_id)
    v = GeographicItem.select(:id, :cached_total_area).find(geographic_item_id).cached_total_area
    return 0 if v.nil?
    case Math.log10(v).to_i
    when 0..2 # 3786
      0.0
    when 3..6 # Perhaps no GeographicAreas hit here
      -100.0
    when 7 # e.g. Monaco 122
      -1000
    when 8
      -2000 # e.g. Calhoun Co. 27490
    when 9
      -4000 # 3786 Cooma-Monaro
    when 10..12
      -12000.0  # e.g. Brazil 33794; Canada 37 !! Seems to be a sweet spot, remainder untested
    else #(max is 13)
      -20000.0 # Antarctic, 10
    end
  end

  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type)
    # return nil unless source_object.persisted?
    o = source_object

    h = {
      origin_object: o,
      cached_map_type:,
      otu_id: [],
      geographic_item_id: [],
      origin_geographic_item_id: nil
    }

    geographic_item_id = nil
    name_hierarchy = nil
    otu_id = nil

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      geographic_item_id = o.geographic_area.default_geographic_item_id
      otu_id = [o.otu_id]
    when 'Georeference'
      geographic_item_id = o.geographic_item_id
      otu_id = o.otus.joins('LEFT JOIN taxon_determinations td on otus.id = td.otu_id').where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    # Some AssertedDistribution don't have shapes
    if geographic_item_id
      h[:origin_geographic_item_id] = geographic_item_id

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id,
        base_class_name,
        cached_map_type.safe_constantize::SOURCE_GAZETEERS
      )

      if h[:geographic_item_id].blank?
        h[:geographic_item_id] = [geographic_item_id]
        h[:untranslated] = true
      end
    end

    h[:otu_id] = otu_id
    h
  end


  # Create breadth-first CachedMapItems
  #   Only applicable to Georeferences.
  #
  # @params batch_stubs [Hash]
  # {
  #  map_type: ,
  #  geographic_item_id: []
  #  otu_id: [ [otu_id, :project_id], ... [] ],
  #  georeference_id: [ [geoference_id, :project_id] ],
  # }
  #
  #
  def self.batch_create_georeference_cached_map_items(batch_stubs)
    map_type = batch_stubs[:map_type]
    j = batch_stubs[:geographic_item_id]
    k = batch_stubs[:otu_id]

    j.each do |geographic_item_id|
      k.each do |otu_id|
        otu_id = otu_id.first
        project_id = otu_id.second

        begin
          a = CachedMapItem.find_or_initialize_by(
            type: map_type,
            otu_id:,
            geographic_item_id:,
            project_id:,
          )

          if a.persisted?
            a.increment!(:reference_count)
          else
            a.reference_count = 1
            a.save!
          end

        rescue ActiveRecord::RecordInvalid => e
          logger.debug e
        rescue PG::UniqueViolation
          logger.debug 'pg unique violation'
        end
      end
    end

    # Register the Georeferences
    registrations = []

    batch_stubs[:georeference_id].each do |georeference_id, project_id|
      registrations.push({
        cached_map_register_object_type: 'Georeference',
        cached_map_register_object_id: georeference_id,
        project_id:,
        created_at: Time.current,
        updated_at: Time.current,
      })
    end

    begin
      CachedMapRegister.insert_all(registrations) if registrations.present?
    rescue
      puts '!! Failed to register Georeferences in batch_create_georeference_cached_map_items.'
    end

    true
  end

end

#otu_id ⇒ Otu#id

Returns , the id of OTU, required.

Returns:

  • (Otu#id) —

    , the id of OTU, required



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'app/models/cached_map_item.rb', line 44

class CachedMapItem < ApplicationRecord
  include Housekeeping::Projects
  include Housekeeping::Timestamps
  include Shared::IsData

  belongs_to :otu, inverse_of: :cached_map_items
  belongs_to :geographic_item, inverse_of: :cached_map_items

  validates_uniqueness_of :otu_id, scope: [:type, :geographic_item_id]
  validates_presence_of :otu, :geographic_item, :type

  # @return Hash
  #   {country:, state:, county: }
  #  Check to see if a record is already present rather than-recalculate spatially
  #  CONSIDER: Use Reddis store to cache these results and look them up from there.
  #
  def self.cached_map_name_hierarchy(geographic_item_id)
    h = CachedMapItem
      .select('level0_geographic_name country, level1_geographic_name state, level2_geographic_name county')
      .where('level0_geographic_name IS NOT NULL OR level1_geographic_name IS NOT NULL OR level2_geographic_name IS NOT NULL')
      .find_by(geographic_item_id:) # finds first
      &.attributes
      &.compact!

    return h.symbolize_keys if h.present?

    GeographicItem.find(geographic_item_id).quick_geographic_name_hierarchy
  end

  def self.cached_map_geographic_items_by_otu(otu_scope = nil)
    return GeographicItem.none if otu_scope.nil?

    s = 'WITH otu_scope AS (' + otu_scope.all.to_sql + ') ' +
      ::GeographicItem
      .joins('JOIN cached_maps on cached_maps.geographic_item_id = geographic_items.id')
      .joins( 'JOIN otu_scope as otu_scope1 on otu_scope1.id = cached_maps.otu_id').to_sql

    ::GeographicItem.from('(' + s + ') as geographic_items').distinct
  end

  # @return Array
  def self.types_by_data_origin(data_origin = [])
    types = []
    data_origin.each do |o|
      CachedMapItem.descendants.each do |d|
        types.push d.name if d::SOURCE_GAZETEERS.include?(o)
      end
    end

    types.uniq!
    types
  end

  # Check CachedMapItemTranslation for previous translations and use
  #   that if possible
  def self.translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
    a = CachedMapItemTranslation.where(
      cached_map_type:,
      geographic_item_id:
    ).pluck(:translated_geographic_item_id)
      .uniq # Just in case we duplicate the index, hopefully not needed

    (a.presence || [])
  end

  # @return [Array]
  #   Return the geographic_item_id if it is already of the requested origin
  def self.translate_by_data_origin(geographic_item_id, data_origin)
    if ::GeographicAreasGeographicItem.where(
        geographic_item_id:,
        data_origin:
    ).any?
      return [geographic_item_id]
    else
      []
    end
  end

  # @return [Array]
  # Return the geographic_item_id if it is already used in a CachedMapItem of the right type
  #   !! Probably redundant with translate_by_data_origin !!
  def self.translate_by_cached_map_usage(geographic_item_id, cached_map_type)
    if ::CachedMapItem.where(geographic_item_id:, type: cached_map_type).any?
      return [geographic_item_id]
    else
      []
    end
  end

  def self.translate_by_alternate_shape(geographic_item_id, data_origin)
    # GeographicItem is an alternate shape to a GeographicArea that *also* has a target gazeteer type
    if a = GeographicItem.find(geographic_item_id)
      .geographic_areas
      .joins(:geographic_items)
      .where(geographic_areas: { data_origin: })
      .order(cached_total_area: :ASC) # smallest first
      .first
      &.id
      return [a]
    else
      []
    end
  end

  # Given a  set of target shapes, return those that intersect with the provided shape
  #
  # @param geographic_item_id [id]
  #   the shape we are translating *from*
  #
  # @param data_origin
  #    defines the shapes we are translating to
  #
  # #param buffer [nil, Decimal] in meters
  #    shrink, (or grow) the shape we are translating from
  #    Typical use is to shrink, so that differences in spatial resolution
  #    are minimized (low res shapes intersect with high res in undesireable ways)
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer)
    return [] if geographic_item_id.blank?

    # !! Assumes all GeographicArea shapes were loaded to multi_polygon
    # (pre-adapts us to a single geometry field type), however be
    # aware of this assumption

    # This is a fast first pass, pure intersection
    a = GeographicItem
      .joins(:geographic_areas_geographic_items)
      .where(geographic_areas_geographic_items: { data_origin: })
      .where( "ST_Intersects( multi_polygon, ( select #{ GeographicItem::GEOGRAPHY_SQL } from geographic_items where geographic_items.id = #{geographic_item_id}) )" )
      .pluck(:id)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    return GeographicItem
      .where(id: a)
      .where( GeographicItem.st_buffer_st_within(geographic_item_id, 0.0, buffer) )
      .pluck(:id)
  end

  # @return [Array]
  # @param origin_type
  #   'AssertedDistribution' or 'Georeference'
  #
  # @param data_origin Array, String
  #   like `ne_states` or ['ne_states, 'ne_countries']
  #
  # @param buffer [nil, Decimal]
  #   shr,ink (or grow) the size of the target shape, in meters
  #   Typical use, do not apply for Georeferences, apply -10km for AssertedDistributions
  #
  def self.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil)
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if origin_type == 'AssertedDistribution'
      a = translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
      return a if a.present?

      a = translate_by_data_origin(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_alternate_shape(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_cached_map_usage(geographic_item_id, cached_map_type)
      return a if a.present?

      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(geographic_item_id, data_origin, b)
  end

  def self.dynamic_buffer(geographic_item_id)
    v = GeographicItem.select(:id, :cached_total_area).find(geographic_item_id).cached_total_area
    return 0 if v.nil?
    case Math.log10(v).to_i
    when 0..2 # 3786
      0.0
    when 3..6 # Perhaps no GeographicAreas hit here
      -100.0
    when 7 # e.g. Monaco 122
      -1000
    when 8
      -2000 # e.g. Calhoun Co. 27490
    when 9
      -4000 # 3786 Cooma-Monaro
    when 10..12
      -12000.0  # e.g. Brazil 33794; Canada 37 !! Seems to be a sweet spot, remainder untested
    else #(max is 13)
      -20000.0 # Antarctic, 10
    end
  end

  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type)
    # return nil unless source_object.persisted?
    o = source_object

    h = {
      origin_object: o,
      cached_map_type:,
      otu_id: [],
      geographic_item_id: [],
      origin_geographic_item_id: nil
    }

    geographic_item_id = nil
    name_hierarchy = nil
    otu_id = nil

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      geographic_item_id = o.geographic_area.default_geographic_item_id
      otu_id = [o.otu_id]
    when 'Georeference'
      geographic_item_id = o.geographic_item_id
      otu_id = o.otus.joins('LEFT JOIN taxon_determinations td on otus.id = td.otu_id').where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    # Some AssertedDistribution don't have shapes
    if geographic_item_id
      h[:origin_geographic_item_id] = geographic_item_id

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id,
        base_class_name,
        cached_map_type.safe_constantize::SOURCE_GAZETEERS
      )

      if h[:geographic_item_id].blank?
        h[:geographic_item_id] = [geographic_item_id]
        h[:untranslated] = true
      end
    end

    h[:otu_id] = otu_id
    h
  end


  # Create breadth-first CachedMapItems
  #   Only applicable to Georeferences.
  #
  # @params batch_stubs [Hash]
  # {
  #  map_type: ,
  #  geographic_item_id: []
  #  otu_id: [ [otu_id, :project_id], ... [] ],
  #  georeference_id: [ [geoference_id, :project_id] ],
  # }
  #
  #
  def self.batch_create_georeference_cached_map_items(batch_stubs)
    map_type = batch_stubs[:map_type]
    j = batch_stubs[:geographic_item_id]
    k = batch_stubs[:otu_id]

    j.each do |geographic_item_id|
      k.each do |otu_id|
        otu_id = otu_id.first
        project_id = otu_id.second

        begin
          a = CachedMapItem.find_or_initialize_by(
            type: map_type,
            otu_id:,
            geographic_item_id:,
            project_id:,
          )

          if a.persisted?
            a.increment!(:reference_count)
          else
            a.reference_count = 1
            a.save!
          end

        rescue ActiveRecord::RecordInvalid => e
          logger.debug e
        rescue PG::UniqueViolation
          logger.debug 'pg unique violation'
        end
      end
    end

    # Register the Georeferences
    registrations = []

    batch_stubs[:georeference_id].each do |georeference_id, project_id|
      registrations.push({
        cached_map_register_object_type: 'Georeference',
        cached_map_register_object_id: georeference_id,
        project_id:,
        created_at: Time.current,
        updated_at: Time.current,
      })
    end

    begin
      CachedMapRegister.insert_all(registrations) if registrations.present?
    rescue
      puts '!! Failed to register Georeferences in batch_create_georeference_cached_map_items.'
    end

    true
  end

end

#reference_count ⇒ Integer

reference this OTU/shape combination. . . . .

Returns:

  • (Integer) —

    the number of Georeferences + AssertedDistributions that



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'app/models/cached_map_item.rb', line 44

class CachedMapItem < ApplicationRecord
  include Housekeeping::Projects
  include Housekeeping::Timestamps
  include Shared::IsData

  belongs_to :otu, inverse_of: :cached_map_items
  belongs_to :geographic_item, inverse_of: :cached_map_items

  validates_uniqueness_of :otu_id, scope: [:type, :geographic_item_id]
  validates_presence_of :otu, :geographic_item, :type

  # @return Hash
  #   {country:, state:, county: }
  #  Check to see if a record is already present rather than-recalculate spatially
  #  CONSIDER: Use Reddis store to cache these results and look them up from there.
  #
  def self.cached_map_name_hierarchy(geographic_item_id)
    h = CachedMapItem
      .select('level0_geographic_name country, level1_geographic_name state, level2_geographic_name county')
      .where('level0_geographic_name IS NOT NULL OR level1_geographic_name IS NOT NULL OR level2_geographic_name IS NOT NULL')
      .find_by(geographic_item_id:) # finds first
      &.attributes
      &.compact!

    return h.symbolize_keys if h.present?

    GeographicItem.find(geographic_item_id).quick_geographic_name_hierarchy
  end

  def self.cached_map_geographic_items_by_otu(otu_scope = nil)
    return GeographicItem.none if otu_scope.nil?

    s = 'WITH otu_scope AS (' + otu_scope.all.to_sql + ') ' +
      ::GeographicItem
      .joins('JOIN cached_maps on cached_maps.geographic_item_id = geographic_items.id')
      .joins( 'JOIN otu_scope as otu_scope1 on otu_scope1.id = cached_maps.otu_id').to_sql

    ::GeographicItem.from('(' + s + ') as geographic_items').distinct
  end

  # @return Array
  def self.types_by_data_origin(data_origin = [])
    types = []
    data_origin.each do |o|
      CachedMapItem.descendants.each do |d|
        types.push d.name if d::SOURCE_GAZETEERS.include?(o)
      end
    end

    types.uniq!
    types
  end

  # Check CachedMapItemTranslation for previous translations and use
  #   that if possible
  def self.translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
    a = CachedMapItemTranslation.where(
      cached_map_type:,
      geographic_item_id:
    ).pluck(:translated_geographic_item_id)
      .uniq # Just in case we duplicate the index, hopefully not needed

    (a.presence || [])
  end

  # @return [Array]
  #   Return the geographic_item_id if it is already of the requested origin
  def self.translate_by_data_origin(geographic_item_id, data_origin)
    if ::GeographicAreasGeographicItem.where(
        geographic_item_id:,
        data_origin:
    ).any?
      return [geographic_item_id]
    else
      []
    end
  end

  # @return [Array]
  # Return the geographic_item_id if it is already used in a CachedMapItem of the right type
  #   !! Probably redundant with translate_by_data_origin !!
  def self.translate_by_cached_map_usage(geographic_item_id, cached_map_type)
    if ::CachedMapItem.where(geographic_item_id:, type: cached_map_type).any?
      return [geographic_item_id]
    else
      []
    end
  end

  def self.translate_by_alternate_shape(geographic_item_id, data_origin)
    # GeographicItem is an alternate shape to a GeographicArea that *also* has a target gazeteer type
    if a = GeographicItem.find(geographic_item_id)
      .geographic_areas
      .joins(:geographic_items)
      .where(geographic_areas: { data_origin: })
      .order(cached_total_area: :ASC) # smallest first
      .first
      &.id
      return [a]
    else
      []
    end
  end

  # Given a  set of target shapes, return those that intersect with the provided shape
  #
  # @param geographic_item_id [id]
  #   the shape we are translating *from*
  #
  # @param data_origin
  #    defines the shapes we are translating to
  #
  # #param buffer [nil, Decimal] in meters
  #    shrink, (or grow) the shape we are translating from
  #    Typical use is to shrink, so that differences in spatial resolution
  #    are minimized (low res shapes intersect with high res in undesireable ways)
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer)
    return [] if geographic_item_id.blank?

    # !! Assumes all GeographicArea shapes were loaded to multi_polygon
    # (pre-adapts us to a single geometry field type), however be
    # aware of this assumption

    # This is a fast first pass, pure intersection
    a = GeographicItem
      .joins(:geographic_areas_geographic_items)
      .where(geographic_areas_geographic_items: { data_origin: })
      .where( "ST_Intersects( multi_polygon, ( select #{ GeographicItem::GEOGRAPHY_SQL } from geographic_items where geographic_items.id = #{geographic_item_id}) )" )
      .pluck(:id)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    return GeographicItem
      .where(id: a)
      .where( GeographicItem.st_buffer_st_within(geographic_item_id, 0.0, buffer) )
      .pluck(:id)
  end

  # @return [Array]
  # @param origin_type
  #   'AssertedDistribution' or 'Georeference'
  #
  # @param data_origin Array, String
  #   like `ne_states` or ['ne_states, 'ne_countries']
  #
  # @param buffer [nil, Decimal]
  #   shr,ink (or grow) the size of the target shape, in meters
  #   Typical use, do not apply for Georeferences, apply -10km for AssertedDistributions
  #
  def self.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil)
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if origin_type == 'AssertedDistribution'
      a = translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
      return a if a.present?

      a = translate_by_data_origin(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_alternate_shape(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_cached_map_usage(geographic_item_id, cached_map_type)
      return a if a.present?

      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(geographic_item_id, data_origin, b)
  end

  def self.dynamic_buffer(geographic_item_id)
    v = GeographicItem.select(:id, :cached_total_area).find(geographic_item_id).cached_total_area
    return 0 if v.nil?
    case Math.log10(v).to_i
    when 0..2 # 3786
      0.0
    when 3..6 # Perhaps no GeographicAreas hit here
      -100.0
    when 7 # e.g. Monaco 122
      -1000
    when 8
      -2000 # e.g. Calhoun Co. 27490
    when 9
      -4000 # 3786 Cooma-Monaro
    when 10..12
      -12000.0  # e.g. Brazil 33794; Canada 37 !! Seems to be a sweet spot, remainder untested
    else #(max is 13)
      -20000.0 # Antarctic, 10
    end
  end

  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type)
    # return nil unless source_object.persisted?
    o = source_object

    h = {
      origin_object: o,
      cached_map_type:,
      otu_id: [],
      geographic_item_id: [],
      origin_geographic_item_id: nil
    }

    geographic_item_id = nil
    name_hierarchy = nil
    otu_id = nil

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      geographic_item_id = o.geographic_area.default_geographic_item_id
      otu_id = [o.otu_id]
    when 'Georeference'
      geographic_item_id = o.geographic_item_id
      otu_id = o.otus.joins('LEFT JOIN taxon_determinations td on otus.id = td.otu_id').where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    # Some AssertedDistribution don't have shapes
    if geographic_item_id
      h[:origin_geographic_item_id] = geographic_item_id

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id,
        base_class_name,
        cached_map_type.safe_constantize::SOURCE_GAZETEERS
      )

      if h[:geographic_item_id].blank?
        h[:geographic_item_id] = [geographic_item_id]
        h[:untranslated] = true
      end
    end

    h[:otu_id] = otu_id
    h
  end


  # Create breadth-first CachedMapItems
  #   Only applicable to Georeferences.
  #
  # @params batch_stubs [Hash]
  # {
  #  map_type: ,
  #  geographic_item_id: []
  #  otu_id: [ [otu_id, :project_id], ... [] ],
  #  georeference_id: [ [geoference_id, :project_id] ],
  # }
  #
  #
  def self.batch_create_georeference_cached_map_items(batch_stubs)
    map_type = batch_stubs[:map_type]
    j = batch_stubs[:geographic_item_id]
    k = batch_stubs[:otu_id]

    j.each do |geographic_item_id|
      k.each do |otu_id|
        otu_id = otu_id.first
        project_id = otu_id.second

        begin
          a = CachedMapItem.find_or_initialize_by(
            type: map_type,
            otu_id:,
            geographic_item_id:,
            project_id:,
          )

          if a.persisted?
            a.increment!(:reference_count)
          else
            a.reference_count = 1
            a.save!
          end

        rescue ActiveRecord::RecordInvalid => e
          logger.debug e
        rescue PG::UniqueViolation
          logger.debug 'pg unique violation'
        end
      end
    end

    # Register the Georeferences
    registrations = []

    batch_stubs[:georeference_id].each do |georeference_id, project_id|
      registrations.push({
        cached_map_register_object_type: 'Georeference',
        cached_map_register_object_id: georeference_id,
        project_id:,
        created_at: Time.current,
        updated_at: Time.current,
      })
    end

    begin
      CachedMapRegister.insert_all(registrations) if registrations.present?
    rescue
      puts '!! Failed to register Georeferences in batch_create_georeference_cached_map_items.'
    end

    true
  end

end

#type ⇒ String

Returns Rails STI.

Returns:

  • (String) —

    Rails STI



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'app/models/cached_map_item.rb', line 44

class CachedMapItem < ApplicationRecord
  include Housekeeping::Projects
  include Housekeeping::Timestamps
  include Shared::IsData

  belongs_to :otu, inverse_of: :cached_map_items
  belongs_to :geographic_item, inverse_of: :cached_map_items

  validates_uniqueness_of :otu_id, scope: [:type, :geographic_item_id]
  validates_presence_of :otu, :geographic_item, :type

  # @return Hash
  #   {country:, state:, county: }
  #  Check to see if a record is already present rather than-recalculate spatially
  #  CONSIDER: Use Reddis store to cache these results and look them up from there.
  #
  def self.cached_map_name_hierarchy(geographic_item_id)
    h = CachedMapItem
      .select('level0_geographic_name country, level1_geographic_name state, level2_geographic_name county')
      .where('level0_geographic_name IS NOT NULL OR level1_geographic_name IS NOT NULL OR level2_geographic_name IS NOT NULL')
      .find_by(geographic_item_id:) # finds first
      &.attributes
      &.compact!

    return h.symbolize_keys if h.present?

    GeographicItem.find(geographic_item_id).quick_geographic_name_hierarchy
  end

  def self.cached_map_geographic_items_by_otu(otu_scope = nil)
    return GeographicItem.none if otu_scope.nil?

    s = 'WITH otu_scope AS (' + otu_scope.all.to_sql + ') ' +
      ::GeographicItem
      .joins('JOIN cached_maps on cached_maps.geographic_item_id = geographic_items.id')
      .joins( 'JOIN otu_scope as otu_scope1 on otu_scope1.id = cached_maps.otu_id').to_sql

    ::GeographicItem.from('(' + s + ') as geographic_items').distinct
  end

  # @return Array
  def self.types_by_data_origin(data_origin = [])
    types = []
    data_origin.each do |o|
      CachedMapItem.descendants.each do |d|
        types.push d.name if d::SOURCE_GAZETEERS.include?(o)
      end
    end

    types.uniq!
    types
  end

  # Check CachedMapItemTranslation for previous translations and use
  #   that if possible
  def self.translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
    a = CachedMapItemTranslation.where(
      cached_map_type:,
      geographic_item_id:
    ).pluck(:translated_geographic_item_id)
      .uniq # Just in case we duplicate the index, hopefully not needed

    (a.presence || [])
  end

  # @return [Array]
  #   Return the geographic_item_id if it is already of the requested origin
  def self.translate_by_data_origin(geographic_item_id, data_origin)
    if ::GeographicAreasGeographicItem.where(
        geographic_item_id:,
        data_origin:
    ).any?
      return [geographic_item_id]
    else
      []
    end
  end

  # @return [Array]
  # Return the geographic_item_id if it is already used in a CachedMapItem of the right type
  #   !! Probably redundant with translate_by_data_origin !!
  def self.translate_by_cached_map_usage(geographic_item_id, cached_map_type)
    if ::CachedMapItem.where(geographic_item_id:, type: cached_map_type).any?
      return [geographic_item_id]
    else
      []
    end
  end

  def self.translate_by_alternate_shape(geographic_item_id, data_origin)
    # GeographicItem is an alternate shape to a GeographicArea that *also* has a target gazeteer type
    if a = GeographicItem.find(geographic_item_id)
      .geographic_areas
      .joins(:geographic_items)
      .where(geographic_areas: { data_origin: })
      .order(cached_total_area: :ASC) # smallest first
      .first
      &.id
      return [a]
    else
      []
    end
  end

  # Given a  set of target shapes, return those that intersect with the provided shape
  #
  # @param geographic_item_id [id]
  #   the shape we are translating *from*
  #
  # @param data_origin
  #    defines the shapes we are translating to
  #
  # #param buffer [nil, Decimal] in meters
  #    shrink, (or grow) the shape we are translating from
  #    Typical use is to shrink, so that differences in spatial resolution
  #    are minimized (low res shapes intersect with high res in undesireable ways)
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer)
    return [] if geographic_item_id.blank?

    # !! Assumes all GeographicArea shapes were loaded to multi_polygon
    # (pre-adapts us to a single geometry field type), however be
    # aware of this assumption

    # This is a fast first pass, pure intersection
    a = GeographicItem
      .joins(:geographic_areas_geographic_items)
      .where(geographic_areas_geographic_items: { data_origin: })
      .where( "ST_Intersects( multi_polygon, ( select #{ GeographicItem::GEOGRAPHY_SQL } from geographic_items where geographic_items.id = #{geographic_item_id}) )" )
      .pluck(:id)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    return GeographicItem
      .where(id: a)
      .where( GeographicItem.st_buffer_st_within(geographic_item_id, 0.0, buffer) )
      .pluck(:id)
  end

  # @return [Array]
  # @param origin_type
  #   'AssertedDistribution' or 'Georeference'
  #
  # @param data_origin Array, String
  #   like `ne_states` or ['ne_states, 'ne_countries']
  #
  # @param buffer [nil, Decimal]
  #   shr,ink (or grow) the size of the target shape, in meters
  #   Typical use, do not apply for Georeferences, apply -10km for AssertedDistributions
  #
  def self.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil)
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if origin_type == 'AssertedDistribution'
      a = translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
      return a if a.present?

      a = translate_by_data_origin(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_alternate_shape(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_cached_map_usage(geographic_item_id, cached_map_type)
      return a if a.present?

      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(geographic_item_id, data_origin, b)
  end

  def self.dynamic_buffer(geographic_item_id)
    v = GeographicItem.select(:id, :cached_total_area).find(geographic_item_id).cached_total_area
    return 0 if v.nil?
    case Math.log10(v).to_i
    when 0..2 # 3786
      0.0
    when 3..6 # Perhaps no GeographicAreas hit here
      -100.0
    when 7 # e.g. Monaco 122
      -1000
    when 8
      -2000 # e.g. Calhoun Co. 27490
    when 9
      -4000 # 3786 Cooma-Monaro
    when 10..12
      -12000.0  # e.g. Brazil 33794; Canada 37 !! Seems to be a sweet spot, remainder untested
    else #(max is 13)
      -20000.0 # Antarctic, 10
    end
  end

  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type)
    # return nil unless source_object.persisted?
    o = source_object

    h = {
      origin_object: o,
      cached_map_type:,
      otu_id: [],
      geographic_item_id: [],
      origin_geographic_item_id: nil
    }

    geographic_item_id = nil
    name_hierarchy = nil
    otu_id = nil

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      geographic_item_id = o.geographic_area.default_geographic_item_id
      otu_id = [o.otu_id]
    when 'Georeference'
      geographic_item_id = o.geographic_item_id
      otu_id = o.otus.joins('LEFT JOIN taxon_determinations td on otus.id = td.otu_id').where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    # Some AssertedDistribution don't have shapes
    if geographic_item_id
      h[:origin_geographic_item_id] = geographic_item_id

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id,
        base_class_name,
        cached_map_type.safe_constantize::SOURCE_GAZETEERS
      )

      if h[:geographic_item_id].blank?
        h[:geographic_item_id] = [geographic_item_id]
        h[:untranslated] = true
      end
    end

    h[:otu_id] = otu_id
    h
  end


  # Create breadth-first CachedMapItems
  #   Only applicable to Georeferences.
  #
  # @params batch_stubs [Hash]
  # {
  #  map_type: ,
  #  geographic_item_id: []
  #  otu_id: [ [otu_id, :project_id], ... [] ],
  #  georeference_id: [ [geoference_id, :project_id] ],
  # }
  #
  #
  def self.batch_create_georeference_cached_map_items(batch_stubs)
    map_type = batch_stubs[:map_type]
    j = batch_stubs[:geographic_item_id]
    k = batch_stubs[:otu_id]

    j.each do |geographic_item_id|
      k.each do |otu_id|
        otu_id = otu_id.first
        project_id = otu_id.second

        begin
          a = CachedMapItem.find_or_initialize_by(
            type: map_type,
            otu_id:,
            geographic_item_id:,
            project_id:,
          )

          if a.persisted?
            a.increment!(:reference_count)
          else
            a.reference_count = 1
            a.save!
          end

        rescue ActiveRecord::RecordInvalid => e
          logger.debug e
        rescue PG::UniqueViolation
          logger.debug 'pg unique violation'
        end
      end
    end

    # Register the Georeferences
    registrations = []

    batch_stubs[:georeference_id].each do |georeference_id, project_id|
      registrations.push({
        cached_map_register_object_type: 'Georeference',
        cached_map_register_object_id: georeference_id,
        project_id:,
        created_at: Time.current,
        updated_at: Time.current,
      })
    end

    begin
      CachedMapRegister.insert_all(registrations) if registrations.present?
    rescue
      puts '!! Failed to register Georeferences in batch_create_georeference_cached_map_items.'
    end

    true
  end

end

#untranslated ⇒ Boolean?

Returns if True then the the shape could not be mapped, by any translation method, to a shape allowable for this CachedMapItemType.

Returns:

  • (Boolean, nil) —

    if True then the the shape could not be mapped, by any translation method, to a shape allowable for this CachedMapItemType



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'app/models/cached_map_item.rb', line 44

class CachedMapItem < ApplicationRecord
  include Housekeeping::Projects
  include Housekeeping::Timestamps
  include Shared::IsData

  belongs_to :otu, inverse_of: :cached_map_items
  belongs_to :geographic_item, inverse_of: :cached_map_items

  validates_uniqueness_of :otu_id, scope: [:type, :geographic_item_id]
  validates_presence_of :otu, :geographic_item, :type

  # @return Hash
  #   {country:, state:, county: }
  #  Check to see if a record is already present rather than-recalculate spatially
  #  CONSIDER: Use Reddis store to cache these results and look them up from there.
  #
  def self.cached_map_name_hierarchy(geographic_item_id)
    h = CachedMapItem
      .select('level0_geographic_name country, level1_geographic_name state, level2_geographic_name county')
      .where('level0_geographic_name IS NOT NULL OR level1_geographic_name IS NOT NULL OR level2_geographic_name IS NOT NULL')
      .find_by(geographic_item_id:) # finds first
      &.attributes
      &.compact!

    return h.symbolize_keys if h.present?

    GeographicItem.find(geographic_item_id).quick_geographic_name_hierarchy
  end

  def self.cached_map_geographic_items_by_otu(otu_scope = nil)
    return GeographicItem.none if otu_scope.nil?

    s = 'WITH otu_scope AS (' + otu_scope.all.to_sql + ') ' +
      ::GeographicItem
      .joins('JOIN cached_maps on cached_maps.geographic_item_id = geographic_items.id')
      .joins( 'JOIN otu_scope as otu_scope1 on otu_scope1.id = cached_maps.otu_id').to_sql

    ::GeographicItem.from('(' + s + ') as geographic_items').distinct
  end

  # @return Array
  def self.types_by_data_origin(data_origin = [])
    types = []
    data_origin.each do |o|
      CachedMapItem.descendants.each do |d|
        types.push d.name if d::SOURCE_GAZETEERS.include?(o)
      end
    end

    types.uniq!
    types
  end

  # Check CachedMapItemTranslation for previous translations and use
  #   that if possible
  def self.translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
    a = CachedMapItemTranslation.where(
      cached_map_type:,
      geographic_item_id:
    ).pluck(:translated_geographic_item_id)
      .uniq # Just in case we duplicate the index, hopefully not needed

    (a.presence || [])
  end

  # @return [Array]
  #   Return the geographic_item_id if it is already of the requested origin
  def self.translate_by_data_origin(geographic_item_id, data_origin)
    if ::GeographicAreasGeographicItem.where(
        geographic_item_id:,
        data_origin:
    ).any?
      return [geographic_item_id]
    else
      []
    end
  end

  # @return [Array]
  # Return the geographic_item_id if it is already used in a CachedMapItem of the right type
  #   !! Probably redundant with translate_by_data_origin !!
  def self.translate_by_cached_map_usage(geographic_item_id, cached_map_type)
    if ::CachedMapItem.where(geographic_item_id:, type: cached_map_type).any?
      return [geographic_item_id]
    else
      []
    end
  end

  def self.translate_by_alternate_shape(geographic_item_id, data_origin)
    # GeographicItem is an alternate shape to a GeographicArea that *also* has a target gazeteer type
    if a = GeographicItem.find(geographic_item_id)
      .geographic_areas
      .joins(:geographic_items)
      .where(geographic_areas: { data_origin: })
      .order(cached_total_area: :ASC) # smallest first
      .first
      &.id
      return [a]
    else
      []
    end
  end

  # Given a  set of target shapes, return those that intersect with the provided shape
  #
  # @param geographic_item_id [id]
  #   the shape we are translating *from*
  #
  # @param data_origin
  #    defines the shapes we are translating to
  #
  # #param buffer [nil, Decimal] in meters
  #    shrink, (or grow) the shape we are translating from
  #    Typical use is to shrink, so that differences in spatial resolution
  #    are minimized (low res shapes intersect with high res in undesireable ways)
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer)
    return [] if geographic_item_id.blank?

    # !! Assumes all GeographicArea shapes were loaded to multi_polygon
    # (pre-adapts us to a single geometry field type), however be
    # aware of this assumption

    # This is a fast first pass, pure intersection
    a = GeographicItem
      .joins(:geographic_areas_geographic_items)
      .where(geographic_areas_geographic_items: { data_origin: })
      .where( "ST_Intersects( multi_polygon, ( select #{ GeographicItem::GEOGRAPHY_SQL } from geographic_items where geographic_items.id = #{geographic_item_id}) )" )
      .pluck(:id)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    return GeographicItem
      .where(id: a)
      .where( GeographicItem.st_buffer_st_within(geographic_item_id, 0.0, buffer) )
      .pluck(:id)
  end

  # @return [Array]
  # @param origin_type
  #   'AssertedDistribution' or 'Georeference'
  #
  # @param data_origin Array, String
  #   like `ne_states` or ['ne_states, 'ne_countries']
  #
  # @param buffer [nil, Decimal]
  #   shr,ink (or grow) the size of the target shape, in meters
  #   Typical use, do not apply for Georeferences, apply -10km for AssertedDistributions
  #
  def self.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil)
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if origin_type == 'AssertedDistribution'
      a = translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
      return a if a.present?

      a = translate_by_data_origin(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_alternate_shape(geographic_item_id, data_origin)
      return a if a.present?

      a = translate_by_cached_map_usage(geographic_item_id, cached_map_type)
      return a if a.present?

      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(geographic_item_id, data_origin, b)
  end

  def self.dynamic_buffer(geographic_item_id)
    v = GeographicItem.select(:id, :cached_total_area).find(geographic_item_id).cached_total_area
    return 0 if v.nil?
    case Math.log10(v).to_i
    when 0..2 # 3786
      0.0
    when 3..6 # Perhaps no GeographicAreas hit here
      -100.0
    when 7 # e.g. Monaco 122
      -1000
    when 8
      -2000 # e.g. Calhoun Co. 27490
    when 9
      -4000 # 3786 Cooma-Monaro
    when 10..12
      -12000.0  # e.g. Brazil 33794; Canada 37 !! Seems to be a sweet spot, remainder untested
    else #(max is 13)
      -20000.0 # Antarctic, 10
    end
  end

  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type)
    # return nil unless source_object.persisted?
    o = source_object

    h = {
      origin_object: o,
      cached_map_type:,
      otu_id: [],
      geographic_item_id: [],
      origin_geographic_item_id: nil
    }

    geographic_item_id = nil
    name_hierarchy = nil
    otu_id = nil

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      geographic_item_id = o.geographic_area.default_geographic_item_id
      otu_id = [o.otu_id]
    when 'Georeference'
      geographic_item_id = o.geographic_item_id
      otu_id = o.otus.joins('LEFT JOIN taxon_determinations td on otus.id = td.otu_id').where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    # Some AssertedDistribution don't have shapes
    if geographic_item_id
      h[:origin_geographic_item_id] = geographic_item_id

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id,
        base_class_name,
        cached_map_type.safe_constantize::SOURCE_GAZETEERS
      )

      if h[:geographic_item_id].blank?
        h[:geographic_item_id] = [geographic_item_id]
        h[:untranslated] = true
      end
    end

    h[:otu_id] = otu_id
    h
  end


  # Create breadth-first CachedMapItems
  #   Only applicable to Georeferences.
  #
  # @params batch_stubs [Hash]
  # {
  #  map_type: ,
  #  geographic_item_id: []
  #  otu_id: [ [otu_id, :project_id], ... [] ],
  #  georeference_id: [ [geoference_id, :project_id] ],
  # }
  #
  #
  def self.batch_create_georeference_cached_map_items(batch_stubs)
    map_type = batch_stubs[:map_type]
    j = batch_stubs[:geographic_item_id]
    k = batch_stubs[:otu_id]

    j.each do |geographic_item_id|
      k.each do |otu_id|
        otu_id = otu_id.first
        project_id = otu_id.second

        begin
          a = CachedMapItem.find_or_initialize_by(
            type: map_type,
            otu_id:,
            geographic_item_id:,
            project_id:,
          )

          if a.persisted?
            a.increment!(:reference_count)
          else
            a.reference_count = 1
            a.save!
          end

        rescue ActiveRecord::RecordInvalid => e
          logger.debug e
        rescue PG::UniqueViolation
          logger.debug 'pg unique violation'
        end
      end
    end

    # Register the Georeferences
    registrations = []

    batch_stubs[:georeference_id].each do |georeference_id, project_id|
      registrations.push({
        cached_map_register_object_type: 'Georeference',
        cached_map_register_object_id: georeference_id,
        project_id:,
        created_at: Time.current,
        updated_at: Time.current,
      })
    end

    begin
      CachedMapRegister.insert_all(registrations) if registrations.present?
    rescue
      puts '!! Failed to register Georeferences in batch_create_georeference_cached_map_items.'
    end

    true
  end

end

Class Method Details

.batch_create_georeference_cached_map_items(batch_stubs) ⇒ Object

Create breadth-first CachedMapItems

Only applicable to Georeferences.


map_type: ,
geographic_item_id: []
otu_id: [ [otu_id, :project_id], ... [] ],
georeference_id: [ [geoference_id, :project_id] ],




308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
 
# File 'app/models/cached_map_item.rb', line 308

def self.batch_create_georeference_cached_map_items(batch_stubs)
  map_type = batch_stubs[:map_type]
  j = batch_stubs[:geographic_item_id]
  k = batch_stubs[:otu_id]

  j.each do |geographic_item_id|
    k.each do |otu_id|
      otu_id = otu_id.first
      project_id = otu_id.second

      begin
        a = CachedMapItem.find_or_initialize_by(
          type: map_type,
          otu_id:,
          geographic_item_id:,
          project_id:,
        )

        if a.persisted?
          a.increment!(:reference_count)
        else
          a.reference_count = 1
          a.save!
        end

      rescue ActiveRecord::RecordInvalid => e
        logger.debug e
      rescue PG::UniqueViolation
        logger.debug 'pg unique violation'
      end
    end
  end

  # Register the Georeferences
  registrations = []

  batch_stubs[:georeference_id].each do |georeference_id, project_id|
    registrations.push({
      cached_map_register_object_type: 'Georeference',
      cached_map_register_object_id: georeference_id,
      project_id:,
      created_at: Time.current,
      updated_at: Time.current,
    })
  end

  begin
    CachedMapRegister.insert_all(registrations) if registrations.present?
  rescue
    puts '!! Failed to register Georeferences in batch_create_georeference_cached_map_items.'
  end

  true
end

.cached_map_geographic_items_by_otu(otu_scope = nil) ⇒ Object 



73
74
75
76
77
78
79
80
81
82
 
# File 'app/models/cached_map_item.rb', line 73

def self.cached_map_geographic_items_by_otu(otu_scope = nil)
  return GeographicItem.none if otu_scope.nil?

  s = 'WITH otu_scope AS (' + otu_scope.all.to_sql + ') ' +
    ::GeographicItem
    .joins('JOIN cached_maps on cached_maps.geographic_item_id = geographic_items.id')
    .joins( 'JOIN otu_scope as otu_scope1 on otu_scope1.id = cached_maps.otu_id').to_sql

  ::GeographicItem.from('(' + s + ') as geographic_items').distinct
end

.cached_map_name_hierarchy(geographic_item_id) ⇒ Object

Check to see if a record is already present rather than-recalculate spatially

CONSIDER: Use Reddis store to cache these results and look them up from there.

Returns:

  • Hash {country:, state:, county: }



60
61
62
63
64
65
66
67
68
69
70
71
 
# File 'app/models/cached_map_item.rb', line 60

def self.cached_map_name_hierarchy(geographic_item_id)
  h = CachedMapItem
    .select('level0_geographic_name country, level1_geographic_name state, level2_geographic_name county')
    .where('level0_geographic_name IS NOT NULL OR level1_geographic_name IS NOT NULL OR level2_geographic_name IS NOT NULL')
    .find_by(geographic_item_id:) # finds first
    &.attributes
    &.compact!

  return h.symbolize_keys if h.present?

  GeographicItem.find(geographic_item_id).quick_geographic_name_hierarchy
end

.dynamic_buffer(geographic_item_id) ⇒ Object 



226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
 
# File 'app/models/cached_map_item.rb', line 226

def self.dynamic_buffer(geographic_item_id)
  v = GeographicItem.select(:id, :cached_total_area).find(geographic_item_id).cached_total_area
  return 0 if v.nil?
  case Math.log10(v).to_i
  when 0..2 # 3786
    0.0
  when 3..6 # Perhaps no GeographicAreas hit here
    -100.0
  when 7 # e.g. Monaco 122
    -1000
  when 8
    -2000 # e.g. Calhoun Co. 27490
  when 9
    -4000 # 3786 Cooma-Monaro
  when 10..12
    -12000.0  # e.g. Brazil 33794; Canada 37 !! Seems to be a sweet spot, remainder untested
  else #(max is 13)
    -20000.0 # Antarctic, 10
  end
end

.stubs(source_object, cached_map_type) ⇒ Hash?

Returns:

  • (Hash, nil) 


248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
 
# File 'app/models/cached_map_item.rb', line 248

def self.stubs(source_object, cached_map_type)
  # return nil unless source_object.persisted?
  o = source_object

  h = {
    origin_object: o,
    cached_map_type:,
    otu_id: [],
    geographic_item_id: [],
    origin_geographic_item_id: nil
  }

  geographic_item_id = nil
  name_hierarchy = nil
  otu_id = nil

  base_class_name = o.class.base_class.name

  case base_class_name
  when 'AssertedDistribution'
    geographic_item_id = o.geographic_area.default_geographic_item_id
    otu_id = [o.otu_id]
  when 'Georeference'
    geographic_item_id = o.geographic_item_id
    otu_id = o.otus.joins('LEFT JOIN taxon_determinations td on otus.id = td.otu_id').where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
  end

  # Some AssertedDistribution don't have shapes
  if geographic_item_id
    h[:origin_geographic_item_id] = geographic_item_id

    h[:geographic_item_id] = translate_geographic_item_id(
      geographic_item_id,
      base_class_name,
      cached_map_type.safe_constantize::SOURCE_GAZETEERS
    )

    if h[:geographic_item_id].blank?
      h[:geographic_item_id] = [geographic_item_id]
      h[:untranslated] = true
    end
  end

  h[:otu_id] = otu_id
  h
end

.translate_by_alternate_shape(geographic_item_id, data_origin) ⇒ Object 



133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'app/models/cached_map_item.rb', line 133

def self.translate_by_alternate_shape(geographic_item_id, data_origin)
  # GeographicItem is an alternate shape to a GeographicArea that *also* has a target gazeteer type
  if a = GeographicItem.find(geographic_item_id)
    .geographic_areas
    .joins(:geographic_items)
    .where(geographic_areas: { data_origin: })
    .order(cached_total_area: :ASC) # smallest first
    .first
    &.id
    return [a]
  else
    []
  end
end

.translate_by_cached_map_usage(geographic_item_id, cached_map_type) ⇒ Array

Return the geographic_item_id if it is already used in a CachedMapItem of the right type

!! Probably redundant with translate_by_data_origin !!

Returns:

  • (Array) 


125
126
127
128
129
130
131
 
# File 'app/models/cached_map_item.rb', line 125

def self.translate_by_cached_map_usage(geographic_item_id, cached_map_type)
  if ::CachedMapItem.where(geographic_item_id:, type: cached_map_type).any?
    return [geographic_item_id]
  else
    []
  end
end

.translate_by_data_origin(geographic_item_id, data_origin) ⇒ Array

Return the geographic_item_id if it is already of the requested origin

Returns:

  • (Array) —

    Return the geographic_item_id if it is already of the requested origin



111
112
113
114
115
116
117
118
119
120
 
# File 'app/models/cached_map_item.rb', line 111

def self.translate_by_data_origin(geographic_item_id, data_origin)
  if ::GeographicAreasGeographicItem.where(
      geographic_item_id:,
      data_origin:
  ).any?
    return [geographic_item_id]
  else
    []
  end
end

.translate_by_geographic_item_translation(geographic_item_id, cached_map_type) ⇒ Object

Check CachedMapItemTranslation for previous translations and use

that if possible



99
100
101
102
103
104
105
106
107
 
# File 'app/models/cached_map_item.rb', line 99

def self.translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
  a = CachedMapItemTranslation.where(
    cached_map_type:,
    geographic_item_id:
  ).pluck(:translated_geographic_item_id)
    .uniq # Just in case we duplicate the index, hopefully not needed

  (a.presence || [])
end

.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer) ⇒ Array

Given a set of target shapes, return those that intersect with the provided shape

#param buffer [nil, Decimal] in meters

shrink, (or grow) the shape we are translating from
Typical use is to shrink, so that differences in spatial resolution
are minimized (low res shapes intersect with high res in undesireable ways)

Parameters:

  • geographic_item_id (id) —

    the shape we are translating from

  • data_origin —

    defines the shapes we are translating to

Returns:

  • (Array) —

    of GeographicItem ids



163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
 
# File 'app/models/cached_map_item.rb', line 163

def self.translate_by_spatial_overlap(geographic_item_id, data_origin, buffer)
  return [] if geographic_item_id.blank?

  # !! Assumes all GeographicArea shapes were loaded to multi_polygon
  # (pre-adapts us to a single geometry field type), however be
  # aware of this assumption

  # This is a fast first pass, pure intersection
  a = GeographicItem
    .joins(:geographic_areas_geographic_items)
    .where(geographic_areas_geographic_items: { data_origin: })
    .where( "ST_Intersects( multi_polygon, ( select #{ GeographicItem::GEOGRAPHY_SQL } from geographic_items where geographic_items.id = #{geographic_item_id}) )" )
    .pluck(:id)

  return a if buffer.nil?

  # Refine the pass by smoothing using buffer/st_within
  return GeographicItem
    .where(id: a)
    .where( GeographicItem.st_buffer_st_within(geographic_item_id, 0.0, buffer) )
    .pluck(:id)
end

.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil) ⇒ Array

Parameters:

  • origin_type (defaults to: nil) —

    ‘AssertedDistribution’ or ‘Georeference’

  • data_origin (defaults to: nil) —

    Array, String like ‘ne_states` or [’ne_states, ‘ne_countries’]

  • buffer (nil, Decimal) (defaults to: nil) —

    shr,ink (or grow) the size of the target shape, in meters Typical use, do not apply for Georeferences, apply -10km for AssertedDistributions

Returns:

  • (Array) 


197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
 
# File 'app/models/cached_map_item.rb', line 197

def self.translate_geographic_item_id(geographic_item_id, origin_type = nil, data_origin = nil, buffer = nil)
  return nil if data_origin.blank?

  cached_map_type = types_by_data_origin(data_origin)

  a = nil

  b = buffer

  # All these methods depend on "prior knowledge" (not spatial calculations)
  if origin_type == 'AssertedDistribution'
    a = translate_by_geographic_item_translation(geographic_item_id, cached_map_type)
    return a if a.present?

    a = translate_by_data_origin(geographic_item_id, data_origin)
    return a if a.present?

    a = translate_by_alternate_shape(geographic_item_id, data_origin)
    return a if a.present?

    a = translate_by_cached_map_usage(geographic_item_id, cached_map_type)
    return a if a.present?

    b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
  end

  translate_by_spatial_overlap(geographic_item_id, data_origin, b)
end

.types_by_data_origin(data_origin = []) ⇒ Object

Returns Array.

Returns:

  • Array



85
86
87
88
89
90
91
92
93
94
95
 
# File 'app/models/cached_map_item.rb', line 85

def self.types_by_data_origin(data_origin = [])
  types = []
  data_origin.each do |o|
    CachedMapItem.descendants.each do |d|
      types.push d.name if d::SOURCE_GAZETEERS.include?(o)
    end
  end

  types.uniq!
  types
end