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
         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?


1 Answer 1


Generally you can make a new geospatial table like this:

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
 FROM wo_borders
 WHERE admin_lvl2 = 'eu';

-- Spatially enable it
SELECT Populate_Geometry_Columns('europe.borders_view'::regclass);
  • Your answer really answered my question and gave me a better understanding in the inner works of postgresql/postgis databases! Thank you!
    – nickves
    May 17, 2012 at 15:13

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .