ProjectGeo451 1 Geo451 | Spatial Databases | FS14 M. Tomko, R. Meile, Uni Zürich Project: Glacier Observatory Information System (GLOBI) Appendix A: Instructions 1. Introduction In Project Geo451 "Findelen Glacier Observatory Information System (GLOBI)" you will practically apply the knowledge and skills acquired during the lectures and exercises to the modeling of spatial databases. You will take the role of an office engineer for a contractor (yourself & Partner). You will thenmodelandimplementaspatialdatabasebasedonasimplifiedbutrealisticexamplefromPhysical Geography. The project covers the entire sequence from requirements analysis, through the ER‐ modeling and the derivation of the DB schema up to implementation in an Oracle spatial database. Whileprepareddatasimplifiesyourwork,youwillalsogenerateexemplarydataandwillinsertthem into the database. Finally, you will test whether your database can correctly answer the specified queries. Learning Objectives  You will deepen your knowledge of the processes and practical skills for planning and implementationofaspatialdatabasebyexecutingallthenecessarysteps.  YouwillstrengthenandexpandyourknowledgeofSQL.  Youwilldeepenyourknowledgeofdatabasetransactions.  Youwillcollectspatialdataandtherebydevelopknowledgeofbasicspatialdatastructures.  Youwilldeepenyourtheoreticalknowledgeofdatabaseconceptssuchaskeysbylinkingtablesand byinsertingdatatothedatabaseinthecorrectorder.  You will understand the meaning and operation of spatial triggers and will learn to program triggersyourself.  You will learn that careful modeling of (spatial) database structures reduces the subsequent implementationeffort. 2. Tasks Please study Section 4: Tips and Tricks before you begin. Also check regularly the FAQ page of the course(http://www.geo.unizh.ch/microsite/geo451/faq.html). 1. Requirements Analysis: Project description GLOBI (Appendix B) presents the results of a requirementsanalysisforaFindelenGLOBIproject.Studythisrequirementsanalysisandidentify entities,relationshipsandconstraintsforaFindelenGLOBIproject.AppendixBspecifiesexplicitly whichentitiesmustbemodeledandwhataretherelationshipsbetweenthem.Pleasepayspecial attentiontokeyrelationships,integrityconstraintsandcardinalities. 2. ConceptualDesign:CreatethefirststructureofanERmodelforaFindelenGLOBIbasedonthe requirementsanalysisfromAppendixB.ThesimplestwaytodothisisusingMSPowerPointwith shapesand connectors. TheER GLOBI representsthe firstmilestonein your projectand isduein week 6 (22.10.2014). You will present your ER at the feedback meeting that will take place on Friday of the same week (24.10.2014). The following information must be specified on your ER report:yourname,groupnumber,adateandversionoftheER(e.g.version1,theERisgoingto change!). 3. Logical design: Create the logical DB design for GLOBI by translating the ER from Task 2 into a relationalmodel. 4. Physical Design (Implementation): Implement the designed database in the Oracle spatial databasemanagementsystem.Createthetablesaccordingtotherelationsinthelogicaldesign. 5. DataLoading:Loadandadjustspatialdataprovidedbytheexerciseteamintoyourprojectschema inthedatabase. a. Glacieroutlines(USRDEMO.GLETS_xxx) b. Referencepoints(USRDEMO.FIXPO) ProjectGeo451 2 6. 7. 8. 9. Geo451 | Spatial Databases | FS14 M. Tomko, R. Meile, Uni Zürich DataCreation:Createanddigitizethefollowingdatabasedonpersonalsourcesoroninformation availableunderK:\geo451\dataoronrasterdatasetsavailableinthedatabase: a. Morainelines‐>USRDEMO.MORVORFELDormap_paper_sommer_1988.pdf b. Periglaciallakes2000‐>Findelenpdf‐documentontheETHZ‐VAWwebpage c. Hikingtrail"Seenweg"(Zermatt[Blauherd]—Zermatt[Riffelalp])‐>anysource d. Skislope"Fluhalp"(No.19,SkiresortRothornparadise)‐>anysource e. Otherinformation‐>RasterdatainDatabase:USRDEMO.PK25_2009, USRDEMO.SWISSIMAGE,USRDEMO.MORVORFELD DatabaseInformationRetrieval:Testyourdatabasebyexecutingspecifiedqueriesidentifiedin therequirementsanalysis.Ifyouhavemodeledeverythingcorrectly,yourdatabaseshouldnowbe abletoanswerthequeries,althoughpossiblybyusingcomplexSQLstatements. ReportingProgress:Inweek9(Friday16.11.2014)youwillagainreporttothecourseteachers abouttheprogressofyourprojectandreceivefeedbackforthefinalpartofthecourse. ProjectReport:Allstepsshouldbedocumentedinaprojectreport.Whereverpossibleuseformal specificationsusedinthelecture(ER,relationalmodel,SQL‐listings).Theprojectreportservesas your own documentation as well as the source for the evaluation by the course teachers. Find a goodbalancebetweenworklog,SQL‐listingsandinterpretationanddiscussionofyourworksteps. Weareinterestednotonlyinwhatyouhavedone,butalsowhyyouhavedonesomething. 3. Data  Youwillhavethefollowingspatialdataatyourdisposal: o Perimeterboundariesoftheglacier o Reference points (excluding the ones that serve as starting points of a measurement series)  Thestatesofmorainelines(Sommer,1988)isavailableasbackgroundmap(Rasterdata)orasa .pdffile;  Acquirethestateoftheperiglaciallake(statefrom2000)fromETHZ‐VAW(seelinkinSection4). Itissufficienttoestimatetheapproximateboundariesofthelakesbyreferencinglandmarksfrom Figure3intheFindelenpdf‐documentontheETHZ‐VAWwebpage;  For quick point coordinate identification, use the Swisstopo map in the portal http://map.geo.admin.ch/(rightclickforcoordinatesearch);  TheWebcampicturesoftheUnterrothornarelocatedatK:\geo451\data.Youcanuseanyofthe imagesasexampleimagesofthecontrolpointsandofthemeasuringcampaign;  You will have to capture the strongly generalized trajectory of the ski slopes and hiking trails yourselves. The information about hiking trails and ski slopes around Zermatt are available at www.zermatt.chandwww.wanderland.ch;  Inventthreesurveycampaigns.Eachcampaignhasatleast4measurementserieswhichinclude6 measurementsincludingareferencepoint.Thispointmarksthefirstmeasurementofaseriesdone with a pole. You invent the movement of the measurement poles as well as the data for accumulation/meltingetc.;  Youcanmakeupanyadditionalnon‐specificdata(suchasstaff,time,etc.). 4. Tips and Tricks  The data you have compiled yourself do not need to be the most accurate and a reasonably approximation of the geographic features is sufficient. The importance lies in the capture of the correct topology. In other words, you may even invent the data yourself, as long as they are plausible. The URL http://map.bafu.admin.ch/ links to a Web GIS portal where you can inspect 1:25000mapandlotsofotherspatialdata,aswellascapturetheircoordinates.  Information about the Findelen glacier are available from the Versuchsanstalt für Wasserbau, HydrologieundGlaziologieoftheETHZ‐VAW(http://glaciology.ethz.ch/inventar/index.html)  Anothersourceofinformationaboutthewalkingandskitrailsishttp://www.openstreetmap.org/. SpatialdatacanbeexportedfromthissourceasXML. 5. Timetable Wed22.10.2014 Fri24.10.2014 Fri14.11.2014 Fri12.12.201415.00h HandoverofthefirstER Initialprojectfeedbackdiscussion Presentationofthecurrentstateoftheprojectandfeedback Handoverofthefinalprojectreport