Importing data using GIS clients

Many GIS software packages can directly connect to databases for reading and writing the data; from our perspective, they are just more database clients. In this section, we'll have a quick look at the very well-known QGIS and the certainly less popular but very powerful Manifold GIS.

Both can export data to databases and read it back. QGIS has a specialized PostGIS export module called SPIT; Manifold's export facility is built in into the GUI and follows the same export routines as other formats handled by the software.

Exporting a shapefile to PostGIS using QGIS and SPIT

QGIS offers a PostGIS export module called SPIT. You can access it by choosing Database\Spit\Import Shapefiles to PostGIS:

SPIT's GUI is very clear and easy to understand. You can import many files at once and you can specify the destination schema and table names. If required you can change the default Spit's geometry name (the_geom) to your liking. SRID also can be changed, but it will be applied to all the imported files. Once you provide all the required information and click on OK, a progress window is displayed and the data is exported to PostGIS.

In newer versions of QGIS, you may not find SPIT anymore. In such cases, you can use a DbManager instead.

Exporting shapefile to PostGIS using QGIS and DbManager

Before using DbManager, you should load a shapefile you want to import to a database.

When ready, launch DbManager by going to Database\DB Manager\DB Manager. When the DbManager UI displays, locate your database, expand its node and select the schema you want to import the data to and then click the Import button (arrow down). You should be prompted with an import dialog; the Input dropdown lets you choose the layer to import.

Once you are happy with the import options (you may want to click the Update options button to populate the dialog), click on OK. When the import finishes you should see a confirmation dialogbox:

Exporting spatial data to PostGIS from Manifold GIS

In order to export the data, Manifold needs to import it internally first. This means that we do not export a particular format but rather we simply export spatial data. Once you bring  a shapefile into Manifold, MapInfo TAB or MIF, SQL Server spatial table or any other supported vector format, exporting it to PostGIS is exactly the same for all of them.

In order to export a vector component, right-click on its node in the project tree and choose the export option. You will see an export dialog, where you should pick the appropriate export format; in this scenario, you need to choose Data Source. You will then be presented with a data source setup window, where you can either pick an existing connection or configure a new one:

Once you choose the appropriate connection, you can then set up the actual export parameters:

You can choose which columns you want to export, and the name of the identity, geometry, and version columns.

There is a minor inconvenience with the exporter: it does not allow for adjusting of the destination schema and always exports to the public schema.

Manifold tries to find the PostGIS projection that best matches the Manifold projection. Unfortunately, it is not always possible as Manifold as such does not rely on EPSG coordinates systems definitions, but rather uses its internal mechanisms for handling projections. If Manifold does not match the PostGIS side projection, you can select it manually.

Export dialogue also offers an option to transform coordinates upon export and create indexes and CREATE/UPDATE triggers when data gets to the database.

In order to enable PostgreSQL connections in Manifold, you may have to copy some PgSQL DLLs over to the Manifold installation directory. The exact information on how to do this can be easily found at georeference.org, the manifold user community forum.