sql/country_grid.sql

   1 -- Script to build a calculated country grid from existing tables
   2 DROP TABLE IF EXISTS tmp_country_osm_grid;
   3 CREATE TABLE tmp_country_osm_grid as select country_name.country_code,st_union(placex.geometry) as geometry from country_name,
   4   placex
   5   where (lower(placex.country_code) = country_name.country_code)
   6     and placex.rank_search < 16 and st_area(placex.geometry) > 0
   7   group by country_name.country_code;
   8 ALTER TABLE tmp_country_osm_grid add column area double precision;
   9 UPDATE tmp_country_osm_grid set area = st_area(geometry::geography);
  10
  11 -- compare old and new
  12 select country_code, round, round(log(area)) from (select distinct country_code,round(log(area)) from country_osm_grid order by country_code) as x
  13   left outer join tmp_country_osm_grid using (country_code) where area is null or round(log(area)) != round;
  14
  15 DROP TABLE IF EXISTS new_country_osm_grid;
  16 CREATE TABLE new_country_osm_grid as select country_code,area,quad_split_geometry(geometry,0.5,20) as geometry from tmp_country_osm_grid;
  17 CREATE INDEX new_idx_country_osm_grid_geometry ON new_country_osm_grid USING GIST (geometry);
  18
  19 -- Sometimes there are problems calculating area due to invalid data - optionally recalc
  20 UPDATE new_country_osm_grid set area = sum from (select country_code,sum(case when st_area(geometry::geography) = 'NaN' THEN 0 ELSE st_area(geometry::geography) END)
  21  from new_country_osm_grid group by country_code) as x where x.country_code = new_country_osm_grid.country_code;
  22
  23 -- compare old and new
  24 select country_code, x.round, y.round from (select distinct country_code,round(log(area)) from country_osm_grid order by country_code) as x
  25   left outer join (select distinct country_code,round(log(area)) from new_country_osm_grid order by country_code) as y
  26     using (country_code) where x.round != y.round;
  27
  28 -- Flip the new table in
  29 BEGIN;
  30 DROP TABLE IF EXISTS country_osm_grid;
  31 ALTER TABLE new_country_osm_grid rename to country_osm_grid;
  32 ALTER INDEX new_idx_country_osm_grid_geometry RENAME TO idx_country_osm_grid_geometry;
  33 COMMIT;