1SQL/SDA: A Query Language for Supporting Spatial Data SQL/SDA: A Query Language for Supporting Spatial Data Analysis and Its WebAnalysis and Its Web--Based ImplementationBased ImplementationWritten by: Written by: HuiHui LinLinBo HuangBo HuangIEEE Transaction of Knowledge and Data Engineering (TKDE), IEEE Transaction of Knowledge and Data Engineering (TKDE), July/August, 2001.July/August, 2001.Presented by Shan Huang (Group 2)Presented by Shan Huang (Group 2)Slides Available atSlides Available athttp://www.users.cs.umn.edu/~joh/csci8715/HWhttp://www.users.cs.umn.edu/~joh/csci8715/HW--list.htmlist.htmTopics:Topics:•• MotivationMotivation•• Problem StatementProblem Statement•• Major ContributionMajor Contribution•• Key ConceptKey Concept•• Validation MethodologyValidation Methodology•• Assumption Assumption2Motivation (contd.)Motivation (contd.)•• Find a land parcels Find a land parcels suitable for growing suitable for growing coffee in Jamaica.coffee in Jamaica.–– Evaluation criteria Evaluation criteria temperature, altitudes, temperature, altitudes, area >1000, etc.area >1000, etc.MotivationMotivation•• If we want to open a If we want to open a new store, which new store, which location should we location should we choose?choose?–– Where are the Where are the customers mostly customers mostly from?from?3Problem StatementProblem Statement•• GivenGiven–– Spatial Data TypeSpatial Data Type–– Spatial Data Analysis FunctionSpatial Data Analysis Function•• FindFind–– Extensions to SQLExtensions to SQL–– WebWeb--based Interface for Spatial Data Analysisbased Interface for Spatial Data Analysis•• ConstraintsConstraints–– Relational DatabaseRelational Database–– SQL with Geometry TypesSQL with Geometry Types•• ObjectiveObjective–– Easy to useEasy to use–– EfficientEfficient–– Ease of Expressing Spatial QuestionsEase of Expressing Spatial QuestionsMajor ContributionMajor Contribution•• SQL/SDASQL/SDA–– Restructuring the FROM ClauseRestructuring the FROM Clause•• Visual Interface and Hybrid ModelVisual Interface and Hybrid Model–– Takes advantage of the Web GIS Design in Takes advantage of the Web GIS Design in Client/Server EnvironmentClient/Server Environment••Client Client –– provides a query interfaceprovides a query interface••Server Server –– carry out query processing on spatial carry out query processing on spatial databasesdatabases4Key ConceptsKey Concepts•• Spatial FeatureSpatial Feature–– Spatial attributes (e.g., coordinates and Spatial attributes (e.g., coordinates and topological relationships)topological relationships)–– NonNon--Spatial attributes (e.g., name and size)Spatial attributes (e.g., name and size)•• Spatial Data TypeSpatial Data Type–– Geometry: Point, Geometry: Point, LinestringLinestring, Polygon, , Polygon, CollectionCollection••Collection: Multipoint, Collection: Multipoint, MultilinestringMultilinestring, , MultipolygonMultipolygonKey Concepts (contd.)Key Concepts (contd.)•• Spatial Analysis FunctionsSpatial Analysis FunctionsCreate a new set of Create a new set of spatial features spatial features Calculate distance and Calculate distance and directiondirectionTest spatial Test spatial relationshipsrelationshipsAccess properties of Access properties of spatial featuresspatial featuresPurposePurposeVORONOI, BUFFER, CONVEXHULL, VORONOI, BUFFER, CONVEXHULL, INTERSECTION, DIFFERENCE, INTERSECTION, DIFFERENCE, UNION, FUSION UNION, FUSION Derivation Derivation DISTANCE, DIRECTIONDISTANCE, DIRECTIONMetric Metric EQUALS, DISJOINT, TOUCH, WITHIN, EQUALS, DISJOINT, TOUCH, WITHIN, OVERLAP, CROSS, INTERSECTS, OVERLAP, CROSS, INTERSECTS, CONTAINS CONTAINS Spatial Spatial Relationship Relationship AREA, LENGTH, CENTROIDAREA, LENGTH, CENTROIDProperty Property Function NameFunction NameFunctionFunction5Richer set of operationsRicher set of operationsCENTROID, DIRECTION, CENTROID, DIRECTION, VORONOI, FUSION VORONOI, FUSION Basic topological and Basic topological and metric spatial metric spatial relationshipsrelationshipsSpatial operationsPoint, Curve, Surface, MultiPoin, MultiCurve, MultiSurfacePoint, Curve, Surface, MultiPoin, MultiCurve, MultiSurfaceSpatial datatypesSQL/SDAOGISOGIS vs. SQL/SDAOGIS vs. SQL/SDAFROM ClauseFROM Clause•• Structure and Syntax of SQL/SDAStructure and Syntax of SQL/SDA–– Align with the SQL design conceptAlign with the SQL design concept•• SELECT SELECT -- projectionprojection•• FROM FROM -- Cartesian productCartesian product•• WHERE WHERE -- selection selection •• Restructured FROM Clause Restructured FROM Clause –– Employ a Employ a subquerysubquery–– Create an intermediate relation as a new derived Create an intermediate relation as a new derived attributesattributes–– Append attributes to the Cartesian product Append attributes to the Cartesian product6Key Concepts (contd.)Key Concepts (contd.)Example of SQL/SDA queryExample of SQL/SDA query•• Display the land parcels and their corresponding area on the conDisplay the land parcels and their corresponding area on the condition that dition that the the landuselandusetype of each parcels brush land and soil type is ‘A’ and area itype of each parcels brush land and soil type is ‘A’ and area is s between 700 hectares to 900 hectaresbetween 700 hectares to 900 hectareslanduse(IDlanduse(ID, Type, Location), Type, Location)soil (ID, type, Location)soil (ID, type, Location)SELECTSELECT lu.IDlu.ID, , sl.IDsl.ID, , ILocationILocation, , areavalareavalFROMFROM ( ( SELECTSELECT *, *, OVERLAP OVERLAP ( ( lu.Locationlu.Location, , sl.Locationsl.Location ) ) ASASoverlapvaloverlapval, , INTERSECTION INTERSECTION ( ( lu.location,sl.locationlu.location,sl.location ) ) ASASiLocationiLocation,,AREAAREA ((iLocationiLocation) ) ASASareavalareavalFROM FROM landuselanduse as as lulu, soil , soil ASASslsl))WHEREWHERE lu.typelu.type = ‘= ‘BrushlandBrushland’ ’ ANDAND sl.typesl.type = ‘A’ = ‘A’ ANDAND overlapvaloverlapval = True = True ANDAND areavalareaval > 700> 700ANDAND areavalareaval < 900< 900vv View.View.vv In SQL, this is called In SQL, this is called inline viewinline view..Key Concepts (contd.)Key Concepts (contd.)•• SolutionSolutionFig.2. The Fig.2. The intermediate intermediate relation of the relation of the FROM clause in FROM clause in queryquery7Key Concepts (contd.)Key