1

I have a table on my PostgreSQL database that I would like to update by inserting the missing lines that I have in many other shapefile tables. I was able to connect to my server PostgreSQL from the Windows' shell (psql -h localhost -U quest -d "test" -p 5432) and import some shapefiles to update my table (to insert the data from pgAdmin), but instead of importing all my tables into PostgreSQL, I would like to update my table directly from the shell. The problem is that I do not know how to go about it. Specifically, I don't know how to tell PostgreSQL the source of my tables in the "from".

In pgAdmin I use :

INSERT INTO bal.bal (bal_pk_uid, bal_agg_id, geom)
            SELECT  bal_pk_uid, bal_agg_id, geom
    FROM bal.bal_2 bal2
    WHERE bal2.bal_pk_uid is not null and not exists (select 1 from bal.bal balglobale where balglobale.bal_pk_uid = bal2.bal_pk_uid);

but in the shell how am I supposed to tell the path to my source "C:"CARTO"\bal.shp bal2"?

3 Answers 3

2

I don't think you can do this directly from the shell and would need some type of intermediate dataset.

I would recommend setting up an OGR Foreign Data Wrapper to the folder of shapefiles you want to insert.

https://github.com/pramsey/pgsql-ogr-fdw

Once you have the OGR FDW installed, you can create a server:

CREATE SERVER fdw_shp
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS (datasource '/home/folder_shp/', format 'ESRI Shapefile')

Then, create a foreign table using the server. Here is an example of an OGR FDW foreign table we point to that is automatically updated via WGET from the City and County of Denver's open data portal (seperate script):

CREATE FOREIGN TABLE etl_fdw.statistical_neighborhoods (
    fid int8 NULL,
    geom geometry(POLYGON, 4326) NULL,
    nbhd_id int4 NULL,
    nbhd_name varchar(50) NULL,
    typology varchar(33) NULL,
    notes varchar(50) NULL
)
SERVER fdw_shp
OPTIONS (layer 'statistical_neighborhoods');

The 'layer' option is pointing to the name of the shapefile in the 'datasource' folder set in the FDW Server specs.

Now you have a foreign table that 'behaves' like a PostGIS table, but is really a shapefile on disk.

You can use INSERT statements to add the data from the SHP into your PostGIS tables.

0

I found another solution that so far works well with a point layer and tables that contain the exact same columns :

shp2pgsql -I -s <SRID> -a  <path to shapefile> <schema>.<table> | psql
-U <username> -d <databasename> -h <hostname> -p <port>

(where -a stands for append)

so for me the solution was :

 shp2pgsql -I -s 2154 -a bal.shp bal.bal| psql  -U quest -d test -p 5432
0

I would use a python jupyter notebook to import the shapefile, convert to WKT, connect to the PostGIS database, and insert the data.

I like this explanation for how to connect to PostGIS with python. Import shapefiles into PostGIS using Python with psycopg2

Here is another StackExchange answer that explains how to import a shapefile into WKT with python.

Import shapefiles into PostGIS using Python with psycopg2

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.