postgisQueryBuilder

The postgisQueryBuilder QGis plugin is aimed to to provide a friendly environment to perform spatial analysis in postgresql/postgis without having to deal with SQL code. It is not intented to replace the Qgis built-in DBManager that have a wide usage that covers all aspects of Qgis/Postgis interaction. It can be used in educational environments for a live analysis of the structure of the common spatial queries. Furthermore, The plugin can manage postgresql views and can be used for deploying spatial analysis frameworks based on chains of spatial relations.

	Plugin functions are organized in six tabs: CONNECTION QUERY FILTER SQL TABLE HISTORYCONNECTION tab the tabbed slot contains the basic parameters for managing the connection with postgis server and for the interection with QGis. 1) In the postgresql connection box it’s possible to select a database connection between those defined by Qgis. The plugin use their credential and network parameters to connect wit postgis servers 2) Once defined the database connection, the operating db schema in the schema box has to be selected between those defined for the db connection. The plugin operates only with the defined schema but allows to move db relation between different available schemas. 3) furthermore default primary key and geometry fields have to be defined or selected between those proposed scanning available db objects 4) A list of available database objects (tables, views and materialized views). Right clicking on the items appears a contextual menu to summarize db object infos and to perform some specifical commands on selected item: – Use for query: keep the selected item and assign it as primary layer for query (for all objects, same for double-clicking) – Add layer to map canvas: view the layer on qgis. Primary key and geometry fields are autodetected (for all objects). – Auto detect primary key and geometry fields: assign autodetected Primary key and geometry fields to default box (for all objects). – View as data table: view the layer as data table in the table result slot without loading in qgis (for all objects). – Rename: rename the object modifying accordingly all objects that depend on it (for all objects). – Delete: delete the object; If it has dependencies ask for cascade deletion of descendant objects (for all objects). – Move to another schema: move the object in another schema creating it if not available (for all objects). ^UP
	QUERY tab In this slot is possible to select the query type to perform between several predefined common queries and fill the needed parameters. Furthermore appears a brief description, a diagram and an help reference to postgis website. Once filled all the parameters slots required for the selected query the plugin compiles and makes visible the SQL query code in the SQL tab. The predefined query types are: FIELD SELECTION ANALYTICAL JOIN SPATIAL JOIN MEASURE FIELDS BUFFER CENTROID/POINT ON SURFACE INTERSECTION DIFFERENCE UNION VALIDATE
	FIELD SELECTION The query performs a selection of fields subset. It is useful for creating a more readable objects from complex tables. The only needed parameters to set are the selecting layer and the attribute list. ^
	ANALYTICAL JOIN This query joins columns from two tables where the specified fields match. It’s the common sql join query and the needed parameters are the the two layers and the corresponding attributes list to be joined, the two fields that have to match and the join type (INNER, CROSS, LEFT OUTER, RIGHT OUTER, FULL OUTER). It is useful when the layers are related each other by id fields. ^
	SPATIAL JOIN The query merge attributes from two layers when they meet a spatial condition. The needed parameters to be specified are the two layers, the corresponding fields lists and the spatial condition to match (one from the available postgis spatial tests: contains, covers, crosses, disjoint, equals, intersects, overlaps, touches, within). ^
	MEASURE FIELDS By this query is possible to add geometry information about area and perimeter of features. The only needed parameters are the layer name and the fields to include. ^
	BUFFER Buffer query returns the geometries that represent the portions of space located within a given distance to the generating geometries. In addition to the layer and the field list the buffer distance has to be specified. For proiected srs the distance has to be expressed in srs units, typically meters, while for geographic srs (WGS84 EPSG:4326) the distance has to be expressed in degrees. ^
	CENTROID/POINT ON SURFACE the centroid query the point geometries representing the center of masses of the generating geometries while the point-on-surface query returns point geometries that are guarantee to lie in the generating geometries. The needed parameters are the generating layer and the respectively list of fields to include ^
	INTERSECTION The intersection query returns the geometries that represent the shared portion of the geometries of the two specified layers. The needed parameters to be set are the two layers name, and the field list of the first DIFFERENCE The query returns the geometries of the portion of the geometries of the first layer masked by the geometries of the second layer. The needed parameters to be set are the two layers name, and the field list of the first ^
	UNION The union query is proposed in two way. The union between the geometries of one layer, eventually dissolved by the specified fields and the uniom of the geometries of two layers ^
	VALIDATE The query is useful to point out the geometries affected by topological errors (self intersection, double vertex and otther geometric issues) ^
	FILTER tab This tab includes an optional filter table that helps to build a filter expression combining attributes filter based on field values and spatial relationship filter between layers. All values in the selection boxes are precharged to help user interaction and to limit possible errors. When the composed expression is well formed is compiled as WHERE clause embedded in SQL command visible SQL tab. The first column box is the command box of the filter table where is possible to declare the row type (Attribute filter, spatial filter or logical operators). The content of the box depends on context and offers only the allowed options to ensure a corrext expression syntax. The attribute filter declaration is composed by three or two boxes: The field to check between those of the query primary layer, the filter operator and the filter value (the box propose the unique values of the fields). If the selected filter operator is “IS NULL” or “IS NOT NULL” the filter value disappear as not necessary The spatial filter declaration is composed by three boxes. The first, fixed, it’s the query primary layer, the second is a postgis spatial relationship (within, intersects, equals …) operator and the last is the layer to apply. The layer box lists all layers available for declared schema. The Logical operator box propose the operators that are allowed by SQL syntax in the current position: AND, OR, NOT and the brackets ( ) As we declare the filter row type by the command box, a new empty row is added. Furthermore by the command box filter declarations can be deleted or other can be inserted by delete/insert command. ^UP
	SQL tab Once specified all needed parameters box in QUERY and FILTER tabs ,in this tab is automatically generated the sql statement and the corresponding query name, used for view creation or as Layer label in QGis legend. The automatic name is composed in a self explaining way, combining the query type name and the object name from wich the query is generated. Selecting the “as view” checkbox, the sql code is used as definition of a view. A view is a virtual table based on the result-set of an SQL statement that can be used in another query or loaded in QGis simply referring to it by its name, as well as it were a real table. Selecting the “materialized” checkbox, enabled when “as view” checkbox is checked too, is created a materialized view that is the result of a sql query that is cached and remains available until it is deleted or refreshed. It differs from a normal view because the result table is is not dynamically created on the fly from from sql definition, but cached for performance improvement. These types of views are particularly recommended for spatial queries because the spatial datasets are not updated in realtime and the spatial functions are very computationally intensive. The refreshing of a materialized view can be done by clicking the contextual menu over a materialized view item in the CONNECTION tab database objects list. The automatic compilation of sql statement and query name can be disabled unchecking the “Autocompiled” checkbox. In this mode the user can customize name and sql statement to derive a different query from the une auto generated. At this point is possible to run the query clicking on the respective button leaving checked the “Add to map” to load the result on the Qgis legend o unchecking it to view the table result in the TABLE tab. ^UP
	TABLE tab The tab contains a widget to view the result of a query or the content of a data object without loading it to QGis. ^UP
	HISTORY tab In this tab is reported the log of valid sql queries performed by the plugin. ^UP
IMAGE	CAPTION

Advertisement