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: 

1. Go to Manage Voyager > Locations
2. Click New Location
3. Select Databases (Advanced)
4. Select SQL Server in the Connections drop-down list 
5. 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.