Gis Access Excel Database - Download as PDF by ujr21188


More Info
									GIS III Exercise 01 – Create a Database & Map Urban Land Use Jan 2010
Student Learning Outcomes:
 Import table data into an MS Access database; NOTE: DOS 8.3 naming is required for files
   being imported into Access!
 Correctly assign relationships (e.g., one-to-many, many-to-one) between tables
 Use ArcCatalog to map a direct connection between Access and ArcMap
 Map directly from the database

Introduction – Modeling change is facilitated by mapping data from different time periods, be
they land use data to model urban sprawl, water chemistry and subsurface hydrogeology to
model a spreading plume of contaminated water, or changing wind conditions and fuel
availability to model the spread of a forest fire. From the GIS perspective, most experts agree
that it is best to house your data in a large, easily accessible and editable database. In fact,
ESRI has developed a GIS format that is driven by a “geodatabase”. Since database-driven
GIS mapping and modeling is here to stay, this exercise introduces you to the basics of MS
Access database creation and usage, working with land use data courtesy of Jersey City, NJ.

Scenario – As the urban planning and redevelopment expert for Jersey City, you have decided
to construct a Brownfield Inventory. Brownfields are current or former commercial / industrial
sites that are either contaminated or perceived to be contaminated. These sites are frequently
found in urban areas, frequently appearing as run-down and underutilized Financial incentives
exist to motivate businesses and banks to redevelop these urban sites, rather than contribute to
urban sprawl by moving new businesses into “greenfields”. Once you have built the Brownfield
Inventory database in MS Access, you will use ArcCatalog to create a direct connection
between the database and ArcMap, which will enable you to map directly from the database.

Part I – Review the “shapefiles” approach to GIS mapping of Jersey City parcels
Step 1. Create exercise folder, copy data from server into your folder.
By now, you know the drill for working with GIS shapefiles. Create your own GIS_III_Exr01
folder on the GIS server in your own semester folder, on a large flashdrive, or on your own
computer. Once you have created your own folder, Copy>Paste ALL of the following data from
the GIS_III_Exr01_input data folder (inside GISStudent on geosrv) to your new folder:

Step 2. Open ArcMap, set Projection to State Plane NAD 83 Feet
This should also be routine for any GIS student who has reached this level of performance.
These files are in the official NJ coordinate system, State Plane NAD 1983 Feet.
 Open ArcMap (Start > Programs > ArcGIS > ArcMap > New map).

   BEFORE you add any data, Rt-click on Layers > Properties > Coordinate System >
    Predefined > Projected CS > State Plane > NAD 1983 (Feet) , and set the coord system to

Click Apply, OK.
Click on the General Tab of the Data Frame Properties window, and make sure that both the
Map and display units are set to Feet; press OK.

Step 3. Add MC_sites and Parcels shapefiles into ArcMap
Use File > Add Data to scroll to, select, and add the Parcels.shp and MC_Sites.shp shapefiles
from your folder to ArcMap:

Use Symbol Selector to make the MC_Sites file transparent (“hollow”) with a bright outline like
red that you can see easily (see below). If necessary, set the order of display of the shapefiles
in the ArcMap Table of Contents so that the MC_Sites file overlays the Parcels file:


        The MC_Sites shapefile was painstakingly built by one of NJCU’s top GIS students, Paul
Kaczka, for the Jersey City Redevelopment Authority. The JCRA needed an accurate Morris
Canal Brownfields Inventory (“MCBI”) to remain in compliance with the US EPA. Paul has
graduated and become a very successful GIS professional. Paul created each MCBI Site
outline by first selecting appropriate Parcels from the Morris Canal Redevelopment Area
(MCRA) Parcels shapefile comprising each site, then Dissolving the internal parcel boundaries,
leaving just the external outline, shown above in red for each site. Although some sites are
composed of just one parcel, many sites are composed of multiple parcels.
        Paul also created the accompanying MC_Sites attribute table (“AT”). Open it now:

The KCSL_ID, CHROME_ID, and STEMRT_ID columns (see above) refer to the various
identification numbers for each sites in different New Jersey databases (KCSL, Chrome, and
Sitemart. Paul also incorporated the Area of each site (in acres) into the attribute table.
Use Properties > Labels to label the Sites with their MCBI_NO identification number:

Zoom into Sites 26 and 43:

Now, open the Parcels AT:

Note the column entitled MCBI_NO

This column is NOT a “primary key” for the Parcels shapefile because the values in this column
are not always unique identifiers for each parcel. But this column WILL allow you, later in the
exercise, to use some database management techniques through MS Access to re-create, on
the fly in a matter of seconds, what it took Paul Kaczka many long hours to create. This is why
you are starting off the GIS III course with this seemingly “simple” exercise – it will show you the
power of creating interactive, relational databases, through ESRI (“geodatabase”) or other
database providers, such as Oracle, SQL, or Access. The use of databases enables the GIS
practitioner to engage in “modeling”, to better understand the past and to predict the future.

Step 4. Analyze “many-to-one” relationship between Parcels (“many”) and Sites (“one”)
Return to ArcMap and focus in on Site 26. In the Parcels AT (opened in the previous step),
highlight the 3 parcels that comprise Site 26 in bright blue:

Confirm that these parcels have turned bright blue on the map and do indeed comprise Site 26:

The relationship you see in this example is known as “many-to-one”, and it is important that you
recognize the difference between this and a “one-to-one” relationship. One-to-one relationships
are the ones you are used to seeing in GIS; they typify the “primary key”, where you need ONE
UNIQUE IDENTIFIER for EACH FEATURE. In the example above, you have “many” Parcels in
one table (in this case, 3) that are linked to “one” Site in another table. That is to say, all 3 of
these parcels belong to – or could be “joined to” - Site 26.

You will see in later portions of this exercise that “Many-to-one” table joins or relationships are
very important in modeling with relational databases, which themselves are composed of
multiple tables. Up to this point in your GIS training, you have become accustomed to seeing
just one table (an attribute table) associated with a group of features. However, to harness the
true power of GIS, especially in “real time” or “on-the-fly”, you must now become familiar with
relational databases and more complex table joins than the simple “one-to-one” that you have
used up until now to connect a GIS attribute table to an external table containing additional data.

Part II – Create / populate a simple MS Access Database and connect it to the GIS
Step 1. Create a new, blank Access database
 Select Start > Programs > MS Office > Access. In the blank window that appears, select
   File > New > Blank Database

   From the “File New Database” window that opens (below), scroll to your GIS III Exr01
    folder, name the new database as Exr01_db1, and press Create:

Step 2. Import the MC_Sites table into Access
Discussion – You have created a blank “shell” of a database, but have not yet populated the
database with data. There are a number of ways to put data into an Access database. Here,
we will practice bringing data from an existing table (DBF or Excel file) into Access, to illustrate
the process. IMPORTANT – Any table (Excel, DBF, etc) that you try to input in Access MUST
BE NAMED WITH 8 CHARACTERS MAXIMUM (DOS 8.3 naming convention). For future
reference, you must RENAME any of your files that exceed 8 characters BEFORE you try and
use them in Access, or you will “crash” the program.
 In the Access window that opened after you pressed “Create” in the previous Step, select
    “Create table in design view”:

   Select File > Get External Data > Import:

   Scroll to Exr01 folder, change Files of Type to dBase IV, select MC_Sites, click Import

A confirmation window appears; press OK:

NOTE: The steps used to Import an Excel table may be different than for the DBF file.

The file is now visible in the main Access window, confirming that it was imported:

   Dbl-click on the MC_Sites table to open it up:

   Rt-click on the MCBI_NO column and select “Sort Ascending”

Confirm that the MC_Sites are now sorted from lowest number to highest number (86):

Step 3. Import the Parcels table into Access
 From the main Access window, select File > Get External Data > Import, scroll to and
   select the Parcels dBase IV file, press Import:

A window appears confirming successful import of the table into Access; press OK:

   From the Access main window, Dbl-click on Parcels:


The Parcels table opens; scroll to the far RH side and confirm the MCBI_NO column is present;

   Rt-click on the MCBI_NO column and select Sort Ascending (see below):

   Scroll down the Parcels table until you start seeing entries in the MCBI_NO column (below)

NOTE: It is critically important that the GIS student understand that the MCBI_NO field in THIS
table (the Parcels table), is NOT BE THE PRIMARY KEY for THIS TABLE. Why? Because the
“MCBI_NO” values in this field do NOT uniquely identify each parcel. For example, there are at
least 6 parcels in the Parcels table that are associated with Site 02 (above); scrolling through
this Parcels table will show you many other instances of multiple parcels associated with just
one site. Can you see how this might be viewed as a “many-to-one” relationship?

Even though this MCBI_NO field is not a primary key for the Parcels tables, it plays an important
role in connecting this table to another table in the database. This field is a “Foreign key” for
the Parcels table, because it is the field that “connects” this table back to the MC_Sites Table
through the Primary key of the MC_Sites table (also labeled MCBI_NO).

Step 4. Define a Primary Key for the MC_Sites table in Access
Discussion -
        As a relational database, Access is capable of creating “relationships” (one-to-one, one-
to-many, many-to-one, many-to-many) between tables. It is very important that you – and
Access – are clear about “relationships”, especially when it come to defining fields. For
example, some sources suggest that a table’s Primary Key field is best defined as “Text” – this
is what I did when creating this exercise. However, some sources (including Microsoft) suggest
that “AutoNumber” is best. Since the MC_Sites table is very important to this exercise (I view it
as the “master table”), it is also important to be very clear that this table’s Primary Key, which is

Procedure -
 From Relationships window in Access, rt-click on the MC_Sites table, select Table Design:

The Table Design window opens. Note that the MCBI_NO field is not currently indexed, nor is
this field defined as the Primary Key:

   Rt-click on the black arrow to the left of the MCBI_NO field (see above), select Primary Key
    (see below);

Note that there is now a “key” displayed to the left of the MCBI_NO field (below), and note also
that this field is now indexed, with “no duplicates”; this is essential for a field for a primary key:

   Close the Table Design window – when prompted to Save changes to Design, say Yes.

Step 5. Create a “one-to-many” relationship between MC_Sites and Parcels tables
With a Primary Key now clearly defined for the master table, you should now be able to Create
a relationship between this table and the Parcels table.
 To create a relationship between the tables, from the “Relationships” window; click on
    MCBI_NO in MC_Sites and drag down to MCBI_NO in Parcels:


In the Edit Relationships window that opens (below), note that the tables are characterized as
“Table” and “Related Table”, and the “Relationship Type” is already set to be “one-to-many”.

An example of the “one-to-many” relationship is Site 02 (one site related to many parcels):

   In the Edit Relationships window, check the Referential Integrity boxes, select “Join Type”

   In the Join Properties window that appears, select option #2. The “Parcels” table has
    thousands of records encompassing all of Jersey City, but the only parcels that we are
    interested in mapping for this exercise are those that are in the Morris Canal
    Redevelopment Area. Press OK

   When the Edit Relationships window returns, press Create. The Relationships window
    returns with the 1 - ∞ (read: “one-to-infinity”, meaning “one to many”) relationship between
    the MC_Sites and Parcels tables clearly labeled, showing the “one” and the “many”:

Step 6. Create a Query in MS Access
Discussion – To some extent, a “query” links your ArcMap features to the Access database that
contains information about these features, not unlike the way that an attribute table contains
information about features. Think of a query as a way for a user to create a “custom” attribute
table for features by selecting columns of information from an entire database.
Procedure –
 Scroll to your GIS folder, open your MS Access database. In the main window, select
    Queries on the LH side of the window (see below):

   A new window opens with 2 Query choices; dbl-click “create query in design view”:


A Show Table window opens:

   Dbl-click on both the MC_sites and parcels tables, then close the Show Table window.

The main Access window shows both tables schematically:

Note that the Primary Key field in the MC_Sites table links it to the Foreign Key in parcels.
You will now select several fields to include in the new table (the “query”).
 From the main access window, select and drag the following fields into the table at the
   bottom of the window:
MCBI_NO and SITE_NAME from the MC_Sites table
PARCELID and OWNER from the parcels table
The table at the bottom of the window now contains these 4 fields:

   From the top of the main Access window, select Query > Run (see below):

The query table’s fields should populate with information taken from the main database:

If necessary, expand the column widths to show the complete owner and site names:

   Close the Query window by clicking the “X” in the upper RH corner:

   When prompted to save changes to the Query, click Yes

   In the Save As window that appears (below)

   Name the Query and click OK (below)

   From the main Access window, select File > Save to save your changes, then close Access:

Step 7. Create and Name a Database Connection (ODC)
You will now start entering territory that you have not seen before, so prepare for some
frustration and confusion at first.
 Open ArcCatalog (Start > Programs > ArcGIS > ArcCatalog). The main AC window opens.
    Select Database Connections in the LH window, then dbl-click Add OLE DB Connection:

A Data Link Properties window opens; select Microsoft Jet 4.0 OLE DB Provider; press Next:

The Data Link Properties window opens – here we find a problem; user is supposed to be
logged in with Administrator rights in order to connect Access to ArcMap via OLE. For now, we
will continue with the instructions, but will have to address this at some point. Press the button
on the RH side of Line #1 to scroll to your database.

Once you have scrolled to your folder, select Exr01_db1, press Open:

The Data Link Properties window re-appears (below, left) with the directory path leading to the
.mdb file (the Access database); press Test Connection

You should see a window indicating a successful test connection (above, right); contact your
instructor otherwise. Press OK, then OK.

Return to the main ArcCatalog window; you should see a new database connection simply
named “OLE DB Connection.odc” (below). You know this is the connection you just created
because you can see the 3 tables in the DB in the RH window (MC_Sites, parcels, and Query):

                                                            Confirm these 3
                                                            tables are present

                                             Rt-Click OLE DB connection and Rename

   Rt-click on OLE DB Connection.odc; select Rename
   Change the name to a DB connection that makes sense, and include your initials in the
    name so that you will recognize this connection as the one YOU made. This will be
    important in a computer lab, where more than one student will be saving an ODC
    connection on the same machine. I named mine “parcels_DB_connection_wwm.odc”
   Once you have renamed your database connection, close the ArcCatalog window.

Step 8. Open a new ArcMap window and connect to the Access database
 Open ArcMap, select a new map. A new map view opens:

   Rt-click Layers > Properties > Coordinate System; set View to State Plane NAD 1983 (Feet):

Press Apply, OK.

The ArcMap window returns.

   Select File > Add Data; scroll to your folder and select the parcels shapefile; press Add:

In the ArcMap view, you should be able to see the Jersey City parcels:

   Select File > Add Data again; select “Database Connections” in the Add Data window:

   Select the database connection you created previously and press Add:

   Select the three tables in your database; press Add:

When the ArcMap window returns (below), make sure that the Source tab is selected at the
base of the ToC. The database connection and 3 tables comprising the database are visible:

   File > Save your project.
Step 9. Join the Database Query to the Shapefile Attribute Table
Discussion - You can already begin to see the benefits of mapping with a database. It enables
you to focus your attribute mapping, and create tables of adequate size and information, without
them being HUGE, like many of the attribute tables with which you have worked in the past.
Here, we will create a simple map (or maps) of MCBI sites in Jersey City. Recall that this took
one of our GIS experts, Paul Kaczka, countless hours to create the map that you will create in a
few minutes. Behold the power of the database!
 Rt-click on Parcels in the ToC > Joins and Relates > Join (below)

   Fill in the Join Data window (below, left) as follows (below, right), press Advanced:

       In the Advanced Join window (below), select “Keep only matching records”; this is a
desirable option here, because the number of parcels records (encompassing all of Jersey City)
greatly exceeds the number of records in the Query table you created. Press OK.

   Press OK again when the Join Table window re-appears.

Return to the ArcMap window:

As noted in the Advanced Join window at the top of the page, the number of visible parcels is
greatly reduced from its original number because only those features in the Attribute Table
(the “Target table”) that are joined to the Query table (the “Join Table”) are shown on the map.
NOTE: If you want to keep all parcels visible, do not select “keep only matching records”.
 File > Save your project before proceeding.
Step 10. Select MCBI parcels by MCBI Number through the Join to the Database Query
Up to this point, you have joined the parcels shapefile AT to the “Query” table that you built
through Access, in a manner that is similar to any “table join” that you might perform with a
conventional DBF table.

Procedure - The first map that our client wants identifies the boundaries of each MCBI Parcel.
 Rt-click on parcels in the AT > Open Attribute Table. Scroll to almost the RH side of the AT:

The last 4 columns on the RH side are those from the “joined” Query table from Access. Move
the table aside and return to the map.

   Rt-click on parcels in the ToC > Properties > Labels (below). Select “Label Features” in
    this layer”, make MCBI_NO the label field, and select the Bold button for the labels

   Press Apply, then OK to make these selections.

Return to the map, which now has each parcel labeled by its MCBI identification number:

You can use an advanced feature of ArcGIS (“dissolve”) to aggregate parcels together as actual
MCBI sites and create boundaries for the sites; these should be identical to the boundaries that
Paul Kaczka created by hand. To learn more about dissolve, use the Help menu on the top bar
of your ArcMap window, and select “dissolve tool, using with features”:

Dissolve enables you to “aggregate” a large number of small features (input above) into a
smaller number of larger features (output above) based upon an attribute such as MCBI_NO.

Dissolve can be accessed by “opening the tool” from above window or by following the path laid
out on the next page.

Step 11. Dissolve parcel boundaries and create new MCBI shapefile
 Select ArcToolbox > Data Management > Generalization > Dissolve
 Select the following fields in the Dissolve window:
 Use the black dropdown arrow to select parcels as the input features
 Scroll to your GIS_III_Exr01 folder and save the new feature class as mcbi_sites
 Select the Dissolve fields as MCBI_NO, Site Name, and Owner

   Press OK
   Close the Dissolve dialog after it indicates that the operation was executed successfully:

The map returns with the new mcbi_sites shapefile:

   Rt-click on parcels and turn OFF the labels by Unchecking “Labels”
   Rt-click on mcbi_sites > Properties > Labels and use the appropriate setting to label the
    polygons with MCBI_NO:

   Dbl-click on mcbi_sites polygon in the ToC; select “Hollow” with a thick outline to portray
    boundaries; press OK to make the changes:

The map reflects the symbology changes to the MCBI site boundaries, and enables you to see
the individual parcels that collectively comprise the MCBI sites:

Zoom out to the map’s full extent (about 1:12,000).

Compare the site polygons to those from MC_Sites

The correspondence between the 2 maps is very good, except for Site 40, the southernmost
site. If we turn the entire Jersey City parcels shapefile on, you can see why there is a
discrepancy: there are no parcels associated with site 40; it was hand-digitized in by Paul.

                                                            No parcels associated
                                                            with site 40

   File > Save your project

Step 12. Create a layout and write a summary of this exercise
 Use the Layer Properties window to set the visible fields in the mcbi_sites AT to be:
   MCBI_NO, Site Name, and Owner:

   Create a map layout that shows the outlines of MCBI sites that are labeled with their site
    numbers. Make sure the layout contains the appropriate elements (N arrow, scale, legend).
   Use the Options button in the lower RH corner of the mcbi_sites AT to first change the
    appearance of the table (make the font size 6) and then add it to the layout. Use Data
    Frame > Properties > General tab > Rotation to rotate the map view to better fit:

In your summary, mention one or two ways that you could see this “database-driven” approach
to GIS used in your profession as a more efficient way to link attribute data to features, as
opposed to building individual attribute tables for each shapefile. Also feel free to mention ways
that this exercise could be improved – your feedback is very valuable for future GIS students.


To top