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.

Branches(geom)

Splits geometric object into branches. A split function.

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.

Coords(geom)

Splits geometric object into coordinates. A split function.

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)

For points, returns True if a point belongs to another object, otherwise returns False. For areas and lines, returns True if the objects share at least one common location that does not lie on a boundary of both objects and if neither object contains the other object; 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.

Islands(geom)

Splits area into islands. An island is a subarea which consists of an outer contour and zero or more inner contours (holes). A split function.

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

 

Reducing the time required for the query from 2500 to 3 seconds is a huge gain, certainly worth the time spent learning constructs such as INNER JOIN.

 

Examples of Queries using Geometry Data

 

See the Linked Drawings topic and the Geometry in Tables topic.

 

See Also

Selecting Objects with Queries

Using SQL to Select Map Objects

Geocoding Extensions

Raster Extensions

Linked Drawings

Geometry in Tables

Queries and Geoms