![]() |
|
Map Room Home page | |
written by Nigel
James
|
|
MAPINFO How to... | |
No. 14: Using geographic queries. | |
(References to tables, folders etc., are not applicable outside the Map Room, so if you are located elsewhere, you should substitute your own) | |
Geographic and non - geographic queries This example shows how a geographic query can be used to select capitals within 1000km of London and uses the Worldcap table which ships with MapInfo. |
|
The first part shows how to use a basic query and the second part refines the result to include the distances in the results table and format the columns. | |
Simple distance query | |
1. Open the Query menu and click SQL select. | |
2. Click the Table list and select the Worldcap table. | |
3. The query required is "Select all capitals which are less than 1000km from London". To put this into a SQL statement requires the use of the Distance function. | |
4. Click in the where Condition box and then select Distance() from the Functions list. Note that the cursor is placed inside the parentheses. | |
5. MapInfo uses special functions, CentroidX(obj) and CentroidY(obj) to extract the coordinates of a point. They do not use existing fields in a record, but instead are geographic functions which find the coordinates of the record's associated object. | |
6. We also need the coordinates (in degrees) of London. These can be found by double-clicking on the symbol for London, which displays the X,Y positions. Note these down (they are in fact -0.18030611 and 51.48894941). If your map is not using degrees, change this by opening the Map menu then clicking Options. Change the coordinate units to degrees and the distance units to km. | |
7. The Distance function requires four parameters - the X and Y coordinates of the first point, and the X and Y coordinates of the second point. The distance units are also included. | |
8. The parameters can now be inserted between the parentheses of the Distance function, so with the cursor in place, select CentroidX(obj) from the Functions list. Add a comma, then insert CentroidY(obj) from the Functions list. Follow this with another comma, the enter the X coordinate for London, another comma, then the Y coordinate (and another comma). Finally enter the units in quoatation marks "km" to complete the function. | |
9. Now add <1000 (outside the parentheses) to the where Condition to finish it. | |
10. The full query should read: Distance(CentroidX(obj),CentroidY(obj),-0.18030611,51.48894941,"km") <1000 | |
11. Check that Browse results in checked and click OK to execute the query. You will now see a table of capitals less than 1000km from London. | |
Refined distance query | |
We can now refine the query. You will see that the query has selected London, but we do not need to include this in the results. In addition, it would be useful to have a column with the distance each capital is from London. To do this we can refine both Select Columns and where Condition. | |
1. Open the SQL query dialog again. It should still have the original query loaded. If not, enter it again. | |
2. Delete the * in the Select Columns box and enter the following (You can either type it in or use the functions list. If you use the Functions list, check that the cursor is correctly positioned each time): Capital,Format$(Distance(CentroidX(obj), CentroidY(obj), -0.18030611, 51.48894941, "km"),"0.00") "Distance from London"
|
|
3. This introduces a new function: Format$(). To format a number (in this case to show a leading zero if the result is less than 1 and show two decimal places) the function Format$() can be used. The pattern for the result is "0.00" and is added after the Distance function. The whole distance function and the pattern is enclosed in the Format$ parentheses. The column is given a name by enclosing it in quotation marks, in this case "Distance from London". If you do not provide a column title then the function is used as a title instead. You do not need to combine formatting the result with using a column title - they are independent of each other. | |
4. The next step is to modify the statement in the where Condition box. All we need to do is exclude London from the result, so add: and Capital <> "London" to the statement (note the quotation marks - these are needed for text, but not for numbers). You could add: And Not Capital = "London" if you wish, the result will be the same. | |
5. Click OK to execute the query. | |
6. You can save the results of the query by using File>Save copy as. | |
This is a brief and specific guide to this topic, for more general information, see: MapInfo - an easy guide for new users | |
Nigel
James
Bodleian Library 2001 |