Postgresql INSERT INTO table WHERE

Question

I'm working with a large dataset and I prefer to handle it within a postgres database with postgis. The data is looped in python into a long string (or multiple if one string gets too big). I would like to add a lot of data if the data (in my case column "pos") is within a polygon.

The dataset is much bigger, hence this is an example of what I would like to do:

INSERT INTO I_test_test (pos, row_id) 
VALUES(ST_PointFromText('POINT(13.55259726 55.39571705)', 4326), 1, 
       ST_PointFromText('POINT(13.55387947 55.39514755)', 4326), 2) 
WHERE st_intersects(pos, ST_GeomFromText('POLYGON((13.5542 55.3956,13.5543 55.3952,13.5586 55.3949,13.5587 55.3954,13.5542 55.3956))',4326))

I could write an sql question and check every "pos" before I add them into the INSERT INTO, hence since it is 10 000 - 1 000 000 rows that I would like to add it would take forever...

Any suggestions?

Answer 1

Echoing @Freight_Train, get the data into Postgres first, then do query/insert.

I would get the points (or the source data) loaded into Postgres first.

And if possible, get the source of the "extra" data loaded into Postgres.

Then build a table with geometry from those and put a spatial index on the geometry column.

Then you could build 2 INSERT statements: 1 for the data outside your poly. And another for data outside your polygon.

I usually workout my approach with a series of CTEs w/ sample data, then figure out how to load in via Copy: https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table. Example below is with a literal CSV, so you'd need to substitute that with whatever format your data is.

with source_point_data as (
    select E'13.55259726,55.39571705,1\n13.55387947,55.39514755,2'::text as csv
)
,extra_source_data as (
    /* Substitute w/ actual data */
    select generate_series as id
    FROM generate_series(1,100)
)
, query_geom as (
    SELECT ST_GeomFromText('POLYGON((13.5542 55.3956,13.5543 55.3952,13.5586 55.3949,13.5587 55.3954,13.5542 55.3956))',4326) as geom   
)
,rows_from_csv as (
    select unnest as val
    FROM 
        (SELECT unnest(string_to_array(csv,E'\n')) FROM source_point_data) t
)
,parsed_rows as (
    select  array_vals[1]::float as y
        ,array_vals[2]::float as x
        ,array_vals[3]::integer as id
    from (
    select string_to_array(val,E',') as array_vals from rows_from_csv) t

)
,construct_geom as (

    SELECT 
        ST_SetSRID(ST_Point(x, y),4326) as geom
        ,id
    FROM parsed_rows    
)
,insert_within_poly as (

    /* JOIN to whatevever extra_source_data is and insert the extra columns */

    /* Insert into temp table or the actual table */
    INSERT INTO tmp_insert_within_poly --(geom,id,extra_col1,extra_col2...)  
    SELECT geom, id
    FROM construct_geom
    WHERE st_intersects(geom, (select geom from query_geom))
    RETURNING * 
)
,insert_outside_poly as (

    /* Insert into temp table or the actual table */
    INSERT INTO tmp_insert_outside_poly
    SELECT geom, id
    FROM construct_geom
    WHERE st_intersects(geom, (select geom from query_geom)) = false
    RETURNING *
)
select 
jsonb_build_object(
    'insert_within_poly', (SELECT array_to_json(array_agg(t)) FROM (SELECT * FROM insert_within_poly) t)
    ,'insert_outside_poly', (SELECT array_to_json(array_agg(t)) FROM (SELECT * FROM insert_outside_poly) t)
)
;

Answer 2

Have you tested that SQL? Your where clause is assuming that the values you have added into I_test_test have already gone in and will be checked. They haven't, the pos column will still be empty (happy to be corrected here).

Where are you keeping the point geometries that you want to add into your dataset? If you have the geometries together I would import them to PostGIS as a their own table, and do something like

INSERT INTO I_test_test(pos,row_id)
SELECT thispos,thisid
FROM pos_table
WHERE st_intersects(pos_table.thispos, ST_GeomFromText('POLYGON((13.5542 55.3956,13.5543 55.3952,13.5586 55.3949,13.5587 55.3954,13.5542 55.3956))',4326)

This method negates Python which would add an extra layer to slow down the process. Happy to update answer depending on how you have stored your pos information.