Class: OpenPlaces::Engine

Inherits:
Rails::Engine
  • Object
show all
Defined in:
lib/open_places/engine.rb

Class Method Summary collapse

Class Method Details

.normalize_data ⇒ Object 



9
10
11
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
 
# File 'lib/open_places/engine.rb', line 9

def self.normalize_data
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET code = 'NO' WHERE name='Norway';")
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET country_code = 'FR' WHERE country_code IN ('GP', 'RE', 'GF', 'MQ', 'YT');")
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET country_code = 'NO' WHERE country_code IN ('SJ', 'NOR');")

  # Set Province and Country
  matches = [
    "(p.province_code=p2.code OR p.province_name=p2.name) AND p.country_code=p2.country_code AND p2.geotype='OpenPlaces::Province'",
    "ST_Contains(p2.geom, p.latlng) AND p2.geotype='OpenPlaces::Province'",
    "p2.code= p.country_code",
    "ST_Contains(p2.geom, p.latlng)"
  ]
  matches.each do |match|
    ActiveRecord::Base.connection.execute("UPDATE open_places p SET country_name = p2.country_name, country_code = p2.country_code,
     province_name = p2.name, province_code = p2.code, continent = p2.continent, region_un = p2.region_un, subregion = p2.subregion, region_wb= p2.region_wb, 
     long_name = concat_ws(' ', concat_ws(', ', p.name, p2.name), p2.country_name),
     tsvector = setweight(to_tsvector(p.name), 'A') || setweight(to_tsvector(p2.name), 'B') || setweight(to_tsvector(p2.country_name), 'C'),
     slug = regexp_replace(trim(regexp_replace(lower(concat_ws('-', p.name, p2.name, p2.country_name)), '[^0-9a-z/]+', ' ', 'g')), '[ ]+', '-', 'g'),
     path = regexp_replace(trim(regexp_replace(lower(concat_ws('/', p2.country_name, p2.name, p.name)), '[^0-9a-z/]+', ' ', 'g')), '[ ]+', '-', 'g')
     FROM open_places p2 WHERE p.continent IS NULL AND p.geotype != 'OpenPlaces::Country'
     AND #{match}")
  end

  # Set GeoSpatial Fields
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET latlng =  ST_SetSRID(ST_Point(longitude, latitude),4326) WHERE latlng IS NULL;")
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET geom = ST_Expand(ST_SetSRID(ST_Point(longitude, latitude),4326), .1) WHERE geom IS NULL;")
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET bbox = ST_Envelope(geom) WHERE bbox IS NULL;")
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET latitude = ST_Y(latlng), longitude = ST_X(latlng) WHERE latitude = 0 OR longitude = 0;")
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET area = ST_Area(geom) WHERE area = 0;")
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET x_min = ST_XMin(geom), y_min = ST_YMin(geom), x_max = ST_XMax(geom), y_max = ST_YMax(geom) WHERE x_min = 0 OR y_min = 0 OR x_max = 0 OR y_max = 0;")

  # Update path, slug & tsvector
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET long_name = concat_ws(' ', concat_ws(', ', name, province_name), country_name) WHERE p.long_name IS NULL;")
  ActiveRecord::Base.connection.execute("UPDATE open_places p SET
    slug = regexp_replace(trim(regexp_replace(lower(concat_ws('-', p.name, p.province_name, p.country_name)), '[^0-9a-z/]+', ' ', 'g')), '[ ]+', '-', 'g'),
    path = regexp_replace(trim(regexp_replace(lower(concat_ws('/', p.country_name, p.province_name, p.name)), '[^0-9a-z/]+', ' ', 'g')), '[ ]+', '-', 'g')
    WHERE (p.slug IS NULL OR p.path IS NULL);")
  ActiveRecord::Base.connection.execute("UPDATE open_places SET short_name = concat_ws(', ', name, province_name) WHERE geotype not IN ('OpenPlaces::Country', 'OpenPlaces::Province') AND country_code = 'US' AND short_name IS NULL;")
  ActiveRecord::Base.connection.execute("UPDATE open_places SET short_name = concat_ws(', ', name, country_name) WHERE country_code != 'US' AND short_name IS NULL AND short_name IS NULL;")
  ActiveRecord::Base.connection.execute("UPDATE open_places SET tsvector = setweight(to_tsvector(name), 'A') || setweight(to_tsvector(province_name), 'B') || setweight(to_tsvector(country_name), 'C') WHERE tsvector IS NULL AND province_name IS NOT NULL;")
  ActiveRecord::Base.connection.execute("UPDATE open_places SET tsvector = setweight(to_tsvector(name), 'A') || setweight(to_tsvector(country_name), 'C') WHERE tsvector IS NULL AND country_name IS NOT NULL;")
  ActiveRecord::Base.connection.execute("UPDATE open_places SET tsvector = setweight(to_tsvector(name), 'A') WHERE tsvector IS NULL;")
end