Structured Query Language (SQL) is a standard language for communication with a relational database management system (RDBMS). SQL provides commands which are used to create, modify, and access the database. Databases contain one or more tables of data with relationships between multiple tables defined in the database. Each table is an object.
SQL Commands
Select - Used to retrieve selected data. Example:
select "columnname1", "columnname2" from "tablebasename" [where "condition"];
A "*" may be used to select all columns. The where clause is optional and only one column name must be specified.
Insert
Update
Delete
Create
Drop
Spatial Extensions
Manifold System provides additional functions within SQL for selecting objects in drawings based on their geometric properties. Manifold also includes SQL functions to manipulate geometry data saved within tables.
The following table lists available spatial functions. Functions are often employed with data in tables of type Geometry, Geometry (SDE), Geometry (SHP) or Geometry (WKB). Different geometry types have different limitations. For example, the Geometry (SDE) type does not distinguish between lines and areas and uses integer coordinate values, and none of the geometry types except Geometry stores coordinate system data.
See the Geometry in Tables and the Queries and Geoms topics for additional information on storing and using geometry in tables.
Function
Comments:
AddCoord(line, point)
Adds the given coordinate to the first branch of the given line and returns the result.
Adjacent(geom, geom)
Returns True if the objects share at least one common location, and all common locations shared by the objects lie on a boundary of both objects; otherwise returns False.
AllBranches(geom)
Works on geoms of the same type and creates a geom with all branches of all source geoms. An aggregate function.
AllCoords(geom)
Works on geoms of any type and creates a multipoint geom with all coordinates (inflection points) of all source geoms. An aggregate function.
Area(geom[, unit])
Returns the area of the object in the specified units.
AreaEarth(geom[, unit])
Returns the ellipsoidal area of the object in the specified metric units.
AssignCoordSys(geom, coordsys)
Assigns a coordinate system to a geom and returns the result.
BorderBuffer(geom, width[, unit])
Returns border buffer of specified width for given area.
Boundary(geom)
Returns boundary of given area.
BoundingBox(geom)
Returns bounding box of given geometric object.
Branch(geom, index)
Returns specified branch of given geometric object.
BranchCount(geom)
Returns number of branches in given geometric object.
BranchOf(geom, index)
Returns number of branch in given geometric object that contains point with specified index.
Buffer(geom, width[, unit])
Returns buffer of specified width for given geometric object.
Centroid(geom)
Returns centroid of given geometric object.
CentroidBox(geom)
Returns box centroid of given geometric object.
CentroidInner(geom)
Returns inner centroid of given area. Returns NULL for line or point.
CentroidWeight(geom)
Returns weight centroid of given area. Returns NULL for line or point.
CentroidX(geom)
Returns the X value of the object's centroid coordinates.
CentroidY(geom)
Returns the Y value of the object's centroid coordinates.
ClipIntersect(geom, clipWith)
Returns parts of given geometric object lying within clipping area.
ClipSubtract(geom, clipWith)
Returns parts of given geometric object lying outside clipping area.
Contains(geom, geom)
Returns True if the first object entirely contains the second object.
ConvertToArea(geom)
Converts a line or a point to an area. Branches containing less than 3 coordinates are padded with the last coordinate.
ConvertToLine(geom)
Converts an area or a point to a line. Branches containing less than 2 points are padded with the last coordinate.
ConvertToPoint(geom)
Converts an area or a line to a point set.
ConvexHull(geom)
Returns convex hull of given geometric object.
ConvexParts(geom[, tolerance])
Decomposes given area to convex parts. The tolerance parameter is in the coordinate system units of the geom.
Coord(geom, index)
Returns coord (inflection point) of given geometric object with specified index.
CoordCount(geom)
Returns total number of coords (inflection points) in all branches of given geometric object.
CoordSys(system
[AS COMPONENT])
Loads a coordinate system from a component or a preset.
CoordSysToWKT(coordsys)
Prints a coordinate system into a WKT (PRJ) string.
Distance(geom, geom[, unit])
Returns the distance between the two objects in the specified units.
DistanceEarth(geomA, geomB [, unit])
Returns ellipsoidal distance between given points.
EnclosingCircle(geom)
Returns minimum circle containing given geometric object.
EnclosingRectangle(geom)
Returns minimum rectangle containing given geometric object.
EndPoint(geom)
Returns last point in given line that contains one branch.
FlipHorizontally(geom)
Flips given geometric object horizontally and returns the result.
FlipVertically(geom)
Flips given geometric object vertically and returns the result.
GeomType(geom)
Returns type of given geometric object as a number.
InnerBuffer(geom, width[, unit])
Returns inner buffer of specified width for given area.
IntersectionPoint(line, lineOther)
Returns arbitrary intersection point of two given lines.
IntersectLine(line, geom)
Intersects given line with another geometric object by splitting it into branches at intersection points and returns the results.
Intersects(geom, geom)
Returns True if the objects share at least one common location that does not lie on a boundary of both objects; otherwise returns False.
IsArea(geom)
Returns True if the object is an area; otherwise returns False.
IsClosed(geom)
Returns True if given line is closed and False otherwise.
IsLine(geom)
Returns True if the object is a line; otherwise returns False.
IsPoint(geom)
Returns True if the object is a point; otherwise returns False.
IsRing(geom)
Returns True if given line is closed and contains one (and only one) branch with no self-intersections and False otherwise.
JoinLines(line, lineOther)
Joins two given lines and returns the result.
Length(geom[, unit])
Returns the length or the perimeter of the object in the specified units.
LengthEarth(geom[, unit])
Returns the ellipsoidal length or the perimeter of the object in the specified metric units.
LinePart(geom, f, t[, unit])
Returns that part of a line between specified distance limits on given line. Both distances are measured from the start of the line and continue between branches. f is the "from" distance and t is the "to" distance for the new line. Suppose we have a road that is 12 miles long. LinePart(0, 6, "mi") will return the first half of the road, and LinePart(12, 6, "mi") will return the second half of the road reversed in backward direction. If a unit is not specified, the default native unit of the geom is used.
LinePoint(geom, d[, unit])
Returns a point that lies at the specified distance on given line. The distance is measured from the start of line and continues between branches.
MaxX(geom)
Returns the maximum X value in the object's coordinates.
MaxY(geom)
Returns the maximum Y value in the object's coordinates.
MinX(geom)
Returns the minimum X value in the object's coordinates.
MinY(geom)
Returns the minimum Y value in the object's coordinates.
MoveHorizontally(geom, distance[, unit])
Moves given geometric object by specified distance horizontally and returns the result.
MoveVertically(geom, distance[, unit])
Moves given geometric object by specified distance vertically and returns the result.
NewLine(geom, geom[, geom ...])
Returns a line using two or more point geom arguments or two or more line geom arguments. The coordinate system of the line equals that of the first geom.
If all of the arguments are point geoms, the result is a line with a single branch from the first point to the last. If all of the arguments are lines, the result is a line with multiple branches. Otherwise (for example, a mix of point and line geoms), the result is a NULL.
NewPoint(x, y)
Returns a point with given XY coordinates in default coordinate system.
NewPointLatLon(x, y)
Returns a point with given XY coordinates in lat/lon coordinate system.
Normalize(geom)
Normalizes metric of given geometric object and returns the result.
Project(geom, system)
Project given geometric object to another coordinate system.
RectHeight(geom[, unit])
Returns the height of the object bounding box in the specified units.
RectWidth(geom[, unit])
Returns the width of the object bounding box for the object in the specified units.
ReverseLine(geom)
Reverses direction of all branches in given line and returns the result.
Rotate(geom, angle)
Rotates given geometric object by specified amount of degrees and returns the result.
RotateAbout(geom, center, angle)
Rotates given geometric object, geom, about another geometric object, the center, by specified amount in degrees in angle and returns the result. If the object given as the center is a line or area the centroid of the object will be used for the axis of rotation.
Scale(geom, factor)
Scales given geometric object by specified factor and returns the result.
ScaleHorizontally(geom, factor)
Scales given geometric object by specified factor horizontally and returns the result.
ScaleVertically(geom, factor)
Scales given geometric object by specified factor vertically and returns the result.
Segments(geom, subsegments)
Splits each segment in given line or area into specified number of subsegments and returns the result.
ShapeHull(geom, cells)
Returns shape hull of given geometric object.
StartPoint(geom)
Returns first point in given line that contains one branch.
Touches(geom, geom)
Returns True if the objects share at least one common location; otherwise returns False.
Triangles(geom[, tolerance])
Returns a triangulation of the given area. The tolerance parameter is in the coordinate system units of the geom.
Triangulation(geom[, tolerance])
Returns areas in the triangulation of a point set. The tolerance parameter is in the coordinate system units of the geom. See the Transform - Triangulation topic for a discussion of triangulation.
TriangulationLines(geom[, tolerance])
Returns lines in the triangulation of a point set. The tolerance parameter is in the coordinate system units of the geom. See the Transform - Triangulation topic for a discussion of triangulation.
UnionAll(geom)
Unions all areas. An aggregate function.
UnionAreas(geom, geom)
Unions two given areas and returns the result.
Voronoi(geom[, tolerance])
Returns areas in the Voronoi diagram of a point set. The tolerance parameter is in the coordinate system units of the geom. See the Transform - Voronoi Operators topic for a discussion of Voronoi diagrams (also called Dirichlet or Thiessen tessellations by some cultures).
VoronoiLines(geom[, tolerance])
Returns lines in the Voronoi diagram of a point set. The tolerance parameter is in the coordinate system units of the geom.
VoronoiPoints(geom[, tolerance])
Returns points in the Voronoi diagram of a point set. The tolerance parameter is in the coordinate system units of the geom.
WKTToCoordSys(wkt)
Parses a WKT (PRJ) string into a coordinate system.
See the Expressions topic for additional SQL expressions.
Aggregate Functions
Queries can use the AllBranches, AllCoords and UnionAll aggregate functions to aggregate Geom columns.
Importing or linking a geom created by AllBranches creates one line, one area, or one or more points. Importing or linking a geom created by AllCoords creates one point at the location of the first coordinate. To import the value returned by AllCoords as a set of points, alter the query to split the composite value returned by AllCoords into a set of individual coordinates by using the SPLIT BY clause. Importing or linking a geom created by UnionAll creates one area. If none of the argument areas overlap, the area returned by UnionAll is the same as that returned by AllBranches.
Split Functions
Queries can use the Branches, Coords, and Islands split functions to split Geom columns. See the SPLIT BY clause topic for details.
Branches outputs each branch of an input geometric object as a separate object. Coords outputs each coordinate of an input geometric object as a separate point object. Islands outputs each island of an input area as a separate area.
Remarks
Geom arguments are either geometric objects of type Geometry or are object IDs.
All spatial computations are done using the location precision parameter taken from the drawing of the first argument if it is an object ID, or the maximum possible precision if it is a geometric object.
All spatial computations are done using the coordinate system of the first argument. Performing spatial computations using data in different coordinate systems will convert the data in the second and any subsequent arguments to the coordinate system of the first argument.
Some functions, such as Area or Distance, allow specifying measurement units. When specified, a unit should be compatible with the coordinate system of the first argument, in that if the coordinate system is Latitude / Longitude the unit must be angular (degrees or other angular unit), and if the coordinate system is not Latitude / Longitude the unit must be metric.
If the unit argument is omitted the functions will use the units specified in the coordinate system of the first argument adjusted with the values of the local scale parameters, the adjusted result being equivalent to the component's native unit. See the Appendices - Tables - Units topic for a list of standard units.
When using buffer functions, the buffer distance used must be at least four times the size of the location precision factor. If precision has been set to 10 meters then the buffer distance must be larger than 40 meters.
Examples
See the Sql.map sample project in the Manifold CD's Examples\Help folder for a Manifold project that contains SQL examples like those below.
We have a drawing D and want to compute its bounding box. We can do this with the following query:
SELECT BoundingBox(AllCoords(Geom([ID]))) FROM [D];
We have a drawing D that contains road segments with each segment assigned a road name in a column called Name. We want to join segments for each road into a single line object. We can do this with the following query:
SELECT AllBranches(Geom([ID])), [Name] FROM [D] GROUP BY [Name];
For the examples below, suppose we have two drawings called "Restaurants" and "Metro Stations" that contain point objects, a "Roads" drawing that contains line objects, and a "Parks" drawing that contains area objects.
This example uses the Area function to select the two largest parks:
SELECT TOP 2 [Park Name] FROM [Parks] ORDER BY Area([Parks].[ID]) DESC;
This example uses the Distance function to select the nearest restaurant to each metro station:
SELECT [Metro Station Name], (SELECT TOP 1 [Restaurant Name] FROM [Restaurants] ORDER BY Distance([Metro Stations].[ID], [Restaurants].[ID])) FROM [Metro Stations];
This example uses the Contains function to select all restaurants within parks:
SELECT [Restaurant Name], [Park Name] FROM [Restaurants], [Parks] WHERE Contains([Parks].[ID], [Restaurants].[ID]);
This example uses the Intersects function to select all roads that intersect parks:
SELECT [Road Name], [Park Name] FROM [Roads], [Parks] WHERE Intersects([Roads].[ID], [Parks].[ID]);
This example uses the Intersects function to select all roads that intersect other roads:
SELECT [Roads].[Road Name], [Copy].[Road Name] FROM [Roads], [Roads] AS [Copy] WHERE [Roads].[ID] <> [Copy].[ID] AND Intersects([Roads].[ID], [Copy].[ID]);
This example uses the Adjacent function to select all roads near a park that are close to a metro station:
SELECT [Road Name] FROM [Roads]
WHERE EXISTS (SELECT * FROM [Parks]
WHERE Adjacent([Parks].[ID], [Roads].[ID]))
AND EXISTS (SELECT * FROM [Metro Stations]
WHERE Distance([Metro Stations].[ID], [Roads].[ID], "yd") < 200);
Suppose we have two drawings containing points where each point represents a ship. Each point has a "Ship ID" column that specifies the identifying number of the ship. Drawing "Monday" shows the positions of the ships on Monday and drawing "Tuesday" shows the positions of the ships on Tuesday. We would like to create a table that shows the distance between the Monday position and the Tuesday position for each ship.
SELECT [Monday].*, Distance([Monday].[ID], [Tuesday].[ID], "km") AS [Sail Distance] FROM [Monday] INNER JOIN [Tuesday] ON [Monday].[Ship ID] = [Tuesday].[Ship ID];
This will create a table with all columns from the "Monday" drawing plus a computed column called "Sail Distance" containing the distance in kilometers between ship positions on Monday and Tuesday. Monday must be a projected drawing to use the optional "km" specification of units. The objects in the Tuesday drawing will be re-projected on the fly, if necessary, into the coordinate system of the Monday drawing so that the same units can be used in both for the purposes of the query.
Advanced Example
This example shows three queries that do the same thing with greatly different performance. This illustrates the importance of considering different SQL approaches to achieve performance gains. The queries operate on a project containing a surface, S, and a drawing, D. The drawing has lines in it, and our task is to select all pixels in the surface that are in a buffer zone within 20 units of a line in the drawing.
Query 1 below is very slow because it re-computes the buffer returned by the inner SELECT for each pixel. The Manifold query engine is smart enough to cache the selected lines the SELECT operates upon, but it is not smart enough to cache the result of the aggregate on these lines. The solution is to get rid of computations in the column list of the SELECT (as is done in Query 2), or, better yet, to get rid of the SELECT in favor of a JOIN (as is done in Query 3).
The number of seconds cited for query execution of each query use a sample set of data that provides a reasonable comparison of the efficiency of the three queries.
Query 1 - Approximately 2500 seconds:
UPDATE [S] SET [Selection (I)] = True
WHERE Intersects(SELECT Buffer(AllBranches([ID]), 20) FROM [D],
NewPointLatLon([S].[Longitude (I)], [S].[Latitude (I)]))
Query 2 - Computations in the column list are reduced to a minimum - 29 seconds:
UPDATE [S] SET [Selection (I)] = True
WHERE (SELECT Min(Distance([ID],
NewPointLatLon([S].[Longitude (I)], [S].[Latitude (I)])))
FROM [D]) <= 20
Query 3 - SELECT is replaced with an INNER JOIN - 3 seconds:
UPDATE (SELECT [S].[Selection (I)] FROM [S]
INNER JOIN [D] ON Distance([D].[ID],
NewPointLatLon([S].[Longitude (I)], [S].[Latitude (I)])) <= 20)
SET [Selection (I)] = True
The following table lists available spatial functions. Functions are often employed with data in tables of type Geometry, Geometry (SDE), Geometry (SHP) or Geometry (WKB). Different geometry types have different limitations. For example, the Geometry (SDE) type does not distinguish between lines and areas and uses integer coordinate values, and none of the geometry types except Geometry stores coordinate system data.
See the Geometry in Tables and the Queries and Geoms topics for additional information on storing and using geometry in tables.
Function
Comments:
AddCoord(line, point)
Adds the given coordinate to the first branch of the given line and returns the result.
Adjacent(geom, geom)
Returns True if the objects share at least one common location, and all common locations shared by the objects lie on a boundary of both objects; otherwise returns False.
AllBranches(geom)
Works on geoms of the same type and creates a geom with all branches of all source geoms. An aggregate function.
AllCoords(geom)
Works on geoms of any type and creates a multipoint geom with all coordinates (inflection points) of all source geoms. An aggregate function.
Area(geom[, unit])
Returns the area of the object in the specified units.
AreaEarth(geom[, unit])
Returns the ellipsoidal area of the object in the specified metric units.
AssignCoordSys(geom, coordsys)
Assigns a coordinate system to a geom and returns the result.
BorderBuffer(geom, width[, unit])
Returns border buffer of specified width for given area.
Boundary(geom)
Returns boundary of given area.
BoundingBox(geom)
Returns bounding box of given geometric object.
Branch(geom, index)
Returns specified branch of given geometric object.
BranchCount(geom)
Returns number of branches in given geometric object.
BranchOf(geom, index)
Returns number of branch in given geometric object that contains point with specified index.
Buffer(geom, width[, unit])
Returns buffer of specified width for given geometric object.
Centroid(geom)
Returns centroid of given geometric object.
CentroidBox(geom)
Returns box centroid of given geometric object.
CentroidInner(geom)
Returns inner centroid of given area. Returns NULL for line or point.
CentroidWeight(geom)
Returns weight centroid of given area. Returns NULL for line or point.
CentroidX(geom)
Returns the X value of the object's centroid coordinates.
CentroidY(geom)
Returns the Y value of the object's centroid coordinates.
ClipIntersect(geom, clipWith)
Returns parts of given geometric object lying within clipping area.
ClipSubtract(geom, clipWith)
Returns parts of given geometric object lying outside clipping area.
Contains(geom, geom)
Returns True if the first object entirely contains the second object.
ConvertToArea(geom)
Converts a line or a point to an area. Branches containing less than 3 coordinates are padded with the last coordinate.
ConvertToLine(geom)
Converts an area or a point to a line. Branches containing less than 2 points are padded with the last coordinate.
ConvertToPoint(geom)
Converts an area or a line to a point set.
ConvexHull(geom)
Returns convex hull of given geometric object.
ConvexParts(geom[, tolerance])
Decomposes given area to convex parts. The tolerance parameter is in the coordinate system units of the geom.
Coord(geom, index)
Returns coord (inflection point) of given geometric object with specified index.
CoordCount(geom)
Returns total number of coords (inflection points) in all branches of given geometric object.
CoordSys(system
[AS COMPONENT])
Loads a coordinate system from a component or a preset.
CoordSysToWKT(coordsys)
Prints a coordinate system into a WKT (PRJ) string.
Distance(geom, geom[, unit])
Returns the distance between the two objects in the specified units.
DistanceEarth(geomA, geomB [, unit])
Returns ellipsoidal distance between given points.
EnclosingCircle(geom)
Returns minimum circle containing given geometric object.
EnclosingRectangle(geom)
Returns minimum rectangle containing given geometric object.
EndPoint(geom)
Returns last point in given line that contains one branch.
FlipHorizontally(geom)
Flips given geometric object horizontally and returns the result.
FlipVertically(geom)
Flips given geometric object vertically and returns the result.
GeomType(geom)
Returns type of given geometric object as a number.
InnerBuffer(geom, width[, unit])
Returns inner buffer of specified width for given area.
IntersectionPoint(line, lineOther)
Returns arbitrary intersection point of two given lines.
IntersectLine(line, geom)
Intersects given line with another geometric object by splitting it into branches at intersection points and returns the results.
Intersects(geom, geom)
Returns True if the objects share at least one common location that does not lie on a boundary of both objects; otherwise returns False.
IsArea(geom)
Returns True if the object is an area; otherwise returns False.
IsClosed(geom)
Returns True if given line is closed and False otherwise.
IsLine(geom)
Returns True if the object is a line; otherwise returns False.
IsPoint(geom)
Returns True if the object is a point; otherwise returns False.
IsRing(geom)
Returns True if given line is closed and contains one (and only one) branch with no self-intersections and False otherwise.
JoinLines(line, lineOther)
Joins two given lines and returns the result.
Length(geom[, unit])
Returns the length or the perimeter of the object in the specified units.
LengthEarth(geom[, unit])
Returns the ellipsoidal length or the perimeter of the object in the specified metric units.
LinePart(geom, f, t[, unit])
Returns that part of a line between specified distance limits on given line. Both distances are measured from the start of the line and continue between branches. f is the "from" distance and t is the "to" distance for the new line. Suppose we have a road that is 12 miles long. LinePart(0, 6, "mi") will return the first half of the road, and LinePart(12, 6, "mi") will return the second half of the road reversed in backward direction. If a unit is not specified, the default native unit of the geom is used.
LinePoint(geom, d[, unit])
Returns a point that lies at the specified distance on given line. The distance is measured from the start of line and continues between branches.
MaxX(geom)
Returns the maximum X value in the object's coordinates.
MaxY(geom)
Returns the maximum Y value in the object's coordinates.
MinX(geom)
Returns the minimum X value in the object's coordinates.
MinY(geom)
Returns the minimum Y value in the object's coordinates.
MoveHorizontally(geom, distance[, unit])
Moves given geometric object by specified distance horizontally and returns the result.
MoveVertically(geom, distance[, unit])
Moves given geometric object by specified distance vertically and returns the result.
NewLine(geom, geom[, geom ...])
Returns a line using two or more point geom arguments or two or more line geom arguments. The coordinate system of the line equals that of the first geom.
If all of the arguments are point geoms, the result is a line with a single branch from the first point to the last. If all of the arguments are lines, the result is a line with multiple branches. Otherwise (for example, a mix of point and line geoms), the result is a NULL.
NewPoint(x, y)
Returns a point with given XY coordinates in default coordinate system.
NewPointLatLon(x, y)
Returns a point with given XY coordinates in lat/lon coordinate system.
Normalize(geom)
Normalizes metric of given geometric object and returns the result.
Project(geom, system)
Project given geometric object to another coordinate system.
RectHeight(geom[, unit])
Returns the height of the object bounding box in the specified units.
RectWidth(geom[, unit])
Returns the width of the object bounding box for the object in the specified units.
ReverseLine(geom)
Reverses direction of all branches in given line and returns the result.
Rotate(geom, angle)
Rotates given geometric object by specified amount of degrees and returns the result.
RotateAbout(geom, center, angle)
Rotates given geometric object, geom, about another geometric object, the center, by specified amount in degrees in angle and returns the result. If the object given as the center is a line or area the centroid of the object will be used for the axis of rotation.
Scale(geom, factor)
Scales given geometric object by specified factor and returns the result.
ScaleHorizontally(geom, factor)
Scales given geometric object by specified factor horizontally and returns the result.
ScaleVertically(geom, factor)
Scales given geometric object by specified factor vertically and returns the result.
Segments(geom, subsegments)
Splits each segment in given line or area into specified number of subsegments and returns the result.
ShapeHull(geom, cells)
Returns shape hull of given geometric object.
StartPoint(geom)
Returns first point in given line that contains one branch.
Touches(geom, geom)
Returns True if the objects share at least one common location; otherwise returns False.
Triangles(geom[, tolerance])
Returns a triangulation of the given area. The tolerance parameter is in the coordinate system units of the geom.
Triangulation(geom[, tolerance])
Returns areas in the triangulation of a point set. The tolerance parameter is in the coordinate system units of the geom. See the Transform - Triangulation topic for a discussion of triangulation.
TriangulationLines(geom[, tolerance])
Returns lines in the triangulation of a point set. The tolerance parameter is in the coordinate system units of the geom. See the Transform - Triangulation topic for a discussion of triangulation.
UnionAll(geom)
Unions all areas. An aggregate function.
UnionAreas(geom, geom)
Unions two given areas and returns the result.
Voronoi(geom[, tolerance])
Returns areas in the Voronoi diagram of a point set. The tolerance parameter is in the coordinate system units of the geom. See the Transform - Voronoi Operators topic for a discussion of Voronoi diagrams (also called Dirichlet or Thiessen tessellations by some cultures).
VoronoiLines(geom[, tolerance])
Returns lines in the Voronoi diagram of a point set. The tolerance parameter is in the coordinate system units of the geom.
VoronoiPoints(geom[, tolerance])
Returns points in the Voronoi diagram of a point set. The tolerance parameter is in the coordinate system units of the geom.
WKTToCoordSys(wkt)
Parses a WKT (PRJ) string into a coordinate system.
See the Expressions topic for additional SQL expressions.
Aggregate Functions
Queries can use the AllBranches, AllCoords and UnionAll aggregate functions to aggregate Geom columns.
Importing or linking a geom created by AllBranches creates one line, one area, or one or more points. Importing or linking a geom created by AllCoords creates one point at the location of the first coordinate. To import the value returned by AllCoords as a set of points, alter the query to split the composite value returned by AllCoords into a set of individual coordinates by using the SPLIT BY clause. Importing or linking a geom created by UnionAll creates one area. If none of the argument areas overlap, the area returned by UnionAll is the same as that returned by AllBranches.
Split Functions
Queries can use the Branches, Coords, and Islands split functions to split Geom columns. See the SPLIT BY clause topic for details.
Branches outputs each branch of an input geometric object as a separate object. Coords outputs each coordinate of an input geometric object as a separate point object. Islands outputs each island of an input area as a separate area.
Remarks
Geom arguments are either geometric objects of type Geometry or are object IDs.
All spatial computations are done using the location precision parameter taken from the drawing of the first argument if it is an object ID, or the maximum possible precision if it is a geometric object.
All spatial computations are done using the coordinate system of the first argument. Performing spatial computations using data in different coordinate systems will convert the data in the second and any subsequent arguments to the coordinate system of the first argument.
Some functions, such as Area or Distance, allow specifying measurement units. When specified, a unit should be compatible with the coordinate system of the first argument, in that if the coordinate system is Latitude / Longitude the unit must be angular (degrees or other angular unit), and if the coordinate system is not Latitude / Longitude the unit must be metric.
If the unit argument is omitted the functions will use the units specified in the coordinate system of the first argument adjusted with the values of the local scale parameters, the adjusted result being equivalent to the component's native unit. See the Appendices - Tables - Units topic for a list of standard units.
When using buffer functions, the buffer distance used must be at least four times the size of the location precision factor. If precision has been set to 10 meters then the buffer distance must be larger than 40 meters.
Examples
See the Sql.map sample project in the Manifold CD's Examples\Help folder for a Manifold project that contains SQL examples like those below.
We have a drawing D and want to compute its bounding box. We can do this with the following query:
SELECT BoundingBox(AllCoords(Geom([ID]))) FROM [D];
We have a drawing D that contains road segments with each segment assigned a road name in a column called Name. We want to join segments for each road into a single line object. We can do this with the following query:
SELECT AllBranches(Geom([ID])), [Name] FROM [D] GROUP BY [Name];
For the examples below, suppose we have two drawings called "Restaurants" and "Metro Stations" that contain point objects, a "Roads" drawing that contains line objects, and a "Parks" drawing that contains area objects.
This example uses the Area function to select the two largest parks:
SELECT TOP 2 [Park Name] FROM [Parks] ORDER BY Area([Parks].[ID]) DESC;
This example uses the Distance function to select the nearest restaurant to each metro station:
SELECT [Metro Station Name], (SELECT TOP 1 [Restaurant Name] FROM [Restaurants] ORDER BY Distance([Metro Stations].[ID], [Restaurants].[ID])) FROM [Metro Stations];
This example uses the Contains function to select all restaurants within parks:
SELECT [Restaurant Name], [Park Name] FROM [Restaurants], [Parks] WHERE Contains([Parks].[ID], [Restaurants].[ID]);
This example uses the Intersects function to select all roads that intersect parks:
SELECT [Road Name], [Park Name] FROM [Roads], [Parks] WHERE Intersects([Roads].[ID], [Parks].[ID]);
This example uses the Intersects function to select all roads that intersect other roads:
SELECT [Roads].[Road Name], [Copy].[Road Name] FROM [Roads], [Roads] AS [Copy] WHERE [Roads].[ID] <> [Copy].[ID] AND Intersects([Roads].[ID], [Copy].[ID]);
This example uses the Adjacent function to select all roads near a park that are close to a metro station:
SELECT [Road Name] FROM [Roads]
WHERE EXISTS (SELECT * FROM [Parks]
WHERE Adjacent([Parks].[ID], [Roads].[ID]))
AND EXISTS (SELECT * FROM [Metro Stations]
WHERE Distance([Metro Stations].[ID], [Roads].[ID], "yd") < 200);
Suppose we have two drawings containing points where each point represents a ship. Each point has a "Ship ID" column that specifies the identifying number of the ship. Drawing "Monday" shows the positions of the ships on Monday and drawing "Tuesday" shows the positions of the ships on Tuesday. We would like to create a table that shows the distance between the Monday position and the Tuesday position for each ship.
SELECT [Monday].*, Distance([Monday].[ID], [Tuesday].[ID], "km") AS [Sail Distance] FROM [Monday] INNER JOIN [Tuesday] ON [Monday].[Ship ID] = [Tuesday].[Ship ID];
This will create a table with all columns from the "Monday" drawing plus a computed column called "Sail Distance" containing the distance in kilometers between ship positions on Monday and Tuesday. Monday must be a projected drawing to use the optional "km" specification of units. The objects in the Tuesday drawing will be re-projected on the fly, if necessary, into the coordinate system of the Monday drawing so that the same units can be used in both for the purposes of the query.
Advanced Example
This example shows three queries that do the same thing with greatly different performance. This illustrates the importance of considering different SQL approaches to achieve performance gains. The queries operate on a project containing a surface, S, and a drawing, D. The drawing has lines in it, and our task is to select all pixels in the surface that are in a buffer zone within 20 units of a line in the drawing.
Query 1 below is very slow because it re-computes the buffer returned by the inner SELECT for each pixel. The Manifold query engine is smart enough to cache the selected lines the SELECT operates upon, but it is not smart enough to cache the result of the aggregate on these lines. The solution is to get rid of computations in the column list of the SELECT (as is done in Query 2), or, better yet, to get rid of the SELECT in favor of a JOIN (as is done in Query 3).
The number of seconds cited for query execution of each query use a sample set of data that provides a reasonable comparison of the efficiency of the three queries.
Query 1 - Approximately 2500 seconds:
UPDATE [S] SET [Selection (I)] = True
WHERE Intersects(SELECT Buffer(AllBranches([ID]), 20) FROM [D],
NewPointLatLon([S].[Longitude (I)], [S].[Latitude (I)]))
Query 2 - Computations in the column list are reduced to a minimum - 29 seconds:
UPDATE [S] SET [Selection (I)] = True
WHERE (SELECT Min(Distance([ID],
NewPointLatLon([S].[Longitude (I)], [S].[Latitude (I)])))
FROM [D]) <= 20
Query 3 - SELECT is replaced with an INNER JOIN - 3 seconds:
UPDATE (SELECT [S].[Selection (I)] FROM [S]
INNER JOIN [D] ON Distance([D].[ID],
NewPointLatLon([S].[Longitude (I)], [S].[Latitude (I)])) <= 20)
SET [Selection (I)] = True
SQL Server Spatial DBMS Facilities
Manifold supports several ways of performing spatial DBMS work with Microsoft SQL Server:
· Microsoft SQL Server 2008 (codenamed Katmai) - This new Microsoft product will provide spatial support using native spatial DBMS capability like other spatial DBMS packages such as Oracle Spatial or IBM DB2 with Spatial Extender. Manifold Enterprise Edition and higher editions have built-in capability to work with native SQL Server 2008 spatial capabilities, including both GEOMETRY and GEOGRAPHY data types. As of this writing, Microsoft has released a spatially-capable Community Technology Preview (CTP) for free download by the public. Manifold will track new Microsoft CTPs as they are released and will continue to support SQL Server 2008 spatial when it is released to the public. SQL Server 2008 will be the first choice for spatial DBMS work in the SQL Server community. See the Example: Configuring SQL Server 2008 topic before working with a SQL Server 2008 CTP.
· Microsoft SQL Server 2005 with Manifold Spatial Extender - Manifold has created a spatial extender for SQL Server 2005 to enable users to work with SQL Server 2005 as a high-performance spatial DBMS until SQL Server 2008 is released. The Manifold spatial extender utilizes Manifold geometry and Manifold spatial indices with a small amount of code that is uploaded into the SQL Server 2005 server for execution server-side. The uploaded code enables SQL Server 2005 to achieve better performance than a purely generic use of spatial indices. The Manifold Spatial Extender also works with SQL Server 2008; however, if we have SQL Server 2008 we should use SQL Server 2008's spatial capabilities. Note: When used with SQL Server Express 2005 the Manifold Spatial Extender requires SQL Server Express 2005 SP2.
· Microsoft SQL Server with Manifold Generic Spatial Index - This applies to SQL Server 2000 and more recent SQL Server editions such as SQL Server 2005 and SQL Server 2008. Because SQL Server, like almost every other big-name DBMS, provides binary storage we can use Manifold's generic, non-native spatial DBMS capability within SQL Server using whatever Manifold-supported geometry type we like (such as Manifold's Geometry or OGC WKB) together with Manifold-created spatial indices. This usage of SQL Server is just as we would use any other ordinary DBMS, such as MySQL, as a spatial DBMS through Manifold-managed spatial facilities. For performance reasons, with SQL Server 2005 it is always a better idea to use the Manifold Spatial Extender for SQL Server than to use generic spatial indices. Manifold generic spatial indices also work with SQL Server 2008; however, if we have SQL Server 2008 we should use SQL Server 2008's spatial capabilities.
· Microsoft SQL Server 2005 with ESRI SDE - ESRI products can utilize a SQL Server 2005 installation to host an ESRI SDE geodatabase. If SQL Server 2005 hosts an ESRI SDE geodatabase Manifold can connect to that ESRI SDE geodatabase and import and link drawings using ESRI conventions. It is possible for SQL Server 2005 to be used by ESRI products to host Personal geodatabases as well, in which case Manifold can also connect and work with that geodatabase like the SDE case. However, as of the present writing ESRI Personal geodatabases are usually encountered only in the form of Access .MDB files and only rarely using SQL Server, where SDE appears to predominate.
Manifold can store images and surfaces in SQL Server 2008 and SQL Server 2005.
SQL Server 2008 Geometry Types
SQL Server 2008 spatial capabilities include support for two native geometry types: a planar GEOMETRY type similar to the geometry types used by other spatial DBMS products and a geodetic GEOGRAPHY type. The internal workings of either type have not been documented by Microsoft as of this writing, so Manifold uses these types the same way as it does SDO_GEOMETRY from Oracle Spatial.
That is, when data is brought in from SQL Server native GEOMETRY or GEOGRAPHY storage by importing or linking a drawing it is dynamically cast into Manifold Geometry type. When a Manifold drawing is exported into SQL Server, Manifold Geometry is automatically cast into SQL Server native GEOMETRY.
Any edits or other operations performed on SQL Server native GEOMETRY or GEOGRAPHY drawings that are linked into Manifold are dynamically manipulated so that whatever is done in the Manifold session is correctly updated within SQL Server native storage. Any re-projection required to show a linked SQL Server native GEOMETRY or GEOGRAPHY drawing within, say, a Manifold map in a different projection will automatically be done on the fly.
Note that Manifold can import or link a drawing from either SQL Server native GEOMETRY or GEOGRAPHY but that exports from Manifold will always be exported into SQL Server native GEOMETRY. Exports are not available into SQL Server GEOGRAPHY.
If you have a drawing that uses SQL Server GEOGRAPHY Manifold will be happy to use it. You can import it or link it into Manifold. If you link it into your Manifold project, Manifold will happily allow you to edit objects in it, to delete them, to create new objects and so forth. But Manifold will not create a new drawing for you within SQL Server that uses GEOGRAPHY. This limitation arises from the unique nature of the GEOGRAPHY type and may be relaxed in future versions. For the time being, it is not in any way a limitation for GIS operations since GIS data at the present time is universally planar in form, such as the SQL Server GEOMETRY type.
It is also possible that should Microsoft document either the GEOMETRY or GEOGRAPHY type Manifold will add these as intrinsic types that can be held in storage outside of SQL Server, much as can OGC WKB be used in the form of the Manifold Geometry(WKB) type.
SQL Server 2008 Projections
The GEOGRAPHY type always uses Latitude / Longitude. Importing or linking a drawing from SQL Server 2008 that uses GEOGRAPHY type will import or link the drawing into Manifold using Latitude / Longitude projection.
Note for programmers: At the present writing, SQL Server 2008 GEOGRAPHY type is unique in that it uses (latitude, longitude), that is (Y, X), ordering instead of the (X, Y), (longitude, latitude) ordering that is universally used by all other spatial DBMS and GIS products and essentially all other vector and raster GIS formats, including SQL Server's own GEOMETRY type when storing Latitude / Longitude data. When importing or linking GEOGRAPHY, Manifold will automatically swap coordinate ordering into the (X, Y) order expected by all other systems, including Manifold. Programmers writing data directly into SQL Server 2008 GEOGRAPHY type should be aware that the SQL Server order is reversed both from conventional practice and from GEOMETRY type as well.
When reading and writing spatial data stored on SQL Server 2008 spatial using Microsoft's GEOMETRY type, Manifold represents coordinate systems using EPSG codes. EPSG, the former European Petroleum Survey Group organization, has been absorbed into the International Association of Oil and Gas Producers (OGP), but the database of coordinate systems is still widely known as EPSG. EPSG comprises one of the largest vendor-neutral, unambiguous and expertly-developed sets of coordinate systems known. Using EPSG with SQL Server 2008 spatial GEOMETRY is a great way of eliminating the confusion one might encounter with proprietary methods of specifying coordinate systems.
Although EPSG codes cover very many coordinate systems, because Manifold can handle a much larger set of projections than those covered by EPSG, it is possible that a drawing in Manifold uses a coordinate system (projection) not available in EPSG. For example, EPSG does not have a code for Orthographic projection.
If a Manifold drawing uses a coordinate system not supported by EPSG, when the drawing is exported into SQL Server 2008 GEOMETRY, Manifold will re-project it on the fly into Latitude / Longitude form (using EPSG code 4326). Built-in Manifold coordinate systems include all EPSG coordinate systems so that drawings imported or linked from SQL Server 2008 GEOMETRY need never be re-projected for use in Manifold.
SQL Server 2008 spatial allows third parties to choose some other coding method to represent coordinate systems instead of EPSG. If some third party application has used some other coding scheme then Manifold will not be able to automatically detect the correct coordinate system in use, and the correct coordinate system will have to be specified using the Edit - Assign Projection dialog after the drawing is imported or linked into Manifold.
Re-projection in SQL Server 2008
Note that although Manifold will happily re-project data as necessary and can even re-project on the fly so that drawings stored within SQL Server 2008 using different projections can be used together for spatial analytics such as topology overlays, as of the present writing SQL Server 2008 does not have server-side re-projection capability. This means that the usual Manifold facilities for changing projections of drawings stored in spatial DBMS won't work if native SQL Server 2008 spatial enhancements are used. We can, however, still use Administrator Console (available in Database Administrator edition or Ultimate edition) to examine the projections in use.
Launching the administrator console we can connect to SQL Server 2008 using the data source that is configured in the example topics below.
We press the Columns button in the console's toolbar to turn on the Projection column if it is not already visible. This reports the projection in use by drawings. We choose a drawing, a version of the standard Mexico example, that has been uploaded to SQL Server 2008 spatial storage using an unusual projection, MTM Zone 3. We double-click into the projection cell for that drawing.
The Projection dialog used with EPSG codes reports the details of the projection in use. We can scroll down to EPSG 4326 and click on it to highlight it. We can try to re-project by pressing the OK button.
However, we cannot modify the projection (coordinate system) in place.
If we want to re-project the drawing this is easy to do in Manifold: import the drawing into Manifold, re-project it within Manifold, and then export it back into SQL Server 2008.
The Manifold Spatial Extender
The Manifold Spatial Extender for SQL Server is a free extension module for SQL Server which provides spatial index functionality for Manifold System. The Manifold spatial extender works with all versions of SQL Server starting with SQL Server 2005. Always make a point of installing the Manifold spatial extender on the server machine whenever using SQL Server 2005 as a spatial DBMS.
Although the Manifold spatial extender works fine with SQL Server 2008 as well, normally we would use native SQL Server 2008 spatial capabilities instead of installing the Manifold spatial extender and using generic Manifold spatial capabilities.
See the Manifold Spatial Extender for SQL Server topic for details.
SQL Server 2008 Examples
SQL Server 2008 CTP 5 has been used in a series of example topics illustrating work with this fine new Microsoft spatial DBMS product.
Example: Configuring SQL Server 2008
Example: Linking a Drawing from SQL Server 2008
Example: Storing a Drawing in Manifold Spatial DBMS
Example: Storing a Drawing in SQL Server 2008
Example: Tracing Virtual Earth into SQL Server 2008
Example: Storing an Image in SQL Server 2008
SQL Server 2005 Examples
SQL Server 2005 Express has been used in a series of example topics illustrating use of generic Manifold spatial DBMS capabilities. Many of the techniques illustrated (such as use of the Administrator Console) also apply to SQL Server 2008.
Example: Storing a Drawing in Manifold Spatial DBMS
Example: Storing an Image in Manifold Spatial DBMS
Example: Storing a Surface in Manifold Spatial DBMS