Indexing MySQL Databases

Voyager Search supports indexing of MySQL tables allowing users to create a new custom location where multiple tables can be indexed at once. Each record in each table becomes an item indexed in Voyager Search.

Dependencies

Python (32-bit) must be installed on the Voyager Server machine. Ideally, the Python version should be 2.7.

DO NOT USE EARLIER VERSIONS OF PYTHON

Alternatively, the Python installer shipped with ArcGIS can also be used to install Python.

NOTE: If you have ArcGIS installed, Python is likely to already be installed.

Indexing Data

After confirming the dependencies, it’s necessary to add a new location. Here are the steps to create a new location and some sample configurations:

Go to Manage Voyager > Locations.
Click New Location
Click Databases (Standard) to define a simple query
or Databases (Advanced) to configure more complex queries
Select MySQL in the Connections drop-down list
Edit the configuration (see examples below)

Configuration Examples

Example 1

This configuration indexes all the tables in a MySQL database named test. It includes all the fields and maps only one field for all tables. The use of the asterisk (*) in this configuration means to include all fields, all tables and apply the field mapping in all tables. Be sure to provide the name of your MySQL driver. This can be found by opening your ODBC Data Source Administrator.

{
    "name": "MySQL – Test Database",
    "type": "TABLES",
    "config": {
        "fields": {
            "include": ["*"]
        },
    "tables": [
    {
        "name": "*",
        "action": "INCLUDE"
    },
    {
        "name": "*",
        "map": {
            "NAME ": "name"
            }
    } ],
    "sql": {"connection": {
      "driver": "MySQL ODBC 5.3 ANSI Driver",
      "server": "VOYAGER",
      "database": "TEST",
      "uid": "",
      "pwd": ""
      }
    }
  }
}

Example 2

Index a single table.

{
    "name": "WORLD_CITIES",
    "type": "TABLES",
    "config": {
        "fields": {
            "include": ["*"]
        },
    "tables": [
    {
        "name": "CITIES",
        "action": "INCLUDE"
    },
    {
        "name": "*",
        "map": {
            "NAME ": "name"
            }
    } ],
    "sql": {"connection": {
      "driver": "MySQL ODBC 5.3 ANSI Driver",
      "server": "VOYAGER",
      "database": "TEST",
      "uid": "",
      "pwd": ""
      }
    }
  }
}

Example3

This example demonstrates the following:

Index only tables starting with the prefix ‘STATE’ or ‘CITIES’
Include only fields that are prefixed with “STATE or “CITY”
Mapping field, STATE_NAME for all tables and map field CITY_NAME for only the City table
Use queries and constraints to limit the number of rows that are indexed. See the Usage notes below for further explanation.

{
  "name": "MySQL – States and Cities",
  "type": "TABLES",
  "config": {
    "fields": {"include": ["*"]},
    "tables": [
      {
        "name": "STATES&",
        "action": "INCLUDE"
      },
      {
        "name": "CITIES&",
        "action": "INCLUDE"
      },
      {
        "name": "*",
        "map": {"STATE_NAME": "name"},
        "query": "STATE_NAME = 'California'"
      },
      {
        "name": "CITIES",
        "map": {"CITY_NAME": "name"},
        "constraint": "POP1990 > 100000"
      }
    ],
    "sql": {"connection": {
      "driver": " MySQL ODBC 5.3 ANSI Driver",
      "server": "VOYAGER ",
      "database": "TEST",
      "uid": "",
      "pwd": ""
    }}
  }
}

Usage Notes

In Example 3, the query is performed for all tables and the constraint and query are combined when indexing the City table. Therefore, the expression used when indexing the City table will be “STATE_NAME = ‘California’ AND POP1990 > 100000”. If a query was used for City instead of a constraint, then the expression would only be “POP1990 > 100000”.
A table cannot have a query and a constraint.
The percent symbol (%) is used to perform wild card searches to limit tables, fields and field mapping. However, if the asterisk is used when you want to include everything.
Geographic information is included in the index for tables with geometry. For point features, the X and Y coordinate location is indexed. For other geometries, the extent coordinates (bounding box) is recorded.
The Well-known text for each geometry can be optionally included in the index. This can be done by setting the wkt option to true. CAUTION: the WKT can be very large for complex polygons and lines.