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
    Commented May 17, 2012 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.