AnalyticDB:Load vector data

Preparations

Before you load a vector data file, execute the following statement on your database to create an extension named ganos_geometry:

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 GanosBase.

• Syntax

  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 system identifier (SRID) of the shapefile.	4490
  -c	Creates a table. You can also use the following parameters: -a: appends data to an existing table. -d: deletes an existing 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 want to create.	public.roads
  -d <dbname>	The name of the database.	mydb
  -h <host>	The endpoint of the AnalyticDB for PostgreSQL instance on which the database resides.	pgm-xxxxxxx.pg.rds.aliyuncs.com
  -U <user_name>	The name of the database account that is used to connect to the AnalyticDB for PostgreSQL instance.	my_name
  -p <port>	The port number that is used to connect to the AnalyticDB for PostgreSQL instance.	3433

• 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 3433

  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 anout vector data types, see Vector drivers.

• Syntax

  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 parameters that are used to connect to the AnalyticDB for PostgreSQL instance.	dbname='mydb' host='pgm-xxxxxxx.pg.rds.aliyuncs.com' port='3433' user='my_name' password='my_pass'
  -lco SPATIAL_INDEX=GIST	The type of the spatial index that you want to create. A value of GiST specifies the generalized search tree (GiST) spatial index.	None
  -lco FID=<FID_NAME>	The name of the feature ID (FID) column.	fid
  -overwrite	The rewrite mode that you want to use. You can also use the append mode by specifying the -append parameter.	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 /home/land.tab
  -nlt GEOMETRY	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 data of the MULTIPOLYGON geometry type and other geometry types.	None

• Example

  ogr2ogr -nln roads -f PostgreSQL PG:"dbname='mydb' host='pgm-xxxxxxx.pg.rds.aliyuncs.com' port='3433' 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, formerly known as Quantum GIS, is a user-friendly, open source desktop tool that supports various vector and raster formats. It allows you to use databases as data sources, view, manage, and analyze data, and prepare maps. For more information, see QGIS.

1. Establish a connection to GanosBase.

   1. In the right-side Browser pane, create a PostGIS connection.

   2. Configure the required parameters.

      

      The following table describes the parameters.

      Parameter	Description
      Name	The name of the connection.
      Host	The IP address or public endpoint that is used to connect to the AnalyticDB for PostgreSQL instance. You can obtain the IP address or public endpoint from the AnalyticDB for PostgreSQL console.
      Port	The port number that is used to connect to the AnalyticDB for PostgreSQL instance. You can obtain the port number from the AnalyticDB for PostgreSQL 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. Then, wait until the connection is established.

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.