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

ST_Buffer

select ST_Buffer(the_geom, bufferSize[, 'butt'|'square'|'round']) from myTable;
ex: select ST_Buffer(the_geom, 100,'square') from hydro_chezine;

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

ST_Contains

select ST_Contains(the_geom1,the_geom2) from myTable;
ex : select ST_Contains(a.the_geom,b.the_geom) from nantes_quartiers a, hydro_chezine b;

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;
ex :select ST_Difference(a.the_geom,b.the_geom) from nantes_quartiers a, hydro_chezine b;

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;
ex : select ST_Envelope(raster) as raster from "France_DEM_1000"

Compute the envelope of the raster and returns a geometry

ST_Equals

select ST_Equals(the_geom1,the_geom2) from myTable;
ex : select ST_Equals(a.the_geom,b.the_geom) from nantes_quartiers a, hydro_chezine b;

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 1-based 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;
ex : select ST_Intersection(a.the_geom,b.the_geom) from nantes_quartiers a, hydro_chezine b;

Compute the intersection between two geometries

ST_Intersects

select ST_Intersects(the_geom1,the_geom2) from myGeom;
ex : select ST_Intersects(a.the_geom,b.the_geom) from nantes_quartiers a, hydro_chezine b;

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

ST_Lenght

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 9-character string representation of the 2 geometries intersectionMatrix

ST_SymDifference

select ST_SymDifference(the_geom1,the_geom2) from myTable;
ex : select ST_SymDifference(a.the_geom,b.the_geom) from nantes_quartiers a, hydro_chezine b;

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

Built-in SQL (right-click in the geocongnition --> Add --> Built-in SQL)

AutoNumeric

select AutoNumeric(),* from myTable;

Produce an auto-numeric (auto-increment) field

Avg

select Avg(myNumericField) from myTable;
ex: select Avg("Shape_Area") from nantes_quartiers ;

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;
ex:  select STO_BigCreateGrid(4000,1000[,15]) from nantes_quartiers;    or
       select STO_BigCreateGrid(200,200) from nantes_quartiers;

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
ex : select Concatenate("NOM",'_Nantes') from nantes_quartiers;

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

Count

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;
ex:  select STO_CreateGrid(4000,1000[,15]) from nantes_quartiers;    or
       select STO_CreateGrid(200,200) from nantes_quartiers;

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

STO_CropRaster

select STO_CropRaster(r.raster, f.the_geom) as raster from mytif r, fence f;
ex : select STO_CropRaster(r.raster, f.the_geom) as raster from "France_DEM_1000" r,nantes_quartiers 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;
ex : select STO_MainDirections(3) from hydro_chezine ;

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('misc|point|linestring|linearring|polygon'[, 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