Step 1

Setup

Wait until the docker image is launched. For simplicity, you will be logged directly into the docker environment.

Connecting to Manticore

Let's connect to Manticore using the MySQL client:

mysql -P9306 -h0

The geodemo table

First, let's look at the table structure

DESC geodemo;

The table contains the geo coordinates in both radians and degrees, as well as several properties like the name of the location, country and state code.

SELECT * FROM geodemo;

Step 2

GEODIST

Manticore provides a function called GEODIST that can be used to calculate distance between 2 geocoordinates

GEODIST(lat1, lon1, lat2, lon2, { option=value, ... })

The first 4 parameters are the geocoordinate pairs. The last parameter uses a simple format similar to JSON to define several options for the GEODIST function:

• in - defines the input unit of measure. Coordinates can be in degrees (degrees or deg) or radians (radians or rad). Both geocoordinate pairs must use same unit of measure
• out - defines the output unit of measure. The distance can be expressed in meters (meters or m), kilometers (kilometers or km), feets (feet or ft) or miles (miles or mi)
• method - defines the algorithm used. 2 values can be used here: adaptive and haversine. haversine was the first algorithm used. Adaptive is the default algorithm used, and it's faster and nore precise than haversine. We recommend leaving the default method.

GEODIST will provide a measured distance, and its result can be used to sort the query result. A typical query will look like:

SELECT *,GEODIST(latitude_deg,longitude_deb, 34.606,-2.799,{in=deg,out=km}) as distance FROM geodemo ORDER BY distance ASC;

Step 3

Performing queries

Let's start first with a regular text search. In this case we will search for stadiums

SELECT * FROM geodemo WHERE MATCH('Stadium');

Now let's add a location and see the closest stadiums next to it:

SELECT name,GEODIST(latitude_deg,longitude_deg,51.5073907,-0.1276999,{in=deg,out=km}) as distance FROM geodemo WHERE MATCH('Stadium') ORDER BY distance ASC;

In this case the distance is expressed in kilometers. If we want to use imperial system, we can switch to miles:

SELECT name,GEODIST(latitude_deg,longitude_deg,51.5073907,-0.1276999,{in=deg,out=miles}) as distance FROM geodemo WHERE MATCH('Stadium') ORDER BY distance ASC;

Step 4

Filtering by distance.

The distance can also be used to filter out the results.

In the previous example we sorted only by the distance. But if we are doing full-text searches we would want the text scoring to have priority and limit the results to a close area from our location.

Let's take this example:

SELECT name,GEODIST(latitude_deg,longitude_deg,51.5073907,-0.1276999,{in=deg,out=miles}) as distance,WEIGHT() FROM geodemo WHERE MATCH('hotel MAYBE club') ORDER BY WEIGHT() DESC;

If we sort by score, in the top results we will get places that are far away from us. Doing the other way around, will give us closest locations, but not all will have a top text score:

SELECT name,GEODIST(latitude_deg,longitude_deg,51.5073907,-0.1276999,{in=deg,out=miles}) as distance,WEIGHT() FROM geodemo WHERE MATCH('hotel MAYBE club') ORDER BY DISTANCE ASC;

The solution is to limit our search results, let's say 10 miles and sort first by text score and secondly by distance:

SELECT name,GEODIST(latitude_deg,longitude_deg,51.5073907,-0.1276999,{in=deg,out=miles}) as distance,WEIGHT() FROM geodemo WHERE MATCH('hotel MAYBE club') AND distance < 10 ORDER BY WEIGHT() DESC, DISTANCE ASC;

Step 5

Search inside polygonal area

Another geographical filtering is by checking if a pair of coordinates is inside a defined polygon.

Manticore Search comes with 2 functions that can create polygons:

• POLY2D - produces a mathematical polygon
• GEOPOLY2D - produces a polygon that makes Earth's curvature in consideration by tessallating the polygon into smaller ones.

GEOPOLY2D expects the vertices to be sets of latitude/longitude in degrees. If radians are used, the GEOPOLY2D will output a polygon in plain space like POLY2D. POLY2D can be used for smaller area defined by polygon with several sides (3-4) and sides shorter than 500km. For polygons with more sides the maximum side length should be considered lower.

The defined poylgon and desired set of coordinates can be tested with CONTAINS function which expects as parameters the polygon function and 2 coordinates (latitude/longitude) in degrees.

For example we can do a search for underground stations, but we want first results to include stations inside a defined area:

SELECT name, latitude_deg,longitude_deg,CONTAINS(GEOPOLY2D(51.5,-0.11, 53.3,-0.11, 53.3, -0.12, 51.5,-0.12),latitude_deg,longitude_deg) AS inside FROM geodemo WHERE MATCH('underground station') ORDER BY inside DESC;

Distance and area matched can be mixed. For the example above we may want to limit the results to the area, but also to order the matches by the distance from a desired point:

SELECT name, latitude_deg,longitude_deg,CONTAINS(GEOPOLY2D(51.5,-0.11, 53.3,-0.11, 53.3, -0.12, 51.5,-0.12),latitude_deg,longitude_deg) AS inside, GEODIST(latitude_deg,longitude_deg,51.5073907,-0.1276999,{in=deg,out=miles}) as distance FROM geodemo WHERE MATCH('underground station')AND inside=1 ORDER BY distance ASC;