Load vector data

Prerequisites

Before you load vector data, you must run the following command in your database to create the ganos_geometry extension:

CREATE EXTENSION ganos_geometry CASCADE;

shp2pgsql command-line tool

The shp2pgsql command-line tool converts an Esri Shapefile into an SQL file. This lets you load the data into Ganos.

• Syntax

  Note

  • If you have PostGIS installed, the shp2pgsql tool is included by default. You can download the installation package for your operating system from the official PostGIS website and follow the installation guide.

  • If you use a Linux operating system, you can install PostGIS by using a package manager. For example, on CentOS, run the yum -y install postgis command to get the shp2pgsql tool.

  shp2pgsql -s <srid> -c -W <charset> <path_to_shpfile> <schema>.<table_name> | psql -d <dbname> -h <host> -U <user_name> -p <port>

• Parameters

  Parameter	Description	Example
  -s <srid>	The spatial reference ID.	4490
  -c	Creates a new table. You can also use other modes: -a: Appends data to an existing table. -d: Drops the table before loading data. -p: Creates only the table schema without writing spatial data.	N/A
  -W <charset>	The character set of the shapefile.	"GBK" or "UTF8"
  <path_to_shpfile>	The path to the shapefile. The file must be accessible to the shp2pgsql command-line tool.	/home/roads.shp
  <schema>.<table_name>	The name for the new geometry table.	public.roads
  -d <dbname>	The database name.	mydb
  -h <host>	The database instance address.	pgm-xxxxxxx.pg.rds.aliyuncs.com
  -U <user_name>	The username.	my_name
  -p <port>	The port number.	5432

• Example

  shp2pgsql -s 4490 -c -W "GBK" /home/roads.shp public.roads | psql -d mydb -h pgm-xxxxxxx.pg.rds.aliyuncs.com -U my_name -p 5432

  Note

  To avoid repeated password prompts for scripted batch imports, you can set the PGPASSWORD environment variable: export PGPASSWORD=my_pass.

ogr2ogr command-line tool

ogr2ogr is a vector data conversion tool provided by GDAL/OGR. It supports common vector data formats such as Esri Shapefile, MapInfo, and FileGDB. For more information about vector data formats, see Vector data types.

• Syntax

  Note

  • If you have GDAL/OGR installed, the ogr2ogr tool is included by default. You can download the installation package for your operating system from the official GDAL/OGR website and follow the installation guide.

  • If you use a Linux operating system, you can install GDAL/OGR by using a package manager. For example, on CentOS, run the following commands to get the ogr2ogr tool.

    sudo yum install epel-release
    sudo yum install gdal gdal-devel
    ogr2ogr --version

  ogr2ogr -nln <table_name> -f PostgreSQL PG:"dbname='<dbname>' host='<host>' port='<port>' user='<user_name>' password='<password>'" -lco SPATIAL_INDEX=GIST -lco FID=<FID_NAME> -overwrite "<PATH_TO_FILE>" -nlt GEOMETRY

• Parameters

  Parameter	Description	Example
  -nln <table_name>	The name of the vector table.	roads
  -f PostgreSQL PG:"dbname='<dbname>' host='<host>' port='<port>' user='<user_name>' password='<password>'"	The PostgreSQL connection string.	dbname='mydb' host='pgm-xxxxxxx.pg.rds.aliyuncs.com' port='5432' user='my_name' password='my_pass'
  -lco SPATIAL_INDEX=GIST	Creates a GiST spatial index.	N/A
  -lco FID=<FID_NAME>	Specifies the name of the feature ID column.	fid
  -overwrite	Overwrite mode. You can also use -append to append data to an existing table.	N/A
  <PATH_TO_FILE>	The path to the vector data file. Ensure that the ogr2ogr command-line tool can access this path.	/home/roads.shp
  -nlt GEOMETRY	Specifies the geometry type as GEOMETRY. This is recommended when you load polygons to prevent errors that can occur when a file contains both MultiPolygon and non-MultiPolygon types.	N/A

• Example

  ogr2ogr -nln roads -f PostgreSQL PG:"dbname='mydb' host='pgm-xxxxxxx.pg.rds.aliyuncs.com' port='5432' user='my_name' password='my_pass'" -lco SPATIAL_INDEX=GIST -lco FID=fid -overwrite "/home/roads.shp" -nlt GEOMETRY

QGIS desktop tool

QGIS, formerly known as Quantum GIS, is a user-friendly open-source desktop application. It supports various vector, raster, and database formats as data sources. QGIS also provides features for data visualization, management, editing, analysis, and map composition.

1. Create a connection to Ganos.

   1. In the Browser panel, right-click PostgreSQL and select New Connection....

   2. Enter the required parameters.

      For example, for Name, enter ganos-geometry. For Host, enter the public endpoint of your instance, such as pgm-xxx.pg.rds.aliyuncs.com. For Port, enter 5432. For Database, enter ganos_geometry. For SSL mode, select disable.

      The following table describes the parameters.

      Parameter	Description
      Name	A custom name for the connection.
      Host	The IP address of the database cluster or the public endpoint of PolarDB. This value is available in the console.
      Port	The port number of the database cluster. This value is available in the console.
      Database	The name of the database that you want to connect to.
      SSL Mode	Specifies whether to use an SSL-encrypted connection.

   3. Click Test Connection. In the Enter Credentials dialog box, enter your Username and Password, and then click OK. A success message appears if the connection is established.

   4. After the connection test succeeds, click OK.

2. Load the vector data.

   1. From the menu bar, choose Database > DB Manager....

   2. In the database connection dialog box, select the data source to import and click Import Layer/File....

   3. In the Import Vector Layer dialog box, select the vector data to import, configure the import options, and then click OK.