Nearest statement - MapBasic - 2023

Purpose

Find the object in a table that is closest to a particular object. The result is a 2-point Polyline object representing the closest distance. You can issue this statement from the MapBasic window in MapInfo Pro.

Syntax

Nearest [ N | All ] 
	From { Table fromtable | Variable fromvar }
	To totable Into intotable 
	[ Type { Spherical | Cartesian }] 
	[ Ignore [ Contains ] [ Min min_value ] [ Max max_value ] 
		Units unitname ] [ Data clause ]

N is an optional parameter representing the number of "nearest" objects to find. The default is 1. If All is used, then a distance object is created for every combination.

fromtable represents a table of objects that you want to find closest distances from.

fromvar represents a MapBasic variable representing an object that you want to find the closest distances from.

totable represents a table of objects that you want to find closest distances to.

intotable represents a table to place the results into.

min_value is the minimum distance to include in the results.

max_value is the maximum distance to include in the results.

unitname is string representing the name of a distance unit (for example, "km") used for min_value and/or max_value.

clause is an expression that specifies the tables that the results come from.

Description

The Nearest statement finds all the objects in the fromtable that are nearest to a particular object. Every object in the fromtable is considered. For each object in the fromtable, the nearest object in the totable is found. If N is defined, then the N nearest objects in totable are found. A two-point Polyline object representing the closest points between the fromtable object and the chosen totable object is placed in the intotable. If All is specified, then an object is placed in the intotable representing the distance between the fromtable object and each totable object.

If there are multiple objects in the totable that are the same distance from a given fromtable object, then only one of them may be returned. If multiple objects are requested (for example, if N is greater than 1), then objects of the same distance will fill subsequent slots. If the tie exists at the second closest object, and three objects are requested, then the object will become the third closest object.

The types of the objects in the fromtable and totable can be anything except Text objects. For example, if both tables contain Region objects, then the minimum distance between Region objects is found, and the two-point Polyline object produced represents the points on each object used to calculate that distance. If the Region objects intersect, then the minimum distance is zero, and the two-point Polyline returned will be degenerate, where both points are identical and represent a point of intersection.

The distances calculated do not take into account any road route distance. It is strictly a "as the bird flies" distance.

Type is the method used to calculate the distances between objects. It can either be Spherical or Cartesian. The type of distance calculation must be correct for the coordinate system of the intotable or an error will occur. If the coordinate system of the intotable is NonEarth and the distance method is Spherical, then an error will occur. If the coordinate system of the intotable is Latitude/Longitude, and the distance method is Cartesian, then an error will occur.

The Ignore clause limits the distances returned. Any distances found which are less than or equal to min_value or greater than max_value are ignored. min_value and max_value are in the distance unit signified by unitname. If unitname is not a valid distance unit, an error will occur. One use of the Min distance could be to eliminate distances of zero. This may be useful in the case of two point tables to eliminate comparisons of the same point. For example, if there are two point tables representing Cities, and we want to find the closest cities, we may want to exclude cases of the same city. The entire Ignore clause is optional, as are the Min and Max subclauses within it.

The Max distance can be used to limit the objects to consider in the totable. This may be most useful in conjunction with N or All. For example, we may want to search for the five airports that are closest to a set of cities (where the fromtable is the set of cities and the totable is a set of airports), but we do not care about airports that are farther away than 100 miles. This may result in less than five airports being returned for a given city. This could also be used in conjunction with the All parameter, where we would find all airports within 100 miles of a city. Supplying a Max parameter can improve the performance of the Nearest statement, since it effectively limits the number of totable objects that are searched.

The effective distances found are strictly greater than the min_value and less than or equal to the max_value:

min_value < distance <= max_value

This can allow ranges or distances to be returned in multiple passes using the Nearest statement. For example, the first pass may return all objects between 0 and 100 miles, and the second pass may return all objects between 100 and 200 miles, and the results should not contain duplicates (for example, a distance of 100 should only occur in the first pass and never in the second pass).

Normally, if one object is contained within another object, the distance between the objects is zero. For example, if the fromtable is WorldCaps and the totable is World, then the distance between London and the United Kingdom would be zero. If the Contains flag is set within the Ignore clause, then the distance will not be automatically be zero. Instead, the distance from London to the boundary of the United Kingdom will be returned. In effect, this will treat all closed objects, such as regions, as polylines for the purpose of this operation.

Data Clause

The Data clause can be used to mark which fromtable object and which totable object the result came from.

Data IntoColumn1=column1, IntoColumn2=column2

The IntoColumn on the left hand side of the equals must be a valid column in intotable. The column name on the right hand side of the equals sign must be a valid column name from either totable or fromtable. If the same column name exists in both totable and fromtable, then the column in totable will be used (e.g., totable is searched first for column names on the right hand side of the equals sign). To avoid any conflicts such as this, the column names can be qualified using the table alias:

Data name1=states.state_name, name2=county.state_name

To fill a column in the intotable with the distance, we can either use Update Column command on the TABLE tab, or use the Update statement.

Examples

Assume that we have a point table representing locations of ATM machines and that there are at least two columns in this table: Business, which represents the name of the business which contains the ATM; and Address, which represents the street address of that business. Assume that the current selection represents our current location. Then the following will find the closest ATM to where we currently are:

Nearest From Table selection To atm Into result Data 
where=Business,address=Address

If we wanted to find the closest five ATM machines to our current location:

Nearest 5 From Table selection To atm Into result Data 
where=Business,address=Address

If we want to find all ATM machines within a 5 mile radius:

Nearest All From Table selection To atm Into result Ignore Max 5 Units 
"mi" Data where=buisness,address=address

Assume we have a table of house locations (the fromtable) and a table representing the coastline (the totable). To find the distance from a given house to the coastline:

Nearest From Table customer To coastline Into result Data 
who=customer.name, 
where=customer.address,coast_loc=coastline.county,type=coastline.designat
ion

If we do not care about customer locations which are greater than 30 miles from any coastline:

Nearest From Table customer To coastline Into result Ignore Max 30 Units 
"mi" Data who=customer.name, 
where=customer.address,coast_loc=coastline.county, 
type=coastline.designation

Assume we have a table of cities (the fromtable) and another table of state capitals (the totable), and we want to find the closest state capital to each city, but we want to ignore the case where the city in the fromtable is also a state capital:

Nearest From Table uscty_1k To usa_caps Into result Ignore Min 0 Units 
"mi" Data city=uscty_1k.name,capital=usa_caps.capital

See Also:

Farthest statement, CartesianObjectDistance() function, ObjectDistance() function, SphericalObjectDistance() function, CartesianConnectObjects() function, ConnectObjects() function, SphericalConnectObjects() function