Importing PostGIS Spatial Database

Overview

PostGIS is an extension to the PostgreSQL object-relational database system which allows GIS objects to be stored in the database. PostGIS includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects.

Importing PostGIS Spatial Geodatabase

In the Import or Multiple Data Import > Add dialog box, set the Format drop-down list to PostGIS. <Auto detect format> does not detect PostGIS spatial databases. Click the Click to select database link to open the Browse PostGIS dialog box.

postgis-import

The Browse PostGIS Database dialog box has options to create, edit and delete connection parameters to PostGIS servers. It also stores recently used and saved MAP Datasets.

postgis-browse

PostGIS Database Connection

In MAPublisher, apply the same connection parameters as in the PostGIS database so a connection can be made.

postgis-create-connection

Select Tables

Feature classes are referred to as tables using PostGIS. In the Select Tables dialog box,  directly import one or more tables or build SQL queries to select attributes of tables. To directly select tables, click the names of the table to import—selected tables are highlighted. To deselect a table, click the selected name again.

postgis-select-tables

Specify Spatial Filter

A spatial filter consists of a rectangle that defines the extents of imported data. Use extents of an existing MAP Layer from the drop-down list or enter new coordinates values manually. When setting points manually, enter two opposite corner coordinates that represent the actual extent of the data. When MAP Locations exist on the document, it is possible to use them to specify corner coordinates. If required, click the Input Coordinate System link to use an alternate coordinate system for the inputs.

specify_spatial_filter

The Relationship type affects how the spatial filter boundary interacts with imported features:

Intersects: import objects that intersect the extents of the spatial filter

Contains: import objects fully contained within the extents of the spatial filter

Crop to Filter: includes only features that are cropped to the spatial extent

Create SQL Select Query

An SQL statement can be built to limit the number of objects and attributes imported from a single feature class. The SELECT clause should always contain one and one only "Shape" object (actual spatial feature). When possible, use the operator button to add formatted query statements (including spaces), to limit the typing errors.

create_sql_selectquery

Example SQL select queries:

Example

SQL Select Query

In feature class "FC_Point", import all points and the "Valve" attribute (other attributes are not imported).

SELECT FC_Point.Shape, FC_Point.Valve_ID FROM FC_Point

In feature class "FC_Line", import lines (vector only) that have the attribute "Id" equals to 1, 2, 3 or 4.

SELECT FC_Line.Shape  FROM FC_Line WHERE FC_Line.Id IN (1,2,3,4)

 

It is recommended to use the Validate button to verify the query syntax prior to import.

 

Multiple feature classes can be imported using multiple SQL statements separated by a semi-colon (;) as long as all classes share the same source coordinate system. SQL queries can also be used to import a feature class and the attribute information from a related non-spatial table (see previous section). To perform SQL queries on multiple feature classes with different source coordinate systems, use the Multiple Data Import and add the feature classes in several Add sessions (see Multiple Data Import).

 

Recently used SQL queries are saved and accessed by clicking the Recent button. The number of recent queries saved is set in the MAPublisher Preferences on Spatial Database > General Spatial Database Editor.

 

 

Copyright © 1995-2020 Avenza Systems. All rights reserved.

 

Last updated: 4/12/2019