This topic provides a collection of queries that select map objects. When such queries are based on spatial relationships such as proximity they are often called spatial queries.
The normal use of queries is to generate a table display showing the results of the query. If the query includes the ID field in its results, query tables can automatically select the drawing objects corresponding to each row in the query table based upon the setting of the Automatically select query records option in the Tools - Options - Miscellaneous dialog. When this option is checked (the default setting) queries will automatically select objects in the drawing.
That is, with this option on querying a drawing will automatically deselect any selected objects and then select all objects returned by the query. This is equivalent to making a selection using Replace mode. When a query includes more than one drawing, all previously selected objects in all of the drawings will first be deselected and then objects from the query will be selected in the first drawing for which an ID column is found in the query.
If the Automatically select query records option is not checked then running a query will generate a tabular display of the query results, but the objects in that query will not automatically be selected. We can then use such a query-generated table to manually select objects in the drawing with interactive mouse commands much as we could use a drawing's table to select objects in the drawing.
The first three examples show the basic mechanism of selecting objects in drawings using the content of fields in their associated tables.
SELECT States.* FROM States WHERE
IsArea(States.ID) AND
(SELECT Max(Area(Counties.ID)) FROM Counties
WHERE Contains(States.ID, Counties.ID)) > 100;
Selects states that have at least one large (>100 area units) county from the drawing called states.
SELECT DISTINCT FastFood.* FROM FastFood, Streets WHERE
FastFood.Type = "McDonalds" AND
Streets.Name = "Rue Delaunay" AND
Distance(FastFood.ID, Streets.ID) < 100;
Selects all McDonalds restaurants near the street named Rue Delaunay in the drawing streets. Note the 'DISTINCT' just before the 'FastFood.*' This suppresses duplicate fastfood entries, a necessary step when querying restaurants in Paris given the famous French interest in McDonalds and other haute cuisine.
When queries are based on spatial relationships such as proximity they are often called spatial queries. The following examples show queries that select objects in maps based on spatial relationships.
SELECT Stations.Name, Distance(Stations.ID, 12, "mi") FROM Stations;
This creates a tabular view where the first column shows the name of the station and the second column shows the distance in miles from this station to the station whose ID is 12.
SELECT Stations.* FROM Stations WHERE
Distance(Stations.ID, 12, "km") Between 50 And 80;
This selects all stations whose distance to station 12 is between 50 and 80 kilometers.
SELECT Stations.* FROM Stations WHERE
(SELECT Min(Distance(Stations.ID, Clients.ID, "mi")) FROM Clients
WHERE Clients.Importance = "High") < 10;
SELECT Stations.* FROM Stations WHERE
(SELECT Max(Distance(Stations.ID, Clients.ID, "mi")) FROM Clients
WHERE Clients.Importance = "High") < 10;
The first query selects all stations within 10 miles of any important client. The second query selects all stations within 10 miles of important clients.
SELECT TOP 5 PowerPlants.* FROM PowerPlants, States WHERE
(States.Name = "California" Or States.Name = "Nevada") AND
Contains(States.ID, PowerPlants.ID) ORDER BY PowerPlants.Power;
This selects the 5 largest power plants in California and Nevada.
SELECT States.Name FROM States WHERE EXISTS
(SELECT * FROM Rivers WHERE Rivers.Name = "Missisippi" AND
Touches(States.ID, Rivers.ID));
This selects all states on the banks of the Mississippi.
We may also use SQL as a utility language to perform functions that might otherwise require a script. For example, suppose we would like to determine the nearest (non-self) neighbor of points in a drawing. We can loop through all points in the drawing and use the following query (a particularly elegant example contributed by Manifold user Ilya Guschin) to select the ID of the nearest neighbor of the point whose ID equals X:
SELECT TOP 1 [ID] FROM [Drawing] WHERE [ID] <> X
ORDER BY Distance([ID], X);
Alternatively, we can execute the following query to list the nearest neighbor of each object:
SELECT [ID], (SELECT TOP 1 [Copy].[ID] AS [Nearest Neighbor] FROM
[Drawing] AS [Copy] WHERE [Copy].[ID] <> [Drawing].[ID] ORDER BY
Distance([Copy].[ID], [Drawing].[ID])) FROM [Drawing];
See Also