Importing Shapefiles in Postgresql in Linux Using Pgadmin 4

Importing shapefiles in postgresql in linux using pgadmin 4

If you're simply trying to import shapefiles into PostgreSQL, you might wanna take a look at shp2pgsql.

Data sample: TM_WORLD_BORDERS_SIMPL-0.3.zip

After unpacking your zip file just execute the following line in your console:

$ shp2pgsql -I -s 4326 TM_WORLD_BORDERS_SIMPL-0.3.shp table_world | psql -d mydb

Things to take into account:

• table_world is the name of the target table
• psql -d mydb takes into account that your current operating system user has an account in the database, that no password is required, that the database is installed at localhost and that it listens at the the standard port 5432. Check the psql documentation to build your own connection command, e.g. psql -U myuser -h 192.168.1.42 -p 5434 -d mydb to login with the user myuser in the database mydb in the remote PostgreSQL at 192.168.1.42 that listens at the port 5434. In case your PostgreSQL isn't configured to accept connections, check this answer.
• 4326 is the identifier for WGS84, which is the spatial reference system of this shapefile - and the most often used worldwide.

.. and your data is ready to be played with. Screenshot from the geometry viewer of pgAdmin4:

Further reading:

• psql
• shp2pgsql tutorial

Python Scripts to ingest a shapefile into a PostgreSQL/PostGIS database utilizing shp2pgsql.exe on windows

Here are some modifications that should make thing work. Note that it would need further modification if you need to be notified if any of the commands fail. Note that it will fail for more than one shapefile, since a new_shp_table table will already exist until you have further logic to move or rename that table elsewhere, or to load it with a unique name.

Also, note that PostgreSQL 8.4 will reach it's end-of-life later this year, so you might want to plan to upgrade to a more recent release before it's too late.

import os, subprocess

# Choose your PostgreSQL version here
os.environ['PATH'] += r';C:\Program Files (x86)\PostgreSQL\8.4\bin'
# http://www.postgresql.org/docs/current/static/libpq-envars.html
os.environ['PGHOST'] = 'localhost'
os.environ['PGPORT'] = '5432'
os.environ['PGUSER'] = 'someuser'
os.environ['PGPASSWORD'] = 'clever password'
os.environ['PGDATABASE'] = 'geometry_database'

base_dir = r"c:\shape_file_repository"
full_dir = os.walk(base_dir)
shapefile_list = []
for source, dirs, files in full_dir:
    for file_ in files:
        if file_[-3:] == 'shp':
            shapefile_path = os.path.join(base_dir, file_)
            shapefile_list.append(shapefile_path)
for shape_path in shapefile_list:
    cmds = 'shp2pgsql "' + shape_path + '" new_shp_table | psql '
    subprocess.call(cmds, shell=True)

Postgis POLYGON of cities

A great source for administrative borders is GADM. There you can find administrative borders from the whole world in many administrative levels and data formats, such as shapefiles and geopackage.

If you wondering how to import the GADM data into PostGIS, take a look at this other answer.

How to configure PostgreSQL to accept all incoming connections

Just use 0.0.0.0/0.

host    all             all             0.0.0.0/0            md5

Make sure the listen_addresses in postgresql.conf (or ALTER SYSTEM SET) allows incoming connections on all available IP interfaces.

listen_addresses = '*'

After the changes you have to reload the configuration. One way to do this is execute this SELECT as a superuser.

SELECT pg_reload_conf();

Note: to change listen_addresses, a reload is not enough, and you have to restart the server.

---

Related Topics