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

ST_Area 
select ST_Area(the_geom) from myTable; 
Return the area of each geometry 
ST_AsWKT 
select ST_AsWKT(the_geom) from myTable; 
Return the geometry field 
ST_Boundary 
select ST_Boundary(the_geom) from myTable; 
Create a new GDMS file in TOC with the boundary of myTable 
select ST_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 

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

Return true if the geometry A contains the geometry B 

ST_ConvexHull 
select ST_ConvexHull(the_geom) from myTable; 
Compute the Convex Hull of the current geometry 
ST_Crosses 
select ST_Crosses(the_geom1,the_geom2) from myTable; 
Return true if the geometry A crosses the geometry B 
ST_Difference 
select ST_Difference(the_geom1,the_geom2) from myTable;

Compute the difference between two geometry 
ST_Dimension 
select ST_Dimension(the_geom) from myTable; 
Return the geometry dimension (0 for [Multi]Point, 1 for [Multi]Line and 2 for [Multi]Polygon 
ST_Disjoint 
select ST_Disjoint(the_geom1,the_geom2) from myTable; 
Return true if the geometry A is disjoint from the geometry B 
ST_DWithin 
select ST_DWithin(the_geom1,the_geom2, distance) from myTable; 
Return true if the distance from geometry A to geometry B is less than or equal to a specified distance 
ST_Envelope 
select ST_Envelope(raster) as raster from mytif; OR select ST Envelope(the_geom) from mytable;

Compute the envelope of the raster and returns a geometry 
ST_Equals 
select ST_Equals(the_geom1,the_geom2) from myTable;

Return true if the geometry A is equal to geometry B 
ST_Extent 
select ST_Extent(the_geom) from myTable; 
Return the extent of a specified geometry 
ST_GeomFromText 
select ST_GeomFromText(myField) from myTable; 
Convert a WKT string value into a geometry value 
ST_GeometryN 
select ST_GeometryN(the_geom) from myTable; 
Return the 1based Nth geometry 
ST_GeometryType 
select ST_GeometryType(the_geom) from myTable; 
Return the type of geometry : Point, Line, Polygon, ... 
ST_Intersection 
select ST_Intersection(the_geom1,the_geom2) from myTable;

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

Return true if the geometry A intersect the geometry B 
ST_IsEmpty 
select ST_IsEmpty(the_geom) from myTable; 
Return true if the geometry is empty 
ST_IsSimple 
select ST_IsSimple(the_geom) from myTable; 
Return true if the geometry is simple 
ST_IsValid 
select ST_IsValid(the_geom) from myTable; 
Return true if the geometry is valid 
select ST_Length(the_geom) from myTable; 
Return the length of a geometry 

ST_MakeLine 
select ST_MakeLine(geometry1, geometry2) from myTable; 
Create a line from two point geometries 
ST_MakePoint 
select ST_MakePoint(X, Y) from myTable; 
Create a point geometry 
ST_NumGeometries 
select ST_NumGeometries(the_geom) from myTable; 
Return the number of geometries 
ST_NumInteriorRings 
select ST_NumInteriorRings(the_geom) from myTable; 
Return the number of holes in a geometry 
ST_NumPoints 
select ST_NumPoints(the_geom) from myTable; 
Return the number of points in a geometry 
ST_Overlaps 
select ST_Overlaps(the_geom1,the_geom2) from myTable; 
Return true if the geometry A overlaps the geometry B 
ST_Relate 
select ST_Relate(the_geom1,the_geom2) from myTable; 
Return a 9character string representation of the 2 geometries intersectionMatrix 
ST_SymDifference 
select ST_SymDifference(the_geom1,the_geom2) from myTable;

Compute the symmetric difference between two geometries 
ST_Touches 
select ST_Touches(the_geom1,the_geom2) from myTable; 
Return true if the geometry A touches the geometry B 
ST_Union 
select ST_Union(the_geom) from myTable; 
Compute the union of current and all previous geometries 
ST_Within 
select ST_Within(the_geom1,the_geom2) from myTable; 
Return true if the geometry A is within the geometry B 
Builtin SQL (rightclick in the geocongnition > Add > Builtin SQL) 

AutoNumeric 
select AutoNumeric(),* from myTable; 
Produce an autonumeric (autoincrement) field 
Avg 
select Avg(myNumericField) from myTable;

Calculate the average value of the field 
STO_BigCreateGrid 
select STO_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 
STO_BuildTIN 
select STO_BuildTIN() from mydatasource 
Implementation of a 2D Constrained Delaunay triangulation written by Michael Michaud (France, IGN) 
STO_CircleCompacity 
select STO_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 
STO_Constraint3D 
select STO_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 

STO_CreateGrid 
select STO_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 
STO_CreateWebGrid 
select STO_CreateWebGrid(4000,1000) from myTable; 
Calculate a regular grid that may be optional oriented 
select STO_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 

STO_D8Accumulation 
select STO_D8Accumulation(raster) as raster from direction; 
Compute the D8 accumulation using a GRAY16/32 grid direction as input table 
STO_D8Outlet 
select STO_D8Outlet(raster) as raster from direction; 
Compute all the watershed outlet using a GRAY16/32 DEM slopes directions as input table 
STO_D8ConstrainedAccumulation 
select STO_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 
STO_D8Direction 
select STO_D8Direction(raster) as raster from mydem; 
Compute the slopes direction using a GRAY16/32 DEM as input table 
STO_D8Distance 
select STO_D8Distance(raster) as raster from direction; 
Calculate the maximum length to the outlet using a GRAY16/32 DEM slopes directions as inupt table 
STO_D8RiverDistance 
select STO_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 
STO_D8Slope 
select STO_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 
STO_D8StrahlerStreamOrder 
select STO_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. 
STO_D8Watershed 
select STO_D8Watershed(dir.raster[, acc.raster, value  GeomFromText("POINT(x y)")]) from dir, acc; 
Compute all watershed or using a threshold integer accumulation value 
STO_Explode 
select STO_Explode( [geomFieldName] ) from myTable; 
Convert any geometrycollection into a set of single geometries 
STO_Extrude 
select STO_Extrude(id, height[, the_geom]) from myTable; 
Extrude a 2D polygon using a height field value 
STO_FillSinks 
select STO_FillSinks(raster, slopeValue) as raster from mydem; 
Depression filling algorithm. Method of Olivier Planchon & Frederic Darboux (2001) 
STO_Generalize 
select STO_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 
STO_Geomark 
select STO_Geomark( [optionalPrefix] ) from myTable; 
Stores each spatial field envelope as a new geomark 
STO_GetZDEM 
select STO_GetZDEM('the_DEM'[, the_geom]) from myTable; 
Build the ground geometry and calculate the building's height 
STO_GetX 
select STO_GetX(the_geom, [index]) from myTable; 
Return the X value for a point geometry 
STO_GetY 
select STO_GetY(the_geom, [index]) from myTable; 
Return the Y value for a point geometry 
STO_GetZ 
select STO_GetZ(the_geom, [index]) from myTable; 
Return the Z value for a point geometry 
STO_InsertPoint 
select STO_InsertPoint(the_geom, point) from myTable; 
Insert a point along a line 
STO_Interpolate 
select STO_Interpolate() from mydata; 
Build a raster using an interpolate method based on Delaunay Triangulation 
STO_IntersectGeometry 
select STO_IntersectGeometry(a.the_geom, b.the_geom) from myTable a, myZone b; 
Select geometries that intersect a geometry area and keep the attributes 
IsUID 
select * from myTable where isUID(column); 
Check if the column is an unique identifier 
STO_KMeans 
select STO_KMeans(cellIndex, 7) from myTable; 
Data clustering problem implementation 
STO_LSFactor 
select STO_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 
STO_MainDirection 
select STO_MainDirections(<nbOfDirections>) from myTable;

Calculate the main directions from a (Multi)Linestring dataset 
Max 
select Max(myField) from myTable; 
Return the maximum value 
STO_Meanspacing 
select STO_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 
PK 
select Pk("fieldName") from myTable; 
Set a primary key constraint to the corresponding field 
STO_PlanarGraph 
select STO_PlanarGraph(the_geom) from myTable; 
Build a planar graph based on polygons 
STO_PointsToLine 
select STO_PointsToLine(the_geom) from mylayer 
Convert an order set of (Multi)points in a single Linestring geometry 
STO_PointsToXYZ 
select STO_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 
STO_RandomGeometry 
select STO_RandomGeometry('miscpointlinestringlinearringpolygon'[, number]); 
Retrun randomly choosen geometries of given type 
STO_RasterToPoints 
select STO_RasterToPoints([raster]) from mytif; 
Transform a raster into a set of points based on the pixel centroid 
STO_RasterToPolygons 
select STO_RasterToPolygons([raster]) from mytif; 
Transform a raster into a set of polygons (one rectangle per pixel) 
STO_RasterToXYZ 
select STO_RasterToXYZ([raster]) from mytif; 
Transform a raster into a XYZ table (set of centroid points) 
STO_RasterizeLine 
select STO_RasterizeLine(the_geom, raster, value) as raster from myTable, mydem; 
Convert a set of lines into a raster grid 
Register 
select register('C:\Temp\myShape.shp','myTable'); 
Register a new file or database 
STO_ShortestPath 
Select STO_ShortestPath(Point A, Point B[,true]) from mytable; 
Compute the shortest path between two points 
Show 
select Show('select * from myTable'); 
Display the query result in a table 
Sqrt 
select Sqrt(myNumericField) from myTable; 
Return the square root value 
StandardDeviation 
select StandardDeviation(myNumericField) from myTable; 
Compute the standard deviation value 
Strlength 
select strlength(myField) from myTable; 
Return the length in number of characters 
STO_StreamPowerIndexOp 
select STO_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 
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 
STO_ToLineNoder 
select STO_ToLineNoder(the_geom) from myTable; 
Build all intersection and convert the geometry into lines 
STO_ToMultiLine 
select STO_ToMultiLine(the_geom) from myTable; 
Convert a geometry into a multilinestring 
STO_ToMultiPoint 
select STO_ToMultiPoint(the_geom) from myTable; 
Convert a geometry into a multipoint 
STO_ToMultiSegment 
select STO_ToMultiSegments(the_geom) from myTable; 
Convert a geometry into a multisegment 
ToString 
select ToString(myField) from myTable; 
Get the textual representation of the value 
STO_UnionArg 
select STO_UnionArg(the_geom1, the_geom2) from myTable; 
Compute the union of the function geometry parameters 
STO_UpdateZ 
select STO_UpdateZ(the_geom, field) from myTable; 
Update all Z value for a geometry 
STO_VectorizeLine 
select STO_VectorizeLine() from mydata; 
Converts a (set of) GeoRaster(s) into a set of MultiLinestring 
STO_WetnessIndex 
select STO_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