Queries and Geoms

Geoms are drawing metrics stored in tables from which linked drawings may be created. See the Linked Drawings and the Geometry in Tables topics for an introduction to geom data.

 

The numerous Spatial Extensions in Manifold SQL allow using SQL as a very powerful and flexible way of manipulating drawing objects when data for object metrics is stored as a geom in a table. In this conceptual model, all object data is stored as geoms in tables (or converted to geoms on the fly from an existing drawing) and whenever a drawing is desired it is created on the fly as a linked drawing.

 

In this model SQL becomes the user interface for manipulating data that is geographic or geometric in nature. Although it may seem retrograde to move away from a WYSIWYG, point and click visual interface into an SQL interface, for certain analyses or manipulations of data SQL offers a tool of formidable compactness and power that can be a better approach. This is especially true when automating procedures that are ill-suited for a lengthy series of point-and-click visual operations.

 

This topic provides a miscellaneous collection of notes and examples using spatial SQL with geoms to illustrate the wide-ranging power of spatial SQL and geoms.

 

Create a Triangulation

 

Triangulate all points in drawing D and output triangulation lines:

 

SELECT TriangulationLines(AllCoords([Geom (I)])) FROM [D]

WHERE IsPoint([ID])

 

Using the SPLIT BY Clause

 

The SPLIT BY clause allows us to split tables using the content of one or more columns.

 

The SPLIT BY clause has the reverse effect of the GROUP BY clause: the GROUP BY clause collapses groups of records into single records, and the SPLIT BY clause explodes single records into groups of records. Unlike the GROUP BY clause, the SPLIT BY clause does not require the use of aggregate functions.

 

Consider a hypothetical table with columns called ID (an integer field) and List (a text field). The values in the ID column are used to uniquely identify each record. The values in the List column contain a list of names separated by commas. Let us say we want to transform this table so that each original record is replaced with one or more records with individual names in the List field corresponding to the same ID.

 

For a conceptual example, if we had a table…

 

ID

List

37

Orange, Blue, Red

38

Yellow, Green

39

Purple

 

…we would like to transform it into…

 

ID

List

37

Orange

37

Blue

37

Red

38

Yellow

38

Green

39

Purple

 

We can use the SPLIT BY clause to accomplish the above. The above is a conceptual example only to illustrate how a group or list of items is split by a given field, the ID field. In actual practice, the SPLIT BY clause is used to split tables by one or more geometry columns. For example, we could use the SPLIT BY clause to split a table of lines by branches.

 

A split function is used to control how the SPLIT BY clause splits data. Currently, the SPLIT BY clause supports the following split functions:

 

Branches

Splits geoms by branches

Islands

Splits area geoms by islands

Coords

Splits geoms by coords (inflection points).

 

SPLIT BY Examples

 

The following query outputs each branch of each object in drawing D as a separate geom:

 

SELECT [ID], [P] FROM [D] SPLIT BY Branches(Geom([ID])) AS [P];

 

The following query creates a convex hull of each area in drawing D and outputs its coords (inflection points). Linking this query as a drawing will produce a point object at each coord of each created convex hull:

 

SELECT [ID], [P] FROM [D] WHERE IsArea([ID]) SPLIT BY Coords(ConvexHull(Geom([ID]))) AS [P];

 

The following query creates a Voronoi diagram for all points in drawing D and outputs the Voronoi areas for each point:

 

SELECT [P] FROM (SELECT Voronoi(AllCoords(Geom([ID]))) AS [V] FROM [D] WHERE IsPoint([ID])) SPLIT BY Branches([V]) AS [P];

 

The following query creates a Voronoi diagram for all points in drawing D and outputs the Voronoi areas for each point whose ID exceeds 5:

 

SELECT [ID], [P] FROM (SELECT [P] FROM (SELECT Voronoi(AllCoords (Geom([ID]))) AS [V] FROM [D] WHERE IsPoint([ID])) SPLIT BY Branches([V]) AS [P]) INNER JOIN [D] ON Contains([P], [ID]) WHERE IsPoint([ID]) AND [ID] > 5;

 

The CoordSys and AssignCoordSys Functions

 

The CoordSys query function loads a coordinate system from a component or a preset with usge as follows:

 

§      CoordSys("Latitude / Longitude") loads a coordinate system from the Latitude / Longitude preset.

§      CoordSys("D" AS COMPONENT) loads a coordinate system from a component named "D".

§      CoordSys([D]) loads a coordinate system from a preset whose name resides in a column named "D".

§      CoordSys([D] AS COMPONENT) loads a coordinate system from a component the name of which resides in a column named "D".

 

The AssignCoordSys query function assigns a coordinate system to a geom.

 

The following query selects each object in drawing D and interprets its coordinates as Latitude / Longitude coordinates.

 

SELECT AssignCoordSys(Geom([ID]), CoordSys("Latitude / Longitude")) FROM [D];

 

The Project Function

 

The Project query function projects a geom to a specified coordinate system.

 

The following query selects each object in drawing D and projects it to the coordinate system used in drawing E:

 

SELECT Project(Geom([ID]), CoordSys("E" AS COMPONENT)) FROM [D];

 

Height Functions and Surfaces

 

The Height, HeightMax, HeightMin, and HeightSum query functions return the average, maximum, minimum, and summed height of pixels in given surface covered by a geometry object.

 

The following query selects all points in drawing D along with the heights at their respective locations in surface S:

 

SELECT [ID], Height([S], Geom([ID])) FROM [D];

 

The following query selects all areas in drawing D for which the maximum height in S

exceeds 1000:

 

SELECT [ID] FROM [D] WHERE HeightMax([S], Geom([ID])) > 1000;

 

The HeightSum function is used to compute cumulative figures such as population in a given region or to compute the volume of a surface (the HeightSum value multiplied by the size of a pixel).

 

Creating New Points

 

The NewPoint and NewPointLatLon query functions create point geoms in either the default (Orthographic) or Latitude / Longitude coordinate systems.

 

The following query uses the UTMX and UTMY columns in table T to create points in the UTM Zone 17N coordinate systems:

 

SELECT AssignCoordSys(NewPoint([UTMX], [UTMY]), CoordSys("Universal Transverse Mercator - Zone 17 (N)")) FROM [T];

 

The following query selects all areas in drawing D that contain the Latitude / Longitude location 89W 32N:

 

SELECT [ID] FROM [D] WHERE Contains(Geom([ID]), NewPointLatLon(-89, 32));

 

Linear Referencing

 

The LinePoint query function creates a point on a line geom at a given distance from the start of the line. The LinePart query function creates a new line constituting that part of a line geom between a given pair of distance limits.

 

Distances continue between branches: If a line contains two branches with lengths of 5 and 6, the first branch contains distances from 0 to 5 and the second branch contains distances from 5 to 11. Obviously, it is easy to get confused when using linear referencing with multi-branched lines so it make sense to split multi-branched objects into single-branched lines before using linear referencing.

 

Unless we specify a unit parameter, all distances are measured in native drawing units.

 

Linear Referencing Example

 

Let us say we have a drawing D and a table T which contains records in the form: ID, F, T, where ID is the ID of a line in D, F is a "from" distance on that line, and T is a "to" distance on that line.

 

The following query traverses all records in T and extracts those parts of lines it specifies:

 

SELECT LinePart([T].[ID], [F], [T]) FROM [T] INNER JOIN [D] ON [D].[ID] = [T].[ID];

 

The following query traverses all records in T and extracts all "to" points it specifies:

 

SELECT LinePoint([T].[ID], [F]) FROM [T] INNER JOIN [D] ON [D].[ID] = [T].[ID];

 

Queries and Coordinate Systems

 

When queries are used to create objects in linked drawings the objects in those drawings will be based upon the coordinate system in use in whatever source drawings or geoms are used in the query. The units used to compute, say, a buffer will be the local units used in the projection.

 

Suppose we have a drawing containing point objects that is called Points. Consider a query like the following:

 

SELECT Buffer(Geom([ID]), 1000, "mi") FROM [Points];

 

Suppose a linked drawing is created from this query. The buffer objects the query creates may or may not appear to be circles depending upon the coordinate system (projection) used in the Points drawing. The query will create buffer areas such that the outline of each area is the same distance, 1000 miles, from the central point for each area. However, this may or may not be shaped like a circle depending on the projection used.

 

If the Points drawing contains one point and the projection for the Points drawing is the Orthographic or similar azimuthal projection centered exactly on that one point then the result will be a true geographic circle. However, if the projection is otherwise what will appear on the screen will be a circular area (because in the drawing's local coordinate system each location on the boundary of the buffer will be equidistant from the point) but it will not actually draw a geographic circle on Earth.

 

For illustrations of this effect and the difference between drawing a circle upon a projected map and creating a geographic circle, see the Adding Shapes topic.

 

See Also

 

Linked Drawings

Geometry in Tables

Spatial Extensions

Queries and Images or Surfaces