Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 13 down vote accepted

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);
share|improve this answer
    
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 '12 at 15:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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