Massachusetts Institute of Technology
Department of Urban Studies and Planning

For class exercises, homework, and class project, some of the data that we will use resides on a password protected 'backend' Postgres database server that is accessible online using standard structured query language (SQL) queries. You can access the server by logging in to your account through a web browser, or through client-side software (pgAdmin) that is installed on Lab machines. If you wish to access them from a personal computer, you may need to install pgAdmin on your machine. This document explains how to obtain and use a few of the many free client tools that can access databases.  You need not install any of the software if you plan to use Lab machines for the class.

(1) PostgreSQL - This open source, freely available database management system supports standard SQL, multi-user, multiple platforms, replication, query optimization, and more and is a non-proprietary alternative to Oracle. ArcGIS, MS-Access, R, and other ODBC-compliant packages can exchange information with Postgres and, with the PostGIS function add-ins, it can read shapefiles and do spatial queries that allow spatial operators in the 'where' clause. The PgAdmin III client-side user interface provides a SQL interface that is better than SQL*Plus though not as visual and graphic as MS-Access.

(2) SQL*Plus - This simple, free, Oracle-provided client software runs on Windows and supports SQL query access to Oracle servers.

(3) ODBC drivers - Application software such as MS-Access, MS-Excel, and ArcGIS support "open database connection" standards for accessing database servers. On a Windows-based client machine, you may need to configure an ODBC driver to enable access to our Postgres database server.

(4) ArcGIS access - You can access Postgres database servers from ArcGIS software (such as ArcMap) using an appropriate ODBC driver.

(5) SQuirreL SQL - This popular, free, open source tool provides universal SQL client access to database servers from Windows, Macs, or unix-based machines.

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

Spring 2018 updates regarding Software:

• ArcGIS - download possible from MIT: http://ist.mit.edu/software-hardware?type=27 (MIT certificate required)
• Kerberos and Cisco VPN software from MIT: http://ist.mit.edu/services/software/available-software
  • needed for virtual private network access to MIT so ArcGIS license manager allows off-campus use
• File access (to class data locker at /afs/athena.mit.edu/course/11/11.521/data):
  • OpenAFS (for AFS network file access to class locker): http://www.openafs.org/
  • Or, fetch, securefx (from MIT software site) or another secure ftp program to transfer files from class locker
• Oracle database engine client software: http://ist.mit.edu/software-hardware?type=All&platform=All&users=All&title=oracle&recommended_only=All (no longer needed for 11.521)
• Open Source tools
  • PostgresSQL relational database management software: http://www.postgresql.org/
    • You may also want PostGIS for spatial operators and pgAdminIII for administration
  • SQuirreL SQL client: a java-based universal SQL client that runs on Macs and PCs (in place of SQL*Plus)
    • Available at: http://squirrel-sql.sourceforge.net/
  • QGIS open source GIS software that is multi-platform and can use ODBC connections to Postgres, SQLserver, and other database engines 
  • Available at: http://qgis.org
• During pre-lab time (3-4 on Tuesday in 37-312) get help from me with personal software setup

Those in DUSP should also ask CRON staff about pre-installed images on ArcGIS 10 that can be downloaded from flash drives and have the latest software patches already installed.

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

PostgreSQL (back end database engine and client-side tools)

(a) Downloading and installing PostgreSQL

• Download from PostgreSQL's website (http://www.postgresql.org/)
  • The download page is here: http://www.postgresql.org/download/
  • As of February 4, 2018, the latest release is: version 10.1 · 9th Nov, 2017
• For Windows and Macs, you most likely want the free installer that is provided (via the above page) by EnterpriseDB (a company selling PostgreSQL services)
  • Download page: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
  • Older versions of the Windows and MacOS packages which are still supported can be found in the class locker:
    • Path: \afs\athena.mit.edu\course\11\\database\postgres\postgresql_for_Win64bit
    • Windows: Choose the 32-bit or 64-bit version depending upon your OS
      • File: postgresql-9.2.3-1-windows-x64.exe
    • MacOS X: here is the 'dmg' installation file:
      • File: postgresql-9.2.3-1-osx.dmg

(b) Adding the ODBC drivers (psqlODBC) - this is all you need if you only want to connect to the class postgres database engine from ArcGIS, MS-Access, etc.

(c) Adding spatial functions (PostGIS)

• Most fresh installation of PostgreSQL include the PostGIS extension.  If yours does not, you can download the relevant software.
• Download from PostGIS's website (https://postgis.net/)
• Further instructions on getting started with spatial SQL queries are available here (https://postgis.net/install/).

(d) Building the ODBC driver

• Download from PostgreSQL's website (https://odbc.postgresql.org/)
• Further instructions on installing the driver are available here (https://odbc.postgresql.org/faq.html#1.3).

(e) Connecting from MS-Access

• Step-by-step instructions are available here (http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html)

(f) Connecting from ArcGIS

• Step-by-step instructions are available here (http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-postgresql/connect-postgresql.htm)

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

While we will not be using Oracle in this class, previous versions of the class have used Oracle. Helpful notes for using Oracle are provided below.

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

(1) Installation notes for Oracle 11gR2 client from MIT's downloadable MSI file

<Step 1:> Getting the needed files
The Oracle client software is available as a free download from Oracle but the MIT online version is much easier to install since it has all
the MIT specific choices already selected with the MSI. The software is listed on the Software page made available by MIT's Information Systems & Technology (IST) webpages: http://ist.mit.edu. The Oracle listing is here: https://ist.mit.edu/software-hardware?type=All&platform=Windows+7&users=All&title=oracle&recommended_only=All

You can download the bundled files but beware that they amount to 600 MB and you will need an MIT web certificate to download the bundle.

<Step 2:> Installing the software
Once you download the bundle and extract all the files, run the installation executable (*.exe) at the top level. The executable file will run for several minutes and, if successful, build a C:\Oracle\... directory tree with all the needed files properly installed. The 'start' menu will contain 'Oracle' choices just as it does in the computing lab.

<Step 3:> Configuring the software
Before you can run SQL*Plus you will still need to change an Oracle configuration file in order for your Oracle client to know how to find our Oracle CRL database on the internet. Use a text editor to create a plain text named TNSnames.ora containing the following (without the '=========' lines):

====================

# TNSNAMES.ORA Network Configuration File: Spring 2016 version only for CRL database
#              on GREENLINE.MIT.EDU and
# Originally generated by Oracle configuration tools.

# START of CRL-specific entries

CRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = greenline.mit.edu)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CRL.greenline.mit.edu)
    )
  )

CRL.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = greenline.mit.edu)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CRL.greenline.mit.edu)
    )
  )
======================

There is a second configuration file, SQLNET.ORA, that may need changing to ensure encrypted authentication and communication, but the version that comes with MIT's distribution of the Oracle client should work.  These two files should replace the ones that you will find in the Admin subdirectory of your Oracle installation.  If you chose the installation defaults, this sub-directory is:
C:\oracle\product\11.2.0\client\network\admin
Before you copy your TNSnames.ora file into this directory, you may want to rename the existing configuration file. We suggest adding 'old' or 'original' to the name. Also, be sure you do not change the name or content of the plain text files when you copy them from the class locker. For example, some copy or transfer programs may 'wrap' lines longer than 80 characters or append a *.txt suffix to the file name.  Also, be sure that the names of the files are not changed when you copy them.  For example, the files are plain text and if you open them in a text editor and then save them to the new location, the software may add a '.txt' suffix to the file name.  If your Windows Explorer settings hide filenames suffixes, the '.txt' will not be visible but the file will be saved as 'tnsnames.ora.txt' - and SQL*Plus will not be able to get the information it needs to find the machine housing the CRL databse.

<Step 4:> Test your Oracle Client installation
Use the newly installed SQL*Plus application to test your Oracle Client. Just as you do in the computing lab, start it up from Start/AllPrograms/Oracle.../Application-Development/SQL*Plus

Make sure you can log in using these parameters:
User Name: = parcels@crl
Password: = <<given to you in class>>

If this connection works, try it again with your personal Oracle account.

+-+-+-+-+- end of Oracle 11gR2 Client installation -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

(2) Installation notes for ODBC drivers to use with Oracle 11g client

+-+-+-+-+ ODBC driver installation +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Next is the ODBC install for Windows

NOTE:
This ODBC connection is needed to setup the ODBC driver provided by Oracle so that it can be used by MS-Access or MS-Excel to connect
to the Oracle 11i database and the ArcSDE engines that we will use.
Later version of Windows come with their own "Microsoft ODBC for Oracle"
driver which is sufficient for some purposes but can have some problems with data type conversions.

Find and run the ODBC Data Source Administrator via:
C:\Windows\SysWOW64\odbcad32.exe
(This is the 32-bit version for use on 64-be Windows and is not the default choice!)

Choose to ADD a SYSTEM DSN
- pick "Oracle in OraClient 11g_home1" (which did not come with Windows but was installed with the Oracle 11gR2 client in Part I above).

Fill in the ODBC Driver Configuration.
For example, we use these settings for an Oracle database named 'CRL':
Data Source Name: CRL Oracle
Description:      CRL Oracle for MIT/CRL servers
TNS Service Name: CRL
User ID:

-- Leave the user ID blank so use of the ODBC connection will require
a userid and password at runtime.
-- Now, press the 'test connection' button and test the connection
with userid=parcels and the password provided in class

If the test works a 'connection successful' window will pop up.

Click OK to exit and you are done...

<<< This completes the Oracle ODBC software installation instructions.>>>

(3) ArcGIS connections to Oracle:

The SQL connection from ArcGIS to Oracle can be made using the same type of OpenDatabaseConnect or Object-Linking-and-Embedding connection as we have used to connect MS-Access to Oracle. However, some obscure software issues with our CRON and WinAthena environment complicate the ArcGIS to Oracle connection this semester. In order to connect ArcGIS to Oracle, we first need to customize ArcCatalog to include an Add OLE DB Connection button under 'Database Connections'. Then we can use the new button to add a specific OLE connection to our Oracle 11g database engine.

NOTE: We need these extra steps only on CRON and WinAthena machines because. If you have installed ArcGIS on a personal computer, the default 'add database connection' will connect to our 'CRL' Oracle 11g database engine as explained in the ArcGIS help pages.

Add an "Add OLE DB Connection" button to ArcCatalog

(1) Open ArcCatalog (separately not from within ArcGIS)
(2 )From the 'Customize' tab choose 'customize-mode' and click the 'Commands' tab
(3) Type ‘ole’ in the ‘show commands containing’ box
(4) Drag and drop ‘Add OLE DB Connection’  choice into  the main toolbar of ArcCatalog. This is the toolbar with the zoom and pan buttons. The newly added tool looks like a cylinder and has the label, 'Add OLE DB Connection'.

Add an OLE DB Connection to our Oracle 11g CRL database

(1) Click on this 'Add OLE DB Connection' icon to open the  'Data Link Properties' window
(2) In the 'Data Link Properties' window, click on 'Microsoft OLE DB Provider for Oracle' and then click 'Next' to open the 'Connection' tab. 
(3) Fill in the following entries:
Enter a server name: crl
user name:  <<your-oracle-username>>
password: <<your oracle password >>
-- for security, do *not* check 'blank password' or 'allow saving password'
-- click 'test connection'
-- after seeing a 'successful' message, click OK
(4) The Oracle connection may take a while to construct - but ArcCatalog will provide no feedback about when it is done or if there are problems. When it is done, a new entry 'OLE DB Connection.odc' will appear under the 'Database Connections' entry in the ArcCatalog Tree. However, you may have to click View/Refresh before the new database connection will appear. If your connection test was successful but the entry does not appear even after waiting a minute and refreshing, then exist ArcCatalog and restart it.
(5) When the new entry does appear, right-click the new connection and choose 'Rename'. Call it something like: OLE-to-Oracle11g-crl' so you can remember that you are connecting to the Oracle 11g class database called CRL.
(6) Finally, double-click on the new entry, re-enter your username and password, and wait about a minute. All the Oracle tables and views that you are allowed to list will appear in the 'Contents' portion of the ArcCatalog window. There are several thousand. Each entry has the owner username followed by a period '.' and the table (or view) name. While you can list all the tables and view, you need 'select' permission to preview the table or to drag and drop the table into ArcMap.

NOTE A : It is important that you do not check the "blank password" or "Allow saving password" boxes in step (3) above. Although checking them will avoid the need to retype the info after you save and then reopen the ArcMap document, it does mean that anyone with a copy of your ArcMap document will be able to open the connection to Oracle using your account! If you leave the options unchecked, you will simply be prompted for the info when you first open up the saved ArcMap document. Saving the name/password will also keep a copy in your ArcGIS configuration file. Since you are working on a WinAthena PC this configuration file will be saved in your private locker rather than on the local driver part of C:\Documents and Settings\ . But, on a more public machine, you would have left your account info for anyone who knew to look for it.

NOTE B: We are using the CRL database services that are running in Oracle on our database server (a virtual machine named greenline.mit.edu). Your machine knows how to find this Oracle database, because the CRL entry has been added to the TNSnames.ORA configuration file that is saved within the C:\Oracle... tree on your machine. To access Oracle from a personal machine you need to install the Oracle 11g client software and replace the TNSnames.ORA file as explained earlier in these notes.

<<Spring 2013 Note: This new driver will truncate all fractions to integers when you grab a view. If you want to import fractions, you will need to import from a table. Use a 'create table t_xxxx as select ...' command in Oracle to save your view into a temporary table - and remember to 'drop table t_xxxx' later on. >>

Older notes regarding ODBC drivers:

Choose one of these two ODBC drivers
(option a) Microsoft OLE DB Provider for ODBC Drivers
On the connection tab: data source:
Use data source name = "CRL Oracle"
User name = 'parcels'
<< or your personal Oracle account if available >>
and password = <<given in class>>
and, for security reasons, leave unchecked both the
'blank password' and 'allow saving password' choices.
Finally, leave item 3 (the catalog) blank and
click 'test connection' button.
If the 'connection successful' window pops up, all is ready
and you can click the 'OK' button

(option b) Microsoft OLE DB Provider for Oracle
On the conn

but not from views. ection tab:
(1) server name = crl
(2) set user name = parcels
and password = <<given in class>>
and, for security reasons, leave unchecked both the
'blank password' and 'allow saving password' choices.
Finally, click 'test connection' button.
If the 'connection successful' window pops up, all is ready
and you can click the 'OK' button

You are now back to the ArcGIS "Add Data" window.  Before clicking
"Add," change the name of your new connection from "OLE DB Connection.odc" to something you remember!.  Then click the new name
to put it in the 'name' box and finally, click "Add"/

(4) SquirreL SQL

SquirreL SQL is an open source, universal SQL client made available via SourceForge. The SquirreL SQL client is written in Java and can run on most machines that can run java code. See: http://www.squirrelsql.org/ for further information.

Oracle also provides some client-side Mac software (written in java) to access Oracle database servers. While we are not familiar with these tools, and do not support their use, you may find some of them to be useful. The relevant site is: http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html

[updated, Feb. 4, 2018, rb]