Data Access → Drivers → PostGIS

The PostGIS native data access driver allows applications to access data stored in a PostgreSQL system with the the geo-spatial extension PostGIS enabled.

This section describes the design and implementation issues of this driver and how it can be used by applications.

The PostGIS driver implements all the data access foundation classes as showned in the following class diagram.

The above implementation allows one to to obtain a PostGIS data source from the data source abstract factory using the “POSTGIS ” identifier as is shown in the code snippet bellow:

std::auto_ptr<te::da::DataSource> ds = te::da::DataSourceFactory::make("POSTGIS");
or
te::da::DataSource* ds = (te::da::DataSourceFactory::make("POSTGIS")).get();

Now you need set the connection information and them open Postgis datasource:

std::map<std::string, std::string> dsInfo;
 
dsInfo["PG_HOST"] = "localhost";   // or "localhost";
dsInfo["PG_USER"] = "postgres";
dsInfo["PG_PASSWORD"] = "secreto";
dsInfo["PG_DB_NAME"] = "mygisdb";
dsInfo["PG_CONNECT_TIMEOUT"] = "4"; 
dsInfo["PG_PORT"] = "5432";
 
ds->setConnectionInfo(dsInfo);
ds->open();
 
te::da::DataSource* ds = te::da::DataSourceFactory::open(dsInfo);
 
//Using connection info as a string... OLD
std::string dsInfoStr = "PG_HOST=localhost&PG_USER=postgres&PG_PORT=5432&PG_DB_NAME=mygisdb&PG_USER=postgres&PG_PASSWORD=secreto&PG_CONNECT_TIMEOUT=4"
te::da::DataSource* ds = te::da::DataSourceFactory::open(dsInfoStr);

When connecting to a PostGIS data source you need to provide some basic information about the data source location. The driver accepts the following case sensitive parameters name when openning a database connection:

• Pool configuration information:
  • PG_MIN_POOL_SIZE: it indicates the minimum number of connections in the pool. (Optional)
  • PG_MAX_POOL_SIZE: it indicates the maximum number of connections in the pool. (Optional)
  • PG_INITIAL_POOL_SIZE: it indicates the initial number of connections opened by the pool. It must be a value between MinPoolSize and MaxPoolSize. (Optional)
  • PG_MAX_IDLE_TIME: the maximum time in seconds that a connection can be in the pool without being used. (Optional)
• Server connection information:
  • PG_HOST: the server name or address. (Mandatory if not using hostaddr).
  • PG_HOST_ADDR: the server address (use this for avoiding name look-ups). (Mandatory if not using host)
  • PG_PORT: port number to connect to the server host, or socket file name extension for Unix-domain connections. The default port is 5432. (Optional)
  • PG_DB_NAME: database name. (Mandatory)
  • PG_USER: user name. (Mandatory)
  • PG_PASSWORD: User password. (Optional)
  • PG_CONNECT_TIMEOUT: maximum wait time for a connection to be opened, in seconds. Zero or not specified means wait indefinitely. It is not recommended to use a timeout of less than 2 seconds. (Optional)
  • PG_OPTIONS: command-line options to be sent to the server. (Optional)
  • PG_SSL_MODE: this option determines whether or with what priority an SSL connection will be negotiated with the server. (Optional)
  • PG_KRBSRVNAME: kerberos service name to use when authenticating with Kerberos 5 or GSSAPI. (Optional)
  • PG_GSSLIB: GSS library to use for GSSAPI authentication. (Optional)
  • PG_CLIENT_ENCODING: the character set encoding used by the client when communicating to the database server. (Optional)
• Table manipulation:
  • PG_HIDE_SPATIAL_METADATA_TABLES: if TRUE the catalog loader will skip spatial metadata tables from the list of available datasets. (Optional)
  • PG_HIDE_RASTER_TABLES: if TRUE the catalog loader will skip raster tables from the list of available datasets. (Optional)
  • PG_HIDE_TABLES: it is possible to provide a list of tables to be hidden from the catalog loader operations. It must be a comma separated list. (Optional)

The PostGIS driver allows applications to create new PostgreSQL databases or drop an existing one.

In order to create a new database you must specify the connection parameters to an auxiliary database. This database will be used to stablish a temporary connection. All the connection info in the previous section can be used. Besides that information you must specify some parameters for the creation of the new database:

• PG_NEWDB_NAME: the name of the new database. (Mandatory)
• PG_NEWDB_TEMPLATE: the name of the template database to be used during the creation of the new one. (Mandatory)
• PG_NEWDB_OWNER: the owner of the new database. (Optional parameter)
• PG_NEWDB_ENCODING: the database encoding. (Optional parameter)
• PG_NEWDB_TABLESPACE: the name of the tablespace that will be associated with the new database. (Optional parameter)
• PG_NEWDB_CONN_LIMIT: how many concurrent connections can be made to this database. As default in PostgreSQL -1 means no limit. (Optional parameter)

If you intend to connect to the database after its creation with different parameters from the auxiliary database, you must specify new parameters with the prefix: PG_NEWDB_. For instance, if you want a different user to be used in the new data source, you can specify: PG_NEWDB_USER = differnt-user-name. All parameters showned in the Connection Parameters section can be prefixed.

To drop a database you must specify the connection parameters to an auxiliary database. This database will be used to stablish a temporary connection. All the connection info in the previous section can be used. Besides that information you must specify:

• PG_DB_TO_DROP: the name of the database to be dropped. (Mandatory)

To check if a database already exists you must specify:

• PG_CHECK_DB_EXISTENCE: the name of the database to be checked for. (Mandatory)

Besides this parameter you must also provide an auxiliary database connection information (as when creating or dropping). The following code snippet shows how to check for a database named mygisdb:

std::map<std::string, std::string> dsInfo;
 
dsInfo["PG_HOST"] = "localhost";
dsInfo["PG_USER"] = "postgres";
dsInfo["PG_PASSWORD"] = "secreto";
dsInfo["PG_DB_NAME"] = "template_postgis";
dsInfo["PG_PORT"] = "5432";
dsInfo["PG_CHECK_DB_EXISTENCE"] = "mygisdb";
 
if(!te::da::DataSource::exists("POSTGIS", dsInfo))
  std::cout << "yes! it exists!";
else
  std::cout << "no! it doesn't exist!";

This driver will make the following mapping between PostgreSQL data types and TerraLib data types:

• … TO BE DONE

The reverse mapping:

• … TO BE DONE

The PostGIS SQLVisitor class re-implements some methods from te::da::SQLVisitor class in order to deal with PostgreSQL specific types: byte array, date and time, mbr and geometries.

TO DO alguns formatos de tabela não suportam indices espaciais - documentar

The Connection class models a physical connection to a PostgreSQL database. It is designed to work together with a connection pool and so it has some special attributes uded by the pool: if the connection is in use by a thread (m_inuse) and a timestamp with the last time the connection was used (m_lastuse). Connections can be also associated to data source transactors. When some code request a transactor from the data source interface an available connection from the pool is associated to the transactor. This connection is used by the transactor in order to make operations in the database.

The ConnectionPool class implements a connection pool for the PostGIS driver. Each PostGIS data source instance can have its own connection pool. In other words, there can be a connection pool for each PostGIS data source object. This way you can have different configurations for each opened PostGIS data source. This gives maximum flexibility when working with more than one PostgreSQL DBMS.

PostGIS doesn't store the spatial data as an OGC WKB. At the time PostGIS started, OGC SFS specification only had support to 2D geometries. So, the PostGIS team has an extended format that embedds the srid and adds support for 3D and 4D geometries. Nowadays OGC SFS supports 3D and 4D geometries but lack the srid in the WKB representation. PostGIS also adopted a different geometry code from OGC and ISO.

The EWKBWriter and EWKBReader classes allows one to handle PostGIS geometries.

The EWKBWriter class can be used to serialize a geometry to the PostGIS Extended-WKB format.

The EWKBReader class can be used to deserialize a geometry from a valid PostGIS Extended-WKB format.

TerraLib allows support for all geometric types from PostGIS.

CONSIDERACOES

CONSIDERACOES

CONSIDERACOES

The PostgreSQL/PostGIS driver also implements all the raster foundation classes as showned in the following class diagram.

The above implementation allows one to access raster stored in a PostgreSQL data source in PostGIS Raster format.

std::map<std::string, std::string> rinfo;
 
rinfo["PG_RASTER_NAME"] = "mosaic_brasil";
rinfo["PG_HOST"] = "localhost";
rinfo["PG_USER"] = "postgres";
rinfo["PG_PASSWORD"] = "secreto";
rinfo["PG_DB_NAME"] = "mygisdb";
rinfo["PG_PORT"] = "5432";
 
te::rst::Raster* inraster = te::rst::RasterFactory::open("POSTGIS", rinfo);

ESTA SECAO PRECISA SER REVISADA

A PostGIS data source can be obtained from the data source factory using the “POSTGIS ” identifier as is shown in the code snippet bellow:

...
 
te::da::DataSource* ds = te::da::DataSourceFactory::make("POSTGIS");
 
...

An existing POSTGIS data source (my_postgis_db) can be a accessed using the connection information as below:

...
 
// This code opens a connection to PostGis data source using the minimal server connection information 
std::map<std::string, std::string> connInfo;
 
connInfo["host"] = "atlas.dpi.inpe.br" ;  // it can be localhost
connInfo["user"] = "postgres";
connInfo["password"] = "xxxxxxx";
connInfo["dbname"] = "my_postgis_db";
connInfo["connect_timeout"] = "4";    
 
ds->open(connInfo);
 
...

Now, that a connection to a PostGIS DataSource has been established it is possible to query the DataSets available in this connection as can be seen in Data Access documentation.

//...
 
// This code create a new POSTGIS data source using the server connection information 
std::map<std::string, std::string> connInfo;
connInfo["host"] = "atlas.dpi.inpe.br" ;  // it can be localhost
connInfo["user"] = "postgres";
connInfo["password"] = "xxxxxxx";
connInfo["dbname"] = "postgres";
connInfo["connect_timeout"] = "4"; 
 
// newdb parameters added to connection info
connInfo["newdb_name"] = "my_newdb";
connInfo["newdb_connect_timeout"] = "4";
connInfo["newdb_template"] = "template_postgis";
connInfo["newdb_tablespace"] = "pg_default";
connInfo["newdb_owner"] = "postgres";
connInfo["newdb_encoding"] = "'LATIN1'";
connInfo["newdb_connection_timeout"] = "4";
 
te::da::DataSource *dsNewPostGis = te::da::DataSource::create("POSTGIS", connInfo);
 
connInfo["dbname"] = "my_newdb";
 
dsNewPostGis->open(connInfo);
 
//...

-------------------------------------------------------------------------------
Language          files     blank   comment      code    scale   3rd gen. equiv
-------------------------------------------------------------------------------
C++                  19      1513       540      4650 x   1.51 =        7021.50
C/C++ Header         22      1226      1583      1689 x   1.00 =        1689.00
XML                   1         0         0        27 x   1.90 =          51.30
-------------------------------------------------------------------------------
SUM:                 42      2739      2123      6366 x   1.38 =        8761.80
-------------------------------------------------------------------------------

Besides the C++ code there is also…

• We need to add support to all geometry types from postgis. Today we support just the basic point, polygons, linestrings and multis. We need to add curve and compound as multisurface and multicurve! We need also to add geography!!!!!
• as geometrias precisam tratar o caso do box estar presente na geometria!!!!! Hummm!!!
• We have a problem with inverse mapping for datetime values!!!!!!

Additional efforts may be taken to make the driver more flexible:

TO BE DONE