SQL Control Bar

ST_Area

Returns the area of a polygon or multipolygon.

Example command line:

SELECT ST_Area(overlay1.sis_geometry) FROM overlay1;

ST_Binary

Returns the Well-Known Binary (WKB) representation of the geometry/geography.

Example command line:

SELECT ST_Binary

ST_AsGeoJSON

SELECT ST_AsGeoJSON

ST_AsText

Returns the Well-Known Text (WKT) representation of the geometry/geography.

Example command line:

SELECT ST_AsText

ST_Boundary

Returns the closure of the combinatorial boundary of the geometry.

Example command line:

SELECT ST_Boundary(overlay1.sis_geometry) FROM overlay1;

ST_Buffer

Returns a geometry covering all points within a given distance from the input geometry.

Example command line:

SELECT ST_Buffer(overlay2.sis_geometry, 100) from overlay2;

ST_Centroid

Returns the geometric centre of a geometry.

Example command line:

SELECT ST_Centroid

ST_Contains

Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.

Example command line:

SELECT ST_Contains

ST_ConvexHull

Returns the convex hull of a geometry object.

Example command line:

SELECT ST_ConvexHull(overlay2.sis_geometry) from overlay2;

ST_Crosses

Returns TRUE if the supplied geometries have some, but not all, interior points in common.

Example command line:

SELECT ST_Crosses

ST_Difference

Returns a geometry that represents that part of geometry A that does not intersect with geometry B. (Also available as an aggregate function).

Example command line:

SELECT ST_Difference(overlay1.sis_geometry,overlay2.sis_geometry) from overlay1,overlay2;

ST_Dimension

Returns the inherent dimension of the Geometry object, which must be less than or equal to the coordinate dimension.

Example command line:

SELECT ST_Dimension(overlay3.sis_geometry) from overlay3

Returns:
0 for Point items
1 for LineString items
2 for Polygon items

ST_Disjoint

Returns TRUE if the Geometries do not "spatially intersect", ie if they do not share any space together.

Example command line:

SELECT ST_Disjoint

ST_Distance

Returns the distance between two geometries. The distance is measured from the closest vertices of the two geometries.

Example command line:

SELECT ST_Distance

ST_DWithin

Returns true if the geometries are within the specified distance of one another

Example command line:

SELECT ST_DWithin

ST_Envelope

Returns a geometry representing the bounding box of the supplied geometry. Uses double-precision..

Example command line:

SELECT ST_Envelope(overlay2.sis_geometry) from overlay2;

ST_Equals

Returns true if the given geometries represent the same geometry. Directionality is ignored.

Example command line:

SELECT ST_Equals

ST_Expand

Returns bounding box expanded in all directions from the bounding box of the input geometry. Uses double-precision.

Example command line:

SELECT ST_Expand

ST_Extent

An aggregate function that returns the bounding box that bounds rows of geometries.

Example command line:

SELECT ST_Extent

ST_First

Only available as an aggregate function, ie function used to aggregate the results of a SQL query.

Example command line:

SELECT ST_First

ST_GeomFromGeoJSON

Takes a geojson representation of a geometry and outputs a PostGIS geometry object.

Example command line:

SELECT ST_GeomFromGeoJSON

ST_GeomFromText

Returns a specified ST_Geometry value from Well-Known Text representation (WKT).

Example command line:

SELECT ST_GeomFromText

ST_GeomFromWKB

Creates a geometry instance from a Well-Known Binary geometry representation (WKB) and optional SRID (Spatial Reference System Identifier).

Example command line:

SELECT ST_GeomFromWKB

ST_Intersection

Returns a geometry that represents the shared portion of geometry A and geometry B. (Also available as an aggregate function).

Example command line:

Select ST_Intersection(overlay1.sis_geometry,overlay2.sis_geometry) from overlay1,overlay2;

ST_Intersects

Returns TRUE if the Geometries spatially intersect in 2D - ie share any portion of space, and FALSE if they do not, ie they are Disjoint.

Example command line:

SELECT ST_Intersects

ST_Length

Returns the 2D length of the geometry if it is a LineString or MultiLineString.

Example command line:

Select ST_Length(linestring4326.sis_geometry) FROM linestring4326;

ST_Length_Spheroid

Returns the 2D or 3D length/perimeter of a geometry on an ellipsoid.

Example command line:

Select ST_Length_Spheroid(linestring.sis_geometry) from linestring;

ST_MakeLine

Creates a LineString from Point, MultiPoint, or LineString geometries. (Also available as an aggregate function).

Example command line:

SELECT ST_MakeLine

ST_Overlaps

Returns TRUE if the Geometries share space, are of the same dimension, but are not completely contained by each other.

Example command line:

SELECT ST_Overlaps

ST_Perimeter

Returns the length measurement of the boundary of an ST_Surface or ST_MultiSurface geometry.

Example command line:

SELECT ST_Perimeter(overlay1.sis_geometry) FROM overlay1;

SELECT ST_Perimeter(ST_GeomFromText(('POLYGON((743238,2967416,743238 2967450,743265 2967450, 743265.625 2967416,743238 2967416))', 2249))

ST_PointOnSurface

Returns an ST_Point guaranteed to lie on the surface of an ST_Polygon or ST_MultiPolygon.

Example command line:

SELECT ST_PointOnSurface

ST_Relate

Compares two geometries and returns true if one Geometry is spatially related to another Geometry, by testing for intersections between the Interior, Boundary and Exterior of the two geometries.

Example command line:

SELECT ST_Relate

ST_SetSRID

Sets the SRID (Spatial Reference System Identifier) of a geometry to a particular integer value.

Example command line:

SELECT ST_SetSRID(circle.sis_geometry,4326) FROM circle;

ST_SRID

Returns the SRID (Spatial Reference System Identifier) of a geometry object.

Example command line:

SELECT ST_SRID(overlay1.sis_geometry) FROM overlay1;

ST_SRIDFromText

Takes a text representation and a spatial reference ID and returns a geometry object.

Example command line:

SELECT ST_SRIDFromText

ST_SymDifference

Returns Symmetric Difference (also available as an aggregate function)

Example command line:

SELECT ST_SymDifference(overlay1.sis_geometry,overlay.sis_geometry) FROM overlay1,overlay2;

ST_Touches

Returns TRUE if the only points in common between geometry 1 and geometry 2 lie in the union of the boundaries of the two geometries.

Example command line:

SELECT ST_Touches

ST_Transform

Transforms the ST_Geometry into the spatial reference specified by the spatial reference ID SRID (Spatial Reference System Identifier).

Example command line:

SELECT ST_Transform (circles.sis_geometry, 3857) FROM circles;
SELECT ST_Transform (circles.sis_geometry, 26986) FROM circles;

The overlay 'circles' was in EPSG 4326.

ST_Union

Returns an ST_Geometry object that is the union of two source objects. (Also available as an aggregate function).

Example command line:

SELECT ST_Union (features.sis_geometry) FROM features WHERE Value='A';

ST_Within

Returns TRUE if the first object is completely within the second.

Example command line:

SELECT ST_Within

Clears the console screen

Turn command echo on or off

Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on

Turn display of headers on or off

Shows help, ie displays this list of non-SQL commands.

Set output mode where MODE is one of:

• add - Adds found items to current editable overlay
• column - Left-aligned columns (see .width)
• copy - Creates a new internal dataset from found items
• csv - Comma-separated values
• find - Selects found items, if hittable
• insert - SQL insert statements for TABLE
• line - One value per line
• list - Values delimited by .separator strings
• tabs - Tab-separated values

To change mode, type .mode followed by the mode name (no semi-colon) eg “.mode find”

Use STRING in place of NULL values

Send output to FILENAME or console

Execute SQL in FILENAME

Show the schema of TABLE

Change the column separator and optionally the row separator for the output mode

Show the current values for various settings

List names of tables. If TABLE specified, only list tables matching LIKE PATTERN ?TABLE?

Set column widths for "column" mode. Negative values right-justify