3561F09_l13 - Managing Tables in GIS Managing Tables in GIS...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Managing Tables in GIS Managing Tables in GIS Looking at fields: types of data Looking at fields: types of data • In a database, each field can contain only one type of data • Common types: – Text or String (letters, or letters mixed with numbers, or numbers that are used as labels, not to indicate quantity) – Integer (whole number) State Alabama Alaska Arizona Population Sq. Mi. Census FIPS code 01 02 04 4,534,842 51,715.794 626,932 576,594.10 … … Fields in GIS attribute tables Fields in GIS attribute tables • Common types, cont: – Float: numbers with decimal places – Date – Binary Large Object (BLOB): images, multimedia, the geometry of spatial features – as long sequences of binary (i.e. unreadable except to the computer) numbers State Alabama Alaska Arizona Population 4,534,842 626,932 … Sq. Mi. 51,715.794 576,594.10 … Statehood Year 1819 1959 … Governor Riley Parnell … Fields & types of data Fields & types of data • Careful: Some string data may look like integer data … Soil­ID Zip code 55414 96822 05156 Place Minneapolis, MN Honolulu, HI Springfield, VT 1001 1002 1003 1004 Suitability 1 1 3 2 Zip codes – a label 1 – High suitability 2 – Moderate suitability 3 – Low suitability Ranking – numbers, but don’t want to add or average them! Data type & level of measurement Data type & Text or String Integer Float Date Binary Large Object (BLOB) Levels of measurement Nominal Ordinal Interval Ratio values differ in amount of thing can tell differences and ratios between values values differ in kind of thing values differ in order/ranking of thing values differ in amount of thing can tell differences but not ratios between values • Level of measurement: classification of data types that governs how they can be analyzed Levels of measurement Levels of measurement • doesn’t make sense to NOMINAL: religions (“catholic” “protestant” “muslim”) state names (“Alabama”, “Alaska”…) ORDINAL: suitability (“1” “2” “3”), effectiveness (“great” “good” “poor”) INTERVAL: temperature (“55.6 F”, “111.2 F”), dates RATIO: anything where zero means “nothing” (no arbitrary zero – rainfall, income, most percentages) average dates – March 14 + May 9 + August 11 ≠ Maugust 11.3 • can you take a density of names? – COKE + PEPSI + FRESCA = 0.66 P’s per word? • is third place three times better than first place? keep levels of measurement in mind when doing GIS analysis spatial and tabular data interaction spatial and tabular data interaction • GIS is unique in that it provides easy interaction between tables and map­ based (spatial) information • can alter a table without altering the map (and vice versa) GIS attribute tables: GIS attribute tables: two primary types spatial • associated with files that contain spatial information for mapping – topology, geometry, arcs, nodes, coordinates... aspatial “without space” • each record is a geographical entity (point, line, polygon...) • in ArcGIS, called a feature class table • not associated with information about mapping • each record can be a geographical entity but not necessarily • usually developed outside of GIS (in Excel, Access, Oracle, etc.) feature classes in ArcGIS feature classes in ArcGIS • feature classes – collection of common spatial features with common set of attributes Feature class tables contain spatial and aspatial information spatial tables spatial tables • feature tables – viewable with Open Attribute Table in ArcGIS this will open the spatial (feature) attribute table in ArcGIS • spatial information is noted in several columns – much of it is hidden in other files The SHAPE column is a BLOB! aspatial tables aspatial tables • in this case, each record is a parcel (geographic) – but nothing in the table can be used to • draw a map • perform spatial analysis • records (and tables) can be entirely aspatial In class exercise (on a colored card) In class exercise 1. Is this a spatial or aspatial table? How can you tell? 2. What are the data type (GIS) and the level of measurement for each field in the table below? How can you tell that this is not a number? Linking two tables together Linking two tables together spatial Very frequently the first step in creating a custom thematic map: Connect the theme (variable) to the spatial (feature) table aspatial Major feature of GIS Joins: linking tables together Joins: linking tables together attribute joins spatial joins • linking an aspatial table to a • linking two spatial tables together spatial table • Each parcel has an owner • We have data about each owner (SSN, home address, number of properties owned, etc.) • We connect the owner aspatial data table to the parcel feature table • Each parcel is in a neighborhood • We have data about each neighborhood (spatial and aspatial) • We connect the spatial table from the neighborhood to the spatial table of the parcels Attribute joins Attribute joins • linking two tables together through a common field – common field is a key: must have identical values in both tables – tables can be created and edited separately – links among tables can be temporary or permanent: flexible In this sense, GIS is a relational database management system: links among tables achieved through keys Relational database system Relational database system • Keys must: – uniquely identify each record if it is different • SSN works as a key for identifying individuals better than name (which might be duplicated) Parcel Area Number (acres) 1006 0.2 1007 0.3 1008 0.6 1009 0.4 1010 0.3 1011 5.0 Parcel Number 1006 1007 1008 1009 1010 1011 spatial Owner Jeff Jones Susan Smith Paul Plane Harry Hectare Susan Smith City of Blaine – be identical, in type and ID, in the two tables, • column names don’t have to match • sorting, counts don’t have to be the same Zoning R-1 R-2 C-1 C-1 C-1 P-3 aspatial Assessed Value 100,000 124,000 2,000,000 800,000 400,000 10,000,000 Relational Database System Relational Database System • a joined table Parcel Number 1006 1007 1008 1009 1010 1011 Area (acres) 0.2 0.3 0.6 0.4 0.3 5.0 Owner Jeff Jones Susan Smith Paul Plane Harry Hectare Susan Smith City of Blaine Zoning R-1 R-2 C-1 C-1 C-1 P-3 Assessed Value 100,000 124,000 2,000,000 800,000 400,000 10,000,000 In GIS, the aspatial table is joined to the spatial table The joiner (aspatial): the source table The joinee (spatial): the destination table The destination table is the one that is added onto Polygon identifiers in the US Polygon identifiers in the US • FIPS codes – numerical codings of most polygon units in the US – states, counties/parishes, zip codes, block groups, census tracts First three numbers: state Second three numbers: county In­class exercise In­class exercise 1. What is the (GIS) data type and the level of measurement for each field in the table below? 2. In a table of all 3000+ US counties, why would COUNTY be a dangerous choice fo COUNTY be a dangerous choice fo a key in a relational database? Cardinality relationships of RDBMS Cardinality relationships of RDBMS • links can be done in several ways: These tables have a – – – – one­to­one many­to­one one­to­many many­to­many one­to­one cardinality relationship Parcel Area Number (acres) 1006 0.2 1007 0.3 1008 0.6 1009 0.4 1010 0.3 1011 5.0 Parcel Number 1006 1007 1008 1009 1010 1011 Owner Jeff Jones Susan Smith Paul Plane Harry Hectare Susan Smith City of Blaine Assessed Value 100,000 124,000 2,000,000 800,000 400,000 10,000,000 • cardinality relationships – how the number of instances of a key in one table compares to that of the other One record in the source table for each record in the destination table Zoning R-1 R-2 C-1 C-1 C-1 P-3 One­to­one join One­to­one join Cardinality relationships of RDBMS Cardinality relationships of RDBMS • many­to­one: destination table (e.g., spatial table) has multiple City Mesa Phoenix Tucson Los Angeles Sacramento San Francisco State Arizona Arizona Arizona California California California instances of each value of the key; source has one instance of each value State Alabama Alaska Arizona Arkansas California Colorado Time Zone Central Pacific Mountain Central Pacific Mountain City Mesa Phoenix Tucson Los Angeles Sacramento San Francisco State Arizona Arizona Arizona California California California Time Zone Mountain Mountain Mountain Pacific Pacific Pacific destination source Often, not all records of the source table are used in a join joined Many­to­one join Many­to­one join Joins and relates Joins and relates • joins are for one­to­one and many­to­one relationships: (very common) • relates are for one­to­many and many­to­many relationships – these relationships can’t lead to a single table (like a join) – relates keep tables separate – keys must still be specified ...
View Full Document

Ask a homework question - tutors are online