Define a Table to Store Geographic Location Data

Create a Table with a Spatial Column

Write a MySQL query to create a table that includes a spatial column for storing geographic location data.

Solution:

-- Create the Locations table to store information about various locations.
CREATE TABLE Locations (

    -- 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 of the location as a string with a maximum length of 100 characters.
    name VARCHAR(100),

    -- Define a 'location' column of type POINT to store geographic coordinates (latitude and longitude).
    location POINT NOT NULL,

    -- Create a spatial index on the 'location' column to optimize spatial queries, such as distance calculations.
    SPATIAL INDEX(location)
);

Explanation:

• Purpose of the Query:
• The goal is to create a table designed to store spatial data.
• This demonstrates how to define a spatial column and add a spatial index for geographic queries.
• Key Components:
• CREATE TABLE Locations : Specifies the table to create.
• location POINT NOT NULL : Declares a spatial column to store point data.
• SPATIAL INDEX(location) : Improves query performance on spatial data.
• Real-World Application:
• Useful for applications that track locations (e.g., mapping services, logistics).

Notes:

• Ensure your MySQL version supports spatial data types (MySQL 5.7+).

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a table "GeoLocations" with columns id, name, and a non-null POINT column "coordinates" with a SPATIAL INDEX.
• Write a MySQL query to create a table "Areas" with columns area_id, description, and a POLYGON column "boundary", then add a SPATIAL INDEX on "boundary".
• Write a MySQL query to create a table "Routes" with columns route_id, route_name, and a LINESTRING column "path", including a SPATIAL INDEX on "path".
• Write a MySQL query to create a table "CityBoundaries" with columns city_id, city_name, and a MULTIPOLYGON column "region", along with a SPATIAL INDEX on "region".

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

Previous MySQL Exercise: MySQL Spatial Data Home.
Next MySQL Exercise: Insert a Spatial Data Point Using ST_GeomFromText.