List of queries
You will find below the list of the available SQL queries in OrbisGIS
SQL Function 
Syntax 
Comments 
OGC 

Area 
select Area(the_geom) from myTable; 
Return the area of each geometry 
AsWKT 
select AsWKT(the_geom) from myTable; 
Return the geometry field 
Boundary 
select Boundary(the_geom) from myTable; 
Create a new GDMS file in TOC with the boundary of myTable 
select Buffer(the_geom, bufferSize[, 'butt''square''round']) from myTable;

Compute a buffer around a geometry. Where butt, square and round are the shape of the end of the lines 

Centroid 
select Centroid(the_geom) from myTable; 
Compute the geometry centroid. The results is a point 
select Contains(the_geom1,the_geom2) from myTable;

Return true if the geometry A contains the geometry B 

ConvexHull 
select ConvexHull(the_geom) from myTable; 
Compute the Convex Hull of the current geometry 
Difference 
select Difference(the_geom1,the_geom2) from myTable;

Compute the difference between two geometry 
Dimension 
select Dimension(the_geom) from myTable; 
Return the geometry dimension (0 for [Multi]Point, 1 for [Multi]Line and 2 for [Multi]Polygon 
Equals 
select Equals(the_geom1,the_geom2) from myTable;

Return true if the geometry A is equal to geometry B 
GeomFromText 
select Equals(a.the_geom,b.the_geom) from nantes_quartiers a, hydro_chezine b; 
Convert a WKT string value into a geometry value 
Geomunion 
select GeomUnion(the_geom) from myTable; 
Compute the union of current and all previous geometries 
GeometryN 
select GeometryN(the_geom) from myTable; 
Return the number of geometry 
GeometryType 
select GeometryType(the_geom) from myTable; 
Return the type of geometry : Point, Line, Polygon, ... 
Intersection 
select Intersection(the_geom1,the_geom2) from myTable;

Compute the intersection between two geometries 
Intersects 
select Intersects(the_geom1,the_geom2) from myGeom;

Return true if the geometry A intersect the geometry B 
IsEmpty 
select IsEmpty(the_geom) from myTable; 
Return true if the geometry is empty 
IsSimple 
select IsSimple(the_geom) from myTable; 
Return true if the geometry is simple 
IsValid 
select IsValid(the_geom) from myTable; 
Return true if the geometry is valid 
select Length(the_geom) from myTable; 
Return the length of a geometry 

NumPoints 
select NumPoints(the_geom) from myTable; 
Return the number of points in a geometry 
SymDifference 
select SymDifference(the_geom1,the_geom2) from myTable;

Compute the symmetric difference between two geometries 
Envelope 
select Envelope(raster) as raster from mytif; OR select Envelope(the_geom) from mytable;

Compute the envelope of the raster and returns a geometry 
Utilities 

select CropRaster(r.raster, f.the_geom) as raster from mytif r, fence f;

Crop the raster in the first argument with the geometry in the second one. The result is a cropped raster 

ToMultiLine 
select ToMultiLine(the_geom) from myTable; 
Convert a geometry into a multilinestring 
ToMultiPoint 
select ToMultiPoint(the_geom) from myTable; 
Convert a geometry into a multipoint 
ToMultiSegment 
select ToMultiSegments(the_geom) from myTable; 
Convert a geometry into a multisegment 
Explode 
select Explode( [geomFieldName] ) from myTable; 
Convert any geometrycollection into a set of single geometries 
IO 

Register 
select register('C:\Temp\myShape.shp','myTable'); 
Register a new file or database 
Builtin SQL (rightclick in the Geocongnition > Add > Builtin SQL) 

AddZ 
select AddZ(b.the_geom, d.raster) from buildings b, dem d orselect AddZ(b.the_geom, fieldname) from builldings b, dem d;

This function modify (or set) the z component of (each vertex of) the geometric parameter to the corresponding value given by the DEM elevation parameter or in a field 
AutoNumeric 
select AutoNumeric(),* from myTable; 
Produce an autonumeric (autoincrement) field 
Avg 
select Avg(myNumericField) from myTable;

Calculate the average value of the field 
BigCreateGrid 
select BigCreateGrid(x size of the cell,y size of the cell[,optionnal degree of the orientation]) from myTable;

Calculate a regular grid that may be optional oriented 
BuildTIN 
select BuildTIN() from mydatasource 
Implementation of a 2D Constrained Delaunay triangulation written by Michael Michaud (France, IGN) 
CircleCompacity 
select CircleCompacity(the_geom) from myBuildingsTable; 
Calculate the compacity of each building's geometry compared to the circle (the one that as the area of the building) 
Concatenate 
select Concatenate(myField1,'d') from mytable

Concatenate two or more strings 
Constraint3D 
select Constraint3D(the_geom) from myTable 
Change the metadata of the parameter by setting its dimension to 3D 
select Count(*) from myTable; 
Count the number of value that are not null. If "*" is used, it counts the number of rows 

CreateGrid 
select CreateGrid(x size of the cell,y size of the cell[,optionnal degree of the orientation]) from myTable;

Calculate a regular grid that may be optional oriented 
CreateWebGrid 
select CreateWebGrid(4000,1000) from myTable; 
Calculate a regular grid that may be optional oriented 
D8Accumulation 
select D8Accumulation(raster) as raster from direction; 
Compute the D8 accumulation using a GRAY16/32 grid direction as input table 
D8Outlet 
select D8Outlet(raster) as raster from direction; 
Compute all the watershed outlet using a GRAY16/32 DEM slopes directions as input table 
D8ConstrainedAccumulation 
select D8ConstrainedAccumulation(d.raster, a.raster) from directions d, constrainedgrid a; 
Compute a constrained grid accumulation based on two grids : a direction's grid and an integer that represent some anthropic constraints such as hedgerows or roads 
D8Direction 
select D8Direction(raster) as raster from mydem; 
Compute the slopes direction using a GRAY16/32 DEM as input table 
D8Distance 
select D8Distance(raster) as raster from direction; 
Calculate the maximum length to the outlet using a GRAY16/32 DEM slopes directions as inupt table 
D8RiverDistance 
select D8RiverDistance(d.raster, a.raster, RiverThreshold) as raster from direction d, accumulation a; 
Calculate the maximum length to the river using a GRAY16/32 DEM slopes directions and a DEM slopes accumulations as input table 
D8Slope 
select D8Slope(raster[, 'radian''degree''percent' ]) as raster from mydem; 
Compute the slope using a GRAY16/32 DEM as input table. Default unit is percent, but it is also possible to specify it as : radian, degree, percent 
D8StrahlerStreamOrder 
select D8StrahlerStreamOrder(d.raster, a.raster, RiverThreshold) from direction d, accumulation a; 
Compute the Strahler Stream Order using a GRAY16/32 DEM slopes accumulations as input table. The RiverThreshold is an integer value that correspond to the minimal value of accumulation for a cell to be seen as a 1th level river. 
D8Watershed 
select D8Watershed(dir.raster[, acc.raster, value  GeomFromText("POINT(x y)")]) from dir, acc; 
Compute all watershed or using a threshold integer accumulation value 
Extrude 
select Extrude(id, height[, the_geom]) from myTable; 
Extrude a 2D polygon using a height field value 
FillSinks 
select FillSinks(raster, slopeValue) as raster from mydem; 
Depression filling algorithm. Method from Olivier Planchon & Frederic Darboux (2001) 
Generalize 
select Generalize(the_geom,maxdistance, [method]) from myTable; 
Reduce the number of point in a polygon or in a line. By default the algorithm is Douglas Peuckerburt, you can use another method named ISA 
Geomark 
select Geomark( [optionalPrefix] ) from myTable; 
Stores each spatial field envelope as a new geomark 
Interpolate 
select Interpolate() from mydata; 
Build a raster using an interpolate method based on Delaunay Triangulation 
GetZDEM 
select GetZDEM('the_DEM'[, the_geom]) from myTable; 
Build the ground geometry and calculate the building's height 
GetZ 
select GetZ(the_geom) from myTable; 
Return the Z value for a point geometry 
KMeans 
select KMeans(cellIndex, 7) from myTable; 
Return the Z value for a point geometry 
StreamPowerIndexOp 
select StreamPowerIndexOp(a.raster,b.raster ) as raster from slopes as a, acc as b; 
Compute the StreamPowerIndexOp index using a slopes grid in radian and an accumulationgrid 
MainDirection 
select MainDirections(<nbOfDirections>) from myTable;

Calculate the main directions from a (Multi)Linestring dataset 
Max 
select Max(myField) from myTable; 
Return the maximum value 
Meanspacing 
select MEANSPACING(a.the_geom,intersection(a.the_geom,b.the_geom)) from grid as a, build as b where intersects(a.the_geom,b.the_geom); 
Calculate Mean Spacing between buildings (grid.the_geom, build.the_geom) 
Min 
select Min(myField) from myTable; 
Return the minimum value 
NumInteriorRing 
select NumInteriorRing(the_geom) from myTable; 
Return the number of holes in a geometry 
PK 
select Pk("fieldName") from myTable; 
Set a primary key constraint to the corresponding field 
PointsToLine 
select PointsToLine(the_geom) from mylayer 
Convert an order set of (Multi)points in a single Linestring geometry 
PointsToXYZ 
select PointsToXYZ(the_geom [, a_numeric_field_name]) from myTable; 
Extract X Y Z coordinates from a point. By default the Z value corresponding to the geometry, but the user can choose corresponding numeric field in the table 
RandomGeometry 
select RandomGeometry('miscpointlinestringlinearringpolygon'[, number]); 
Return randomly choosen geometries of given type 
RasterToPoints 
select RasterToPoints([raster]) from mytif; 
Transform a raster into a set of points based on the pixel centroid 
RasterToPolygons 
select RasterToPolygons([raster]) from mytif; 
Transform a raster into a set of polygons (one rectangle per pixel) 
RasterToXYZ 
select RasterToXYZ([raster]) from mytif; 
Transform a raster into a XYZ table (set of centroid points) 
RasterizeLine 
select RasterizeLine(the_geom, raster, value) as raster from myTable, mydem; 
Convert a set of lines into a raster grid 
Show 
select Show('select * from myTable'); 
Display the query result in a table 
Sqrt 
select Sqrt(myNumericField) from myTable; 
Return the square root value 
StandadDeviation 
select StandardDeviation(myNumericField) from myTable; 
Compute the standard deviation value 
Strlength 
select strlength(myField) from myTable; 
Return the length in number of characters 
LSFactor 
select LSFactor(a.raster,b.raster ) as raster from slopes as a, acc as b; 
Compute the LS Factor index using a slopes grid in radian and a accumulation grid 
StringToBoolean 
select * from myTable where StringToBoolean(myStringField); 
Converts a string into a boolean 
StringToDate 
select StringToDate('date_literal'[ , date_format]) from mytable 
Converts a string into a date 
StringToDouble 
select string2double(myStringField) from myTable; 
Converts a string into double 
StringToInt 
select StringToInt(myStringField) from myTable; 
Converts a string into integer 
SubString 
select SubString(text, integer[, integer]) from myTable 
Extract a substring. Arguments = right digits 
Sum 
select Sum(myField) from myTable; 
Return the sum 
ToLineNoder 
select ToLineNoder(the_geom) from myTable; 
Build all intersection and convert the geometry into lines 
ToString 
select ToString(myField) from myTable; 
Get the textual representation of the value 
VectorizeLine 
select VectorizeLine() from mydata; 
Converts a (set of) GeoRaster(s) into a set of MultiLinestring 
WetnessIndex 
select WetnessIndex(a.raster,b.raster ) as raster from slopes as a, acc as b; 
Compute the wetness index using a slopes grid in radian and a accumulation grid 
Copyright  IRSTV  2009  www.orbisgis.org