Indexing SQL Server Databases

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 10.1/10.2 can be used to install Python. NOTE: If ArcGIS 10.1 or 10.2 is installed, Python is likely to already be installed.

Indexing Data

After confirming the dependencies, you need to add a new location.

To create a new location:

Go to Manage Voyager > Locations
Click New Location
Select Databases (Advanced)
Select SQL Server in the Connections drop-down list
Edit the configuration

Configuration Examples

Example 1

This configuration indexes all the tables in a SQL Server database. 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.

{
    "name": "SQLServer_Sample1",
    "type": "python",
    "config": {
        "fields": {
            "include": ["*"]
        },
    "tables": [
    {
        "name": "*",
        "action": "INCLUDE"
    },
    {
        "name": "*",
        "map": {
            "NAME ": "name"
            }
    } ],
    "sql": {"connection": {
      "driver": "SQL Server",
      "server": "VOYAGER",
      "database": "VOYAGERTEST",
      "uid": "",
      "pwd": ""
      }
    }
  }
}

Example 2

Index a single table.

{
    "name": "WORLD_CITIES",
    "type": "python",
    "config": {
        "fields": {
            "include": ["*"]
        },
    "tables": [
    {
        "name": "CITIES",
        "action": "INCLUDE"
    },
    {
        "name": "*",
        "map": {
            "NAME ": "name"
            }
    } ],
    "sql": {"connection": {
      "driver": "SQL Server",
      "server": "VOYAGER",
      "database": "VOYAGERTEST",
      "uid": "",
      "pwd": ""
      }
    }
  }
}

Example 3

This example demonstrates the following:

Index the STATES and CITIES tables
Include all fields
Mapping STATE_NAME and CITY_NAME to name
Use queries to limit the number of rows that are indexed. See the Usage notes below for further explanation.

{
  "name": "A SQL Server Location",
  "type": "python",
  "config": {
    "fields": {"include": ["*"]},
    "tables": [
      {
        "name": "STATES",
        "action": "INCLUDE",
        "map": {"STATE_NAME": "name"},
        "query": "select * from STATES where STATE_NAME = 'California'"
      },
      {
        "name": "CITIES",
        "map": {"CITY_NAME": "name"},
        "constraint": "select * from CITIES where POP1990 > 100000 and STATE_NAME = 'California'"
      }
    ],
    "sql": {"connection": {
      "driver": "SQL Server",
      "server": "VOYAGER ",
      "database": "VOYAGERTEST",
      "uid": "",
      "pwd": ""
    }}
  }
}

Example 4

SQL Server supports the ability to index related tables as links. This example demonstrates the following:

Index a table that with a one to many relationship using a SQL Query
Include all fields
Mapping STATE_NAME and CITY_NAME to name

{
  "name": "A SQL Server Location",
  "type": "python",
  "config": {
    "fields": {"include": ["*"]},
    "tables":[
     {
       "name":"STATES",
       "action": "INCLUDE",
       "map":{"STATE_NAME":"name"},
       "related_tables": ["CITIES"],
       "query":"select * from STATES,CITIES where STATES.STATE_NAME=CITIES.STATE_NAME ORDER BY STATES.STATE_NAME"
     },
     {
       "name": "CITIES",
       "map": {"CITY_NAME": "name"}
     }
 ],
    "sql": {"connection": {
      "driver": "SQL Server",
      "server": "VOYAGER ",
      "database": "VOYAGERTEST",
      "uid": "",
      "pwd": ""
    }}
  }
}

As shown in this illustration, the state will now have linked items. For example, the state of California will contain cities such as San Diego, Fresno, etc.

Usage Notes

In Example 3, a SQL Query statement is entered limiting how many items are index.
The percent symbol (%) is used to perform wild card searches to limit tables, fields and field mapping. However, use an asterisk (*) 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.

Voyager Search Help System