Everybody knows we can create a drawing from a table, but we can also
create a drawing from a query. When the query reports different
results the drawing changes too. This example show step by
step how to create a query and then how to create a drawing from that
query. We show how to command Manifold to write a query for
us that grabs a selection, and then how to create a drawing based on that
new query. This example duplicates the Drawings
from Queries video using the Mexico_queries.mxb
sample project that may be downloaded from the Examples
page on the Manifold web site.
Tech Tip: This topic uses a simple query to generate objects, so we can create a drawing from that query. It works perfectly, but at the cost of the system asking us if we would like to build a temporary spatial index whenever we open the drawing that was created from the query. We can avoid that by using a slightly more sophisticated query that automatically generates a spatial index on the geom field. The more sophisticated approach is illustrated in the Example: Create a Drawing Dynamically from a Geocoded Table topic.
We use one of the standard example drawings many topics use in this documentation, the provinces of Mexico.
We start with a drawing that shows the provinces of Mexico as areas, seen above as a layer in a map using a Bing satellite web server layer as a base layer, to provide a background context.
The Component tab in the Info pane reports the projection used by the Mexico drawing is Pseudo-Mercator, the same as used by the map and one of the standard defaults.
Opening Mexico Table, the drawing's table, we see it is a typical table from which a drawing is created. The Geom field contains the geometry for the area object that represents each province.
Choose File - Create - New Query to create a new query.
In the New Query dialog we accept the default name of "Query" and press the Create Query button.
A new query appears in the Project pane. We double-click it to open it in a Command Window.
A new query appears with sample text using one of the System Data tables. We will edit it to do something more useful.
We drag and drop Mexico Table into the lower right pane, so we have the schema of the table and the name of the table available in the Query Builder. We highlight [mfd_root] and then double-click the name of the table.
That automatically replaces the name of the system table with the name of Mexico Table. This is quicker and less error-prone than manually keyboarding. We press the ! run button and see the results of the query, exactly as we expected.
We do not need to do the following step, but as a matter of education it is helpful. We take a look at the schema reported by the query.
Right-click on Query and choose Schema.
If we are not sure what results table a query will generate, we can always right-click on it and choose Schema to take a look at the schema of the results table. Doing that also has the side effect that we can verify the query really does create a results table that can be used to generate a drawing. If the query does not report a schema, then it cannot be used to create a drawing. Complicated queries can consist of dozens or hundreds of lines of SQL, so sometimes the easiest way to see if the query generates a schema is to right-click on it and ask.
In the Schema illustration above the Geom field has a red projection symbol, indicating a projection has not been assigned. When we create a drawing from the query we can deal with that by assigning the projection that should be used.
Creating a drawing from the query requires just one step:
Right-click on Query and choose Create - New Drawing.
The New Drawing dialog automatically offers a default name for the new drawing, which we will accept. The dialog automatically sees the Geom the query will create, and it offers to use that. Behind the scenes, the query will also pass through the Geom_x spatial index on the Geom field, which the dialog also uses.
The red text for coordinate system warns us there is no coordinate system assigned, so we click on the Coordinate Picker button and choose Pseudo-Mercator (we know that is the right coordinate system from what we read in the Component pane at the top of this example. We can then press Create Drawing.
A new drawing appears in the Project.
We drag and drop Drawing into the map. It includes all of the provinces of Mexico and appears initially in default, gray formatting. The objects we see have been created on the fly by the query.
Even though the drawing is created from a query, we can Style it just like a drawing created directly from a table.
In the above we have applied a thematic formatting style using unique values of the Name field, coloring areas using the Color Brewer Pastel B palette. We have begun selecting areas in the drawing by Ctrl-clicking them. Even though the drawing is created from a results table from a query, we can nonetheless select areas in it.
We have selected three areas, and then we press Ctrl-C to Copy those three areas.
In the Project pane we press Ctrl-V to Paste the objects. They appear pasted as a new table, called Query 2. The new table's name is constructed, by default, from the name of the "table" on which the drawing is based from which the objects were copied.
It is slightly confusing to give the name Query 2 to what is a table and is not a query, but for the purposes of this example we will leave the name as is.
Opening the new table we see it contains three records for the three areas that were copied from the drawing. We right-click onto the Query 2 table and we choose Create - New Drawing to create a drawing based on that table. We choose Pseudo-Mercator for the coordinate system and press the Create Drawing button.
A new drawing called Drawing 2 is created. We can drag and drop Drawing 2 into our map to see that it indeed contains the three areas that we copied and then pasted into the new table in the project.
Queries can show results based on selections that are made in drawings or tables. We can then create a drawing based on such a query and that drawing's contents will be controlled by what we select in other components. See the SQL Example: Using Selection Query Functions topic for more info and examples.
The easiest way to write a query that is based on a selection is to ask Manifold to write that query for us automatically. We open the Mexico drawing in its own window, as seen above.
We select a few areas in the drawing. It does not matter which we select, since we simply want some selected objects.
Next, we open the drawing's table, Mexico Table.
We choose View - Filter - Selected to show only selected records using a Filter, as see above.
We now choose View - Filter - Filter using Query. That is a command to Manifold to open a Command Window that contains a query which accomplishes the current Filter setting.
Manifold writes the query for us, which selects all fields for selected records in Mexico Table. :
SELECT * FROM CALL Selection([Mexico Table], TRUE);
We choose Edit - Save as Query to save the query text into a new query, called Query 3.
Double-click on Query 3 to open it.
We see that it does indeed contain the query which Manifold wrote for us. If we press the ! run button we can see the results of the query, that it reports the records for the areas that were selected in the drawing.
IN the Mexico drawing we press Shift-Ctrl-A to select none, and then we Ctrl-click some additional areas as seen above.
Pressing ! to run the query text in Query 3 we see it reports the records for the selected areas. Our query works as expected - no surprise, since Manifold wrote the query for us.
We can now create a drawing from Query 3.
In the Project pane, right-click on Query 3 and choose Create - New Drawing. As before, using the Coordinate Picker button we change the coordinate system to Pseudo-Mercator and then we press the Create Drawing button.
A new drawing called Drawing 3 appears in the Project pane.
We can drag and drop Drawing 3 into the map. It appears in default gray formatting and shows the areas that were selected in the Mexico drawing. The new Drawing 3 drawing is created on the fly as a results table from the query on which it is based, Query 3.
We can Style the new Drawing 3, using a bright yellow color for areas.
To illustrate how Query 3 automatically harvests the selection and then in turn Drawing 3 is built on the results table generated on the fly by Query 3, we can change the selection in the Mexico drawing to the areas seen above.
Back in the map with the focus on the Drawing 3 tab, we press View - Refresh to refresh, that is, to update, Drawing 3.
When we press View - Refresh the drawing updates to show the areas that are now selected.
The example of selecting areas in a drawing, which already is a layer in a map, to show those areas in another drawing that is created from a query might seem artificial. How might we use that capability in real life?
A good example of real life use would be to show only selected records. We can select records in a table based upon sorted lists of attributes or other characteristics, and then see only the corresponding objects in the map window.
A more complex example would be to show completely synthetic areas or objects, created on the fly based upon intersections of multiple drawings. For example, suppose we wish to see all regions where particular zoning areas intersect with buffer zones that are a given distance from riparian areas or tidal marshlands: a query can generate those areas and a drawing based on that query can display them.
Let us put the above into action. We will select records of interest from a table and then see the corresponding areas in the map.
In the Mexico Table above, we have Ctrl-clicked on the Automobiles column header to sort that column. The first few records show the provinces in Mexico that have the greatest number of automobiles. We have Ctrl-clicked the top record and then Shift-Ctrl-clicked the fifth record to select both of those records and all records in between. We have selected the top five provinces in Mexico by number of automobiles.
Back in the map with the focus on Drawing 3, pressing View - Refresh updates the map to show the corresponding provinces. Those are the top five by number of automobiles.
The above is a useful display, but suppose we would like to show a display like our very first drawing, called Drawing, based on a query, which used a thematic format in Style to color areas using the Pastel B palette? That is easy to accomplish in one of two ways: we can either copy the style property from Drawing into Drawing 3's style properties, or we can simply change the query that creates Drawing. We will do the latter.
In Query 3 we highlight the SQL
SELECT * FROM CALL Selection([Mexico Table], TRUE);
and press Ctrl-C to Copy.
In Query, which is the query on which Drawing is based, we highlight the SQL and we press Ctrl-V to Paste.
The result is that the SQL text from the query that does selections has now been pasted into the query that powers Drawing.
SELECT * FROM CALL Selection([Mexico Table], TRUE);
We press the ! run button to see that it works as expected:
Important: When changing a query we must run that query at least once to update the system's understanding of the results table.
Clicking the Drawing 3 layer off and the Drawing layer on, we see that the Drawing layer still shows the old results from the old version of Query, before we edited the query by copying and pasting text from Query 3.
We choose View - Refresh to update the drawing. It now shows the results from the new version of Query, which creates a results table based on what objects have been selected in the Mexico Table and Mexico drawing ensemble. Those results are colored using the thematic format we defined for the drawing. Style characteristics like a thematic format are a property of the drawing, and will be applied to whatever happen to be the contents of the drawing.
We can show the system is working by making a different selection in Mexico Table. We Ctrl-click the Population field to sort the records by population. We select the top eight provinces by population.
Choose View - Refresh to update the drawing and it now shows the top eight provinces by population.
When creating drawings from queries, we often want those drawings to have dynamic use of a spatial index. If no spatial index is created by the query on which the drawing is based, then every time we open that drawing it will open up as a blank drawing with a message from Manifold saying there is no spatial index, and asking if we want to build a temporary spatial index. That is annoying, and something we can avoid by using the TableCacheIndexGeoms function in our queries.
For a discussion and examples for the TableCacheIndexGeoms function, see the How the Query Works discussion in the Example: Create a Drawing Dynamically from a Geocoded Table topic.
In the context of this topic, the Drawing layer is created based on the query:
SELECT * FROM CALL Selection([Mexico Table], TRUE);
If we use only the Drawing layer in a map with a Bing background layer, when we open the map the map will zoom to the full extents of Bing, that is, the entire world, because there is no information on the bounding box of the objects in the Drawing layer. We can add that information by wrapping our query in a call to the TableCacheIndexGeoms function:
TABLE CALL TableCacheIndexGeoms((
SELECT * FROM CALL Selection([Mexico Table], TRUE)
), TRUE);
Note the parentheses which surround the inner SELECT statement, in the form
TABLE CALL TableCacheIndexGeoms( (<SELECT statement>), TRUE);
Automatic computation of extents - Centering or zooming a map layer built on a query automatically computes the extents of the layer and remembers them for future use in zoom commands, until the layer is refreshed or the window is closed.
Command Window - Query Builder
Example: Create and Run a Query - See how the different parts of a command window operate when creating and running SQL queries. Includes use of the Log tab as well as the ?expression and !fullfetch commands.
SQL Example: Using Selection Query Functions - How to manipulate selections within a query.