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

Buffer

select Buffer(the_geom, bufferSize[, 'butt'|'square'|'round']) from myTable;

ex: select 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

Centroid

select Centroid(the_geom) from myTable;

Compute the geometry centroid. The results is a point

Contains

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

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

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;
ex : select 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

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

Compute the intersection between two geometries

Intersects

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

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

Lenght

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

Compute the symmetric difference between two geometries

Envelope

select Envelope(raster) as raster from mytif; ---OR--- select Envelope(the_geom) from mytable;
ex : select Envelope(raster) as raster from "France_DEM_1000"

Compute the envelope of the raster and returns a geometry

Utilities

CropRaster

select CropRaster(r.raster, f.the_geom) as raster from mytif r, fence f;
ex : select 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

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

Built-in SQL (right-click in the Geocongnition --> Add --> Built-in 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;

ex : select AddZ(b.the_geom, d.raster) from nantes_quartiers b, "France_DEM_1000" 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 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

BigCreateGrid

select BigCreateGrid(x size of the cell,y size of the cell[,optionnal degree of the orientation]) from myTable;
ex:  select BigCreateGrid(4000,1000[,15]) from nantes_quartiers;    or
       select BigCreateGrid(200,200) from nantes_quartiers;

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

Concatenate two or more strings

Constraint3D

select 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

CreateGrid

select CreateGrid(x size of the cell,y size of the cell[,optionnal degree of the orientation]) from myTable;
ex:  select CreateGrid(4000,1000[,15]) from nantes_quartiers;    or
       select CreateGrid(200,200) from nantes_quartiers;

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;
ex : select MainDirections(3) from hydro_chezine ;

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