Queries and Images or Surfaces

Queries may be written using the virtual tables of images or surfaces. Such queries can extract data from images or surfaces or they can manipulate the data in images or surfaces. See the Virtual Tables for Images and Surfaces topic for a list of columns in a virtual table and other details on virtual tables.

 

Queries can refer to the virtual table of an image or a surface by using the name of the image or surface. For example, The following query displays the total number of selected pixels in an image named Bronze:

 

SELECT Count(*) FROM [Bronze] WHERE [Selection (I)];

 

The following query displays the total number of selected pixels in a surface named Foothills, the heights of which do not exceed 100:

 

SELECT Count(*) FROM [Foothills] WHERE [Selection (I)] AND [Height (I)] <= 100;

 

Example

 

Consider the sample bronze image in which a rectangular region of pixels has been selected.

 

images\eg_virtual_tables_01.gif

 

We can write a query, called Bronze Query, which selects selected pixels in the image.

 

images\eg_virtual_tables_02.gif

 

The above query uses the Selection (I) intrinsic field of the virtual table to select all fields for all pixels that are selected.

 

If desired, we can now create a linked image using that query. We choose File - Link - Image and in the Files of type box in the Link dialog we choose This Project ().

 

images\eg_virtual_tables_03.gif

 

In the Link Data dialog we choose the query we created. The rest of the fields use default values since we know the query has been created from an image using SELECT * so that all the required columns are generated by the query.

 

images\eg_virtual_tables_04.gif

 

The result of the image linking operation is that a new component, a linked image, appears in the project pane.

 

images\eg_virtual_tables_05.gif

 

If we open the linked image we see that it consists of the pixels that were selected in the image which is the subject of the query from which the linked image was created.

 

images\eg_virtual_tables_06.gif

 

Suppose we change the selection in the Bronze image.

 

images\eg_virtual_tables_07.gif

 

If we click on the linked image and do a View - Refresh Data command then the linked image will automatically change to show the current results of the query. Note that, since images must be rectangular in extent, any additional pixels required to create a rectangular image will be invisible.

 

Example

 

Consider a portion of the example Montara Mountain surface on the Manifold CD.

 

images\eg_virtual_tables_08.gif

 

We can create a query that selects all pixels where the height of the surface is greater than or equal to 1000.

 

images\eg_virtual_tables_09.gif

 

We shall call the query Montara Query. We can then create a linked surface based upon that query.

 

images\eg_virtual_tables_10.gif

 

To do so, we choose File - Link - Surface and in the Data Source dialog we choose This Project (). In the resulting Link Surface dialog, seen above, we choose the Montara Query query and use default settings otherwise.

 

images\eg_virtual_tables_11.gif

 

The result is that a linked surface appears in the project pane. Note that the linked surface has a terrain associated with it just as would any other surface.

 

images\eg_virtual_tables_12.gif

 

If we open the linked surface, we can see it contains those pixels in the original surface that are at a height of 1000 or greater. Note that the new surface is not colored with any palette. We can use the View - Display Options command to color it as we see fit. If we would like to duplicate the coloring used in the original Montara surface, we can open the Montara surface, choose View - Display Options and then use Save to File to save the exact palette being used to an .XML file.

 

We can then open the linked surface, choose View - Display Options and use Load from File to load the palette desired from the .XML file to which it was saved.

 

Projections and the OPTIONS Clause

 

By default, queries using virtual tables for images or selections do not save projection information. The image and surface created in the above examples will be created in a default Orthogonal projection that is not georeferenced. To save projection information, we can use the OPTIONS clause with the CoordSys query function. The OPTIONS clause allows a declaration of options for query execution and usage. At the present time, the only option for this clause is the CoordSys query function.

 

Note: The OPTIONS clause must be the first clause in the query, since it potentially specifies the operation of all the rest of the query.

 

Consider the following two queries operating on a surface named Surface:

 

SELECT * FROM [Surface] WHERE [Height (I)] > 100;

 

…and…

 

OPTIONS CoordSys("Surface" AS COMPONENT);

SELECT * FROM [Surface] WHERE [Height (I)] > 100;

 

Both queries select all pixels with a height greater than 100. However, linking a surface from the first query will create a surface in the default Orthographic projection, since pixels do not carry coordinate system data, but linking a surface from the second query will create a surface using the coordinate system (that is, the projection) of the original surface.

 

The OPTIONS clause works for drawings, too.

 

Example

 

We have a large surface and we would like to create a query that grabs only a portion of that surface within a given latitude and longitude range. We will then create a linked surface based on the query. The result will be a surface that is only a portion of the larger surface. We will use an OPTIONS clause in the query to make sure the resulting linked surface is correctly georegistered using the same projection as the original surface. This is a common technique for Internet Map Server (IMS) applications.

 

images\eg_virtual_tables_13.gif

 

We begin with a surface showing dropout rates from high school in the US (blue represents lower dropout rates, red shows higher dropout rates). This is the same surface created in the Displaying Data in a Gradient Map example. We have colored the background black and have turned on a graticule showing latitude and longitude lines every five degrees.

 

images\eg_virtual_tables_14.gif

 

We write a query called Surface Query that selects pixels between -85 and -80 degrees longitude and between 35 and 40 degrees latitude. The OPTIONS clause will ensure that linked surface created from the query will use the same projection as the original surface.

 

images\eg_virtual_tables_15.gif

 

If we create a linked surface from the query we see that only that portion of the surface within the desired latitude and longitude ranges is in the linked surface. [The illustration seen above was created by using View - Display Console - Save to File from the original surface to save the palette, and then View - Display Console - Load from File in the linked surface to load the saved palette. This assures that both surfaces are colored the same way.]

 

images\eg_virtual_tables_16.gif

 

For use in an application like an IMS website, we would probably rewrite the query used in this example as a parameter query as seen above. This would allow users to specify the minimum and maximum longitude and latitude range to get the desired portion of the surface.

 

To see the effect of parameter queries hen working interactively with Manifold at the PC console (that is, not using IMS) we will have to manually refresh the linked component using the following procedure:

 

Create a parameter query. Launch the query using the ! button and supply values for the parameters. Next, link a surface from the query. The surface will use the data computed with the parameters that were supplied. Launch the query one more time and supply new values for the parameters. Refresh the surface by invoking View - Refresh Data. The surface will then use data computed with the new parameters.

 

Example

 

Spatial SQL can include functions for dynamic geometry that can be used to create linked drawings, that is, linked drawings that are created from spatial SQL queries. These can even be created from queries using an image's or a surface's virtual table.

 

images\eg_virtual_tables_17.gif

 

Suppose we start with the surface from the previous example, with part of the surface selected as seen above.

 

images\eg_virtual_tables_18.gif

 

We can write a query, called Selection Points, that creates a point for each selected pixel in the surface. The OPTIONS clause at the beginning of the query assures that any linked drawing created from this query will have the same coordinate system as the surface and thus will be correctly georegistered.

 

images\eg_virtual_tables_19.gif

 

Using File - Link - Drawing with This Project in the Data Source dialog, we can create a linked drawing. The Link Drawing dialog is completed as seen above.

 

images\eg_virtual_tables_20.gif

 

The result is a linked drawing that consists of a mass of points arranged in the shape of the selected region of the surface.

 

The drawing is read-only, because it is created based on a query. If we want to use this drawing in subsequent operations and we would like it to be writeable, we can make a writeable copy of it by right clicking on the linked drawing in the project pane and choosing Duplicate from the context menu. That will create a copy of the drawing as a separate, ordinary drawing.

 

Alternatively, we could right click on the linked drawing in the project pane and choose unlink to convert it from a linked drawing into a regular drawing. Choosing unlink severs all connections with the query.

 

Relink and Unlink

 

If a connection is lost between a linked image or surface and its originating data, the Image - Relink or Surface - Relink command allows us to restore the connection. If we would like to convert a linked image or surface into a regular image, the Image - Unlink or Surface - Unlink command will sever all connections to the originating source and will convert it into a regular image or surface.

 

Updates

 

Suppose we create a linked image or surface from a query or table. If the data changes we must use View - Refresh Data to update the linked image or surface. Simply closing and opening the component is not enough to refresh it.

 

Refreshing a component linked from a table or query is always done manually or via a script. One of the reasons for this is that the table or query providing data for the linked component may be external to Manifold. For example, it may reside in a remote database managed by SQL Server or Oracle. In such cases, depending upon the nature of the connection to that database, it could be very computationally expensive to determine when the remote data has been updated and thus a change in the linked component is required. Some queries can also be very expensive to run in terms of processor bandwidth and user interface pauses.

 

For these reasons, Manifold caches linked components so that the system is not loaded running unnecessary queries or continuously making computationally expensive determinations that no changes have occurred.

 

Units of Measure in Linked Surfaces

 

A limitation of Manifold's linked surfaces mechanism is that File - Link - Surface dialog expects heights to be represented in meters. If a linked surface is generated using a data set in which heights are expressed in feet or some other unit, then the hill shading effect will be overly dark.

 

A workaround for this limitation is to convert heights to meters if they are in some other unit. This may easily be done within the spatial query from which the linked surface is generated. For example, the Montara Mountain on the Manifold CD expresses heights in feet. To use heights in meters, we could write the following query:

 

OPTIONS CoordSys("Montara" AS COMPONENT);

SELECT [Height (I)]*0.3048 AS Z, [X (I)], [Y (I)] FROM Montara;

 

We could then use Z as the height in the Height box in the Link Data dialog.

 

Checking for Invisible Pixels

 

When writing a query that uses an image's or a surface's virtual table, it is usually wise to add a check for invisible pixels. Images or surfaces may have large regions of invisible pixels, and it usually does not make sense to return via a query many pixels that cannot be seen. We can easily accomplish this using the Invisible (I) field. For example, we could re-write the parameter query for the Montara surface as follows:

 

OPTIONS

CoordSys("Surface" AS COMPONENT);

PARAMETERS

MaxLon DOUBLE, MinLon DOUBLE, MaxLat DOUBLE, MinLat DOUBLE;

SELECT

*

FROM

[Surface]

WHERE

([Longitude (I)] >= MinLon AND [Longitude (I)] <= MaxLon) AND

([Latitude (I)] >= MinLat AND [Latitude (I)] <= MaxLat) AND

NOT [Invisible (I)];

 

To illustrate that different indentation styles are possible the query text above uses a different style of indenting than the other examples in this topic. Manifold SQL is very tolerant of "white space" characters such as tabs, extra spaces or returns so that we can arrange our text using whatever style we think is most comprehensible.

 

Examples of UPDATE Queries

 

Because some fields of an image's or surface's virtual table are writeable (see the Virtual Tables for Images and Surfaces topic for a list of writeable fields), we can use UPDATE queries to change an image or surface.

 

Make all pixels with a value in the alpha channel greater than 200 invisible:

 

UPDATE [Image] SET [Invisible (I)] = True

WHERE [Alpha (I)] > 200;

 

Make all selected pixels red:

 

UPDATE [Image] SET [Color (I)] = RGB(255, 0, 0)

WHERE [Selection (I)];

 

Raise heights of all selected pixels by 10:

 

UPDATE [Surface] SET [Height (I)] = [Height (I)] + 10

WHERE [Selection (I)];

 

Select all pixels with heights between 10 and 30:

 

UPDATE [Surface] SET [Selection (I)] = True

WHERE [Height (I)] BETWEEN 10 AND 30;

 

Select all pixels with heights between 10 and 30 degrees and deselect all other pixels:

 

UPDATE [Surface] SET [Selection (I)] =

([Height (I)] BETWEEN 10 AND 30);

 

Examples of Analysis done with Queries

 

Queries may be used to perform analysis on images and surfaces as the following examples illustrate.

 

Determine the number of pixels falling into a given height range:

 

SELECT Count(*) FROM [Montara]

WHERE [Height (I)] BETWEEN 1000 AND 1200;

 

Determine the number of pixels falling into a given intensity range:

 

SELECT Count(*) FROM [Montara]

WHERE Intensity([Color (I)]) BETWEEN 240 AND 255;

 

Create a table of all heights along with their relative frequencies:

 

SELECT

Sum(Iif([Height (I)] <= 499, 1, 0)) / Count(*),

Sum(Iif([Height (I)] BETWEEN 500 AND 999, 1, 0)) / Count(*),

Sum(Iif([Height (I)] BETWEEN 1000 AND 1499, 1, 0)) / Count(*),

Sum(Iif([Height (I)] >= 1500, 1, 0)) / Count(*)

INTO [Percentages] FROM [Montara];

 

Tech Tip

 

Consider the linked drawing created from a surface in an example above, which contains a mass of points arranged in the shape of the selected region of the surface. How might we use those points to create an area object in the same shape?

 

This is a surprisingly complex task due to the ambiguities involved in determining where "holes" or "islands" should be placed in the resultant area object. It's an easy thing for the human eye to see in cases of such dense point placement, but in regions where points might be very sparsely placed it could be difficult even for the human eye to decide where an area should begin or end.

 

For an automated process, we can create buffers or rectangular areas or a convex hull about the points to approximate the region covered with an area object. The following queries provide some example approaches.

 

Buffers (slowest, area slightly increased):

 

SELECT

UnionAll(Buffer(AssignCoordSys(

NewPoint([X (I)], [Y (I)]),

CoordSys("Montara" AS COMPONENT)), 10, "m"))

FROM [Montara] WHERE [Height (I)] > 1600;

 

Rectangles (slightly faster than buffers, area slightly increased):

 

SELECT

UnionAll(BoundingBox(Buffer(AssignCoordSys(

NewPoint([X (I)], [Y (I)]),

CoordSys("Montara" AS COMPONENT)), 10, "m")))

FROM [Montara] WHERE [Height (I)] > 1600;

 

Convex hull (fastest, but probably least "accurate"):

 

SELECT

ConvexHull(AllCoords(AssignCoordSys(

NewPoint([X (I)], [Y (I)]),

CoordSys("Montara" AS COMPONENT))))

FROM [Montara] WHERE [Height (I)] > 1600;

 

Notes

 

Many of the above techniques (such as using a spatial query to select by latitude and longitude) can be used with images, surfaces or, with slight modifications, drawings.

 

See Also

 

Linked Images

Linked Surfaces

Virtual Tables for Images and Surfaces

Queries and Geoms

Linked Drawings

Geometry in Tables

Spatial Extensions