Questions tagged [shp2pgsql]

Command line tool for converting shapefiles into database tables.

Filter by
Sorted by
Tagged with
0 votes
0 answers
22 views

shp2pgsql not writing all the features and INSERT INTO statements written incorrectly

My shapefile contains 2400 features. When I use shp2pgsql my output contains 111 INSERT INTO statements, so not every feature is getting written for some reason. Additionally, the SQL is written fine ...
MKF's user avatar
  • 571
1 vote
0 answers
29 views

PostGIS: Export a buffer with shp2pgsql [closed]

I want to create a buffer around a line: SELECT ST_Buffer(wkb_geometry, 50, 'endcap=flat join=round') FROM linien WHERE route = 'Rundweg' and export it with shp2pgsql: pgsql2shp -f <path to output ...
radagastDerBraune's user avatar
0 votes
0 answers
55 views

Stop processes if pgsql2shp/shp2pgsql fails

I have a process where data is extracted from PostgreSQL (PostGIS) to shapefiles. The data is used as input creating new data (shapefiles) through a program outside PostgreSQL. The new data is then ...
Reje's user avatar
  • 1
1 vote
1 answer
112 views

How to edit primary keys from shp2pgsql-gui output?

I have a newbie question. I am importing multiple shapefiles into shp2pgsql-gui and creating a postgis database. While creating the database, shp2pgsql adds a new gid column for each layer that ...
pygisuser's user avatar
2 votes
1 answer
54 views

shp2pgsql special characters Error

sorry it's google translate An error occurred while importing data into PostGIS using shp2pgsql. Among multiple shp files, an error occurs only in data with "" special characters. The ...
Jihyun Kim's user avatar
0 votes
0 answers
253 views

Import shapefile to PostgreSQL using Java

I have to import a shapefile to PostgreSQL via a Java application. There are few codes that I've found and the code I've tried: package shptodb; import java.io.BufferedReader; import java.io....
Fatima22's user avatar
1 vote
1 answer
1k views

PostGIS Column not found in geometry_columns table

I've just loaded a bunch of shapefiles into postgis using psql2shp (from the command line). All of the shapefiles appeared to have been inserted correctly and the table I created is populated. But the ...
marty_c's user avatar
  • 197
0 votes
0 answers
52 views

Changing only schema, from public to another, with shp2pgsql

For example shp2pgsql myGeoms.shp | more produce a SQL script that creates and inserts values into a public.MagicName table. I need to preserve MagicName and change only public to mySchema... Is it ...
Peter Krauss's user avatar
  • 2,292
-1 votes
1 answer
126 views

How to add US State NHGIS historical GIS data to PostGIS? ERROR: INSERT has more target columns than expressions

I am trying to add historical data extracted from NHGIS to PostGIS, but I get the INSERT has more target columns than expressions error when I run the code below: "C:\psql.exe" -c "...
iDevPy's user avatar
  • 99
1 vote
1 answer
2k views

shp2pgsql available in postgis:13-3.1-alpine but no in postgis:13-3.1 docker image

From the postgis/postgis:13-3.1-alpine docker image I can push 3 shapefiles into my database using shp2pgsql in an init script. But from the postgis/postgis:13-3.1 image I cannot: (...) db_1 | /docker-...
swiss_knight's user avatar
  • 10.3k
0 votes
0 answers
30 views

pgAdmin3 shp2pgsql tools: missing, how to obtain [duplicate]

We currently have installations of postgreSQL DBs of 9.3 and 9.4. On our clients in the folder C:\Program Files\PostgreSQL[9.3/9.4]\bin I cannot find the shp2pgsql tools. I thought these older ...
user9491577's user avatar
5 votes
2 answers
951 views

Inserting only subset of columns of shapefile to existing PostGIS table with different column names?

The situation So I have a shapefile with several columns: id|name_en|name_es|name_de|name_fr|... I also have an existing PostGIS table on my remote server: county_id|county_name|county_name_de|...
JoeBe's user avatar
  • 1,230
3 votes
1 answer
1k views

Importing .shp file into PostGIS with the function shp2pgsql

I am trying to import a .shp file into PostgreSQL, but so far no success. After completing the setup, I installed the PostGIS extension and finally went for some geo exploration. But, when I try to ...
Guilherme Viegas's user avatar
0 votes
0 answers
727 views

Install shp2pgsql without PostGIS

I have Django app that handles an uploaded shapefile and extracts the geom using shp2pgsql. As the Django app and the PostgreSQL(PostGIS) are in different docker containers, I cannot call shp2pgsql ...
WebNovice's user avatar
  • 101
2 votes
0 answers
472 views

shp2pgsql drop database table option doesn't work if table doesn't exist yet

Context I have a PostGIS database. If I use: shp2pgsql -I -s 4326 -d "my_shapefile.shp" myschema.vectordata \ | psql -h localhost -U username -d my_database -v ON_ERROR_STOP=1 To inject data ...
swiss_knight's user avatar
  • 10.3k
0 votes
1 answer
131 views

ST_Intersect is giving wrong polygon back

I imported the french IRIS dataset by shp2pgsql. Added LAMB93 to the PostGIS spatial_ref_sys. shp2pgsql -W latin1 -I -s 97527:4326 CONTOURS-IRIS.shp iris20 | psql -d iris20 -v ON_ERROR_STOP=1 So ...
kampfkeks18's user avatar
2 votes
1 answer
430 views

Insert data in a PostgreSQL database using shp2pgsql in peer mode (without password for user postgres)?

Context I wish to add some data that are for the moment in ESRI shapefiles into a PostgreSQL database. To do so, I am using the shp2pgsql (RELEASE: 2.4.3 (r16312)) tool from an Ubuntu server (18.04 ...
swiss_knight's user avatar
  • 10.3k
2 votes
1 answer
181 views

Batch import of DEM tiles (with shapefiles) into PostGIS creates non continuous contour lines

I want to import a large amount of ASTER DEM data into PostGIS (covering Europe). I use this script: https://wiki.openstreetmap.org/wiki/Contour_relief_maps_using_mapnik#Importing_ASTER_GDEMv2_data ...
Andreas's user avatar
  • 73
1 vote
0 answers
499 views

shp2pgsql no more AddGeometryColumn() function?

Problem: I encounter a fresh error when using shp2pgsql on Ubuntu 18.04. This commands worked a few months ago: $ sudo shp2pgsql -s 4326 /path/to/myshapefile.shp | psql -h localhost -p 5432 -d ...
swiss_knight's user avatar
  • 10.3k
1 vote
0 answers
1k views

Import SHPs and DBFs to PostgreSQL in pgAdmin 4 on a Mac

I am receiving the following error when trying to import a dbf file into PostgreSQL (v 12, pgAdmin 4) using the Boundless/OpenGeo Suite for Mac pgShapeLoader GUI (which was recommended for Mac users ...
Kristen G.'s user avatar
  • 1,100
3 votes
2 answers
496 views

Add/update shapefile to existing PostgreSQL database in QGIS

I've an exiting state level PostgreSQL database with some layer's and I need to update an additional data to the existing database. Ex: I've a layer called "Road_NW" need to add additional ...
Sudhakar P's user avatar
0 votes
1 answer
570 views

Datatype changed to qlonglong from integer when export to shp and SQL

I have a view that basis on the table 'mytable' with these columns: The view created as CREATE or REPLACE VIEW viewbox.myview AS SELECT mytable.h AS height, mytable.geom FROM mytable; with columns ...
Jane's user avatar
  • 1,066
1 vote
1 answer
187 views

shp2pgsql Load Data Type Inconsistent

I'm having trouble when trying to upload a shape file in postgresql/PostGIS. There's a field with type "date", but actually there'r two elements with content : '20012060' and it returns ERROR when I ...
Wendel cunha souto's user avatar
0 votes
1 answer
120 views

shp2pgsql creates one dimensional shapes

I use shp2pgsql to import an .shp ESRI shapefile into PostgreSQL database. I use brew installed shp2pgsql. I used this command first: shp2pgsql -I -s 4326 /path/to/shape_file.shp public.os_gb | psql -...
sinemy's user avatar
  • 3
-2 votes
2 answers
271 views

Using original shapefile vs osm for routing in South Australia using pgRouting?

I'm looking at building a simple routing application using pgRouting. I'm running on Windows 10 at the moment but could migrate to Windows Server at some point. I'm trying to figure out how best to ...
Fr Jeremy Krieg's user avatar
10 votes
1 answer
2k views

Using shp2pgsql instead of ogr2ogr to import shapefile to PostGIS? [closed]

I may very well be doing something wrong here, but: If I import some shapefile to a PostGIS database using shp2pgsql, I have to first figure out the SRID/EPSG of that shapefile. I think this is, at ...
jac's user avatar
  • 341
1 vote
1 answer
447 views

Why does shp2pgsql generate a MultiPolygon from a Polygon shapefile?

I created a shapefile with a single Polygon in it using QGIS. When I test it with ogrinfo it says it is of type "Polygon". When I convert to GeoJSON using ogr2ogr it also comes out as Polygon. But ...
Koz's user avatar
  • 47
1 vote
2 answers
3k views

Using Python Subprocess module to import shapefile into postgres

I am trying to use your mentioned code..but getting an error. The code used is mentioned below: import os import subprocess cmd = 'shp2pgsql -s 4326 E:\Forest\ForestFire\28052018\...
Aroop Chakraborty's user avatar
1 vote
1 answer
278 views

Catching shp2psql errors from python's subprocess module

I'm importing data into postgresql with shp2psql and pyhton's subprocess module. command = 'shp2pgsql -s {SRID} -a -g geom -W "UTF-8" {SHP_path} {table_name} -I | ' \ 'psql -h {hostname} -p ...
Basile's user avatar
  • 3,543
1 vote
0 answers
64 views

Shapefile export issue [closed]

Is there a geometry extension that can be enabled in PostGIS, in order to save Shapefile data in it? I imported Shapefile data in to PostGIS using shp2pgsql plugin, having the notion that PostGIS ...
IYKE's user avatar
  • 107
3 votes
0 answers
1k views

"DBF file can not be opened" error using shp2pgsql

I am trying to import a shapefile using shp2pgsql. I wrote this command: shp2pgsql -I -s 2154 -W "LATIN1" W:\IGN\BDPARCELLAIRE-RGF93-LB93-MAJ2013-V4\BDPV_1-2_SHP_LAMB93_D01\COMMUNE.shp CADASTRE....
maneva's user avatar
  • 31
1 vote
0 answers
946 views

Can't open shp2pgsql on Windows 10

I am trying to open shp2pgsql.exe to import shapefile to PostgreSQL. But something happens because shp2pgsql never displays an opening window. For a very little time it seems it will open a window. ...
Maurocrispin's user avatar
1 vote
0 answers
941 views

shp2pgsql throws "ERROR: current transaction is aborted, commands ignored until end of transaction block"

I am trying to upload a shapefile to postgres using the command : shp2pgsql -I -s 4326 "/Users/abc/Landmark.shp" table_name | psql -U user_name --dbname=db_name I get the error : ERROR: ...
Rajeev A Nair's user avatar
3 votes
1 answer
684 views

PostGIS inserting shapefile with shp2pgsql, wrong coordinate system?

I'm trying to load a shapefile (link) into my database with shp2pgsql. shp2pgsql -D -W UTF8 -I -s 4326 scbel01012011_gen13.shp bel | psql -d stat no problem so far, but when I'm trying to get some ...
kampfkeks18's user avatar
2 votes
1 answer
2k views

How to specify a password with shp2pgsql using CentOS?

I'm developing a python script that uses shp2sql via subprocess.call(). The string I'm using works fine on Linux Mint: shp2pgsql -s 4326 ../data/download.shp ccd_hoods_new | psql -h hostname -d ...
DPSSpatial_BoycottingGISSE's user avatar
5 votes
1 answer
3k views

How to run shp2pgsql in python without reporting every row loaded?

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 ...
DPSSpatial_BoycottingGISSE's user avatar
1 vote
2 answers
1k views

Change decimal commas to points in QGIS created dbf-file

There is a bug in the QGIS desktop application where two merged shapefiles produce a dbf-file with commas as decimal separators. I need to have this merged file imported to PostGIS database, but the ...
Gemmu's user avatar
  • 113
0 votes
0 answers
97 views

Data limits & runtime of shp2pgsql? [duplicate]

I am using shp2pgsql in a bash script to import shapefiles into postgres. I can successfully do this for shapefiles with hundreds of records however shapefiles with tens of thousands of records (in ...
Don's user avatar
  • 805
0 votes
1 answer
711 views

Shapefile failing to import using shp2pgsql?

I'm trying to import a shapefile (boundary.shp) using shp2pgsql. I'm using the following query shp2pgsql -I -s 4326 boundary ch01.test_boundary > psql -h 127.0.0.1 -d spatial_database -U postgres ...
Don's user avatar
  • 805
1 vote
0 answers
204 views

Syntax erro at or near "[" shp2pgsql

I am trying to import a shapefile into my database, but I got a error message when I typed the following command: shp2pgsql -I -s 0 /home/IC/Ferramenta_carga/shp/tl_2016_us_state/tl_2016_us_state.shp ...
Gabriel Limoni's user avatar
5 votes
3 answers
3k views

OSGeo4W does not add bin directory to Windows PATH

I am trying to run the following shp2pgsql cmd using the OSGeo4W shell: shp2pgsql -s 4269 -g geom_4269 -I -W "latin1" "tl_2012_states" staging.tl_2012_states | psql -h localhost -p 5432 -d dvdtwo -U ...
Matt's user avatar
  • 1,191
1 vote
1 answer
481 views

Importing a shapefile with no specifed projection to Postgres?

I am working with a shapefile that does not come with a .prj file - only .shp, .shx and .dbf files. The file's accompanying README says: ALL DATA IS IN A GEOGRAPHIC COORDINATE SYSTEM (NOT PROJECTED) ...
Richard's user avatar
  • 3,289
0 votes
1 answer
341 views

MULTIPOLYGONM vs MULTIPOLYGON with shp2pgsql

I have already seen this answer I am running the following: shp2pgsql -I -s 3401 -t 2D theShapeFile.shp table_name > file_name.sql And the response I see is: Shapefile type: PolygonM Postgis ...
Ask613's user avatar
  • 243
3 votes
1 answer
557 views

shapefile driver table name mismatch for ogr vs. shp2pgsql

I have a shapefile which if I convert to sql statements using shp2pgsql return table name all in lowercase, for e.g. 'liberia'. When I read the same shapefile using ogr and use layer.GetName() it ...
Kabira  K's user avatar
  • 115
1 vote
0 answers
69 views

PostGIS Batch File generation error

I am following the link : http://sharpmap-postgis.blogspot.in/ I have successfully setup my database as mentioned in the above link. I have downloaded and extracted all the source code from the link ...
Shreee's user avatar
  • 53
5 votes
0 answers
400 views

pgAdmin does not integrate shp2pgsql in menu [duplicate]

Recently I updated my pgAdmin to 4, and I still want to use PostGIS Shapefile Import/Export Manager. I did not find any method to make this happen and even if shp2pgsql-gui still works outside pgadmin,...
marina_d's user avatar
2 votes
0 answers
799 views

Password Authentication failed when using shp2psgsql

I want to load shp data in my postgis enabled database. I am typing the command: shp2pgsql -s 4326 -G -I "shapefile_name" public.table_name | psql -h localhost -p 5432 -d gis -U postgres and I am ...
dimitris lepipas's user avatar
2 votes
1 answer
2k views

Importing shapefile issue PostGIS, no import made or recognized reference

I am doing an exercise where I am about to load shapefiles into a PostGIS-database. I am doing it from an already created database and schema, this will mean that the only new item is the table that ...
MichaelR's user avatar
  • 542
3 votes
1 answer
2k views

How to use shp2pgsql to import a very large shapefile into PostGIS?

I need to import very large datasets into the Postgis server so I need to use the -D option in the prompt. Problem is that I'm getting an error no matter what I do. The command lines I'm using are: ...
Luffydude's user avatar
  • 2,308
4 votes
3 answers
939 views

What is the easiest way to avoid shapefile columns being imported as PostGIS `numeric(p,s)` data type?

Considering the number of shapefiles I've imported to PostGIS (using shp2pgsql or ogr2ogr), I'm a little surprised I haven't come across this before. But I have a shapefile that has numeric columns ...
Lee Hachadoorian's user avatar