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_in_use?, #similar

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:



40
41
42
43
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
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'app/models/cached_map_item.rb', line 40

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

  # Given a set of target shapes (via data_origin), return those that intersect
  # with the provided geographic_item_id 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)
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3") [an array would be better,
  #   but this saves 200k array joins on length-4k arrays during batch create]
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(
    geographic_item_id, data_origin, buffer, precomputed_data_origin_ids: nil
  )
    return [] if geographic_item_id.blank?
    data_origin = Array(data_origin).compact
    return [] if data_origin.empty?

    precomputed_ids = nil
    if data_origin.length == 1
      if precomputed_data_origin_ids.present?
        precomputed_ids = precomputed_data_origin_ids[data_origin.first]
      else
        precomputed_ids = precomputed_data_origin_ids_for(data_origin.first)
      end
    end

    raise TaxonWorks::Error, "Missing pre-computed ids for cached maps origin '#{data_origin}'" if precomputed_ids.blank?
    raise TaxonWorks::Error, "Expected pre-computed ids for cached maps origin '#{data_origin}' to be a SQL IN list" unless precomputed_ids.is_a?(String)

    sql = <<~SQL
      SELECT gi.id
      FROM geographic_items gi
      CROSS JOIN (
        SELECT geography
        FROM geographic_items
        WHERE id = #{geographic_item_id.to_i}
      ) AS target
      -- Careful with IN here if we move to other data_origins (currently ~4k ids)
      WHERE gi.id IN (#{precomputed_ids})
        AND ST_Intersects(gi.geography, target.geography)
    SQL

    a = ActiveRecord::Base.connection.select_values(sql).map!(&:to_i)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    buffer_filter = GeographicItem
      .st_buffer_st_within_sql(geographic_item_id, 0.0, buffer)
      .or(
        # The intention here is to keep ne_states shapes that failed the buffer
        # check because the buffer reduced them to nothing, IF they're subsets
        # of geographic_item_id. This should prevent unexpected holes.
        GeographicItem.subset_of_sql(
          GeographicItem.st_buffer_sql(
            GeographicItem.select_geometry_sql(geographic_item_id),
            100
          )
        )
      )

    return GeographicItem
      .where(id: a)
      .where(buffer_filter)
      .pluck(:id)
  end

  def self.precomputed_data_origin_ids_for(data_origin, refresh: false)
    return nil unless data_origin == 'ne_states' # currently only ne_states supported

    @precomputed_data_origin_ids ||= {}
    if refresh || @precomputed_data_origin_ids[data_origin].blank?
      @precomputed_data_origin_ids[data_origin] = GeographicAreasGeographicItem
        .where(data_origin:)
        .distinct
        .pluck(:geographic_item_id)
        .join(',')
        .freeze
    end

    @precomputed_data_origin_ids[data_origin]
  end

  # @return [Array]
  # @param geographic_area_based [Boolean]
  #   true if geographic_item_id is the geographic_item id of some geographic
  #   area (and so has a chance of already being associated with a cached map
  #   item/translation)
  #
  # @param search_existing_translates [Boolean]
  #   true if existing cached_map_item_translations should be checked for a
  #   match.
  #
  # @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
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3")
  #
  def self.translate_geographic_item_id(
    geographic_item_id, geographic_area_based,
    search_existing_translates = true, data_origin = nil, buffer = nil,
    precomputed_data_origin_ids: nil
  )
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    if search_existing_translates
      a = translate_by_geographic_item_translation(
        geographic_item_id, cached_map_type
      )
      return a if a.present?
    end

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if geographic_area_based
      a = translate_by_data_origin(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?

      # TODO should Gazetteer GIs be getting a dynamic buffer? They don't
      # currently.
      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(
      geographic_item_id, data_origin, b, precomputed_data_origin_ids:
    )
  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? || v.to_i == 0
    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

  # @param context [Hash] of cached_map_types to pre-computed data for
  # source_object.
  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type, context: nil)
    # 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
    otu_id = nil
    context = context&.symbolize_keys || {}

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      if o.is_absent == true
        return h
      end
      if o.asserted_distribution_object_type == 'Otu'
        otu_id = [context[:otu_id] || o.asserted_distribution_object_id]
      else
        # TODO handle other types
        return h
      end
      geographic_item_id = context[:geographic_item_id] ||
        o.asserted_distribution_shape.default_geographic_item_id
    when 'Georeference'
      geographic_item_id = context[:geographic_item_id] || o.geographic_item_id
      otu_id = context[:otu_id] ||
        o.otus.left_joins(:taxon_determinations).where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    otu = nil
    taxon_name_id = context[:otu_taxon_name_id] ||
      Otu.where(id: otu_id).pick(:taxon_name_id)
    return h if otu_id.nil? || taxon_name_id.nil?
    # otus without taxon name have no hierarchy, so don't contribute to cached
    # maps.
    return h if taxon_name_id.blank?

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

      geographic_area_based = context.key?(:geographic_area_based) ?
        context[:geographic_area_based] :
        base_class_name == 'AssertedDistribution' &&
          o.asserted_distribution_shape_type == 'GeographicArea'
      search_existing_translates = base_class_name == 'AssertedDistribution'

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id, geographic_area_based, search_existing_translates,
        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



40
41
42
43
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
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'app/models/cached_map_item.rb', line 40

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

  # Given a set of target shapes (via data_origin), return those that intersect
  # with the provided geographic_item_id 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)
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3") [an array would be better,
  #   but this saves 200k array joins on length-4k arrays during batch create]
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(
    geographic_item_id, data_origin, buffer, precomputed_data_origin_ids: nil
  )
    return [] if geographic_item_id.blank?
    data_origin = Array(data_origin).compact
    return [] if data_origin.empty?

    precomputed_ids = nil
    if data_origin.length == 1
      if precomputed_data_origin_ids.present?
        precomputed_ids = precomputed_data_origin_ids[data_origin.first]
      else
        precomputed_ids = precomputed_data_origin_ids_for(data_origin.first)
      end
    end

    raise TaxonWorks::Error, "Missing pre-computed ids for cached maps origin '#{data_origin}'" if precomputed_ids.blank?
    raise TaxonWorks::Error, "Expected pre-computed ids for cached maps origin '#{data_origin}' to be a SQL IN list" unless precomputed_ids.is_a?(String)

    sql = <<~SQL
      SELECT gi.id
      FROM geographic_items gi
      CROSS JOIN (
        SELECT geography
        FROM geographic_items
        WHERE id = #{geographic_item_id.to_i}
      ) AS target
      -- Careful with IN here if we move to other data_origins (currently ~4k ids)
      WHERE gi.id IN (#{precomputed_ids})
        AND ST_Intersects(gi.geography, target.geography)
    SQL

    a = ActiveRecord::Base.connection.select_values(sql).map!(&:to_i)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    buffer_filter = GeographicItem
      .st_buffer_st_within_sql(geographic_item_id, 0.0, buffer)
      .or(
        # The intention here is to keep ne_states shapes that failed the buffer
        # check because the buffer reduced them to nothing, IF they're subsets
        # of geographic_item_id. This should prevent unexpected holes.
        GeographicItem.subset_of_sql(
          GeographicItem.st_buffer_sql(
            GeographicItem.select_geometry_sql(geographic_item_id),
            100
          )
        )
      )

    return GeographicItem
      .where(id: a)
      .where(buffer_filter)
      .pluck(:id)
  end

  def self.precomputed_data_origin_ids_for(data_origin, refresh: false)
    return nil unless data_origin == 'ne_states' # currently only ne_states supported

    @precomputed_data_origin_ids ||= {}
    if refresh || @precomputed_data_origin_ids[data_origin].blank?
      @precomputed_data_origin_ids[data_origin] = GeographicAreasGeographicItem
        .where(data_origin:)
        .distinct
        .pluck(:geographic_item_id)
        .join(',')
        .freeze
    end

    @precomputed_data_origin_ids[data_origin]
  end

  # @return [Array]
  # @param geographic_area_based [Boolean]
  #   true if geographic_item_id is the geographic_item id of some geographic
  #   area (and so has a chance of already being associated with a cached map
  #   item/translation)
  #
  # @param search_existing_translates [Boolean]
  #   true if existing cached_map_item_translations should be checked for a
  #   match.
  #
  # @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
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3")
  #
  def self.translate_geographic_item_id(
    geographic_item_id, geographic_area_based,
    search_existing_translates = true, data_origin = nil, buffer = nil,
    precomputed_data_origin_ids: nil
  )
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    if search_existing_translates
      a = translate_by_geographic_item_translation(
        geographic_item_id, cached_map_type
      )
      return a if a.present?
    end

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if geographic_area_based
      a = translate_by_data_origin(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?

      # TODO should Gazetteer GIs be getting a dynamic buffer? They don't
      # currently.
      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(
      geographic_item_id, data_origin, b, precomputed_data_origin_ids:
    )
  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? || v.to_i == 0
    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

  # @param context [Hash] of cached_map_types to pre-computed data for
  # source_object.
  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type, context: nil)
    # 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
    otu_id = nil
    context = context&.symbolize_keys || {}

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      if o.is_absent == true
        return h
      end
      if o.asserted_distribution_object_type == 'Otu'
        otu_id = [context[:otu_id] || o.asserted_distribution_object_id]
      else
        # TODO handle other types
        return h
      end
      geographic_item_id = context[:geographic_item_id] ||
        o.asserted_distribution_shape.default_geographic_item_id
    when 'Georeference'
      geographic_item_id = context[:geographic_item_id] || o.geographic_item_id
      otu_id = context[:otu_id] ||
        o.otus.left_joins(:taxon_determinations).where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    otu = nil
    taxon_name_id = context[:otu_taxon_name_id] ||
      Otu.where(id: otu_id).pick(:taxon_name_id)
    return h if otu_id.nil? || taxon_name_id.nil?
    # otus without taxon name have no hierarchy, so don't contribute to cached
    # maps.
    return h if taxon_name_id.blank?

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

      geographic_area_based = context.key?(:geographic_area_based) ?
        context[:geographic_area_based] :
        base_class_name == 'AssertedDistribution' &&
          o.asserted_distribution_shape_type == 'GeographicArea'
      search_existing_translates = base_class_name == 'AssertedDistribution'

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id, geographic_area_based, search_existing_translates,
        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



40
41
42
43
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
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'app/models/cached_map_item.rb', line 40

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

  # Given a set of target shapes (via data_origin), return those that intersect
  # with the provided geographic_item_id 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)
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3") [an array would be better,
  #   but this saves 200k array joins on length-4k arrays during batch create]
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(
    geographic_item_id, data_origin, buffer, precomputed_data_origin_ids: nil
  )
    return [] if geographic_item_id.blank?
    data_origin = Array(data_origin).compact
    return [] if data_origin.empty?

    precomputed_ids = nil
    if data_origin.length == 1
      if precomputed_data_origin_ids.present?
        precomputed_ids = precomputed_data_origin_ids[data_origin.first]
      else
        precomputed_ids = precomputed_data_origin_ids_for(data_origin.first)
      end
    end

    raise TaxonWorks::Error, "Missing pre-computed ids for cached maps origin '#{data_origin}'" if precomputed_ids.blank?
    raise TaxonWorks::Error, "Expected pre-computed ids for cached maps origin '#{data_origin}' to be a SQL IN list" unless precomputed_ids.is_a?(String)

    sql = <<~SQL
      SELECT gi.id
      FROM geographic_items gi
      CROSS JOIN (
        SELECT geography
        FROM geographic_items
        WHERE id = #{geographic_item_id.to_i}
      ) AS target
      -- Careful with IN here if we move to other data_origins (currently ~4k ids)
      WHERE gi.id IN (#{precomputed_ids})
        AND ST_Intersects(gi.geography, target.geography)
    SQL

    a = ActiveRecord::Base.connection.select_values(sql).map!(&:to_i)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    buffer_filter = GeographicItem
      .st_buffer_st_within_sql(geographic_item_id, 0.0, buffer)
      .or(
        # The intention here is to keep ne_states shapes that failed the buffer
        # check because the buffer reduced them to nothing, IF they're subsets
        # of geographic_item_id. This should prevent unexpected holes.
        GeographicItem.subset_of_sql(
          GeographicItem.st_buffer_sql(
            GeographicItem.select_geometry_sql(geographic_item_id),
            100
          )
        )
      )

    return GeographicItem
      .where(id: a)
      .where(buffer_filter)
      .pluck(:id)
  end

  def self.precomputed_data_origin_ids_for(data_origin, refresh: false)
    return nil unless data_origin == 'ne_states' # currently only ne_states supported

    @precomputed_data_origin_ids ||= {}
    if refresh || @precomputed_data_origin_ids[data_origin].blank?
      @precomputed_data_origin_ids[data_origin] = GeographicAreasGeographicItem
        .where(data_origin:)
        .distinct
        .pluck(:geographic_item_id)
        .join(',')
        .freeze
    end

    @precomputed_data_origin_ids[data_origin]
  end

  # @return [Array]
  # @param geographic_area_based [Boolean]
  #   true if geographic_item_id is the geographic_item id of some geographic
  #   area (and so has a chance of already being associated with a cached map
  #   item/translation)
  #
  # @param search_existing_translates [Boolean]
  #   true if existing cached_map_item_translations should be checked for a
  #   match.
  #
  # @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
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3")
  #
  def self.translate_geographic_item_id(
    geographic_item_id, geographic_area_based,
    search_existing_translates = true, data_origin = nil, buffer = nil,
    precomputed_data_origin_ids: nil
  )
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    if search_existing_translates
      a = translate_by_geographic_item_translation(
        geographic_item_id, cached_map_type
      )
      return a if a.present?
    end

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if geographic_area_based
      a = translate_by_data_origin(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?

      # TODO should Gazetteer GIs be getting a dynamic buffer? They don't
      # currently.
      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(
      geographic_item_id, data_origin, b, precomputed_data_origin_ids:
    )
  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? || v.to_i == 0
    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

  # @param context [Hash] of cached_map_types to pre-computed data for
  # source_object.
  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type, context: nil)
    # 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
    otu_id = nil
    context = context&.symbolize_keys || {}

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      if o.is_absent == true
        return h
      end
      if o.asserted_distribution_object_type == 'Otu'
        otu_id = [context[:otu_id] || o.asserted_distribution_object_id]
      else
        # TODO handle other types
        return h
      end
      geographic_item_id = context[:geographic_item_id] ||
        o.asserted_distribution_shape.default_geographic_item_id
    when 'Georeference'
      geographic_item_id = context[:geographic_item_id] || o.geographic_item_id
      otu_id = context[:otu_id] ||
        o.otus.left_joins(:taxon_determinations).where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    otu = nil
    taxon_name_id = context[:otu_taxon_name_id] ||
      Otu.where(id: otu_id).pick(:taxon_name_id)
    return h if otu_id.nil? || taxon_name_id.nil?
    # otus without taxon name have no hierarchy, so don't contribute to cached
    # maps.
    return h if taxon_name_id.blank?

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

      geographic_area_based = context.key?(:geographic_area_based) ?
        context[:geographic_area_based] :
        base_class_name == 'AssertedDistribution' &&
          o.asserted_distribution_shape_type == 'GeographicArea'
      search_existing_translates = base_class_name == 'AssertedDistribution'

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id, geographic_area_based, search_existing_translates,
        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. !!



40
41
42
43
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
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'app/models/cached_map_item.rb', line 40

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

  # Given a set of target shapes (via data_origin), return those that intersect
  # with the provided geographic_item_id 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)
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3") [an array would be better,
  #   but this saves 200k array joins on length-4k arrays during batch create]
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(
    geographic_item_id, data_origin, buffer, precomputed_data_origin_ids: nil
  )
    return [] if geographic_item_id.blank?
    data_origin = Array(data_origin).compact
    return [] if data_origin.empty?

    precomputed_ids = nil
    if data_origin.length == 1
      if precomputed_data_origin_ids.present?
        precomputed_ids = precomputed_data_origin_ids[data_origin.first]
      else
        precomputed_ids = precomputed_data_origin_ids_for(data_origin.first)
      end
    end

    raise TaxonWorks::Error, "Missing pre-computed ids for cached maps origin '#{data_origin}'" if precomputed_ids.blank?
    raise TaxonWorks::Error, "Expected pre-computed ids for cached maps origin '#{data_origin}' to be a SQL IN list" unless precomputed_ids.is_a?(String)

    sql = <<~SQL
      SELECT gi.id
      FROM geographic_items gi
      CROSS JOIN (
        SELECT geography
        FROM geographic_items
        WHERE id = #{geographic_item_id.to_i}
      ) AS target
      -- Careful with IN here if we move to other data_origins (currently ~4k ids)
      WHERE gi.id IN (#{precomputed_ids})
        AND ST_Intersects(gi.geography, target.geography)
    SQL

    a = ActiveRecord::Base.connection.select_values(sql).map!(&:to_i)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    buffer_filter = GeographicItem
      .st_buffer_st_within_sql(geographic_item_id, 0.0, buffer)
      .or(
        # The intention here is to keep ne_states shapes that failed the buffer
        # check because the buffer reduced them to nothing, IF they're subsets
        # of geographic_item_id. This should prevent unexpected holes.
        GeographicItem.subset_of_sql(
          GeographicItem.st_buffer_sql(
            GeographicItem.select_geometry_sql(geographic_item_id),
            100
          )
        )
      )

    return GeographicItem
      .where(id: a)
      .where(buffer_filter)
      .pluck(:id)
  end

  def self.precomputed_data_origin_ids_for(data_origin, refresh: false)
    return nil unless data_origin == 'ne_states' # currently only ne_states supported

    @precomputed_data_origin_ids ||= {}
    if refresh || @precomputed_data_origin_ids[data_origin].blank?
      @precomputed_data_origin_ids[data_origin] = GeographicAreasGeographicItem
        .where(data_origin:)
        .distinct
        .pluck(:geographic_item_id)
        .join(',')
        .freeze
    end

    @precomputed_data_origin_ids[data_origin]
  end

  # @return [Array]
  # @param geographic_area_based [Boolean]
  #   true if geographic_item_id is the geographic_item id of some geographic
  #   area (and so has a chance of already being associated with a cached map
  #   item/translation)
  #
  # @param search_existing_translates [Boolean]
  #   true if existing cached_map_item_translations should be checked for a
  #   match.
  #
  # @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
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3")
  #
  def self.translate_geographic_item_id(
    geographic_item_id, geographic_area_based,
    search_existing_translates = true, data_origin = nil, buffer = nil,
    precomputed_data_origin_ids: nil
  )
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    if search_existing_translates
      a = translate_by_geographic_item_translation(
        geographic_item_id, cached_map_type
      )
      return a if a.present?
    end

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if geographic_area_based
      a = translate_by_data_origin(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?

      # TODO should Gazetteer GIs be getting a dynamic buffer? They don't
      # currently.
      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(
      geographic_item_id, data_origin, b, precomputed_data_origin_ids:
    )
  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? || v.to_i == 0
    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

  # @param context [Hash] of cached_map_types to pre-computed data for
  # source_object.
  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type, context: nil)
    # 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
    otu_id = nil
    context = context&.symbolize_keys || {}

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      if o.is_absent == true
        return h
      end
      if o.asserted_distribution_object_type == 'Otu'
        otu_id = [context[:otu_id] || o.asserted_distribution_object_id]
      else
        # TODO handle other types
        return h
      end
      geographic_item_id = context[:geographic_item_id] ||
        o.asserted_distribution_shape.default_geographic_item_id
    when 'Georeference'
      geographic_item_id = context[:geographic_item_id] || o.geographic_item_id
      otu_id = context[:otu_id] ||
        o.otus.left_joins(:taxon_determinations).where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    otu = nil
    taxon_name_id = context[:otu_taxon_name_id] ||
      Otu.where(id: otu_id).pick(:taxon_name_id)
    return h if otu_id.nil? || taxon_name_id.nil?
    # otus without taxon name have no hierarchy, so don't contribute to cached
    # maps.
    return h if taxon_name_id.blank?

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

      geographic_area_based = context.key?(:geographic_area_based) ?
        context[:geographic_area_based] :
        base_class_name == 'AssertedDistribution' &&
          o.asserted_distribution_shape_type == 'GeographicArea'
      search_existing_translates = base_class_name == 'AssertedDistribution'

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id, geographic_area_based, search_existing_translates,
        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



40
41
42
43
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
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'app/models/cached_map_item.rb', line 40

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

  # Given a set of target shapes (via data_origin), return those that intersect
  # with the provided geographic_item_id 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)
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3") [an array would be better,
  #   but this saves 200k array joins on length-4k arrays during batch create]
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(
    geographic_item_id, data_origin, buffer, precomputed_data_origin_ids: nil
  )
    return [] if geographic_item_id.blank?
    data_origin = Array(data_origin).compact
    return [] if data_origin.empty?

    precomputed_ids = nil
    if data_origin.length == 1
      if precomputed_data_origin_ids.present?
        precomputed_ids = precomputed_data_origin_ids[data_origin.first]
      else
        precomputed_ids = precomputed_data_origin_ids_for(data_origin.first)
      end
    end

    raise TaxonWorks::Error, "Missing pre-computed ids for cached maps origin '#{data_origin}'" if precomputed_ids.blank?
    raise TaxonWorks::Error, "Expected pre-computed ids for cached maps origin '#{data_origin}' to be a SQL IN list" unless precomputed_ids.is_a?(String)

    sql = <<~SQL
      SELECT gi.id
      FROM geographic_items gi
      CROSS JOIN (
        SELECT geography
        FROM geographic_items
        WHERE id = #{geographic_item_id.to_i}
      ) AS target
      -- Careful with IN here if we move to other data_origins (currently ~4k ids)
      WHERE gi.id IN (#{precomputed_ids})
        AND ST_Intersects(gi.geography, target.geography)
    SQL

    a = ActiveRecord::Base.connection.select_values(sql).map!(&:to_i)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    buffer_filter = GeographicItem
      .st_buffer_st_within_sql(geographic_item_id, 0.0, buffer)
      .or(
        # The intention here is to keep ne_states shapes that failed the buffer
        # check because the buffer reduced them to nothing, IF they're subsets
        # of geographic_item_id. This should prevent unexpected holes.
        GeographicItem.subset_of_sql(
          GeographicItem.st_buffer_sql(
            GeographicItem.select_geometry_sql(geographic_item_id),
            100
          )
        )
      )

    return GeographicItem
      .where(id: a)
      .where(buffer_filter)
      .pluck(:id)
  end

  def self.precomputed_data_origin_ids_for(data_origin, refresh: false)
    return nil unless data_origin == 'ne_states' # currently only ne_states supported

    @precomputed_data_origin_ids ||= {}
    if refresh || @precomputed_data_origin_ids[data_origin].blank?
      @precomputed_data_origin_ids[data_origin] = GeographicAreasGeographicItem
        .where(data_origin:)
        .distinct
        .pluck(:geographic_item_id)
        .join(',')
        .freeze
    end

    @precomputed_data_origin_ids[data_origin]
  end

  # @return [Array]
  # @param geographic_area_based [Boolean]
  #   true if geographic_item_id is the geographic_item id of some geographic
  #   area (and so has a chance of already being associated with a cached map
  #   item/translation)
  #
  # @param search_existing_translates [Boolean]
  #   true if existing cached_map_item_translations should be checked for a
  #   match.
  #
  # @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
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3")
  #
  def self.translate_geographic_item_id(
    geographic_item_id, geographic_area_based,
    search_existing_translates = true, data_origin = nil, buffer = nil,
    precomputed_data_origin_ids: nil
  )
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    if search_existing_translates
      a = translate_by_geographic_item_translation(
        geographic_item_id, cached_map_type
      )
      return a if a.present?
    end

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if geographic_area_based
      a = translate_by_data_origin(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?

      # TODO should Gazetteer GIs be getting a dynamic buffer? They don't
      # currently.
      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(
      geographic_item_id, data_origin, b, precomputed_data_origin_ids:
    )
  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? || v.to_i == 0
    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

  # @param context [Hash] of cached_map_types to pre-computed data for
  # source_object.
  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type, context: nil)
    # 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
    otu_id = nil
    context = context&.symbolize_keys || {}

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      if o.is_absent == true
        return h
      end
      if o.asserted_distribution_object_type == 'Otu'
        otu_id = [context[:otu_id] || o.asserted_distribution_object_id]
      else
        # TODO handle other types
        return h
      end
      geographic_item_id = context[:geographic_item_id] ||
        o.asserted_distribution_shape.default_geographic_item_id
    when 'Georeference'
      geographic_item_id = context[:geographic_item_id] || o.geographic_item_id
      otu_id = context[:otu_id] ||
        o.otus.left_joins(:taxon_determinations).where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    otu = nil
    taxon_name_id = context[:otu_taxon_name_id] ||
      Otu.where(id: otu_id).pick(:taxon_name_id)
    return h if otu_id.nil? || taxon_name_id.nil?
    # otus without taxon name have no hierarchy, so don't contribute to cached
    # maps.
    return h if taxon_name_id.blank?

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

      geographic_area_based = context.key?(:geographic_area_based) ?
        context[:geographic_area_based] :
        base_class_name == 'AssertedDistribution' &&
          o.asserted_distribution_shape_type == 'GeographicArea'
      search_existing_translates = base_class_name == 'AssertedDistribution'

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id, geographic_area_based, search_existing_translates,
        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



40
41
42
43
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
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'app/models/cached_map_item.rb', line 40

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

  # Given a set of target shapes (via data_origin), return those that intersect
  # with the provided geographic_item_id 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)
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3") [an array would be better,
  #   but this saves 200k array joins on length-4k arrays during batch create]
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(
    geographic_item_id, data_origin, buffer, precomputed_data_origin_ids: nil
  )
    return [] if geographic_item_id.blank?
    data_origin = Array(data_origin).compact
    return [] if data_origin.empty?

    precomputed_ids = nil
    if data_origin.length == 1
      if precomputed_data_origin_ids.present?
        precomputed_ids = precomputed_data_origin_ids[data_origin.first]
      else
        precomputed_ids = precomputed_data_origin_ids_for(data_origin.first)
      end
    end

    raise TaxonWorks::Error, "Missing pre-computed ids for cached maps origin '#{data_origin}'" if precomputed_ids.blank?
    raise TaxonWorks::Error, "Expected pre-computed ids for cached maps origin '#{data_origin}' to be a SQL IN list" unless precomputed_ids.is_a?(String)

    sql = <<~SQL
      SELECT gi.id
      FROM geographic_items gi
      CROSS JOIN (
        SELECT geography
        FROM geographic_items
        WHERE id = #{geographic_item_id.to_i}
      ) AS target
      -- Careful with IN here if we move to other data_origins (currently ~4k ids)
      WHERE gi.id IN (#{precomputed_ids})
        AND ST_Intersects(gi.geography, target.geography)
    SQL

    a = ActiveRecord::Base.connection.select_values(sql).map!(&:to_i)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    buffer_filter = GeographicItem
      .st_buffer_st_within_sql(geographic_item_id, 0.0, buffer)
      .or(
        # The intention here is to keep ne_states shapes that failed the buffer
        # check because the buffer reduced them to nothing, IF they're subsets
        # of geographic_item_id. This should prevent unexpected holes.
        GeographicItem.subset_of_sql(
          GeographicItem.st_buffer_sql(
            GeographicItem.select_geometry_sql(geographic_item_id),
            100
          )
        )
      )

    return GeographicItem
      .where(id: a)
      .where(buffer_filter)
      .pluck(:id)
  end

  def self.precomputed_data_origin_ids_for(data_origin, refresh: false)
    return nil unless data_origin == 'ne_states' # currently only ne_states supported

    @precomputed_data_origin_ids ||= {}
    if refresh || @precomputed_data_origin_ids[data_origin].blank?
      @precomputed_data_origin_ids[data_origin] = GeographicAreasGeographicItem
        .where(data_origin:)
        .distinct
        .pluck(:geographic_item_id)
        .join(',')
        .freeze
    end

    @precomputed_data_origin_ids[data_origin]
  end

  # @return [Array]
  # @param geographic_area_based [Boolean]
  #   true if geographic_item_id is the geographic_item id of some geographic
  #   area (and so has a chance of already being associated with a cached map
  #   item/translation)
  #
  # @param search_existing_translates [Boolean]
  #   true if existing cached_map_item_translations should be checked for a
  #   match.
  #
  # @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
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3")
  #
  def self.translate_geographic_item_id(
    geographic_item_id, geographic_area_based,
    search_existing_translates = true, data_origin = nil, buffer = nil,
    precomputed_data_origin_ids: nil
  )
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    if search_existing_translates
      a = translate_by_geographic_item_translation(
        geographic_item_id, cached_map_type
      )
      return a if a.present?
    end

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if geographic_area_based
      a = translate_by_data_origin(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?

      # TODO should Gazetteer GIs be getting a dynamic buffer? They don't
      # currently.
      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(
      geographic_item_id, data_origin, b, precomputed_data_origin_ids:
    )
  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? || v.to_i == 0
    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

  # @param context [Hash] of cached_map_types to pre-computed data for
  # source_object.
  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type, context: nil)
    # 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
    otu_id = nil
    context = context&.symbolize_keys || {}

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      if o.is_absent == true
        return h
      end
      if o.asserted_distribution_object_type == 'Otu'
        otu_id = [context[:otu_id] || o.asserted_distribution_object_id]
      else
        # TODO handle other types
        return h
      end
      geographic_item_id = context[:geographic_item_id] ||
        o.asserted_distribution_shape.default_geographic_item_id
    when 'Georeference'
      geographic_item_id = context[:geographic_item_id] || o.geographic_item_id
      otu_id = context[:otu_id] ||
        o.otus.left_joins(:taxon_determinations).where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    otu = nil
    taxon_name_id = context[:otu_taxon_name_id] ||
      Otu.where(id: otu_id).pick(:taxon_name_id)
    return h if otu_id.nil? || taxon_name_id.nil?
    # otus without taxon name have no hierarchy, so don't contribute to cached
    # maps.
    return h if taxon_name_id.blank?

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

      geographic_area_based = context.key?(:geographic_area_based) ?
        context[:geographic_area_based] :
        base_class_name == 'AssertedDistribution' &&
          o.asserted_distribution_shape_type == 'GeographicArea'
      search_existing_translates = base_class_name == 'AssertedDistribution'

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id, geographic_area_based, search_existing_translates,
        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



40
41
42
43
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
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'app/models/cached_map_item.rb', line 40

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

  # Given a set of target shapes (via data_origin), return those that intersect
  # with the provided geographic_item_id 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)
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3") [an array would be better,
  #   but this saves 200k array joins on length-4k arrays during batch create]
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(
    geographic_item_id, data_origin, buffer, precomputed_data_origin_ids: nil
  )
    return [] if geographic_item_id.blank?
    data_origin = Array(data_origin).compact
    return [] if data_origin.empty?

    precomputed_ids = nil
    if data_origin.length == 1
      if precomputed_data_origin_ids.present?
        precomputed_ids = precomputed_data_origin_ids[data_origin.first]
      else
        precomputed_ids = precomputed_data_origin_ids_for(data_origin.first)
      end
    end

    raise TaxonWorks::Error, "Missing pre-computed ids for cached maps origin '#{data_origin}'" if precomputed_ids.blank?
    raise TaxonWorks::Error, "Expected pre-computed ids for cached maps origin '#{data_origin}' to be a SQL IN list" unless precomputed_ids.is_a?(String)

    sql = <<~SQL
      SELECT gi.id
      FROM geographic_items gi
      CROSS JOIN (
        SELECT geography
        FROM geographic_items
        WHERE id = #{geographic_item_id.to_i}
      ) AS target
      -- Careful with IN here if we move to other data_origins (currently ~4k ids)
      WHERE gi.id IN (#{precomputed_ids})
        AND ST_Intersects(gi.geography, target.geography)
    SQL

    a = ActiveRecord::Base.connection.select_values(sql).map!(&:to_i)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    buffer_filter = GeographicItem
      .st_buffer_st_within_sql(geographic_item_id, 0.0, buffer)
      .or(
        # The intention here is to keep ne_states shapes that failed the buffer
        # check because the buffer reduced them to nothing, IF they're subsets
        # of geographic_item_id. This should prevent unexpected holes.
        GeographicItem.subset_of_sql(
          GeographicItem.st_buffer_sql(
            GeographicItem.select_geometry_sql(geographic_item_id),
            100
          )
        )
      )

    return GeographicItem
      .where(id: a)
      .where(buffer_filter)
      .pluck(:id)
  end

  def self.precomputed_data_origin_ids_for(data_origin, refresh: false)
    return nil unless data_origin == 'ne_states' # currently only ne_states supported

    @precomputed_data_origin_ids ||= {}
    if refresh || @precomputed_data_origin_ids[data_origin].blank?
      @precomputed_data_origin_ids[data_origin] = GeographicAreasGeographicItem
        .where(data_origin:)
        .distinct
        .pluck(:geographic_item_id)
        .join(',')
        .freeze
    end

    @precomputed_data_origin_ids[data_origin]
  end

  # @return [Array]
  # @param geographic_area_based [Boolean]
  #   true if geographic_item_id is the geographic_item id of some geographic
  #   area (and so has a chance of already being associated with a cached map
  #   item/translation)
  #
  # @param search_existing_translates [Boolean]
  #   true if existing cached_map_item_translations should be checked for a
  #   match.
  #
  # @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
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3")
  #
  def self.translate_geographic_item_id(
    geographic_item_id, geographic_area_based,
    search_existing_translates = true, data_origin = nil, buffer = nil,
    precomputed_data_origin_ids: nil
  )
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    if search_existing_translates
      a = translate_by_geographic_item_translation(
        geographic_item_id, cached_map_type
      )
      return a if a.present?
    end

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if geographic_area_based
      a = translate_by_data_origin(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?

      # TODO should Gazetteer GIs be getting a dynamic buffer? They don't
      # currently.
      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(
      geographic_item_id, data_origin, b, precomputed_data_origin_ids:
    )
  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? || v.to_i == 0
    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

  # @param context [Hash] of cached_map_types to pre-computed data for
  # source_object.
  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type, context: nil)
    # 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
    otu_id = nil
    context = context&.symbolize_keys || {}

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      if o.is_absent == true
        return h
      end
      if o.asserted_distribution_object_type == 'Otu'
        otu_id = [context[:otu_id] || o.asserted_distribution_object_id]
      else
        # TODO handle other types
        return h
      end
      geographic_item_id = context[:geographic_item_id] ||
        o.asserted_distribution_shape.default_geographic_item_id
    when 'Georeference'
      geographic_item_id = context[:geographic_item_id] || o.geographic_item_id
      otu_id = context[:otu_id] ||
        o.otus.left_joins(:taxon_determinations).where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    otu = nil
    taxon_name_id = context[:otu_taxon_name_id] ||
      Otu.where(id: otu_id).pick(:taxon_name_id)
    return h if otu_id.nil? || taxon_name_id.nil?
    # otus without taxon name have no hierarchy, so don't contribute to cached
    # maps.
    return h if taxon_name_id.blank?

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

      geographic_area_based = context.key?(:geographic_area_based) ?
        context[:geographic_area_based] :
        base_class_name == 'AssertedDistribution' &&
          o.asserted_distribution_shape_type == 'GeographicArea'
      search_existing_translates = base_class_name == 'AssertedDistribution'

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id, geographic_area_based, search_existing_translates,
        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



40
41
42
43
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
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'app/models/cached_map_item.rb', line 40

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

  # Given a set of target shapes (via data_origin), return those that intersect
  # with the provided geographic_item_id 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)
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3") [an array would be better,
  #   but this saves 200k array joins on length-4k arrays during batch create]
  #
  # @return [Array] of GeographicItem ids
  #
  def self.translate_by_spatial_overlap(
    geographic_item_id, data_origin, buffer, precomputed_data_origin_ids: nil
  )
    return [] if geographic_item_id.blank?
    data_origin = Array(data_origin).compact
    return [] if data_origin.empty?

    precomputed_ids = nil
    if data_origin.length == 1
      if precomputed_data_origin_ids.present?
        precomputed_ids = precomputed_data_origin_ids[data_origin.first]
      else
        precomputed_ids = precomputed_data_origin_ids_for(data_origin.first)
      end
    end

    raise TaxonWorks::Error, "Missing pre-computed ids for cached maps origin '#{data_origin}'" if precomputed_ids.blank?
    raise TaxonWorks::Error, "Expected pre-computed ids for cached maps origin '#{data_origin}' to be a SQL IN list" unless precomputed_ids.is_a?(String)

    sql = <<~SQL
      SELECT gi.id
      FROM geographic_items gi
      CROSS JOIN (
        SELECT geography
        FROM geographic_items
        WHERE id = #{geographic_item_id.to_i}
      ) AS target
      -- Careful with IN here if we move to other data_origins (currently ~4k ids)
      WHERE gi.id IN (#{precomputed_ids})
        AND ST_Intersects(gi.geography, target.geography)
    SQL

    a = ActiveRecord::Base.connection.select_values(sql).map!(&:to_i)

    return a if buffer.nil?

    # Refine the pass by smoothing using buffer/st_within
    buffer_filter = GeographicItem
      .st_buffer_st_within_sql(geographic_item_id, 0.0, buffer)
      .or(
        # The intention here is to keep ne_states shapes that failed the buffer
        # check because the buffer reduced them to nothing, IF they're subsets
        # of geographic_item_id. This should prevent unexpected holes.
        GeographicItem.subset_of_sql(
          GeographicItem.st_buffer_sql(
            GeographicItem.select_geometry_sql(geographic_item_id),
            100
          )
        )
      )

    return GeographicItem
      .where(id: a)
      .where(buffer_filter)
      .pluck(:id)
  end

  def self.precomputed_data_origin_ids_for(data_origin, refresh: false)
    return nil unless data_origin == 'ne_states' # currently only ne_states supported

    @precomputed_data_origin_ids ||= {}
    if refresh || @precomputed_data_origin_ids[data_origin].blank?
      @precomputed_data_origin_ids[data_origin] = GeographicAreasGeographicItem
        .where(data_origin:)
        .distinct
        .pluck(:geographic_item_id)
        .join(',')
        .freeze
    end

    @precomputed_data_origin_ids[data_origin]
  end

  # @return [Array]
  # @param geographic_area_based [Boolean]
  #   true if geographic_item_id is the geographic_item id of some geographic
  #   area (and so has a chance of already being associated with a cached map
  #   item/translation)
  #
  # @param search_existing_translates [Boolean]
  #   true if existing cached_map_item_translations should be checked for a
  #   match.
  #
  # @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
  #
  # @param precomputed_data_origin_ids [Hash] Optional hash of data_origin =>
  #   SQL IN list String of all geographic_item ids corresponding to Geographic
  #   Areas of data origin data_origin (e.g. "1,2,3")
  #
  def self.translate_geographic_item_id(
    geographic_item_id, geographic_area_based,
    search_existing_translates = true, data_origin = nil, buffer = nil,
    precomputed_data_origin_ids: nil
  )
    return nil if data_origin.blank?

    cached_map_type = types_by_data_origin(data_origin)

    a = nil

    b = buffer

    if search_existing_translates
      a = translate_by_geographic_item_translation(
        geographic_item_id, cached_map_type
      )
      return a if a.present?
    end

    # All these methods depend on "prior knowledge" (not spatial calculations)
    if geographic_area_based
      a = translate_by_data_origin(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?

      # TODO should Gazetteer GIs be getting a dynamic buffer? They don't
      # currently.
      b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
    end

    translate_by_spatial_overlap(
      geographic_item_id, data_origin, b, precomputed_data_origin_ids:
    )
  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? || v.to_i == 0
    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

  # @param context [Hash] of cached_map_types to pre-computed data for
  # source_object.
  # @return [Hash, nil]
  def self.stubs(source_object, cached_map_type, context: nil)
    # 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
    otu_id = nil
    context = context&.symbolize_keys || {}

    base_class_name = o.class.base_class.name

    case base_class_name
    when 'AssertedDistribution'
      if o.is_absent == true
        return h
      end
      if o.asserted_distribution_object_type == 'Otu'
        otu_id = [context[:otu_id] || o.asserted_distribution_object_id]
      else
        # TODO handle other types
        return h
      end
      geographic_item_id = context[:geographic_item_id] ||
        o.asserted_distribution_shape.default_geographic_item_id
    when 'Georeference'
      geographic_item_id = context[:geographic_item_id] || o.geographic_item_id
      otu_id = context[:otu_id] ||
        o.otus.left_joins(:taxon_determinations).where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
    end

    otu = nil
    taxon_name_id = context[:otu_taxon_name_id] ||
      Otu.where(id: otu_id).pick(:taxon_name_id)
    return h if otu_id.nil? || taxon_name_id.nil?
    # otus without taxon name have no hierarchy, so don't contribute to cached
    # maps.
    return h if taxon_name_id.blank?

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

      geographic_area_based = context.key?(:geographic_area_based) ?
        context[:geographic_area_based] :
        base_class_name == 'AssertedDistribution' &&
          o.asserted_distribution_shape_type == 'GeographicArea'
      search_existing_translates = base_class_name == 'AssertedDistribution'

      h[:geographic_item_id] = translate_geographic_item_id(
        geographic_item_id, geographic_area_based, search_existing_translates,
        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] ],




387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
 
# File 'app/models/cached_map_item.rb', line 387

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 



69
70
71
72
73
74
75
76
77
78
 
# File 'app/models/cached_map_item.rb', line 69

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



56
57
58
59
60
61
62
63
64
65
66
67
 
# File 'app/models/cached_map_item.rb', line 56

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 



280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
 
# File 'app/models/cached_map_item.rb', line 280

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? || v.to_i == 0
  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

.precomputed_data_origin_ids_for(data_origin, refresh: false) ⇒ Object 



205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
 
# File 'app/models/cached_map_item.rb', line 205

def self.precomputed_data_origin_ids_for(data_origin, refresh: false)
  return nil unless data_origin == 'ne_states' # currently only ne_states supported

  @precomputed_data_origin_ids ||= {}
  if refresh || @precomputed_data_origin_ids[data_origin].blank?
    @precomputed_data_origin_ids[data_origin] = GeographicAreasGeographicItem
      .where(data_origin:)
      .distinct
      .pluck(:geographic_item_id)
      .join(',')
      .freeze
  end

  @precomputed_data_origin_ids[data_origin]
end

.stubs(source_object, cached_map_type, context: nil) ⇒ Hash?

source_object.

Parameters:

  • context (Hash) (defaults to: nil) —

    of cached_map_types to pre-computed data for

Returns:

  • (Hash, nil) 


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
364
365
366
367
368
369
370
371
372
 
# File 'app/models/cached_map_item.rb', line 304

def self.stubs(source_object, cached_map_type, context: nil)
  # 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
  otu_id = nil
  context = context&.symbolize_keys || {}

  base_class_name = o.class.base_class.name

  case base_class_name
  when 'AssertedDistribution'
    if o.is_absent == true
      return h
    end
    if o.asserted_distribution_object_type == 'Otu'
      otu_id = [context[:otu_id] || o.asserted_distribution_object_id]
    else
      # TODO handle other types
      return h
    end
    geographic_item_id = context[:geographic_item_id] ||
      o.asserted_distribution_shape.default_geographic_item_id
  when 'Georeference'
    geographic_item_id = context[:geographic_item_id] || o.geographic_item_id
    otu_id = context[:otu_id] ||
      o.otus.left_joins(:taxon_determinations).where(taxon_determinations: { position: 1 }).distinct.pluck(:id)
  end

  otu = nil
  taxon_name_id = context[:otu_taxon_name_id] ||
    Otu.where(id: otu_id).pick(:taxon_name_id)
  return h if otu_id.nil? || taxon_name_id.nil?
  # otus without taxon name have no hierarchy, so don't contribute to cached
  # maps.
  return h if taxon_name_id.blank?

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

    geographic_area_based = context.key?(:geographic_area_based) ?
      context[:geographic_area_based] :
      base_class_name == 'AssertedDistribution' &&
        o.asserted_distribution_shape_type == 'GeographicArea'
    search_existing_translates = base_class_name == 'AssertedDistribution'

    h[:geographic_item_id] = translate_geographic_item_id(
      geographic_item_id, geographic_area_based, search_existing_translates,
      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_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) 


121
122
123
124
125
126
127
 
# File 'app/models/cached_map_item.rb', line 121

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



107
108
109
110
111
112
113
114
115
116
 
# File 'app/models/cached_map_item.rb', line 107

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



95
96
97
98
99
100
101
102
103
 
# File 'app/models/cached_map_item.rb', line 95

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, precomputed_data_origin_ids: nil) ⇒ Array

Given a set of target shapes (via data_origin), return those that intersect with the provided geographic_item_id 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

  • precomputed_data_origin_ids (Hash) (defaults to: nil) —

    Optional hash of data_origin => SQL IN list String of all geographic_item ids corresponding to Geographic Areas of data origin data_origin (e.g. “1,2,3”) [an array would be better, but this saves 200k array joins on length-4k arrays during batch create]

Returns:

  • (Array) —

    of GeographicItem ids

Raises:

  • (TaxonWorks::Error) 


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
 
# File 'app/models/cached_map_item.rb', line 148

def self.translate_by_spatial_overlap(
  geographic_item_id, data_origin, buffer, precomputed_data_origin_ids: nil
)
  return [] if geographic_item_id.blank?
  data_origin = Array(data_origin).compact
  return [] if data_origin.empty?

  precomputed_ids = nil
  if data_origin.length == 1
    if precomputed_data_origin_ids.present?
      precomputed_ids = precomputed_data_origin_ids[data_origin.first]
    else
      precomputed_ids = precomputed_data_origin_ids_for(data_origin.first)
    end
  end

  raise TaxonWorks::Error, "Missing pre-computed ids for cached maps origin '#{data_origin}'" if precomputed_ids.blank?
  raise TaxonWorks::Error, "Expected pre-computed ids for cached maps origin '#{data_origin}' to be a SQL IN list" unless precomputed_ids.is_a?(String)

  sql = <<~SQL
    SELECT gi.id
    FROM geographic_items gi
    CROSS JOIN (
      SELECT geography
      FROM geographic_items
      WHERE id = #{geographic_item_id.to_i}
    ) AS target
    -- Careful with IN here if we move to other data_origins (currently ~4k ids)
    WHERE gi.id IN (#{precomputed_ids})
      AND ST_Intersects(gi.geography, target.geography)
  SQL

  a = ActiveRecord::Base.connection.select_values(sql).map!(&:to_i)

  return a if buffer.nil?

  # Refine the pass by smoothing using buffer/st_within
  buffer_filter = GeographicItem
    .st_buffer_st_within_sql(geographic_item_id, 0.0, buffer)
    .or(
      # The intention here is to keep ne_states shapes that failed the buffer
      # check because the buffer reduced them to nothing, IF they're subsets
      # of geographic_item_id. This should prevent unexpected holes.
      GeographicItem.subset_of_sql(
        GeographicItem.st_buffer_sql(
          GeographicItem.select_geometry_sql(geographic_item_id),
          100
        )
      )
    )

  return GeographicItem
    .where(id: a)
    .where(buffer_filter)
    .pluck(:id)
end

.translate_geographic_item_id(geographic_item_id, geographic_area_based, search_existing_translates = true, data_origin = nil, buffer = nil, precomputed_data_origin_ids: nil) ⇒ Array

Parameters:

  • geographic_area_based (Boolean) —

    true if geographic_item_id is the geographic_item id of some geographic area (and so has a chance of already being associated with a cached map item/translation)

  • search_existing_translates (Boolean) (defaults to: true) —

    true if existing cached_map_item_translations should be checked for a match.

  • 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

  • precomputed_data_origin_ids (Hash) (defaults to: nil) —

    Optional hash of data_origin => SQL IN list String of all geographic_item ids corresponding to Geographic Areas of data origin data_origin (e.g. “1,2,3”)

Returns:

  • (Array) 


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
 
# File 'app/models/cached_map_item.rb', line 242

def self.translate_geographic_item_id(
  geographic_item_id, geographic_area_based,
  search_existing_translates = true, data_origin = nil, buffer = nil,
  precomputed_data_origin_ids: nil
)
  return nil if data_origin.blank?

  cached_map_type = types_by_data_origin(data_origin)

  a = nil

  b = buffer

  if search_existing_translates
    a = translate_by_geographic_item_translation(
      geographic_item_id, cached_map_type
    )
    return a if a.present?
  end

  # All these methods depend on "prior knowledge" (not spatial calculations)
  if geographic_area_based
    a = translate_by_data_origin(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?

    # TODO should Gazetteer GIs be getting a dynamic buffer? They don't
    # currently.
    b = dynamic_buffer(geographic_item_id) # -1000.0 # Monaco
  end

  translate_by_spatial_overlap(
    geographic_item_id, data_origin, b, precomputed_data_origin_ids:
  )
end

.types_by_data_origin(data_origin = []) ⇒ Object

Returns Array.

Returns:

  • Array



81
82
83
84
85
86
87
88
89
90
91
 
# File 'app/models/cached_map_item.rb', line 81

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