Data functionality

3.3 Data functionality

After the foreign data on file system has been represented on foreign table, it can be operated using database facilities on the PostgreSQL side, including query functionality, aggregate function, data ma-nipulation and type conversion. The interaction between SQL server and foreign data wrapper is de-fined in SQL/MEDstandard as introduced in section 2.4, while the communication between foreign server and foreign data wrapper is implementation dependent. Therefore, the implementation of for-eign data wrapper should participate in the process of the functionalities.

3.3.1 Query

SQL query

Querying a table is to retrieve data from a table. PostgreSQL executes the command by SQL language SELECTstatement used to specify queries. Foreign data wrapper is responsible for the retrial of data for a foreign table. The general syntax of SELECT statement is:

[ WITH w i t h q u e r i e s ]

SELECT s e l e c t l i s t FROM t a b l e e x p r e s s i o n [ s o r t s p e c i f i c a t i o n ]

SQL command consists two basic parts: table expression and select list. According to the select lists, table expression can provide all columns or a subset of available columns, and make calculations using columns.

A table expression computes a table. The table expression contains a FROM clause that is optionally fol-lowed by WHERE, GROUP BY, and HAVING clauses. The optional WHERE, GROUP BY, and HAVING clauses specify a pipeline of subsequent transformed on the table derived in the FROM clause, i.e. computed by table expression.

The FROM clause derives a table from one or more tables given in a table reference list, by using foreign data wrapper, the foreign tables can be the reference table.

FROM t a b l e r e f e r e n c e [ , t a b l e r e f e r e n c e [ , . . . ] ]

A table reference can be a table name, or derived table like subquery, a JOIN construct, or even complex combinations of these. A joined table is a table derived from two other (real or derived) tables according to the rules of the particular join type. Inner, outer, and cross-joins are available.A temporary name can be given to tables and complex table references to be used for references to the derived table in the rest of the query, which is called a table alias. Subqueries specifying a derived table must be enclosed in parentheses and must be assigned a table alias name. In our cases, one single foreign table of point clouds data, the foreign table of point clouds data can join the local table of other GIS data for data combination, or a subquery resulting potential points can join with a local table storing query polygons, can constructs a table reference, thus deriving a table for querying. The result of the FROM list is an intermediate virtual table that can then be subject to transformations by the WHERE, GROUP BY, and HAVING clauses and is finally the result of the overall table expression.

The WHERE clause comes along with a search condition which is any value expression that returns a value of type boolean.

WHERE s e a r c h c o n d i t i o n

3 Methodology

After the processing of the FROM clause is done, each row of the derived virtual table is checked against the search conditions. If results of the condition is true, the row is kept in the output table, otherwise it is discard. After passing the WHERE filter, the derived input table might be subject to grouping, using the GROUP BYclause, and elimination of group rows using the HAVING clause.

The table expression in the SELECT command constructs an intermediate virtual table by possibly combin-ing tables, views, eliminatcombin-ing rows, groupcombin-ing, etc. This table is finally passed on to processcombin-ing by the select list. The select list determines which columns of the intermediate table are actually output. The simplest kind of select list is * which emits all columns that the table expression produces. Otherwise, a select list is a comma-separated list of value expressions.

Spatial query

The research object is point clouds data which is spatial data, other than attribute selection, the query can be executed is spatial selection. Spatial query is to select the points within a query region, which can be any region including simple rectangle and circle, or irregular polygons. For this purpose, the PostGIS functions will be used for Point-in-Polygon selection.

The following functions supported bu PostGIS can be used as condition depending on the spatial topo-logical relationship.

• ST Contains Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A. Geometry A contains Geometry B if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A. This function can be used as one of the search condition in WHERE clause for Point-in-Polygon selection, with A being query regions like POLYGON and B being the POINT.

boolean ST Contains ( geometry geomA , geometry geomB )

• ST Intersects Returns TRUE if the Geography spatially intersect in 2D which means sharing any portion of space and FALSE if they don’t which means they are Disjoint.

boolean S T I n t e r s e c t s ( geometry geomA , geometry geomB ) ;

• ST Within Returns true if the geometry A is completely inside geometry B. This function can be used as one of the search condition in WHERE clause for Point-in-Polygon selection, with B being query regions like POLYGON and A being the POINT.

boolean ST Within ( geometry A, geometry B ) ;

The following functions can be used to measuring the attribute of searching condition i.e. query re-gion.

• ST Extent is an aggregate function that returns the bounding box that bounds rows of geome-tries.This function can be used to get the extent i.e. bounding box information of the query poly-gon, with extent information a pre-selection can be queried as box selection.

box2d S T E x t e n t ( geometry s e t geomfield ) ;

• ST Area returns the area of a polygonal geometry, this can be used to get the area of query polygon for computation of efficiency related to ratio of area of query region and selected file extent.

f l o a t ST Area ( geometry geom ) ;

28

3.3 Data functionality Possible queries

With the support of PostgreSQL and PostGIS, the possible queries on the foreign data representing the foreign data from the point clouds file system, can have a wide range. The system should be able to support points selection, and the search condition in WHERE can be based on position and also additional attributes, in addition the aggregate function can be added in select lists.

- select nearest neighbor of one location with a buffer - select all the points within rectangle of different sizes - select all the points within a circle of different sizes - select all the points within a polygon of different sizes It is possible to make selection based on attribute field conditons

- select the ground/ water/ building points insides a region - select points based on intensity, RGB values insides a region It is also possible to make aggregation function

- select the highest point of a region

- select the highest, lowest and average elevation value of points in a region - select the total point density and local point density of a region

FDW routine

When a SQL command is executed in PostgreSQL prompt, the supporting foreign data wrapper will do the retrieving from the external data source which is point clouds file, and then fetch the asked content to the foreign table. The mapping of foreign data wrapper does not copy any data but redirects any query to the remote database server and table [Stanisavljevic,2019]. From a user perspective, there is no difference between a foreign table and any other relation in database. There is a schema holding data content and it can be used in select queries without any restrictions [Roijackers et al.,2012]. A foreign data wrapper is responsible for the retrieval of data for a foreign table. The callback functions are used for planning, explaining and retrieving data for a asked query. Retrieving data records to the table rows is implemented using an iteration function which returns the next record according to the schema of the used foreign table [Roijackers et al.,2012].

The execute() method of multicorn.ForeignDataWrapper can execute a query in the foreign data wrapper.

This method is called at the first iteration. This is where the actual remote query execution takes place.

It should return a python objects that are iterable and can be converted to PostgreSQL. These kind of iterable objects can be: sequences containing exactly same number of columns in the corresponding tables; dictionaries mapping column names to their values. The parameter of execute() are: quals, a list of Qual instances, containing the basic WHERE clauses in the SQL query. columns, a list of columns that PostgreSQL is going to need. You should return at least those columns when returning a dictionary. If returning a sequence, every column from the table should be in the sequence as structured. sortkeys, a list of SortKey that the wrapper announced it can enforce.

multicorn.ForeignDataWrapper class provides anAPIfor improving the retrieve efficiency, which is apply-ing the Qual class. A Qual describes a PostgreSQL qualifier, which is defined as an expression of the type: col name operator value. The attributes of Qual are:

- field name (string): The name of the column as defined in the foreign table.

- operator (string or tuple): The name of the operator.

- value (object): The constant value on the right side

3 Methodology

The initialization method for the Qual class happens when WHERE clause exists in the SELECT statement. A Qual object is constructed and referenced to the one of search conditions in the WHERE clause, after extract-ing the field name, operator and values from WHERE clause and assignextract-ing to the attributes Qual.field name, Qual.operator and Qual.value respectively. Whenever there is one search condition in the WHERE clause, a Qual instance is built, therefore, if there are several union search conditions, a list of Qual instances is created. This constructed list of Qual instances is named quals that will be passed as the parameter of execute() method when the query is executed.

Then in the execute() method of multicorn.ForeignDataWrapper, the information in Qual can be used as a filtering when execute() method retrieving the data content from remote data source. By using quals, the wrapper will conditionally fetching the data content in the external data source, otherwise, all the content in the foreign data will be fetched to the PostgreSQL foreign table.

3.3.2 Data manipulation

Foreign data wrapper also implements the functionality of update, delete and insert data, which is important support for system architecture. Data manipulate in PostgreSQL includes insert, update, and delete table data. When a table like the metadata table of the file system is created, it contain no data.

The first thing to do is to insert data. Data is conceptually inserted one row at a time. In addition, the point records in the point clouds file are outside the bounding box given in the header information, these outside points may influence the accuracy spatial query, thus they could be deleted.

SQL update

To create a new row, use the INSERT command which requires the table name and column values, listing column names explicitly or not. It is also possible to insert multiple rows in a single command.

INSERT INTO table name

VALUES ( c o l 1 v a l u e , c o l 2 v a l u e , c o l 3 v a l u e , . . . ) ;

We can modify the data that is already exists, updating individual rows, all the rows in table, or a subset of all rows. To update existing rows, use the UPDATE command, which requires information including the name of table and column to update, the new value of the column and which row to update.

UPDATE table name

SET col1 name = new value WHERE s e a r c h c o n d i t i o n ;

Besides adding and changing data. deleting data is also supported. If the data is no longer needed, we can delete it by DELETE command to remove rows, with providing the information of table name and which rows to delete.

DELETE FROM table name WHERE s e a r c h c o n d i t i o n ;

30