Geometric and Geographic Data

• Definitions:

  • Metric Objects: Points, lines, polygons, or spheres specified as coordinates in n-dimensional space.

  • Spatial Data: Generally written in Well-Known Text (WKT) format, which specifies a shape name followed by vertex coordinates.

• Dimensional Data:

  • 2D Data: Used in mapping applications.

  • 3D Data: Used in CAD (Computer-Aided Design) for engineering applications (e.g., airplanes, buildings, circuits).

Coordinate Systems

• Cartesian Coordinate System:

  • Geographic data is embedded in a flat plane or three-dimensional space.

  • Geographic points are described using latitude, longitude, and elevation above sea level.

• Spatial Reference Systems:

  • Standardized systems identified with Spatial Reference System Identifiers (SRID).

  • Can describe complex shapes of the Earth in various systems.

Spatial Types and Geometry

• Basic Spatial Types:

  • POINT: Represents a specific location (e.g., an address).

  • LINESTRING: Composed of one or more line segments.

  • POLYGON: Describes two-dimensional surfaces, such as regions or postal code areas; can include inner polygons (holes).

• Geometry Value: Can support multiple types, including:

  • GEOMETRY: Represents various geometric types.

  • GEOMETRY COLLECTION: Can contain multiple geometry values.

SQL Spatial Data Storage

• Storage Format:

  • 4 bytes for SRID, 4 bytes for spatial type, and varying bytes for each coordinate.

• Operations:

  • In INSERT and UPDATE statements, spatial data is transformed from WKT to internal format.

  • In SELECT statements, data is converted back to WKT format.

Spatial Functions in SQL

• Common Functions:

  • ST_Area(): Returns the area of a polygon or multipolygon.

  • ST_Distance(): Determines the distance between two spatial values.

  • ST_Overlaps(): Checks if two spatial values overlap.

  • ST_Union(): Merges two spatial values into one.

• MBR (Minimum Bounding Rectangle):

  • Smallest rectangle that contains a set of spatial values.

  • Functions for MBR manipulation, such as ST_Envelope() and MBR Contains().

Spatial Indexing with R-trees

• R-tree Index Structures:

  • Utilizes MBRs to store values rather than raw data.

  • Has multiple index levels for efficient data querying.

  • Bottom level contains one entry for each spatial value.

• Quadratic Split Algorithm:

  • Used to group MBRS into higher-level MBRs across multiple levels.

  • A split may occur during insertion if all blocks are full.

R-tree Behavior and Characteristics

• Dense vs. Sparse Index:

  • The bottom level of an R-tree index is dense, containing entries for each indexed value.

• Insertion Behavior:

  • When blocks are full, adding a new value results in a block split, redistributing entries across two blocks.

• Effects of Deletion:

  • Removing values adjusts MBRs, potentially reducing the size of higher-level entries.