1

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.

2

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 $$
DECLARE 
  var_sql text;
  var_tbl text;
BEGIN
  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;
END IF;
var_sql := 'INSERT INTO ' || TG_TABLE_SCHEMA
  || '.' || var_tbl 
     || '(gid, county_num, geom) 
      VALUES($1, $2, $3)';
EXECUTE var_sql USING 
     NEW.gid, NEW.county_num, NEW.geom;
RETURN NULL;
END;
$$ 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.

| improve this answer | |
  • 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 Aug 20 '13 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. – HeyOverThere Aug 20 '13 at 18:46
1

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.

| improve this answer | |
  • 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 – simplexio Aug 21 '13 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 Aug 21 '13 at 10:47
0

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

CREATE OR REPLACE trigger_oninsert()
  RETURNS TRIGGER AS
$BODY$
DECLARE
    var_co int;
BEGIN
    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.*;
    ELSE
        INSERT INTO other_counties SELECT NEW.*;
    END IF;
    RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
| improve this answer | |
  • 1
    see blog.cleverelephant.ca/2011/02/…. i dont know how tha works, but maybe be useful information – simplexio Aug 22 '13 at 17:27
  • Interesting link, not quite what I was looking for, but certainly could be useful to keep around. – eseglem Aug 22 '13 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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