GIS 1

GIS Lecture 4 Geodatabases

GIS 2

Outline •Administrative Data Example •Data Tables •Data Joins •Common Datasets •Spatial Joins •ArcCatalog •Geodatabases •Editing Tables •Excel Tips

GIS 3

Administrative Data Example

GIS 4

Administrative Data Mission

• What does the organization do? • Transaction event (who, what, where, when)

-must have spatial component

What data is available? • Databases • Spreadsheets

GIS 5

Example Kaiser Family Foundation

•non-profit, private operating foundation focusing on the major health care issues facing the nation

•independent voice and source of facts and analysis for policymakers, the media, the health care community, and the general public

GIS 6

Kaiser Family Foundation State Health Facts

GIS 7

Collects Data on all 50 States •Demographics and the Economy •Health Status •Health Coverage and Uninsured •Medicaid and SCHIP •Medicare •Health Costs and Budgets •Managed Care and Health Insurance •Providers and Service Use •Minority Health •Women’s Health •HIV/AIDS

GIS 8

Data Tables

GIS 9

Download Data

GIS 10

Download Data

GIS 11

Open data in Excel

GIS 12

Data in Excel

GIS 13

Clean Data in Excel •Delete Unnecessary Data

GIS 14

Save Database Saved as .CSV or .DBF

GIS 15

Data Joins

GIS 16

Add Data Table in ArcMap

GIS 17

Add Shapefile

GIS 18

Choose Fields to Join

GIS 19

Join Data

GIS 20

Fields Added to States Table

GIS 21

Problems with Joins

Must remove dashes!

• Data in fields not exactly the same

GIS 22

Common Problems with Joins •Different data field types

GIS 23

Problems with Joins •Saving .DBF and .CSV files in Excel •Avoid special formats •Keep it simple

GIS 24

Number Fields Important •Quantities

GIS 25

Common Datasets

GIS 26

Common Datasets U.S. Census Maps and U.S. Census Tables

GIS 27

Common Datasets Zip Codes

-Use with U.S. Census data or Geocoding

GIS 28

Place Geocodes: FIPS Codes Federal Information Processing Standards Codes

Developed by the National Institute of Standards and Technology

Codes for Place Names Throughout the World •countries •states/provinces •counties •metropolitan statistical areas (MSA’s) •cities •places - indian reservations, airports, and post offices in the US

GIS 29

Place Geocodes: Hierarchy

Country: US

FIPS CODES

County: 003 (Allegheny) State: 42 (Pennsylvania)

Tract: 0501

Block: 12 (US420030501312) Block Group: 3

CENSUS CODES

Minor Civil Division: 85188 (Wilkinsburg)

Parcel (Block & Lot#)

GIS 30

Other Datasets •Contact agencies •Obtain data on-line

GIS 31

Spatial Joins

GIS 32

Spatial Joins Spatially count points within polygons Join tables using Shape field Join points to polygons or polygons to points

GIS 33

Spatial Joins Join municipality name to points spatially

GIS 34

Spatial Joins Resultant point layer (food points) now contains municipality information

GIS 35

Aggregate Data Summarize on spatial join

- Counts the number of points (records) for each municipality

GIS 36

Aggregate Data Resultant Table

- Count of points (food stores) in each polygon (municipality)

GIS 37

Join Summary Table Join summary table of counts (based on municipality name from sum table) back to municipalities for labeling

GIS 38

Label Counts Label the count of stores from the summary join

GIS 39

Label Counts Show counts as a Choropleth Map

GIS 40

Map with Points

GIS 41

Map without Points

GIS 42

ArcCatalog

GIS 43

ArcCatalog

GIS 44

Metadata

GIS 45

Metadata

GIS 46

Metadata

GIS 47

Metadata

GIS 48

Metadata

GIS 49

Geodatabases

GIS 50

Geodatabase •Represents geographic features and attributes as objects and is hosted inside a relational database management system

•Manages coverages, grids, and shapefiles inside a database management system, or DBMS

GIS 51

Enterprise Geodatabases •Requires a ‘host’ DBMS

-SQL Server, Oracle, or IBM DB2

GIS 52

Personal Geodatabases •Based on the Microsoft JET engine

-Appears as an .mdb file (Microsoft’s JET engine is also used by Microsoft Access).

GIS 53

Geodatabase Advantages •Geodatabases have several important advantages over the traditional file-based GIS data models -Makes GIS datasets smarter -More accurately represent the shape of features -Provides a uniform and IT compliant repository for geographic data -Many users can edit geographic data simultaneously

GIS 54

Geodatabases

GIS 55

Importing Shapefiles into Geodatabases

GIS 56

Importing Tables into Geodatabases

GIS 57

dBASE and Tables

GIS 58

dBASE Tables

GIS 59

Editing Tables Field definitions edited in ArcCatalog Layers cannot be in use elsewhere

GIS 60

Calculator Functions

GIS 61

Calculator Functions

Not just for numeric fields

GIS 62

Excel Tips and Tricks

GIS 63

Excel Tips and Tricks •Formatting •Find and Replace •Transpose •MID Function •Concatenate

GIS 64

Formatting and Replace •Delete any Excel formatting (e.g. borders, patterns, etc.)

•Delete unwanted text (e.g. word “tract”) -Find and replace

GIS 65

Transpose Data •Transpose data

-Some census data saves data as rows instead of columns

-You need transpose the data so that tracts will be a column -Select your data in excel and go to Edit menu and select copy -Click on a cell outside of your selected data and go to edit menu to select paste special…

GIS 66

Now you see the data transposed in the right way. All you need to do is to delete the original rows and save your file.

GIS 67

Mid Function Sometimes, a simple replace won’t do for you to substring a column. In this case, you can use Excel’s mid function to get a subset of a value.

For example, if you have the following data want extract two letters in the middle, say 12 from the first row, and 13 from the second row, and so on, the Excel MID function is your choice.

GIS 68

Mid Function You enter: =mid(A1,4,2) in the cell next to A1. What you tell excel to do is: From cell A1, extract 2 characters starting from the 4th character from left.

GIS 69

Mid Function Then you copy and paste the formula from B1 to the rest of the column and you get what you need.

GIS 70

Concatenate Strings Sometimes you need add certain string before or after a value, and sometimes you want to combine two values. The excel CONCATENATE function makes that happen for you.

GIS 71

If you have the below dataset, and want to combine the state ID and county ID together as another variable and you want to make sure the new StateCountyID has the length of 5

For example, you want to add a “0” to State IDs that are only 1 character long, and you want add “00” to county IDs that that is 1 character long and “0” to those that are 2 characters.

Concatenate Strings

GIS 72

Start by adding a new column right to StateID, and enter:

=IF(LEN(A2)=1,CONCATENATE(0,A2),A2)

What you tell excel is: if the length of the cell A2 is 1, add 0 before the original value, otherwise, keep the original value

Concatenate Strings

GIS 73

After that, you copy and paste the formula to the rest of the column, and you will see the following result

Concatenate Strings

GIS 74

Now let’s solve the problem for county ID. In the cell next to C2, you enter:

=IF(LEN(C2)=1,CONCATENATE("00",C2),IF(LEN(C2)=2,CONCATENATE("0",C2),C2))

What this tells excel is if the length of county ID is 1, add “00” before it, if the length is 2, add “0” before it, if the length is 3, keep the original

Concatenate Strings

GIS 75

Now you copy and paste the formula to the rest of the column and you get the above result

Concatenate Strings

GIS 76

Next, you create a new column called StateCountyID, and enter is the cell E2:

=concatenate(B2,D2)

By this, you are telling Excel to combine the two new columns that you created in the previous steps

Concatenate Strings

GIS 77

Now you just need to copy and paste the formula to the rest of the column and you finally get what you want.

Concatenate Strings

GIS 78

Other Excel Functions You might want to learn some other excel

functions that could help you clean the data, such as: • Right() • Left() • Exact()

GIS 79

Summary •Administrative Data Example •Data Tables •Data Joins •Common Datasets •Spatial Joins •ArcCatalog •Geodatabases •Editing Tables •Excel Tips