# rds-spatial

Part of **RDS**

# ApsaraDB RDS Spatial Data Processing Console Guide

## Operations Overview

| Operation | Console Entry Path | Prerequisites | Description |
|----------|-------------------|---------------|-------------|
| Connect via GIS Tools | Console > RDS > Instances > Connection Information | Running RDS/PolarDB instance with GanosBase enabled; endpoint, port, DB name, credentials | Connect desktop GIS applications (QGIS, uDig, OpenJump) to GanosBase for visualization and analysis |
| Load Spatial Data | Console > RDS > Instances > Select Instance > Database Management > SQL Console | PolarDB for PostgreSQL with Ganos_Raster extension; OSS access if loading from cloud storage | Load raster data (e.g., GeoTIFF) from local or OSS sources into GanosBase |
| Register OSS Data | Console > RDS > Databases > Connect to Database | PolarDB cluster with GanosBase; OSS bucket in same region; AccessKey pair | Register OSS Shapefiles as foreign tables for direct spatial querying |
| Raster Data Management | Console > RDS > Instances > Select Instance > Databases > SQL Console | RDS for PostgreSQL instance; OSS access with valid credentials | Import raster from OSS, build pyramids, clip regions, and query dimensions via SQL console |
| Grid Data Management | N/A (SQL-based) | None | Create grid codes using GeoSOT and perform location-based queries (no GUI steps) |
| Geospatial Data Publishing | GeoServer Web Interface > Stores > Add new Store | Running GeoServer; Ganos database with geometry/raster data | Publish geometric or raster layers as OGC web map services |
| Point Cloud Table Creation | Console > RDS > Instances > Select Instance > Databases > SQL Console | RDS for PostgreSQL instance; sufficient privileges | Create point cloud tables for 3D spatial data storage and analysis |
| Enable GPU-accelerated Computing | Console > RDS > Instances > Select Instance > Database Management > SQL Console | GPU-equipped PolarDB node; ganos_raster extension | Activate GPU acceleration for raster processing workloads |
| Create Spatial Indexes | N/A (SQL-based) | Table with spatial column (GeomGrid/H3Grid) | Create GiST indexes for efficient 2D spatial queries |
| Optimize Trajectory Performance | ApsaraDB RDS > Instances > Select Instance > Databases > Manage Trajectory Data | RDS with Ganos extension; SQL access | Apply indexing, compression, and attribute tuning for trajectory performance |
| Enable Spatio-Temporal Optimization | RDS Console > Instances > Select Instance > Database Management > SQL Console | RDS with GanosBase; table with GiST index | Activate two-phase query optimization to reduce false positives |
| Enable Spatio-Temporal Parallel Query | Console > RDS > Instances > Select Instance > Database Management > SQL Console | RDS with GanosBase; config file access | Enable parallel execution for CPU-intensive spatio-temporal queries |

## Step-by-Step Instructions

### Connect via GIS Tools

**Navigation**: Console > RDS > Instances > Connection Information

**Prerequisites**:
- A running ApsaraDB RDS or PolarDB instance with GanosBase enabled
- Public endpoint and port number of the instance
- Database name, username, and password

1. In QGIS, open the **Create a New PostGIS Connection** dialog
   - Element: **Create a New PostGIS Connection** (link) — main content area

2. Fill in connection parameters: Name, Host (endpoint), Port, Database
   - Element: **Test Connection** (button) — bottom of dialog box
   - Notes: Set SSL mode to "disable" as shown in documentation

3. After successful connection, browse layers by double-clicking
   - Element: **Double-click a layer** (text_input) — layer list panel

4. In uDig, choose **Layer** > **Add Data** from top menu
   - Element: **Layer** (menu) — top navigation panel

5. Select **PostGIS** and click Next
   - Element: **PostGIS** (radio) — dialog box

6. Enter **Host**, **Port**, **User Name**, **Password**
   - Element: **Host** (text_input) — connection information form
   - Element: **Port** (text_input) — connection information form
   - Element: **User Name** (text_input) — connection information form
   - Element: **Password** (text_input) — connection information form

7. Select target database from **Database** dropdown
   - Element: **Database** (dropdown) — connection information form

8. In OpenJump, go to **File** > **Data Store Layer**
   - Element: **File** (menu) — top menu bar

9. Click **Add** in Connection Manager
   - Element: **Add** (button) — Connection Manager window

10. Fill **Connection Configuration Dialog** with driver=PostGIS, server, port, etc.
    - Element: **Connection Configuration Dialog** (dialog) — center of screen

11. Click **Confirm** to establish connection
    - Element: **Confirm** (button) — bottom right of dialog

| Parameter | Type | Required | Options/Values | Description |
|-----------|------|----------|----------------|-------------|
| Name | text | No | — | Custom connection name |
| Host | text | Yes | — | IP or public endpoint from RDS console |
| Port | text | Yes | — | Database port from console |
| Database | text | Yes | — | Target database name |
| SSL mode | dropdown | No | disable | Set to "disable" for compatibility |
| User Name | text | Yes | — | Database username |
| Password | text | Yes | — | Database password |
| driver | dropdown | Yes | PostGIS | Must be PostGIS for GanosBase |

### Load Spatial Data

**Navigation**: Console > ApsaraDB RDS > Instances > Select Instance > Database Management > SQL Console

**Prerequisites**:
- PolarDB for PostgreSQL instance with Ganos_Raster extension installed
- Raster file accessible from instance (local or OSS in same region)

1. Navigate to **SQL Console** under Database Management
   - Element: **SQL Console** (link) — left navigation panel

2. Run `CREATE EXTENSION Ganos_Raster WITH SCHEMA public CASCADE;`
   - Element: **Run** (button) — top-right corner of SQL editor

3. Create raster table: `CREATE TABLE raster_table(id integer, raster_obj raster);`
   - Element: **Run** (button) — top-right corner of SQL editor

4. Insert raster using `ST_ImportFrom` with OSS URI
   - Element: **Run** (button) — top-right corner of SQL editor
   - Notes: Replace `<AccessKeyId>:<AccessKeySecret>@<endpoint>/<bucket>/<path>` with actual values

### Register OSS Data

**Navigation**: Console > ApsaraDB RDS > Databases > Connect to Database

**Prerequisites**:
- PolarDB for PostgreSQL cluster with GanosBase
- OSS bucket containing complete shapefile (.shp, .shx, .dbf) in same region
- AccessKey ID and Secret with OSS read permissions

1. Install FDW extension: `CREATE EXTENSION ganos_fdw WITH schema public CASCADE;`
   - Element: **CREATE EXTENSION ganos_fdw WITH schema public CASCADE;** (text_input) — SQL editor

2. Register shapefile: `SELECT ST_RegForeignTables('OSS://<access_id>:<secret_key>@[<endpoint>]/<bucket>/path/to/file');`
   - Element: **ST_RegForeignTables** (text_input) — SQL editor

3. Verify registration: `SELECT foreign_table_name FROM information_schema.foreign_tables ORDER BY foreign_table_name ASC;`
   - Element: **SELECT foreign_table_name FROM information_schema.foreign_tables...** (text_input) — SQL editor

4. Query foreign table: `SELECT fid, ST_AsText(geom), name, age, height FROM poly WHERE fid = 1;`
   - Element: **SELECT fid, ST_AsText(geom)...** (text_input) — SQL editor

5. Import to local table: `CREATE TABLE poly_db AS SELECT * FROM poly;`
   - Element: **CREATE TABLE poly_db AS SELECT * FROM poly;** (text_input) — SQL editor

### Raster Data Management

**Navigation**: Console > RDS > Instances > Select Instance > Databases > SQL Console

**Prerequisites**:
- ApsaraDB RDS for PostgreSQL instance
- OSS bucket with raster data (e.g., GeoTIFF)
- Valid AccessKey ID and Secret

1. Open **SQL Console** for the instance
   - Element: **SQL Console** (link) — main content area

2. Enable raster extension: `CREATE EXTENSION Ganos_Raster WITH SCHEMA public CASCADE;`
   - Element: **Run** (button) — top-right corner of SQL editor

3. Create raster table: `CREATE TABLE raster_table(id integer, raster_obj raster);`
   - Element: **Run** (button) — top-right corner of SQL editor

4. Import raster from OSS: `INSERT INTO raster_table VALUES(1, ST_ImportFrom('chunk_table','OSS://...'));`
   - Element: **Run** (button) — top-right corner of SQL editor

5. Query dimensions: `SELECT ST_Height(raster_obj), ST_Width(raster_obj) FROM raster_table WHERE id = 1;`
   - Element: **Run** (button) — top-right corner of SQL editor

6. Build pyramid: `UPDATE raster_table SET raster_obj = ST_BuildPyramid(raster_obj) WHERE id = 1;`
   - Element: **Run** (button) — top-right corner of SQL editor
   - Notes: May take several minutes for large rasters

7. Clip region: `SELECT ST_Clip(raster_obj, 0, '((128.980,30.0),(129.0,30.2))', 'World') FROM raster_table WHERE id = 1;`
   - Element: **Run** (button) — top-right corner of SQL editor

8. Drop extension when done: `DROP EXTENSION Ganos_raster CASCADE;`
   - Element: **Run** (button) — top-right corner of SQL editor
   - Notes: Irreversible — back up data first

### Geospatial Data Publishing

**Navigation**: GeoServer Web Interface > Stores > Add new Store

**Prerequisites**:
- Running GeoServer 2.23.2 instance
- Ganos database with geometry or raster data loaded
- Database connection parameters (host, port, name, user, password)

1. In GeoServer, click **Stores** in left navigation
   - Element: **Stores** (link) — left-side navigation pane

2. Click **Add new Store**
   - Element: **Add new Store** (button) — main content area

3. For geometry data, select **PostGIS** radio button
   - Element: **PostGIS** (radio) — store type selection

4. For raster data, select **GanosRaster(PG/PolarDB)** in Raster Data Sources
   - Element: **GanosRaster(PG/PolarDB)** (radio) — Raster Data Sources section

5. Fill connection form: host, port, database, schema, user, passwd
   - Element: **Save** (button) — bottom of form

6. For raster, also specify: table, column name, filter (optional), display name
   - Element: **table** (text_input) — form fields
   - Element: **column name** (text_input) — form fields

| Parameter | Type | Required | Options/Values | Description |
|-----------|------|----------|----------------|-------------|
| host | text | Yes | — | Ganos database endpoint |
| port | text | Yes | 5432 | Database port (default: 5432) |
| database | text | Yes | — | Database name |
| schema | text | No | public | Schema containing tables (default: public) |
| user | text | Yes | — | Database username |
| passwd | text | Yes | — | Database password |
| table | text | Yes | — | Table storing raster data |
| column name | text | Yes | — | Raster column name |
| filter | text | No | — | WHERE clause to filter rasters |
| name | text | Yes | — | Display name in GeoServer |

### Point Cloud Table Creation

**Navigation**: Console > ApsaraDB RDS > Instances > Select Instance > Databases > SQL Console

**Prerequisites**:
- ApsaraDB RDS for PostgreSQL instance
- User with privileges to create extensions and tables

1. Navigate to **SQL Console** under selected instance
   - Element: **SQL Console** (link) — left navigation panel

2. Enable PointCloud extension: `CREATE EXTENSION ganos_pointcloud CASCADE;`
   - Element: **Run** (button) — bottom of SQL editor

3. Insert point cloud schema (XML) into pointcloud_formats table
   - Element: **Run** (button) — bottom of SQL editor

4. Create PCPOINT and PCPATCH tables using pcid=1
   - Element: **Run** (button) — bottom of SQL editor

5. Insert test points: `INSERT INTO points ... SELECT ST_MakePoint(1, ARRAY[x,y,z]) ...`
   - Element: **Run** (button) — bottom of SQL editor

6. Aggregate into patches: `INSERT INTO patches SELECT ST_Patch(points) ...`
   - Element: **Run** (button) — bottom of SQL editor

7. Query statistics: `SELECT ST_PatchAvg(patch) FROM patches;`
   - Element: **Run** (button) — bottom of SQL editor

### Enable GPU-accelerated Computing

**Navigation**: Console > ApsaraDB RDS > Instances > Select Instance > Database Management > SQL Console

**Prerequisites**:
- PolarDB for PostgreSQL cluster with GPU-equipped node
- ganos_raster extension installed or installable

1. Connect via **SQL Console**
   - Element: **SQL Console** (link) — Database Management section

2. Install extension if needed: `CREATE EXTENSION ganos_raster CASCADE;`
   - Element: **Run SQL** (button) — SQL Console input area

3. Check GPU availability: `SELECT st_checkgpu();`
   - Element: **Run SQL** (button) — SQL Console input area
   - Notes: Verify output shows GPU properties; troubleshoot driver issues if error appears

4. Enable GPU acceleration: `SET ganos.raster.use_cuda = on;`
   - Element: **Run SQL** (button) — SQL Console input area

5. Confirm setting: `SHOW ganos.raster.use_cuda;`
   - Element: **Run SQL** (button) — SQL Console input area

### Create Spatial Indexes

**Navigation**: N/A (SQL-only operation)

**Prerequisites**:
- Table with GeomGrid or H3Grid column

1. Create GiST index on GeomGrid column:
   ```sql
   CREATE INDEX geomgrid_gist_test_idx ON geomgrid_gist_test USING GIST (code);
   ```

2. Create GiST index on H3Grid column:
   ```sql
   CREATE INDEX h3grid_gist_test_idx ON h3grid_gist_test USING GIST (code);
   ```

### Optimize Trajectory Performance

**Navigation**: ApsaraDB RDS > Instances > Select Instance > Databases > Manage Trajectory Data

**Prerequisites**:
- RDS instance with Ganos extension enabled
- Trajectory table created

1. Create spatial index: `CREATE INDEX tr_spatial_geometry_index ON trajtab USING GIST (st_trajectoryspatial(traj));`

2. Create temporal indexes:
   ```sql
   CREATE INDEX tr_timespan_time_index ON trajtab USING GIST (st_timespan(traj));
   CREATE INDEX tr_starttime_index ON trajtab USING BTREE (st_starttime(traj));
   ```

3. Create spatio-temporal composite index:
   ```sql
   CREATE EXTENSION btree_gist;
   CREATE INDEX tr_traj_test_stm_etm_sp_index ON traj_test USING GIST (st_starttime(traj), st_endtime(traj), st_trajectoryspatial(traj));
   ```

4. Tune string attributes: `SET ganos.trajectory.attr_string_length = 32;`

5. Enable LZ4 compression:
   ```sql
   ALTER DATABASE dbname SET toast_compression_use_lz4 = true;
   ```

### Enable Spatio-Temporal Optimization

**Navigation**: RDS Console > Instances > Select Instance > Database Management > SQL Console

**Prerequisites**:
- RDS for PostgreSQL with GanosBase enabled
- Table with GiST spatial index

1. Open **SQL Console**
   - Element: **SQL Console** (link) — left navigation panel

2. Enable two-phase optimization: `SET rds_enable_gist_refine = on;`
   - Element: **Run** (button) — bottom-right corner of SQL editor
   - Notes: Session-scoped parameter; not visible in pg_settings

### Enable Spatio-Temporal Parallel Query

**Navigation**: Console > ApsaraDB RDS > Instances > Select Instance > Database Management > SQL Console

**Prerequisites**:
- RDS instance with GanosBase extension
- Access to postgresql.conf configuration file

1. Navigate to **SQL Console**
   - Element: **SQL Console** (link) — left navigation panel

2. Edit **postgresql.conf** to set:
```text
   max_parallel_workers = 8
   max_parallel_workers_per_gather = 4
   force_parallel_mode = on
   ```
   - Element: **Edit Configuration File** (button) — main content area
   - Notes: Requires instance restart to take effect

3. Override worker estimate per table: `ALTER TABLE table_name SET (parallel_workers = n);`
   - Element: **Run SQL** (button) — SQL Console input area

## FAQ

Q: Where do I find my RDS instance endpoint and port for GIS connections?
A: In the ApsaraDB RDS console, go to Instances > Select your instance > Connection Information. The public endpoint and port are listed there.

Q: Can I modify spatial indexes after creation?
A: Yes, you can drop and recreate indexes using standard PostgreSQL DDL commands (`DROP INDEX`, `CREATE INDEX`) via the SQL Console.

Q: What happens if I leave the SSL mode field empty in QGIS?
A: The connection may fail. The documentation explicitly states to set SSL mode to "disable" for compatibility with GanosBase.

Q: Do I need to keep the Ganos_Raster extension enabled after importing raster data?
A: Yes, the extension must remain enabled to query or process the raster data. Dropping it will make raster functions unavailable.

Q: Can I connect to GanosBase using GIS tools without public endpoints?
A: Yes, if your GIS client is in the same VPC as the RDS instance, you can use the internal endpoint instead of the public one.

## Pricing & Billing

### Billing Model
per_instance_hour

### Free Tier

### Quota Limits
 100TB

### Billing Notes
1