Attribute Tables GIS 1 Data table format Rectangular

Attribute Tables GIS 1

Data table format • Rectangular table with one value per cell -Columns (fields) are attributes -Rows are observations (data) GIS 2

Data table format • First row must have column names that are selfdocumenting labels -e. g. , Pop 5 To 17, Area -First character is a letter -Remaining characters can be any letter, digit, or the underscore character (but no blanks) • All additional rows of a data table must contain only attribute values -None of the rows can be sums, averages, or other statistics for other rows • Sometimes columns can be functions of other columns (sums, averages, etc. ) GIS 3

Primary keys Each data table has a primary key with two properties -Each value in the attribute column is unique -There are no null values GIS 4

FIPS Geocodes • Federal Information Processing Standards -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 See http: //www. genesyssampling. com/pages/Template 2/site 2/61/default. aspx for additional geocodes. GIS 5

FIPS Geo codes: Hierarchy FIPS codes Country: US State: 42 (Pennsylvania) County: 003 (Allegheny) Minor Civil Division: 85188 (Wilkinsburg) Census codes Tract: 0501 Block Group: 3 Block: 12 (US 420030501312) Local government cadastral data Parcel (Block & Lot#) GIS 6

ANSI Codes American National Standards Institute (ANSI) Codes www. census. gov/geo/www/ansi. html Replaces FIPS codes In many cases, the codes are the same (e. g. Pennsylvania, Allegheny County = 42003) GIS 7

External table file formats for import to Arc. GIS • Plain ASCII text with comma separated values (. csv) - Very transportable format, very large files - Each table record is a row terminated with a line-break character - Has values separated by a delimiter, usually a comma - For data values that contain the delimiter, enclose the value in double quotes - Sometimes columns get wrong data type on import (use double quotes to force text data type for digits, say for house numbers) GIS 8

External table file formats for import to Arc. GIS • Excel (. xls, . xlsx) -Excel 2003, up to 65, 000 rows and 256 columns -Excel 2007, up to 1, 048, 576 rows and 16, 384 columns • d. Base database table(. dbf) -Legacy format -Arc. Map truncates field names to 1 st 10 characters -d. Base IV has max of 255 columns -Can open d. Base file in Excel but cannot save Dbase from Excel • Microsoft Access database (. mdb) -Up to 2 GB file size -See following for other limits: http: //www. databasedev. co. uk/access_specifications. html GIS 9

Field calculator (numeric) GIS 10

Field calculator (numeric) Calculate acres to square miles GIS 11

Field calculator (text) Concatenate house number and street fields GIS 12

Field calculator (text) Populate field with county name GIS 13

Data Table Joins GIS 14

Joins • Feature attribute tables, data tables • Puts two tables together, on the fly, to make one table -One-to-one join -One-to-many join GIS 15

Example join U. S. Counties GIS layer Unemployment databases + = GIS 16

Determine Fields to Join GIS 17

Create New Field GIS 18

Columns Ready to Join GIS 19

Join Data • Right-click Counties layer > Joins and Relates > Join GIS 20

Resultant Attribute Table GIS 21

Classifying Unemployment • Values will only appear with number fields GIS 22

Final Map Percentage Unemployment by U. S. Counties, 2005 GIS 23

Problems with Joins • Field types are different (e. g. one is numeric and one is text) GIS 24

Problems with Joins • Data is different Must remove dashes! GIS 25

Problems with Joins • Too much formatting in Excel • Keep it simple! GIS 26

Spatial Joins GIS 27

Spatial Joins • Joins using shape (not attribute field) • Enables data aggregation (counting or summing points by polygon) • Common Spatial Joins -Points to Polygons (counts) -Polygons to Points (adds text) -Points to points (distances) GIS 28

Points to polygons How many convenience stores are in each neighborhood? • Start with: -Store points -Neighborhood polygons GIS 29

Points to polygons • Right-click neighborhoods > Joins and Relates > Join GIS 30

Spatial join result New polygon layer with count of points (number of stores) GIS 31

Spatial join result Show as a choropleth map, with labels, or table Neighborhood Bloomfield Central Business District Strip District Brookline Central Lawrenceville Larimer Middle Hill Central Oakland Squirrel Hill South Oakland Carrick Highland Park Garfield North Oakland Terrace Village Brighton Heights Shadyside Morningside Marshall-Shadeland East Liberty Friendship Sheraden Homewood South Greenfield Store Count 22 12 10 10 8 8 7 7 7 6 6 5 5 4 4 4 4 GIS 32

Polygons to points What neighborhood is a store in? • Start with: -Store points -Neighborhood polygons GIS 33

Polygons to points • Right-click business points> Joins and Relates > Join GIS 34

Spatial join result Point shapefile with neighborhood data on each store GIS 35

Points to points How close is a school to a store? • Start with: -Store points -School points GIS 36

Points to points Right-click school points> Joins and Relates > Join GIS 37

Result Join GIS 38

Geodatabases GIS 39

Geodatabases A geodatabase is a container used to hold a collection of datasets (GIS features, tables, raster images, and other objects). Country layer World. gdb Graticule layer GIS 40

Geodatabase types File geodatabase (. gdb) • stores datasets in a folder of files • each dataset a file up to 1 TB in size • can be used across platforms • can be compressed and encrypted for read-only, secure use • ESRI’s recommended choice GIS 41

Geodatabase types Personal geodatabase (. mdb) • stores datasets in a Microsoft Access format • storage sizes between 250 and 500 MB • limited to 2 GB • only supported on Windows Arc. SDE geodatabase • stores datasets in a number of optional DBMSs: -IBM DB 2, IBM Informix , Microsoft SQL Server , Oracle, or Postgre. SQL • unlimited size and users GIS 42

New file geodatabase Arc. Catalog GIS 43

Import into geodatabase Shapefile features GIS 44

Import into geodatabase Tables GIS 45

Export from geodatabase GIS 46

View geodatabases Cannot identify names in Windows Explorer Must use Arc. Catalog GIS 47

Compact geodatabases File and personal geodatabases • Reduces size and improves performance • Compact personal geodatabases > 250 MB. • Geodatabases with frequent data entry, deletion, or general editing • Open geodatabases in Arc. Map cannot be compacted -remove any layers with a source table or feature class in that database from the TOC GIS 48

Compress geodatabases File geodatabases • Once compressed, a feature class or table is read-only and cannot be edited • Compression is ideally suited to mature datasets that do not require further editing • Compressed dataset can be uncompressed to return it to its original, read-write format GIS 49

Arc. Catalog GIS 50

Arc. Catalog utility program Like Windows Explorer but for Esri-format data • Copy, delete, rename, move, search files, view metadata • Import/export popular spatial data format files • Create new shapefiles, file geodatabases, etc. for input in Arc. Map GIS 51