sql/country_grid.sql

   1 drop table country_osm_grid2;
   2 create table country_osm_grid2 as select country_name.country_code,st_union(placex.geometry) as geometry from country_name,
   3   placex
   4   where (lower(placex.country_code) = country_name.country_code)
   5     and placex.rank_search < 16 and st_area(placex.geometry)>0
   6   group by country_name.country_code;
   7 alter table country_osm_grid2 add column area double precision;
   8 update country_osm_grid2 set area = st_area(geometry::geography);
   9 drop table country_osm_grid3;
  10 create table country_osm_grid3 as select country_code,area,quad_split_geometry(geometry,0.5,20) as geometry from country_osm_grid2;
  11 drop table country_osm_grid;
  12 alter table country_osm_grid3 rename to country_osm_grid;
  13 CREATE INDEX idx_country_osm_grid_geometry ON country_osm_grid USING GIST (geometry);
  14 update 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)
  15  from country_osm_grid group by country_code) as x where x.country_code = country_osm_grid.country_code;