How to create a table from existing tables (applying new schemas) in postgis DB?

Question

I have a table that is geospatially enabled; that contain world borders. I want to break it down to different schemas (global, europe etc..) . My thought is to use use something that looks like this:

CREATE TABLE europe.borders
  AS (SELECT *
         FROM wo_borders
         WHERE admin_lvl2='eu' ); //just an example!

What's the correct to create a tables from existing tables taking account new schemas and making them geospatially enabled? Do I have to create beforehand the new tables using my default postgis template?

Answer 1

Generally you can make a new geospatial table like this:

SELECT *
INTO europe.borders
FROM wo_borders
WHERE admin_lvl2 = 'eu';

-- Define a primary key
ALTER TABLE europe.borders ADD PRIMARY KEY (gid);

-- Spatially enable it
SELECT Populate_Geometry_Columns('europe.borders'::regclass);

However, by doing this you are segregating your database (un-normalizing it). This means it has redundancy, so if there is an update for any info in one table, it is difficult to update in another. Furthermore, you wouldn't be able to perform queries on the whole world, just subregions. You could consider using VIEWs to make virtual tables of partitions of the main table:

-- Make an index on your column used to query the view
CREATE INDEX wo_borders_admin_lvl2_idx ON wo_borders USING btree (admin_lvl2);

-- Now the view
CREATE OR REPLACE VIEW europe.borders_view AS
 SELECT *
 FROM wo_borders
 WHERE admin_lvl2 = 'eu';

-- Spatially enable it
SELECT Populate_Geometry_Columns('europe.borders_view'::regclass);