Example: Create a Drawing from a Query

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.

A Sample Drawing

We use one of the standard example drawings many topics use in this documentation, the provinces of Mexico.

 

eg_dwg_from_query01_00.png

 

We start with a drawing that shows the provinces of Mexico as areas, seen above as a layer in a map with a Bing satellite web server layer providing a background context.

 

eg_dwg_from_query01_01.png

 

The Component panel of the Contents 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.

 

eg_dwg_from_query01_02.png

 

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.

 

Create a Query

 

Choose File - Create - New Query to create a new query.

 

eg_dwg_from_query01_03.png

 

In the New Query dialog we accept the default name of "Query" and press the Create Query button.

 

eg_dwg_from_query01_04.png

A new query appears in the Project pane.   We double-click it to open it in a Command Window.

 

eg_dwg_from_query01_05.png

 

A new query appears with sample text using one of the System Data tables.   We will edit it to do something more useful.

 

eg_dwg_from_query01_06.png

 

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.

 

eg_dwg_from_query01_07.png

 

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.

 

eg_dwg_from_query01_08.png

 

Right-click on Query and choose Schema.  

 

eg_dwg_from_query01_09.png

 

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.

Create a Drawing from the Query

Creating a drawing from the query requires just one step:  

 

eg_dwg_from_query01_10.png

 

Right-click on Query and choose Create - New Drawing.

 

eg_dwg_from_query01_11.png

 

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 panel at the top of this example.    We can then press Create Drawing.

 

eg_dwg_from_query01_12.png

 

A new drawing appears in the Project.

 

eg_dwg_from_query01_13.png

 

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.

 

eg_dwg_from_query01_14.png

 

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.

 

eg_dwg_from_query01_15.png

 

We have selected three areas, and then we press Ctrl-C to Copy those three areas.

 

 

eg_dwg_from_query01_16.pngeg_dwg_from_query01_17.png

 

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.

 

eg_dwg_from_query01_18.png

 

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.

 

eg_dwg_from_query01_19.png

 

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.

Create a Query that Works with Selections

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.  

 

eg_dwg_from_query01_20.png

 

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.   

 

eg_dwg_from_query01_21.png

 

We select a few areas in the drawing.   It does not matter which we select, since we simply want some selection.

 

Next, we open the drawing's table, Mexico Table.

 

eg_dwg_from_query01_22.png

 

We choose View - Filter - Selected to show only selected records using a Filter, as see above.   

 

eg_dwg_from_query01_23.png

 

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.

 

eg_dwg_from_query01_24.png

 

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.

 

eg_dwg_from_query01_25.png

 

Double-click on Query 3 to open it.

 

eg_dwg_from_query01_26.png

 

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.

 

eg_dwg_from_query01_27.png

 

IN the Mexico drawing we press Shift-Ctrl-A to select none, and then we Ctrl-click some additional areas as seen above.  

 

eg_dwg_from_query01_28.png

 

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.

 

Create a Drawing from Query 3

We can now create a drawing from Query 3.  

 

eg_dwg_from_query01_29.png

 

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.

 

eg_dwg_from_query01_30.png

 

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.

 

eg_dwg_from_query01_31.png

 

We can Style the new Drawing 3, using a bright yellow color for areas.

 

eg_dwg_from_query01_32.png

 

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.  

 

eg_dwg_from_query01_33.png

 

When we press View - Refresh the drawing updates to show the areas that are now selected.

Practical Uses

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.

Select and Display Records from a Table

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.

 

eg_dwg_from_query01_34.png

 

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.

 

eg_dwg_from_query01_35.png

 

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.  

 

Using a Palette for All Areas

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.

 

eg_dwg_from_query01_36.png

 

In Query 3 we highlight the SQL and press Ctrl-C to Copy.

 

eg_dwg_from_query01_37.png

 

In Query, which is the query on which Drawing is based, we highlight the SQL and we press Ctrl-V to Paste.

 

eg_dwg_from_query01_38.png

 

The result is that the SQL text from the query that does selections has now been pasted into the query that powers Drawing.   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.   

 

eg_dwg_from_query01_39.png

 

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.

 

eg_dwg_from_query01_40.png

 

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.

 

eg_dwg_from_query01_41.png

 

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.

 

eg_dwg_from_query01_42.png

 

Choose View - Refresh to update the drawing and it now shows the top eight provinces by population.

 

 

Notes

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.

 

See Also

Tables

 

Drawings

 

Selection

 

Queries

 

Style: Drawings

 

Command Window

 

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.