I have a large amount of point data that I have in CSV form. I am able to insert it all into a PostGIS table no problem. What I want to do though, is split the data up on insert into multiple separate tables. The data is statewide and I would like to have separate tables for each county within the state.

I know I could insert all of the points into a temp table and then INSERT INTO each county based on an ST_Intersect() but is there a better way of doing this? Some sort of trigger function that will run faster? Or is there a way to use a CONSTRAINT on each table where anything within/not within a certain polygon is rejected?

Edit: Sorry for the change but we ended up changing how we wanted to store the data: One table for our county of interest and another for the rest of the data because we occasionally need it. The other answers were not quite what we were looking for, we really wanted to avoid temp tables and adding fields and I did come up with a solution for it.

3 Answers 3


If you're incoming data does not include a county field add one and use an update query to populate it:

UPDATE point_data a SET county_num = b.county_num FROM county_layer b
WHERE ST_Intersects(a.geom, b.geom);

Then create your destination table and use a trigger on the county field to populate the child tables. You can even set that trigger's function to create the child table if it doesn't exist yet.

CREATE OR REPLACE FUNCTION trigger_county_child_insert() RETURNS TRIGGER AS $$
  var_sql text;
  var_tbl text;
  var_tbl := TG_TABLE_NAME || '_county' || NEW.county_num::text;
  IF NOT EXISTS(SELECT * FROM information_schema.tables
    WHERE table_schema = TG_TABLE_SCHEMA
      AND table_name = var_tbl) THEN
  var_sql := 'CREATE TABLE ' || TG_TABLE_SCHEMA || '.' || var_tbl
    || '(CONSTRAINT pk_' || var_tbl || 
      '  PRIMARY KEY(gid) ) INHERITS (' || 
          TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME  || ');
        CREATE INDEX idx_' || var_tbl || '_geom ON ' 
          || TG_TABLE_SCHEMA || '.' || var_tbl || ' USING gist(geom); 
          ALTER TABLE ' || TG_TABLE_SCHEMA || '.' || var_tbl 
            || ' ADD CONSTRAINT chk_county_num CHECK (county_num = ' || 
          NEW.county_num::text || ');' ;
  EXECUTE var_sql;
  || '.' || var_tbl 
     || '(gid, county_num, geom) 
      VALUES($1, $2, $3)';
     NEW.gid, NEW.county_num, NEW.geom;
$$ language plpgsql;

And then assign the above to the trigger:

CREATE TRIGGER trig_county_child_insert BEFORE INSERT
  ON destination_point_layer FOR EACH ROW
  EXECUTE PROCEDURE tigger_county_child_insert();

I originally got this from PostGIS In Action by Regina Obe and Leo Hsu and it's come in handy many times. I've had several situations like yours where I'm pulling data by county. We use zero padded county numbers as IDs (01 - 09, etc) if that helps make sense of the above.

I apologize for the indenting and formatting, I pulled this from one of my scripts and I get sloppy with my indents sometimes.

  • I do not have any county data within the incoming data sadly. If I am right that would mean I need to insert the data into a new table, intersect it with the county data and then insert it into the table the the trigger on it? Was hoping to be able to avoid that temporary table doing the intersect. Looks like its almost what I am looking for though.
    – eseglem
    Commented Aug 20, 2013 at 18:29
  • Yeah, the way I have it set now needs a temp table. I'm a big fan of temp tables because I get sketchy data that needs to be cleaned and verified, but if you have confidence in what you've got coming in you can alter the function to pull the county number from an intersect query. The plpgsql docs will help you out with the syntax you'll need to capture and pass the results of the intersect query. Commented Aug 20, 2013 at 18:46

Any operation while importing data is going to be slower than using COPY FROM to load the CSV into a staging table and then working in the data inside the db.

  • 2
    From my experience inserting data using sql script 'INSERT ...' is 10x slower that using 'COPY FROM'. Also using triggers will slow down it even more. If Data is read in small chunks then triggers are good, if in one or two big 50k lines sets then copy from will be a lot faster Commented Aug 21, 2013 at 8:26
  • Good to know, I think what we are doing right now already uses COPY FROM, but I will make sure of that. This doesn't solve my main problem as to the best way to split an input csv into multiple tables based on location though.
    – eseglem
    Commented Aug 21, 2013 at 10:47

Below is the code that ended up being used. Any inputs to speed it up would be appreciated.

CREATE OR REPLACE trigger_oninsert()
    var_co int;
    var_co := 1 FROM county_boundaries b WHERE c.num = 10 AND ST_Intersects(NEW.point_geom, b.poly_geom);
    IF var_co = 1 THEN
        INSERT INTO main_county SELECT NEW.*;
        INSERT INTO other_counties SELECT NEW.*;
    END IF;
  COST 100;
  • 1
    see blog.cleverelephant.ca/2011/02/…. i dont know how tha works, but maybe be useful information Commented Aug 22, 2013 at 17:27
  • Interesting link, not quite what I was looking for, but certainly could be useful to keep around.
    – eseglem
    Commented Aug 22, 2013 at 18:38

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.