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.


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.   



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.





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 SQL CREATE query does the trick.




We begin by importing into our project a MapInfo file 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 that drawing from the 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.



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.




See Also





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.


Example: Run JavaScript in the Command Window - How to run a simple V8 JavaScript script in the Command window.


SQL Example: Learning to Union Areas in SQL from Edit Query - We learn how to write an SQL query that does a custom Union Areas operation by cutting and pasting from what the Edit Query button automatically generates.