Shaowen Wang CyberInfrastructure and Geospatial Information Laboratory (CIGI) Department of Geography and

1. Principles of GIS Fundamental database concepts Shaowen Wang CyberInfrastructure and Geospatial Information Laboratory (CIGI) Department of Geography and National Center for Supercomputing Applications (NCSA) University of Illinois at Urbana-Champaign January - February, 2011

2. Database Characteristics • Reliability • Integrity • Security • Concurrency • Data dependence • Distributed access • Interface • Self-describing

3. Database Applications • Business • Engineering • Medicine • Government • Etc.

4. DBMS • User interface and query language • Query compiler and optimizer • Constraint enforcer • Runtime database processor • Stored data manager • System catalog • Metadata

5. Metadata • Data about data • XML (eXtensible Markup Language) • GML • http://www.opengis.net/gml/

6. Database Transaction • Insert • Modify • Delete • Retrieve

7. Transaction Support • Concurrency • Interleaving • Lost update • Recovery control • Atomicity • Independence • DBMS operations • Commit • Rollback

8. Database Models • Relational • Object-oriented • Conceptual model • Designers • Machines • Users

9. Entity-relationship model (E-R) • Entity • Type • Instance • Identifier • Relationship • One-to-one • One-to-many • Many-to-many

10. Structured Query Language (SQL) • Domain creation • Relation scheme creation • Data manipulation • Data retrieval

11. Connecting to DB % sshusername@geo480.cigi.uiuc.edu Enter password Login to DB % psql -U username -d database_name Help: %psql --help

12. Some Postgres Commands List all accessible databases # \l Connect to a DB named 'tutorial' # \c tutorial List all the tables in current DB # \dt, # \d (show all relations)‏ Quit # \q

13. SQL Commands Create DB CREATE DATABASE dbname OWNER rolename; E.g. # create database tutorial;

14. SQL Commands Create Tables # create table test(key int, attrvarchar(20), value float); Delete table # drop table test;

15. SQL Commands Insert a row # insert into test values(1, 'attr0', 100); Update table contents # update test set attr='attr1' where key=1; Delete rows # delete from test where key=1;

16. SQL Commands List contents of table # select * from test; # select * from test where attr='attr1';

17. Documentation Postgres http://www.postgresql.org/docs/8.3/interactive/index.html http://www.postgresql.org/docs/8.3/interactive/sql-commands.html An SQL Tutorial http://www.w3schools.com/sql/default.asp

18. Extended Entity-Relationship Model • Sub-type • Specialization • Super-type • Generalization • Inheritance

19. Object-Orientation • Object • State • Behavior • Class • Attributes • Method

20. O-O Features • Encapsulation • Reduces modeling complexity • Promotes reuse • Inheritance and polymorphism • Combats impedance mismatch • Metaphorical power

21. Relational Databases • Attribute • Tuple • Relation scheme • Relation

22. Relation ID • Candidate key • Primary key

23. Operations on Relations • Project • Restrict

24. Relational Algebra • Derived relational operators • Join • Natural join • Performance

25. Extensible RDBMS • RDBMS problems when handling spatial data • Data structure • Performance • Search

26. Importing data from CSV Data format CSV file First is assumed to be column names Data values are separated by , and non numeric values are quoted.

27. Importing data from CSV Create insert file from csv file /srv/cigi/code/csv2insert.pl --csv-file /srv/cigi/code/test.csv --output-file $HOME/insert.sql --table-name test /srv/cigi/code/csv2insert.pl --help Getting data to DB psql -U username -d database < insertfile

28. Logging in to the machine % sshnetid@geog480.cigi.uiuc.edu Login name: netid Password: your password % psql -U username –d tutorial Login name: geog480 Password: same

29. End of This Class