Load vector data

Before you begin

Before loading vector data, ensure you have created the ganos_geometry extension in your database by running the following command:

CREATE EXTENSION ganos_geometry CASCADE;

shp2pgsql command-line tool

The shp2pgsql command-line tool converts an Esri Shapefile into SQL statements to load 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 instructions.

  • On Linux, you can install PostGIS using a package manager. For example, on CentOS, run the command yum -y install postgis 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 (SRID).	4490
  -c	Creates a new table. You can also use other options: -a: Appends data to an existing table. -d: Drops the table before loading data. -p: Creates only the table schema without loading spatial data.	N/A
  -W <charset>	The character set of the shapefile.	"GBK" or "UTF8"
  <path_to_shpfile>	The file path to the shapefile. The path must be accessible to the shp2pgsql command-line tool.	/home/roads.shp
  <schema>.<table_name>	The name of the geometry table to be created.	public.roads
  -d <dbname>	The name of the database.	mydb
  -h <host>	The public endpoint of the database instance.	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 run bulk imports from a script without password prompts, set the PGPASSWORD environment variable: export PGPASSWORD=my_pass.

ogr2ogr command-line tool

The ogr2ogr tool is a vector data conversion utility provided by GDAL/OGR. It supports common vector data formats, including Esri Shapefile, MapInfo, and FileGDB. For more information about supported formats, see Vector Drivers.

• Syntax

  Note

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

  • On Linux, you can install GDAL 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 (FID) column.	fid
  -overwrite	Replaces the existing table. You can also use -append to append data to an existing table.	N/A
  <PATH_TO_FILE>	The file path to the vector data. The path must be accessible to the ogr2ogr command-line tool.	/home/roads.shp
  -nlt GEOMETRY	Specifies the geometry type. This is recommended when you load polygon data to prevent errors caused by mixed multipolygon and non-multipolygon types in the same file.	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 Quantum GIS) is an open-source desktop application with a user-friendly interface. It supports a wide range of vector and raster formats, as well as databases as data sources. You can use QGIS to visualize, manage, edit, and analyze data, and to create printable maps.

1. Create a Ganos connection.

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

   2. Enter the required parameters.

      For example, set the Name to ganos-geometry and the Host to the public endpoint of your RDS instance, such as pgm-instanceID.pg.rds.aliyuncs.com. Enter 5432 for Port and ganos_geometry for Database. 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 instance or the public endpoint of your RDS or PolarDB instance. You can find this value in the Alibaba Cloud console.
      Port	The port number of the database instance. You can find this value in the Alibaba Cloud console.
      Database	The name of the database to which you want to connect.
      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 confirmation message appears if the connection is successful.

   4. After the connection test succeeds, click OK to create the connection.

2. Load the vector data.

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

   2. In the DB Manager dialog box, select the data source and click Import Layer/File....

   3. In the import dialog box, specify the vector data to import, configure the import options, and then click OK.