Define a Table for Mixed Spatial Information

Create a Table with Multiple Spatial Data Types

Write a MySQL query to create a table that includes both POINT and POLYGON spatial columns.

Solution:

-- Create a table named GeoData to store geographic data, including point locations and area boundaries.
CREATE TABLE GeoData (

    -- Define an 'id' column as an integer that auto-increments with each new record and serves as the primary key.
    id INT AUTO_INCREMENT PRIMARY KEY,

    -- Define a 'name' column to store the name or description of the geographic entity (e.g., city, park).
    name VARCHAR(100),

    -- Define a 'location' column of type POINT to store geographic coordinates for point-based data (e.g., landmarks).
    location POINT,

    -- Define an 'area' column of type POLYGON to store geographic boundaries for region-based data (e.g., city limits, park boundaries).
    area POLYGON,

    -- Create a spatial index on the 'location' column to optimize queries involving point-based spatial data.
    SPATIAL INDEX(location),

    -- Create a spatial index on the 'area' column to optimize queries involving polygon-based spatial data.
    SPATIAL INDEX(area)
);

Explanation:

• Purpose of the Query:
• The goal is to set up a table for storing different types of spatial data.
• This demonstrates the creation of multiple spatial columns with appropriate indexing.
• Key Components:
• location POINT and area POLYGON : Define columns for point and polygon data.
• SPATIAL INDEX(...) : Improves query performance on spatial columns.
• Real-World Application:
• Useful for systems that manage both precise locations and area boundaries (e.g., city planning).

Notes:

• Ensure compatibility of spatial data types with your MySQL version.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a table "GeoData" with a POINT column named "location" and a POLYGON column named "boundary", each with a SPATIAL INDEX.
• Write a MySQL query to create a table "SpatialInfo" with a GEOMETRY column and a MULTIPOINT column, and add SPATIAL INDEXES on both columns.
• Write a MySQL query to create a table "MapFeatures" with a LINESTRING column for routes and a POLYGON column for areas, ensuring both columns are spatially indexed.
• Write a MySQL query to create a table "AreaData" with a POINT column for centers and a POLYGON column for boundaries, including SPATIAL INDEXES on both.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Retrieve the Nearest Location.
Next MySQL Exercise: Insert a Polygon Using ST_GeomFromText.