Example: Refer to a Table within a Data Source

In this example, we will connect to an .gpkg file database that contains a table of administrative place names around the world.

To fit into this documentation, illustrations show a small Manifold desktop, with only a few panes, docked to the right side. In real life we use a much larger Manifold desktop, and more panes would be turned on, with panes docked to the left or to the right, or undocked, as we prefer. Right-click a pane's tab to change where it is docked. Manifold will remember that new arrangement for our next session.

In a new project we choose File - Create - New Data Source. The dropdown menu provides a list of favorites to choose from as well as a More... option.

Choose More... to launch the New Data Source dialog.

For the Name, we enter GeoNames GPKG File. Choose File: gpkg as the Type. In the Source box we use the browse button to navigate to and to pick the file. Press the Create Data Source button.

A new data source appears in the Project pane. We click on the + icon by the new data source to expand it, to see that it contains several tables.

We can take a look at the GeoNames table by double-clicking it to see the contents.

We will now write a query using the GeoNames table. From the main toolbar we launch a Command window by choosing View - New Command Window - SQL.

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.

Using the built-in capabilities of the query builder, we drag and drop the GeoNames table into the tables pane of the Command window.

We can then write a SELECT query:

SELECT * FROM [GeoNames GPKG File]::[GeoNames]

WHERE StringContains([Name], 'grad');

Note the naming syntax for referring to the name of a table within a data source. Two colon : characters are used in the form [data source]::[name].

That same syntax extends into nested data sources. Suppose, for example, we had a table called dbo.orders that existed within a data source called postgresql that in turn existed within a data source called sqlserver in our project. We could refer to that table as [sqlserver]::[postgresql]::[dbo.orders] and it will be found by the query.

In this example we refer to the GeoNames table within the GeoNames GPKG File data source using the syntax [GeoNames GPKG File]::[GeoNames]. The query uses the StringContains function to find all records where the [Name] field includes the substring grad.

Press the ! button in the main Manifold toolbar to run the query. Running the query produces the results seen above. In the illustration above we right-clicked the Counts pane in the Status bar and pressed Refresh to get a count of the total number of records returned by the query.

To refer to data sources nested within other data sources we simply add more :: separators.

Consider the Customers table shown above, opened in a project from a data source nested several levels within other nested data sources. We could refer to it in an SQL query as in:

SELECT *

FROM [Europe]::[France]::[Centre]::[Eure-et-Loir]::[Customers];

It is easy to see the nomenclature for such tables: simply drag and drop the table into the Command window's Tables pane to see how it is named.

That also provides easy access to it in the Command window query builder for double-clicking on its elements for use in queries.

Example: Refer to a Table within a Data Source

See Also