SQL/SDA: A Query Language for Supporting Spatial Data Analysis and Its Web-Based Implementation Written by: Hui Lin Bo Huang IEEE Transaction of Knowledge and Data Engineering (TKDE), July/August, 2001. Presented by Shan Huang (Group 2) Slides Available at http://www.users.cs.umn.edu/~joh/csci8715/HW-list.htmTopics:MotivationMotivation (contd.)Problem StatementMajor ContributionKey ConceptsKey Concepts (contd.)OGIS vs. SQL/SDAFROM ClauseSlide 11Slide 12Slide 13Slide 14Key Concepts (contd.) Implementation of SQL/SDA on the WebValidation MethodologyAssumptionRewriteThanks!SQL/SDA: A Query Language for Supporting Spatial Data Analysis and Its Web-Based Implementation Written by: Hui LinBo HuangIEEE Transaction of Knowledge and Data Engineering (TKDE), July/August, 2001.Presented by Shan Huang (Group 2)Slides Available at http://www.users.cs.umn.edu/~joh/csci8715/HW-list.htmTopics:MotivationProblem StatementMajor ContributionKey ConceptValidation MethodologyAssumption RewriteMotivationLimited GIS-based decision-making Spatial analysis functionsno effective interface to support user requestCurrent spatial query language (Spatially Extended SQLs)Representation of spatial dataManagement of spatial dataComplexity of current GIS-softwareMotivation (contd.)Find a land parcels suitable for growing coffee.Evaluation criteria temperature, altitudes, area >1000, etc.Problem StatementGivenSpatial DataSpatial Data TypeSpatial Data Analysis FunctionFindExtensions to SQLWeb-based Interface for Spatial Data AnalysisConstrainsRelational DatabaseSQL with Geometry TypesObjectiveEasy to useEfficientEase of Expressing Spatial QuestionsMajor ContributionSQL/SDARestructuring the FROM ClauseVisual Interface and Hybrid ModelTakes advantage of the Web GIS Design in Client/Server EnvironmentClient – provides a query interfaceServer – carry out query processing on spatial databasesKey ConceptsSpatial FeatureSpatial attributes (e.g., coordinates and topological relationships)Non-Spatial attributes (e.g., name and size)Spatial Data TypeGeometry: Point, Linestring, Polygon, CollectionCollection: Multipoint, Multilinestring, MultipolygonKey Concepts (contd.)Spatial Analysis FunctionsFunction Purpose Function NameProperty Access properties of spatial featuresAREA, LENGTH, CENTROIDSpatial Relationship Test spatial relationshipsEQUALS, DISJOINT, TOUCH, WITHIN, OVERLAP, CROSS, INTERSECTS, CONTAINS Metric Calculate distance and directionDISTANCE, DIRECTIONDerivation Create a new set of spatial features VORONOI, BUFFER, CONVEXHULL, INTERSECTION, DIFFERENCE, UNION, FUSIONOGIS SQL/SDASpatial datatypesPoint, Curve, Surface, MultiPoin, MultiCurve, MultiSurfacePoint, Curve, Surface, MultiPoin, MultiCurve, MultiSurfaceSpatial operationsBasic topological and metric spatial relationshipsRicher set of operationsCENTROID, DIRECTION, FUSION OGIS vs. SQL/SDAFROM ClauseStructure and Syntax of SQL/SDAAlign with the SQL design conceptSELECT - projectionFROM - Cartesian productWHERE - selection Restructured FROM Clause Employ a subqueryCreate an intermediate relation as a new derived attributesAppend attributes to the Cartesian productKey Concepts (contd.)Example of SQL/SDA queryDisplay the land parcels and their corresponding area on the condition that the landuse type of each parcels brush land and soil type is ‘A’ and area is between 700 hectares to 900 hectares landuse(ID, Type, Location) soil (ID, type, Location) SELECT lu.ID, sl.ID, ILocation, areaval FROM ( SELECT *, OVERLAP ( lu.Location, sl.Location ) AS overlapval, INTERSECTION ( lu.location,sl.location ) AS iLocation, AREA (iLocation) AS areaval FROM landuse as lu, soil AS sl ) WHERE lu.type = ‘Brushland’ AND sl.type = ‘A’ AND overlapval = True AND areaval > 700 AND areaval < 900View.In SQL, this is called inline view.Key Concepts (contd.)CREATE VIEW LandArea AS SELECT lu.ID AS landID, sl.ID AS soidID, OVERLAP ( lu.Location, sl.Location ) AS overlapval, INTERSECTION ( lu.location,sl.location ) AS iLocation, AREA (iLocation) AS areaval FROM landuse as lu, soil AS sl WHERE lu.type = ‘Brushland’ AND sl.type = ‘A’SELECT landID, soidID, iLocation, areaval FROM LandAreaWHERE overlapval = True AND areaval > 700 AND areaval < 900Key Concepts (contd.)Solution Fig.2. The intermediate relation of the FROM clause in queryKey Concepts (contd.)Solution (contd.)Fig. 3. The result of queryKey Concepts (contd.)Implementation of SQL/SDA on the Web Clientprovides query interfaceServer Query processor and Spatial Database Engine (SDE)carry out query processingValidation Methodology+ Examples of SQL/SDA queries+ Case StudyMacro Languages of GISDifficult to learnNo optimization strategyPrevious Spatially Extended SQLs (SESOL)spatial relationship and metric constrains, derivation functions are not accommodatedSpatial functions are applied in the main SELECT or WHERE clauseAssumptionSpatial indexing is already provided by Spatial Data Engine (SDE) Users familiar with SQL and relational databasefamiliar with spatial data types and operationsRewritePerform user studies to evaluate the ease of expressing spatial queriesMaking the query interface more friendlierQuery