shp2pgsql(1)

💖 shp2pgsql(1) 💖

okay bestie, welcome to the shp2pgsql page from the PostGIS manual 🦋 — it's the shapefile-to-PostGIS loader and honestly? she's iconic.

✨ NAME ✨

shp2pgsql — shapefile to postgis loader. that's it, that's the name, no notes 💅

🌸 SYNTAX 🌸

shp2pgsql [options] shapefile [schema.]table

look at her. short. sweet. does exactly what it says. ✨

🦋 DESCRIPTION 🦋

The shp2pgsql data loader converts ESRI Shape files into SQL that's suitable for insertion into a PostGIS/PostgreSQL database. it's giving "glow up your shapefile into a real database table" energy fr fr 🔥

Version: 1.1.5 (2006/10/06) ⭐

🎀 USAGE 🎀

okay so the <shapefile> is the name of the shape file, without any extension information. for example, 'roads' would be the name of the shapefile comprising the 'roads.shp', 'roads.shx', and 'roads.dbf' files. like, she knows the gang, you don't gotta introduce them all 😭

The <tablename> is the (optionally schema-qualified) name of the database table you want the data stored in in the database. and inside that table, the geometry goes in the 'geo_value' column by default. periodt 💅

💅 OPTIONS 💅

The loader has several operating modes distinguished by command line flags. it's giving "pick your lane" 🌈

⚠️ Note that -a, -c, -d and -p are mutually exclusive. pick ONE, bestie, they do not share, no cap 💀

• -d — drops the database table before creating a new table with the data in the Shape file. it's giving "clean slate, out with the old" 🙈
• -a — appends data from the Shape file into the database table. note that to use this option to load multiple files, the files must have the same attributes and same data types. like they gotta match, she's not doing the work of figuring out your chaos 😤
• -c — creates a new table and populates it from the Shape file. this is the default mode. the main character 👑
• -p — only produces the table creation SQL code, without adding any actual data. use this if you need to completely separate the table creation and data loading steps. very "structure now, vibes later" ✨
• -D — use the PostgreSQL "dump" format for the output data. this can be combined with -a, -c and -d. it is much faster to load than the default "insert" SQL format. use this for very large data sets — big data, big slay 🚀
• -w — output WKT format, instead of WKB. note that this can introduce coordinate drifts due to loss of precision. she's warning you, listen to her 🫶
• -e — execute each statement on its own, without using a transaction. this allows loading of the majority of good data when there are some bad geometries that generate errors. note that this cannot be used with the -D flag as the "dump" format always uses a transaction. sorry babe, that combo is a no 💀
• -s [<FROM_SRID>:]<SRID> — creates and populates the geometry tables with the specified SRID. if FROM_SRID is given, the geometries will be reprojected. reprojection cannot be used with -D. glowed up but with rules ✨
• -G — use the geography type instead of geometry. Geography is used to store lat/lon data. at the moment the only spatial reference supported is 4326. literally just the one, bestie 💅
• -g <geometry_column> — specify the name of the geometry column (mostly useful in append mode). name your child, it's fine 🎀
• -k — keep identifiers case (column, schema and attributes). note that attributes in Shapefile are usually all UPPERCASE. so like, don't be shocked 😭
• -m <filename> — specify a file containing a set of mappings of (long) column names to 10 character DBF column names. the content of the file is one or more lines of two names separated by white space and no trailing or leading space:

COLUMNNAME DBFFIELD1\n
AVERYLONGCOLUMNNAME DBFFIELD2\n

etc. yes the \n is right there in the example, keep it exact bestie 💖

• -i — coerce all integers to standard 32-bit integers, do not create 64-bit bigints, even if the DBF header signature appears to warrant it. she said "we're keeping it cute and small" 🌸
• -S — generate simple Geometries instead of MULTIgeometries. Shape files don't differ between LINESTRINGs and MULTILINESTRINGs, so shp2pgsql generates MULTILINESTRINGs 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. so like, know what you're doing, okay? 😭
• -W <encoding> — specify the character encoding of Shapefile's attributes. if this option is used the output will be encoded in UTF-8. glow up the encoding, slay ✨
• -I — create a GiST index on the geometry column. index her, she deserves it 💅
• -N <policy> — specify NULL geometries handling policy (insert,skip,abort). three doors, choose wisely 🚪
• -T <tablespace> — specify the tablespace for the new table. Indexes will still use the default tablespace unless the -X parameter is also used. The PostgreSQL documentation has a good description on when to use custom tablespaces. like, go read it, i'll wait 📖
• -X <tablespace> — specify the tablespace for the new table's indexes. this applies to the primary key index, and the GIST spatial index if -I is also used. tag team, iconic 🤝
• -? — display version and usage information. the "who am i" button 🦋

🌈 INSTALLATION 🌈

To compile the program from source, simply run "make" in the source directory. then copy the binary in your shell search path (or wherever you like, she's not judging 🫶). This text is also available as a man page in the ../doc/man/ directory, ready for copying it into the manual search path on unixoid systems. very plug-and-play of her 💖

🔥 EXAMPLES 🔥

An example session using the loader to create an input file and uploading it might look like this:

# shp2pgsql shaperoads roadstable roadsdb > roads.sql
# psql -d roadsdb -f roads.sql

A conversion and upload can be done all in one step using UNIX pipes. one line, no waiting, ate 💅

# shp2pgsql shaperoads roadstable roadsdb | psql -d roadsdb

👑 AUTHORS 👑

Originally written by Jeff Lounsbury <jeffloun@refractions.net>. Improved and maintained by Sandro Santilli <strk@kbt.io>. Includes small contributions and improvements by others. community effort, we love to see it 🫶

This application uses functionality from shapelib 1.2.9 by Frank Warmerdam <warmerda@gdal.velocet.ca> to read from ESRI Shape files. shoutout to the library that makes it all happen 🌸

🎀 SEE ALSO 🎀

• pgsql2shp(1) — the reverse direction, she does that too 💫

More information is available at http://postgis.net — go look, it's free, it's iconic ✨

okay that's the whole page bestie, no skips, no summaries, every single flag accounted for 💖

sign-off: you've been reading shp2pgsql(1) from the PostGIS manual, version 1.1.5 dated 2006/10/06 — go load some shapefiles, you got this 🦋💅✨