PostGIS Remote Import

Import spatial data from external PostGIS databases.

Overview

PostGIS remote import allows you to connect to external PostgreSQL/PostGIS databases and import spatial tables as datasets in Aurora GIS.

Connection Setup

Create Connection

  1. Navigate to PostGIS import page

  2. Click “New Connection”

  3. Enter connection details:

    • Host: Database server address

    • Port: Database port (default: 5432)

    • Database: Database name

    • Username: Database username

    • Password: Database password

  4. Test connection

  5. Save connection

Connection Management

  • Save Connections: Store credentials securely (encrypted)

  • Test Connections: Verify connectivity before import

  • Delete Connections: Remove saved connections

Import Process

Step 1: Select Connection

Choose a saved PostGIS connection or enter new connection details.

Step 2: Browse Database

Browse available schemas and tables:

  • Schemas: List of database schemas

  • Tables: Spatial tables in selected schema

  • Columns: Table columns and geometry information

Step 3: Configure Import

Set import options:

  • Schema: Source schema name

  • Table: Source table name

  • Geometry Column: Geometry column name (auto-detected)

  • ID Column: Primary key column (optional)

  • Update Mode: Replace, append, or upsert

Step 4: Execute Import

Import can be:

  • Materialized: Copy data to local database

  • Foreign Table: Create foreign table (read-only, live connection)

Import Modes

Materialized Import

Full data copy to local database:

  • Pros: Fast queries, no external dependency

  • Cons: Data duplication, requires refresh for updates

  • Use Case: Static datasets, analysis workflows

Foreign Table Import

Live connection to external database:

  • Pros: Always current, no data duplication

  • Cons: Requires external connection, slower queries

  • Use Case: Frequently updated data, large datasets

Update Modes

Replace

Replace all existing data:

  • Delete existing data

  • Import all source data

  • Use for complete refresh

Append

Add new data to existing:

  • Keep existing data

  • Add new records

  • Use for incremental updates

Upsert

Update existing, insert new:

  • Requires key columns

  • Updates matching records

  • Inserts new records

  • Use for incremental updates with changes

Scheduled Imports

Set up recurring imports:

  1. Configure import

  2. Set schedule:

    • Daily: Run at specified time

    • Weekly: Run on specified day

    • Monthly: Run on specified date

  3. Configure update mode

  4. Save schedule

Refresh Import

Manually refresh existing imports:

  1. Navigate to import history

  2. Select import to refresh

  3. Click “Refresh”

  4. System re-imports data using original settings

Connection Security

Credential Storage

  • Passwords encrypted in database

  • Secure connection testing

  • Access control per user

Network Security

  • Use SSL connections when available

  • Configure firewall rules

  • Use VPN for remote databases

Example: Materialized Import

{
  "connection_id": 1,
  "schema": "public",
  "table": "parcels",
  "geometry_column": "geom",
  "id_column": "parcel_id",
  "update_mode": "replace",
  "materialize": true
}

Example: Foreign Table Import

{
  "connection_id": 1,
  "schema": "public",
  "table": "parcels",
  "geometry_column": "geom",
  "materialize": false
}

Troubleshooting

Common Issues

Connection failed

  • Verify host, port, database name

  • Check network connectivity

  • Verify credentials

  • Check firewall rules

Table not found

  • Verify schema name

  • Check table exists

  • Verify user permissions

Geometry column not detected

  • Ensure PostGIS extension enabled

  • Check geometry column type

  • Verify spatial reference system

Import timeout

  • Check table size

  • Use materialized import for large tables

  • Consider filtering data