Python – How to Run shp2pgsql in PostGIS Without Reporting Every Row Loaded

postgispostgresqlpythonshp2pgsql

I'm running shp2pgsql in python with a simple script, which works just fine:

import os
import subprocess

cmd = 'shp2pgsql -s 4326 ../data/shp/statistical_neighborhoods.shp temp_table | psql -h hostname -d databasename -U username'

subprocess.call(cmd, shell=True)

The problem is, for every record loaded, there is a message reported:

INSERT 0 1

That message is reported for the 78 records in the neighborhoods layer in the example above, but for a file with 280,000 records (address point file from the City, or Parcels layer), that would be a very unnecessary amount of messages.

Is this problem coming from shp2pgsql, or is it a result of the subprocess.call() function?

Best Answer

To avoid a message from subprocess.call when psql writes its informations to stdout you can simply tell psql to not write any information. The operator is -q or --quiet as taken from the psql manual.

Related Solutions

[GIS] Problem loading OS OpenData multipoint shapefile into PostGIS

As suggested by other answers, the problem is related with the POINT and MULTIPOINT types. shp2pgsql (and ogrinfo) detects the geometry type as MULTIPOINT, but then, when creating the EWKB representation of the geometry (the value to insert in the_geom column), it seems to be generating a POINT geometry:

select geometrytype('0101000020346C000000000000302A0A4100000000304F1C41');
--POINT

(I took the first generated record)

According to the shp2pgsql man page, there is a flag that should force new geometries to be created simple, but apparently, it does not work on points:

   -S     Generate simple Geometries instead of MULTIgeometries. Shape files don't differ between LINESTRINGs and MULTILINESTRINGs, so shp2pgsql generates MUL‐
          TILINESTRINGs  by  default. This switch will produce LINESTRINGs instead, but shp2pgsql will fail when it hits a real MULTILINESTRING. The same works
          for POLYGONs vs. MULTIPOLYGONs.

Instead of dropping the offending constraint, I will suggest changing the geometry column in the generated SQL script before executing it with psql:

SELECT AddGeometryColumn('','strategi_point','the_geom','27700','POINT',2);

This way you will get your records loaded, and at the same time keep a constrain that may ensure your data integrity in the future.

[GIS] shp2pgsql: how to know the schema.table

Schema is something different. Schema is in database, not somewhere with your shapefile. When you created new database, and also extensions by:

CREATE EXTENSION POSTGIS;

You've created new schemas in your database: public and topology. Look at the picture below:

In each of my databases, there are schemas. You can create new schemas, just check the documentation.

http://www.postgresql.org/docs/9.3/static/sql-createschema.html

http://www.tutorialspoint.com/postgresql/postgresql_schema.htm

A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions. Schemas are analogous to directories at the operating system level, except that schemas cannot be nested. PostgreSQL statement CREATE SCHEMA creates a schema.

Best Answer

Related Solutions

[GIS] Problem loading OS OpenData multipoint shapefile into PostGIS

[GIS] shp2pgsql: how to know the schema.table

Related Question