View source for OTB-PostGIS Interface

== Introduction ==

PostGIS is a geospatial extension to PostgreSQL which allows very powerful geospatial analysis and storage possibilities. The ability for OTB to communicate to this kind of data base is of major interest.

Although other geospatial databases exist, PostGIS seems to be the more widely accepeted among the open source alternatives. A generic implementation of the OTB/PostGIS interface should make possible to switch data bases without major problem.

== Client library ==

The choice which has been evaluated is [http://pqxx.org/devprojects/libpqxx/doc/stable/html/Reference/ libpqxx], which is a C++ abstraction of the low level lipq C client library distributed with PostGIS.

=== Connecting to a data base ===

Here is an example of establishing a connection to a PostGIS DB called ''testgis'' on the PostGIS server running on the localhost:
 std::stringstream connstring;
 connstring << "host=" << "localhost " << "dbname=" << "testgis " << "user=" << "postgres " ;
 pqxx::basic_connection<pqxx::connect_direct> Conn(connstring.str().c_str());

=== Performing transactions ===

libpqxx uses the concept of transactor in order to perform DB transactions. Luckily, transactors are just functors, so we can create a transactor for performing our custom SQL query like this:

 class PopulateTable : public pqxx::transactor<pqxx::nontransaction>
 {
    pqxx::result m_Result;  
   public:
    PopulateTable() : pqxx::transactor<pqxx::nontransaction>("PopulateTable") {}
    void operator()(argument_type &T)
    {
      m_Result = T.exec("INSERT INTO testtable VALUES ( 1,'pieton 1', GeometryFromText( 'POINT(10 70)', -1 ) );");
    }
    void on_commit()
    {
      std::cout << "\t Table is populated \t"  << std::endl;
    }
 };

So we inheritate from the ''pqxx::transactor'' class and overload the ''()'' operator and the ''on_commit()'' method. The former performs the transaction and the latter is some kind of callback which can eventually operate on the result. As you can see the SQL code is passed to the ''exec()'' method of a transaction ''T''. The example here inserts a point called ''pieton 1'' into a table called ''testtable''. This example is taken from [http://www.davidgis.fr/documentation/win32/html/index.html this PostGIS tutorial].

In order to use this transactor, we do as follows (after the connection to the DB is established):

 Conn.perform(PopulateTable());

And that's it.

== Persistence ==

=== The data types ===

One thing for which the interface between OTB and PostGIS is interesting is the capability to get or store large amounts of data in a spatial database and therefore being able to share these data with desktop GIS systems.

For this matter, readers and writers should be implemented. The OTB data types to handle for these IO tools are the ''otb::VectorData'' and the ''itk::LabelObjectMap''. The latter describes regions with attributes which will be coded as polygons and associated attributes. 

For the ''otb::VectorData'' part, we have a direct mapping between the features (polygons, lines, points, etc.) and the OGC-compliant geometries accepted by PostGIS, so there should not be any major problem.

=== The accessors for the readers/writers ===

In order to perform the connection to the DB (either for reading and writing) we will need a set of accessors, which are analogous to the ''SetFileName()'' method of classical readers/writers. These values are (to be validated):
* data base name
* user name
* host name and port
* table name
* password

We can imagine to keep the ''SetFileName'' accessor and point it to a simple ascii file containing pairs of key and value, so that the end user can easily change configurations.

=== Architecture ===

It would be interesting to have generic access as we have for vector and raster data which are independent of the data base implementation used. Even if here we are exclusively thinking about PostGIS, one may want to use MySQL or Oracle spatial extensions. This should be transparent for the API user. Therefore, we can use a set of DB connectors (PostGIS, MySQL, etc.).

By now, we will only implement the PostGIS, but we have to leave the door open to external contribs!

At the present moment, the otb::GISConnectionImplementation class models a connection to a GIS DB. From it, we have inherited the otb::PostGISConnectionImplementation. This class is responsible for establishing the connection to the DB and performing transations.

Using these connectors, the conversions from/to otb::VectorData or itk::LabelObjectMap can be easily implemented.

== Processing ==

=== Motivation ===

Seeing the simplicity of executing PostGIS code from C++, it would be a pity to limit the OTB-PostGIS interface to persistence. Indeed, transactors could be used as filters or functions (int the OTB sense) acting on SQL tables. If we had this technology, we could be able to do things like this (taken from [http://postgis.refractions.net/documentation/manual-1.3/ch04.html#id2573016 here]).

==== Functions ====

Functions take PostGIS tables and generate a single object output (a numerical value or an object id in the table). The following classes have been implemented:

* otbGISTableFunction.h: base class for applying functions to tables
* otbTransactorGISTableFunction.h: specialization which applies a transactor to a table in order to get a result
* otbPostGISCreateTableTransactor.h: transactor to create a new table
* otbPostGISFromStringTransactor.h: this is a very generic transactor which takes as parameter a std::string containing the SQL query.

===== What is the total length of all roads, expressed in kilometers? =====

 SELECT sum(ST_Length(the_geom))/1000 AS km_roads FROM bc_roads;

===== What is the largest municipality in the province, by area? =====

 SELECT 
  name, 
  ST_Area(the_geom)/10000 AS hectares 
 FROM 
  bc_municipality 
 ORDER BY hectares DESC 
 LIMIT 1;


==== Filters ====

Filters get tables and generate tables or views. We have started implementing an otb::GISTableToGISTableFilter which inherits from otbGISTableSource.

===== Create a new table with all the roads within the city of Prince George =====
This is a filter with 2 inputs and one output.

 CREATE TABLE pg_roads as 
 SELECT 
  ST_Intersection(r.the_geom, m.the_geom) AS intersection_geom,
  ST_Length(r.the_geom) AS rd_orig_length, 
  r.* 
 FROM 
  bc_roads AS r, 
  bc_municipality AS m 
 WHERE ST_Intersects(r.the_geom, m.the_geom)
  AND m.name = 'PRINCE GEORGE';

=== Pipeline with DBs ===

==== Data Object Model ====

If we want to be able to efficiently write processing chains using PostGIS, we have to have filters which talk to each other easily. One way to do this is that all filters in a pipeline have to use a formalism to represent their inputs and outputs. This formalism is just a data object model which represents a PostGIS table. This class is called ''otb::GISTable'' and contains a pointer to a GISConnectionImplementation.


==== Adaptors ====

Adaptors, or conversion filters between the 3 elements of the triplet ''otb::VectorData'', ''itk::LabelObjectMap'' and ''otb::GISTable'' are the killer tool which will make us able to combine simple kml files with the result of [[Object-based image analysis]] and high level geospatial analysis.

== Use cases ==

=== Building extraction ===

* Image segmentation (e.g. MeanShift)
* Use the LabelObjectMap approach to generate a set of regions with radiometric attributes
* Import the LabelObjectMap into a PostGIS DB
* Select the regions which 
** Have a size > threshold
** Have the appropriate radiometry
** Have a shadow towards the NW
* Export a VectorData with the polygons containing the selected regions
* Save as a KML file / Display in the viewer

=== OSM layer for the Viewer ===

* Assume that the whole OSM base is available in a PostGIS DB (locally for instance)
* Open any image in the Viewer
* Add a layer containing the OSM data for the image 
** Eventually select only the objects which verify a condition (roads longer than, etc.)

=== Urban area detection on OSM ===

* Assume that the whole OSM base is available in a PostGIS DB (locally for instance)
* Define a spatial extent
* Extract the regions where the density of roads is higher than a threshold

=== Road DB update ===

* Assume that the whole OSM base is available in a PostGIS DB (locally for instance)
* Define a spatial extent
* We want to check that roads in the DB are also present in an image
** Use PostGIS to create a buffer on the image around the location of DB roads
** Use edge detections or something like this only on the selected buffers to check that the roads are there
* We want to detect new roads 
** Since new roads are connected to existing roads, look for roads close to the buffers defined above

=== Counting trees along the roads ===

* Assume that the whole OSM base is available in a PostGIS DB (locally for instance)
* Open the image to analyze
* Select a buffer of X m. along the roads in OSM
* Segment the image (only in the buffers) with meanshift
* Export the segmented regions to PostGIS with the NDVI attribute (LabelObjectMap to PostGIS?)
* Trees are compact regions (area, perimeter) with a minimum and maximum size.
* Export the detected trees as VectorData

== System configuration issues ==

In order to compile, the classes for the PostGIS interface will need both ''libpqxx'' and ''libpq''. The first one, can be easily integrated in OTB/Utilities. The second one, is available with classical PostgreSQL development packages. An internal/external option could be provided at the CMake level.

It seems appropriate to assume the PostgreSQL and PostGIS are installed on the host system and that a spacific data base and user are used by OTB. These settings can be set during the CMake configuration step. These will be the default values to  be used by OTB unless otherwise indicated. The otb::GISConnectionImplementation will alwas give the possibility to use a different DB and user.

== Conclusion ==

With this OTB-PostGIS interface, OTB will cover the whole information production chain from sensor model to spatial analysis. Now we deserve the 57 million dollars!