How to import a shapefile to PostGIS with ogr2ogr?

How to import a shapefile to PostGIS with ogr2ogr?

As you discovered by trial and error, there were few nagging issues you needed to fix, the last of which was resolved using ogr2ogr’s -nlt GEOMETRY * argument.

What kind of geospatial data can ogr2ogr support?

ogr2ogr is supporting 5 types of geospatial data : “ESRI Shapefile”, “TIGER”, “MapInfo File”, “GML”, “PostgreSQL” ( Even if not mentionned in GDAL documentation, GeoJSON is actually supported ). ogr2ogr is an effective tool to use when you need to upload large shapefiles into a PostgreSQL/PostGIS database (compare to QGIS DB Manager).

How to use ogr2ogr as a command line tool?

This guide is intented to GIS analyst using QGIS who need to use ogr2ogr command line easily on a Windows environment. What is ogr2ogr ? ogr2ogr is a Command Line tool provides by GDAL, a Geospatial Data Abstraction Library. Traditionally GDAL used to design the raster part of the library, and OGR the vector part for Simple Features.

Can a raster2pgsql be run in PostGIS?

Just like shp2pgqsl, raster2pgsql comes packaged with a PostGIS bundle installation. Just like shp2pgsql, this outputs a SQL file that you can then run in PostGIS. OpenStreetMap is a slightly specific case, but worth going through here separately, as it is widely used.

How to import spatial data into PostGIS database?

The most common data format for spatial data has traditionally been the ESRI shapefile. shp2pgsql is a command line tool to import ESRI shapefiles to the database. Under Unix, you can use the following command for importing a new PostGIS table:

Why do I get error import shapefile in PostGIS?

ERROR 1: Terminating translation prematurely after failed translation of layer world_boundaries (use -skipfailures to skip errors) I also tried to import it through the GUI shp2pgsql and I get the following error:

How to test PostGIS import with shp2pgsql?

I would have been stumped by your second error for awhile, but by testing your SQL user with a different import utility (shp2pgsql), which was smart, you got a more precise error message and gave your SQL user necessary privileges on the spatial_ref_sys table.

How to run ogr2ogr command as Sudo?

At first, consider executing your ogr2ogr command as sudo to rule-out permissions issues until after you know for certain your script is working as intended. Also as @user1919 realized, make sure your SQL user has sufficient privileges on both the database targeted by your script, as well as the spatial_ref_sys table.

Why does SQL server-ogr2ogr does not create tables?

It works fine exept that it does not load the GEOGRAPHY colum (area), it remains NULL. Im using the file provided in the guide and the user in the connection string is the admin so it is not a permission problem. Thank you all in advance. Solved it adding -lco UPLOAD_GEOM_FORMAT=wkt to the command, its related to some geometry formating.

When to add geoengine modules with OpenERP getting below?

When I’m trying to add CamptoCamp GeoEngine Modules with OpenERP getting below find error:

How is ogr2ogr used to convert GIS data?

Ogr2ogr is the swiss-army knife when it comes to conversion of GIS data. It is part of the Geospatial Data Abstraction Library and provides an easy way to convert data between common storage formats: GeoJSON, Shapefile, PostGIS and others.

How to insert a GeoJSON point into a JSON column?

The following example inserts a GeoJSON point into a JSON column and afterwards updates the geometry column with the above mentioned function. Thanks for contributing an answer to Stack Overflow!

How to import ISO2 features in ogr2ogr?

Using some of the options from ogr2ogr, you will import only the features from SUBREGION=2 (Africa), and the ISO2 and NAME attributes, and rename the feature class to africa_countries:

How to import and export vector data from PostGIS?

In this recipe, you will use the popular ogr2ogr GDAL command for importing and exporting vector data from PostGIS. Firstly, you will import a shapefile in PostGIS using the most significant options of the ogr2ogr command.

What are the parameters of the ogr2ogr command?

The ogr2ogr command has many options and parameters; in this recipe, you have seen some of the most notable ones such as -f to define the output format, -t_srs to reproject/transform the dataset, and -sql to define an (eventually spatial) query in the input OGR dataset.

What is the error for numeric field overflow?

ERROR: numeric field overflow DETAIL: A field with precision 19, scale 11 must round to an absolute value less than 10^8. CONTEXT: COPY test, line 1, column TLID: “142691303.00000000000”

How can I import data from OpenStreetMap to PostGIS?

OpenStreetMap: OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. Many people refer to OSM as the Wikipedia of maps. If you seek easy ways to get an extract of the data, you can check for GeoFabrik for Shapefiles or osmdata.xyz for GeoPackages. OpenStreetMap data from Africa.

Why do I get error when creating a PostgreSQL database?

So it seems I need to use the parameter: -nlt MULTIPOLYGON But when I do so I get another error, which doesn’t make any sense to me: ERROR 1: PostgreSQL driver doesn’t currently support database creation. Please create database with the `createdb’ command.

Which is the default solution for PostGIS shapefile?

* Note the recommendation in @LeeHachadoorian’s comment that -nlt PROMOTE_TO_MULTI be used as a default solution, rather than nlt GEOMETRY, as the former promotes best practice in addition to ancillary benefits.

Why is PostgreSQL driver not supporting database creation?

ERROR 1: PostgreSQL driver doesn’t currently support database creation. Please create database with the `createdb’ command. PostgreSQL driver failed to create PG:host=localhost user=my_user_name dbname=my_database password=password -nlt POLYGON

Which is the best tool to import data to PostGIS?

The tool I use most often for loading data to PostGIS is probably ogr2ogr. It is a very powerful tool to convert data into and from PostGIS to almost all possible vector data formats. GDAL/OGR is a software package that powers a great variety of different geospatial software tools and it comes with QGIS already installed on the server computer.

How can I import raster data into PostGIS?

For loading raster to PostGIS, two main options are raster2pgsql tool or GDAL with Python. Just like shp2pgqsl, raster2pgsql comes packaged with a PostGIS bundle installation. Just like shp2pgsql, this outputs a SQL file that you can then run in PostGIS.

How is geometry data assigned in ogr2ogr?

The geometry data is assigned the projected SRID EPSG:2249 (assumed to be the SRID in which the source data is supplied, but this is not checked by OGR2OGR). The following code listing takes data from the precincts.shp ESRI shapefile and loads it into the sql2012 SQL Server instance running on localhost .

What kind of srid is required for OpenGIS?

GEOMETRYCOLLECTION(POINT(2 3),LINESTRING(2 3,3 4)) The OpenGIS specification also requires that the internal storage format of spatial objects include a spatial referencing system identifier (SRID). The SRID is required when creating spatial objects for insertion into the database.

How to read geodatabase In Postgres with ogr2ogr?

My set up of ogr2ogr looks like this: When I run ogrinfo on the test dataset I get error: Unable to open datasource ‘C:\\david recks.gdb’ When I run ogr2ogr on my set up I get error: Unable to open datasource ‘host=pg.rambck.local dbname=projects…….’

Why does PostGIS not reference geodatabase in file path?

One thing that can trip people up is that the FileGDB drivers don’t pay attention to Feature Datasets inside the GDB, so you shouldn’t reference them in your file path at all. I don’t think this is an issue in your situation, but could be confusing to others.

When to use promote to multi in PostGIS?

Starting with GDAL 1.10, PROMOTE_TO_MULTI can be used to automatically promote layers that mix polygon or multipolygons to multipolygons, and layers that mix linestrings or multilinestrings to multilinestrings. Can be useful when converting shapefiles to PostGIS and other target drivers that implement strict checks for geometry types.

Why is PostgreSQL Driver failed to create PG?

PostgreSQL driver failed to create PG:host=localhost user=my_user_name dbname=my_database password=password -nlt POLYGON But it gets loaded using the shp2pgsql GUI. Comment of @elrobis enabled this to finally work. Data loaded in db correctly!

How to update a pgSQL table using ogr2ogr?

So, to update with new datas, I would like to do so: BUT pgsql doesn’t like that at all! I want it to replace data with the same PK by the new one, but it wants to keep the old one and take the new one, which is not possible of course, so the message is:

How to use PostGIS in Ogr and PostgreSQL?

The PostgreSQL/OGR/PostGIS bump: (as Holly Orr says, ) POSTGRESQL + GDAL (OGR) ~ POSTGIS = OGR_FDW POSTGRESQL FOREIGN DATA WRAPPER Doesn’t require PostGIS to use, but will expose spatial columns as PostGIS geometry if PostGIS is installed. Many thanks to Paul Ramsey and Even Rouault. it’s like getting a hug from an ogre

Which is foreign data wrapper for PostGIS geometry?

– PostgreSQL foreign data wrapper for connecting to Oracle databases. Will expose Oracle SDO_Geometry as PostGIS geometry. ogr2ogr is a popular command- line tool used for loading data from one vector source to another (including PostGIS), popular companion of PostGIS.

How to parse geometry WKB with OGR-geographic?

OGR does not want to parse it. Keeps giving the following error: Internally, PostGIS stores geometries in a binary specification, but it is queried and viewed outside as a hex-encoded string. There are two popular variations of well-known binary (WKB): EWKB (via ST_AsEWKB) – an extended WKB specification designed by PostGIS.

Where can I find ogr2ogr in Fw tools?

Ogr2ogr is available as part of the FW Tools Toolkit: http://fwtools.maptools.org/ . It is usually called from the command line using the following syntax: If we want to specify a destination table, we can use the -nln table option.