All Products
Search
Document Center

ApsaraDB RDS:Load vector data

Last Updated:Sep 22, 2023

This topic describes how to load a vector data file into Ganos by using shp2pgsql, ogr2ogr, or QGIS.

Before you begin

Before you load a vector data file, run the following command on your database to create the ganos_geometry extension:

CREATE EXTENSION ganos_geometry CASCADE;

Load vector data by using shp2pgsql

shp2pgsql is a command line tool that is used to convert Esri shapefiles into SQL files. For more information about shp2pgsql, see Chapter 4. Data Management. After the conversion, you can load the SQL files into Ganos.

  • Syntax

    Note
    • shp2pgsql is built in PostGIS. If you have installed PostGIS, shp2pgsql is automatically installed. You can download the installation package that is suitable for your operating system from the PostGIS official website and follow the installation guide to install PostGIS.

    • If you use a Linux operating system, you can use a package manager to install PostGIS. For example, in CentOS, you can run the yum -y install postgis command to install 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 value

    -s <srid>

    The spatial reference system identifier (SRID) of the vector data file.

    4490

    -c

    Creates a table.

    You can also use the following parameters:

    • -a: appends data to a table.

    • -d: deletes a table before data is loaded.

    • -p: creates a schema in SQL without writing spatial data.

    None

    -W <charset>

    The character set that is used by the shapefile.

    GBK or UTF8

    <path_to_shpfile>

    The storage path of the shapefile. The storage path must be accessible from the shp2pgsql command line tool.

    /home/roads.shp

    <schema>.<table_name>

    The name of the geometry table that you created.

    public.roads

    -d <dbname>

    The name of the database.

    mydb

    -h <host>

    The endpoint of the instance on which the database resides.

    pgm-xxxxxxx.pg.rds.aliyuncs.com

    -U <user_name>

    The username of the account.

    my_name

    -p <port>

    The port number.

    5432

  • Example

    5432shp2pgsql -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

    If you want to import multiple vector data files at a time by using a script, run the export PGPASSWORD=my_pass command to set the PGPASSWORD environment variable to my_pass.

Load vector data by using ogr2ogr

ogr2ogr is a command line tool that is provided by Geospatial Data Abstraction Library (GDAL) and OpenGIS Simple Features Reference Implementation (OGR) to convert data. ogr2ogr supports common vector data types, such as Esri ShapFile, MapInfo, and FileGDB. For more information about ogr2ogr, see Vector drivers.

Note

Before you load vector data by using ogr2ogr, make sure that GDAL supports the PostGIS vector driver.

  • Syntax

    Note
    • ogr2ogr is built in GDAL. If you have installed GDAL, ogr2ogr is automatically installed. You can download the installation package that is suitable for your operating system from the GDAL official website and follow the installation guide to install GDAL.

    • If you use a Linux operating system, you can use a package manager to install GDAL. For example, in CentOS, you can run the following commands to install 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 value

    -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 information that is used to connect to the PostgreSQL database.

    dbname='mydb' host='pgm-xxxxxxx.pg.rds.aliyuncs.com' port='5432' user='my_name' password='my_pass'

    -lco SPATIAL_INDEX=GIST

    The generalized search tree (GiST) spatial index that you want to create.

    None

    -lco FID=<FID_NAME>

    The ID of the specified feature.

    fid

    -overwrite

    The rewrite mode that you want to use.

    You can also select the -append mode.

    None

    <PATH_TO_FILE>

    The storage path of the vector data file. Make sure that the storage path is accessible from the ogr2ogr command line tool.

    /home/roads.shp

    -nlt GEOMETRY

    Specifies the geometry data type. If the vector data file contains polygon data, we recommend that you specify this parameter. This prevents errors that occur if the vector data file contains not only data of the MultiPolygon type but also data of other types.

    None

  • 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

Load vector data by using QGIS

QGIS was known as Quantum GIS. It is a user-friendly, open source desktop tool that supports various vector and raster formats. It allows you to use databases as data sources. In addition, it allows you to view, manage, and analyze data and to prepare maps. For more information, see QGIS.

  1. Establish a connection to Ganos.

    1. In the Browser window, right-click PostgreSQL and select New Connection... to create a connection.image.png

    2. Configure the required parameters.

      image.png

      The following table describes the parameters.

      Parameter

      Description

      Name

      The name of the connection.

      Host

      The IP address of your RDS instance or the public endpoint of your RDS instance or PolarDB cluster. You can obtain this information from the Alibaba Cloud Management Console.

      Port

      The port number of your RDS instance. You can obtain this information from the Alibaba Cloud Management Console.

      Database

      The name of the database to which you want to connect.

      SSL Mode

      Specifies whether to establish an encrypted connection based on SSL.

    3. Click Test Connection. In the Enter Credentials window that appears, specify the Username and Password parameters, click OK, and then wait until the connection is established.

    4. After the test is successful, click OK.

  2. Load vector data.

    1. Choose Database > DB Manager...

    2. In the dialog box that appears, select the data source from which you want to import vector data and click Import Layer/File....

    3. In the dialog box that appears, specify the vector data that you want to import and configure the import options. Then, click OK.