Class: Map

Inherits:
ActiveRecord::Base
  • Object
show all
Defined in:
app/models/map.rb

Instance Method Summary collapse

Instance Method Details

#construct_boundaries_for_nhgis_terrapop_sample(sgl) ⇒ Object 



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

def construct_boundaries_for_nhgis_terrapop_sample(sgl)

  #select_stmt provides the values to be inserted into boundaries
  select_stmt = "SELECT #{self.id} AS map_id,
                        gi.id      AS geog_instance_id,
                        gi.code    AS code,
                        gi.label   AS description,
                        shape.geog AS geog,
                        shape.geom AS geom
                  FROM #{self.shape_table_name} shape
                  INNER JOIN geog_instances gi ON gi.sample_geog_level_id = #{sgl.id} AND gi.code = #{self.gisjoin_as_numeric_clause}
                  WHERE shape.gisjoin <> ''"

  #prepare the insert statement using the select statement above
  insert_stmt = "INSERT INTO boundaries(map_id, geog_instance_id, code, description, geog, geom)
                  #{select_stmt}"

  #execute the insert statement and catch any errors presenting them to the user.  NOTE: duplicates are not checked and will be added when run twice.
  begin
    $stderr.puts "inserting boundaries...".color(:magenta)
    puts insert_stmt
    #p ActiveRecord::Base.connection.exec_query(select_stmt)            #slow: the selects can take a while with the geometry and all...
    records = ActiveRecord::Base.connection.insert(insert_stmt)
  rescue Exception => e
    $stderr.puts e.message.color(:red)
  end

end

#construct_boundaries_for_terrapop_sample(tps, code_column = 'ipums_code') ⇒ Object 



111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
 
# File 'app/models/map.rb', line 111

def construct_boundaries_for_terrapop_sample(tps, code_column = 'ipums_code')
  my_sgl = tps.sample_geog_level_for_country_level(self.country_level)
  unless my_sgl.nil?

    boundaries_count = Boundary.where({map_id: self.id}).count

    if boundaries_count > 0
      $stderr.puts " Found Boundary[map_id: #{self.id}]"
      #Boundary.where({map_id: self.id}).destroy_all
    else
      insert_stmt =
        "insert into boundaries(map_id, geog_instance_id, code, description, geog, geom)
          select #{self.id}, gi.id, CAST(shape.#{code_column} as numeric) as code, shape.label, shape.geog, shape.geom
          from #{self.shape_table_name} shape
          inner join geog_instances gi
            on gi.sample_geog_level_id = #{my_sgl.id} and gi.code = CAST(shape.#{code_column} as numeric) WHERE shape.#{code_column} <> ''"

      records = ActiveRecord::Base.connection.insert(insert_stmt)
    end
  end
end

#construct_instances_for_nhgis_terrapop_sample(nhgis_tps, sgl, parent_sgl = nil) ⇒ Object 



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

def construct_instances_for_nhgis_terrapop_sample(nhgis_tps, sgl, parent_sgl = nil)
  return nil if nhgis_tps.nhgis_dataset_id.nil?       #get out when the terrapop sample is NOT related to an NHGIS dataset

  $stderr.puts "----------------------------------------------------------------------------"
  $stderr.puts "#{nhgis_tps.country.full_name} => #{sgl.country_level.geog_unit.code} ==> 'GISJOIN'"
  $stderr.puts "----------------------------------------------------------------------------"

  # the insert statement for the national level is simpler than others, so if parent-level is nil.
  # check that we're doing national.
  if parent_sgl.nil?
    if sgl.internal_code.rindex('NAT').nil? and sgl.internal_code.rindex('FLAD').nil?
      raise "attempt to call construct_instances_for_terrapop_sample with no parent_level: #{sgl.internal_code}"
    end
  end

  (schema, table) = self.shape_table_name.split(".", 2)

  columns_stmt = "SELECT column_name FROM information_schema.columns where table_schema = '#{schema}' AND table_name = '#{table}'"

  columns_records = ActiveRecord::Base.connection.execute(columns_stmt).map{|c| c['column_name']}.uniq

  # once there's a shape_area column on all the maps, we can populate that from here
  join_clause = ""
  if !sgl.internal_code.rindex("NAT").nil?                        #nation-level GISJOIN code format varies: e.g. "G1," "G010" depending on the year
    label_column_source = "'United States'"

  elsif !sgl.internal_code.rindex("FLAD").nil?                    #state-level GISJOIN code format: "G" + "SSS" -- 3 of which is numeric

    potential_names = ['statenam', 'name10', 'name']

    overlap = columns_records & potential_names

    if overlap.count == 0
      raise "'potential_names' does not contain an overlap with '#{columns_records.join(", ")}' - #{table}"
    end

    label_column_source = "shape.#{overlap.first}"                        #in NHGIS, states do not have parents

  elsif !sgl.internal_code.rindex("SLAD").nil?
    #when working with a county, the GISJOIN code will be 8 characters long: "G" + "SSS" + "CCCC" -- 7 of which is numeric

    potential_names = ['nhgisnam', 'name10', 'name']

    overlap = columns_records & potential_names

    if overlap.count == 0
      raise "'potential_names' does not contain an overlap with '#{columns_records.join(", ")}' - #{table}"
    end

    label_column_source = "shape.#{overlap.first}"

    join_clause = "INNER JOIN geog_instances parent ON parent.sample_geog_level_id = #{parent_sgl.id} AND parent.code = CAST(SUBSTR(shape.gisjoin,2,3) AS NUMERIC)"

  else
    raise "SampleGeogLevel was neither 'NAT', 'FLAD', nor 'SLAD' -- unable to generate an select/insert statement for #{self.source_file}"
  end

  parent_id_column_source = join_clause.empty? ? "NULL" : "parent.id"

  #the select statement will serve as the select for the bulk insert.  The select statement could also be used for debugging purposes
  select_stmt = "SELECT #{sgl.id}                            AS sample_geog_level_id,
                           #{parent_id_column_source}        AS parent_id,
                           #{self.gisjoin_as_numeric_clause} AS code,
                           #{label_column_source}            AS label,
                           ST_Area(shape.geog)               AS shape_area,
                           'GISJOIN'                         AS geog_code,
                           shape.gisjoin                     AS str_code,
                           #{self.terrapop_sample_id}        AS terrapop_sample_id
                      FROM #{self.shape_table_name} shape
                     #{join_clause}
                      WHERE shape.gisjoin <> ''"

  insert_stmt = "INSERT INTO geog_instances(sample_geog_level_id, parent_id, code, label, shape_area, geog_code, str_code, terrapop_sample_id)
                    #{select_stmt}"
  begin
    $stderr.puts "inserting geog instances...".color(:magenta)
    $stderr.puts insert_stmt
    #p ActiveRecord::Base.connection.exec_query(select_stmt)
    records = ActiveRecord::Base.connection.insert(insert_stmt)
  rescue Exception => e
    raise e.message.color(:red)
  end
end

#construct_instances_for_terrapop_sample(tps, parent_level = nil, code_column = 'ipums_code', include_numeric_code = true) ⇒ Object

Be sure to call construct_instances before calling construct_boundaries



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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
 
# File 'app/models/map.rb', line 12

def construct_instances_for_terrapop_sample(tps, parent_level = nil, code_column = 'ipums_code', include_numeric_code = true)
  my_sgl = tps.sample_geog_level_for_country_level(self.country_level)
  unless my_sgl.nil?
    geog_unit_code = my_sgl.country_level.geog_unit.code

    if self.terrapop_sample_id.nil?
      self.terrapop_sample_id = tps.id
      self.save
    end

    geog_code = nil
    short_name = tps.country.short_name.upcase #my_sgl.terrapop_sample.country.short_name.upcase

    if geog_unit_code == 'NAT'
      geog_code = 'CNTRY'
    elsif geog_unit_code == 'HFLAD'
      geog_code = 'GEOLEV1'
    elsif geog_unit_code == 'FLAD'
      geog_code = 'GEO1B_' + short_name
    elsif geog_unit_code == 'HSLAD'
      geog_code = 'GEO2A_' + short_name
    elsif geog_unit_code == 'SLAD'
      geog_code = 'GEO2B_' + short_name
    end

    #$stderr.puts "----------------------------------------------------------------------------"
    #$stderr.puts "#{my_sgl.terrapop_sample.country.full_name} => #{my_sgl.country_level.geog_unit.code} ==> #{geog_code.to_s}"
    #$stderr.puts "----------------------------------------------------------------------------"

    # the insert statement for the national level is simpler than others, so if parent-level is nil.
    # check that we're doing national.
    #if parent_level.nil? and my_sgl.internal_code.rindex('NAT').nil?
    #  raise "attempt to call construct_instances_for_terrapop_sample with no parent_level: #{my_sgl.internal_code}"
    #end

    # once there's a shape_area column on all the maps, we can populate that from here

    inc = include_numeric_code

    geog_instances_count = GeogInstance.where({sample_geog_level_id: my_sgl.id, geog_code: geog_code.to_s}).count

    #GeogInstance.delete_all(["sample_geog_level_id = ? AND geog_code = ?", my_sgl.id, geog_code.to_s])

    if geog_instances_count == 0

      insert_stmt = if parent_level.nil?
        "insert into geog_instances(sample_geog_level_id, parent_id, #{inc ? 'code,' : ''} label, geog_code, str_code, terrapop_sample_id)
        select #{my_sgl.id}, NULL, #{inc ? "CAST(shape.#{code_column} as numeric) as code," : ''} shape.label, '#{geog_code.to_s}', shape.#{code_column}, #{tps.id}
        from #{self.shape_table_name} shape WHERE shape.#{code_column} IS NOT NULL"
      else
        "insert into geog_instances(sample_geog_level_id, parent_id, #{inc ? 'code, ' : ''} label, geog_code, str_code, terrapop_sample_id)
          select #{my_sgl.id}, parent.id, #{inc ? "CAST(shape.#{code_column} as numeric) as code," : ''} shape.label, '#{geog_code.to_s}', shape.#{code_column}, #{tps.id}
          from #{self.shape_table_name} shape
          inner join geog_instances parent ON parent.code = CAST(shape.parent as numeric) AND parent.sample_geog_level_id = #{parent_level.id} WHERE shape.#{code_column} IS NOT NULL"
          # inner join geog_instances parent ON parent.code = CAST(shape.parent as numeric) and parent.sample_geog_level_id = #{parent_level.id} WHERE shape.#{code_column} IS NOT NULL
      end

      records = ActiveRecord::Base.connection.execute(insert_stmt)

    else
      $stderr.puts "Found GeogInstances for SampleGeogLevel[#{my_sgl.id}], geog_code: #{geog_code.to_s}"

      #if parent_level.nil?
        sql = "select #{my_sgl.id} AS sample_geog_level_id, NULL AS parent_id, #{inc ? "CAST(shape.#{code_column} as numeric) as code," : ''} shape.label AS label, '#{geog_code.to_s}' AS geog_code, shape.#{code_column} AS str_code, #{tps.id} AS terrapop_sample_id FROM #{self.shape_table_name} shape WHERE shape.#{code_column} IS NOT NULL"
      #else
      #  sql = "select #{my_sgl.id} AS sample_geog_level_id, parent.id AS parent_id, #{inc ? "CAST(shape.#{code_column} as numeric) as code," : ''} shape.label AS label, '#{geog_code.to_s}' AS geog_code, shape.#{code_column} AS str_code, #{tps.id} AS terrapop_sample_id from #{self.shape_table_name} shape inner join geog_instances parent ON parent.code = CAST(shape.parent as numeric) and parent.sample_geog_level_id = #{parent_level.id} WHERE shape.#{code_column} IS NOT NULL"
      #end

      records = ActiveRecord::Base.connection.execute(sql)

      geog_instances = GeogInstance.where({sample_geog_level_id: my_sgl.id, geog_code: geog_code.to_s})

      geog_instances.each{|geog_instance|

        records.each{|record|

          if geog_instance.label == record['label']
            geog_instance.parent_id = record['parent_id']

            if inc
              geog_instance.code = record['code']
            end

            geog_instance.geog_code = record['geog_code']
            geog_instance.str_code  = record['str_code']

            geog_instance.save

          end

        }
      }

    end
  else
    $stderr.puts "=*=*=> SampleGeogLevel for #{tps.inspect} at #{self.country_level.inspect}"
  end
end

#gisjoin_as_numeric_clause ⇒ Object 



251
252
253
 
# File 'app/models/map.rb', line 251

def gisjoin_as_numeric_clause
  "CAST(SUBSTR(shape.gisjoin,2,LENGTH(shape.gisjoin)-1) AS NUMERIC)"   #same for all levels, just abstracting away complexity
end

#shape_table_name ⇒ Object 



246
247
248
249
 
# File 'app/models/map.rb', line 246

def shape_table_name
  #using the map's source file, generate the table name for the corresponding shape file table.
  Rails.configuration.database_configuration[Rails.env]['gis_schema_name'] + '.' + self.source_file
end