Example: Automatically Generating CREATE Queries

The query builder in Manifold's Command Window can automatically generate the CREATE queries which are used to create components, such as images or drawings, from tables.


Important: For simplicity, the following examples do not include a THREADS SystemCpuCount() command in the query.  When writing queries manually using the Command Window we should make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in our system.  See the THREADS command for more info.


We begin by importing into our project the sample bronze.jpg image.   



Doing so creates a table called bronze Tiles with records containing tiles for the image's data and also an image component called bronze that is created from that table.   We know from experience the image is created from the bronze Tiles table, but we could always confirm that by looking at the image's Properties.


Double-clicking the image opens it so we can see what it looks like.



Opening the image's table shows the table's records contain tiles from which the image is formed.  It is a very small image so there are few tiles.



Next we choose View - New Command Window - SQL to open a blank Command window.



We highlight the bronze image in the Project pane and then choose Copy from the Project pane's toolbar.



Clicking into the query text portion of the Command window we Paste the component with a CTRL-V or by right-clicking and choosing Paste.  



The Command window automatically generates the SQL CREATE statement that would be used to create the bronze image from the bronze Tiles table:


CREATE IMAGE [bronze] (

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 186, 216 ]',

  PROPERTY 'Table' '[bronze Tiles]'



We can prove this is valid SQL that will actually work in real life by modifying the query and then pressing the ! button to run the query.  We will modify the query so that it creates a new image called yet another bronze.   



The query now reads:


CREATE IMAGE [yet another bronze] (

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 186, 216 ]',

  PROPERTY 'Table' '[bronze Tiles]'



If we make that change to the CREATE query and press the ! button to run it, a new image is created in the Project pane called yet another bronze.



We can open that image to see it is, indeed, a correctly created new image that uses the same tiles from the bronze Tiles table.


Create A Drawing

Images are visual displays of raster data stored in tables as type tile.   Drawings are visual displays of vector data stored in tables as type geom.  To see how drawings are created from table data we can copy and paste a drawing into the Command window to see what sort of CREATE query does the trick.



We begin by importing into our project a shapefile showing provinces in Mexico.



That creates a drawing called Mexico which is created from a table called Mexico




If we look at Mexico Table we see there is a record for each province with various fields giving information for each province.   If we scroll all the way to the right we see that the record for each province contains a geom providing spatial data for the province.



In the Project pane we copy the Mexico drawing and then we paste the drawing into the query text pane of the Command window.  



The Manifold engine automatically generates the SQL CREATE query that would be used to create the Mexico drawing from Mexico Table:



  PROPERTY 'FieldGeom' 'Geom',

  PROPERTY 'Table' '[Mexico Table]'



Note that the CREATE statement is compact, shorter and simpler than the CREATE statement used to create the bronze image.   That's because geom data embeds within the geom information about coordinates and coordinate systems, so in general all a drawing needs to know about spatial data in geom form is what table provides the data and which field in the table to use.


We can prove the SQL is valid by modifying the name of the created drawing to Nuevo Mexico and running the query.



The modified query is:


CREATE DRAWING [Nuevo Mexico] (

  PROPERTY 'FieldGeom' 'Geom',

  PROPERTY 'Table' '[Mexico Table]'



Running the query creates a new drawing in the Project called Nuevo Mexico.



If we double-click on the Nuevo Mexico drawing to open it we see that it is indeed a correctly created drawing using the same Geom field in the table as the original Mexico drawing.




Automatically writing SQL - Other ways of generating SQL queries are to use the Edit Query button that appears in numerous Manifold dialogs and panes, and also using the Save update query feature in the Edit - Join  dialog.

See Also





Edit - Join


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.


Example: Refer to a Table within a Data Source -  Within a query, how to refer to a table that is in a data source.


Example: Switching between Manifold and Native Query Engines - How to use the !manifold and !native commands to switch a query in the Command window from use the Manifold query engine to whatever query engine is provided by a data source.


SQL Example: Learn SQL from Edit Query - Merging Areas - We learn how to write an SQL query that does a Merge : area (dissolve) operation by cutting and pasting from what the Edit Query button automatically generates.


SQL Example: Process Images with 3x3 Filters -  Shows a step-by-step example of developing an SQL query that takes a query written by the Edit Query button and then modifies that query into a general purpose query that can apply any 3x3 filter.   This makes it easy to use matrix filters we find on the web for custom image processing.   We extend the query by using parameters and adding a function, and then show how it can be adapted to use a 5x5 filter.