Building Intelligent Geo Applications with MySQL Spatial Functions and JSON

1. JSON Data Type and Functions

MySQL has supported the JSON data type since version 5.7, allowing storage of semi‑structured data.

1.1 JSON Data Type Overview

-- Create a table with a JSON column
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON,  -- JSON data type
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert JSON rows
INSERT INTO products (name, attributes) VALUES
('笔记本电脑', '{"brand": "Dell", "specs": {"cpu": "i7", "ram": "16GB"}, "price": 1200, "tags": ["电脑", "电子产品"]}'),
('智能手机', '{"brand": "Apple", "specs": {"storage": "256GB", "color": "黑色"}, "price": 899, "tags": ["手机", "通讯"]}');

1.2 JSON Path Expressions

The $ symbol denotes the document root; . accesses object members; [n] accesses array elements.

-- Extract specific attributes
SELECT
    name,
    attributes->>'$.brand' AS brand,          -- returns text
    attributes->'$.specs.cpu' AS cpu,          -- returns JSON
    JSON_EXTRACT(attributes, '$.price') AS price
FROM products;

-- Query with JSON path
SELECT name
FROM products
WHERE attributes->>'$.brand' = 'Dell'
   OR JSON_EXTRACT(attributes, '$.price') > 1000;

1.3 JSON Functions

-- 1. Create JSON values
SELECT
    JSON_OBJECT('id',1,'name','产品A','active',true) AS json_obj,
    JSON_ARRAY('苹果','香蕉','橙子') AS json_array;

-- 2. Modify JSON values
UPDATE products
SET attributes = JSON_SET(
    attributes,
    '$.discount', 0.1,          -- add new attribute
    '$.price', 1100             -- modify existing attribute
)
WHERE id = 1;

-- 3. Search JSON values
SELECT name, attributes
FROM products
WHERE JSON_CONTAINS(attributes->'$.tags', '"电子产品"');

-- 4. Aggregate JSON values
SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) AS product_list
FROM products
WHERE attributes->>'$.price' > 500;

2. Spatial Data Types

2.1 Spatial Types Overview

MySQL implements the OpenGIS standard and provides several geometry types.

-- Create a table with spatial columns
CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    point_location POINT,               -- single point
    line_path LINESTRING,               -- line or path
    area POLYGON,                       -- polygon area
    geometry_collection GEOMETRYCOLLECTION
);

-- Insert spatial data
INSERT INTO locations (name, point_location) VALUES
('公司总部', ST_PointFromText('POINT(116.404 39.915)')),
('分公司A', ST_GeomFromText('POINT(121.473 31.230)'));

2.2 Spatial Function Operations

-- 1. Create geometry objects
SELECT
    ST_GeomFromText('POINT(10 20)') AS point,
    ST_GeomFromText('LINESTRING(0 0, 10 10, 20 25, 50 60)') AS line,
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))') AS polygon;

-- 2. Geometry attribute functions
SELECT
    name,
    ST_X(point_location) AS longitude,   -- get longitude
    ST_Y(point_location) AS latitude,    -- get latitude
    ST_AsText(point_location) AS wkt    -- WKT representation
FROM locations;

-- 3. Spatial relationship functions
SET @area = ST_GeomFromText('POLYGON((115 38, 125 38, 125 42, 115 42, 115 38))');
SELECT name FROM locations WHERE ST_Within(point_location, @area);

-- 4. Distance calculation (approximate km)
SELECT
    a.name AS location1,
    b.name AS location2,
    ST_Distance(
        ST_SRID(a.point_location, 4326),
        ST_SRID(b.point_location, 4326)
    ) * 111.045 AS distance_km
FROM locations a, locations b
WHERE a.id = 1 AND b.id = 2;

3. Geo‑Query Optimization

3.1 Using Spatial Indexes

-- Create a table with a spatial index
CREATE TABLE spatial_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    location_name VARCHAR(100),
    geo_point POINT NOT NULL,
    SPATIAL INDEX idx_geo_point (geo_point)  -- spatial index
);

-- Generate 100,000 random points
DELIMITER $$
CREATE PROCEDURE generate_spatial_data(IN num_rows INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < num_rows DO
        INSERT INTO spatial_data (location_name, geo_point)
        VALUES (
            CONCAT('Location_', i),
            ST_Point(
                70 + RAND()*50,   -- longitude 70‑120
                15 + RAND()*35    -- latitude 15‑50
            )
        );
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL generate_spatial_data(100000);

3.2 Spatial Query Optimizations

-- 1. Use MBR (minimum bounding rectangle) for fast filtering
SELECT location_name, ST_AsText(geo_point)
FROM spatial_data
WHERE MBRContains(
    ST_GeomFromText('POLYGON((75 20, 115 20, 115 45, 75 45, 75 20))'),
    geo_point
);

-- 2. Combine traditional index for complex queries
ALTER TABLE spatial_data ADD INDEX idx_name (location_name);

-- Composite query: filter by name then compute distance
SELECT location_name,
       ST_Distance_Sphere(
           geo_point,
           ST_GeomFromText('POINT(116.404 39.915)', 4326)
       ) / 1000 AS distance_km
FROM spatial_data
WHERE location_name LIKE 'Location_1%'
HAVING distance_km < 100
ORDER BY distance_km
LIMIT 100;

-- 3. Stored procedure for batch nearby search
DELIMITER $$
CREATE PROCEDURE find_nearby_locations(
    IN center_lon DOUBLE,
    IN center_lat DOUBLE,
    IN radius_km DOUBLE,
    IN max_results INT
)
BEGIN
    -- Compute approximate bounding box (1° ≈ 111 km)
    SET @lon_min = center_lon - (radius_km / 111.0);
    SET @lon_max = center_lon + (radius_km / 111.0);
    SET @lat_min = center_lat - (radius_km / 111.0);
    SET @lat_max = center_lat + (radius_km / 111.0);

    SELECT location_name,
           ST_X(geo_point) AS longitude,
           ST_Y(geo_point) AS latitude,
           ST_Distance_Sphere(
               geo_point,
               ST_SRID(POINT(center_lon, center_lat), 4326)
           ) / 1000 AS distance_km
    FROM spatial_data
    WHERE MBRContains(
        ST_GeomFromText(CONCAT(
            'POLYGON((',
            @lon_min, ' ', @lat_min, ', ',
            @lon_max, ' ', @lat_min, ', ',
            @lon_max, ' ', @lat_max, ', ',
            @lon_min, ' ', @lat_max, ', ',
            @lon_min, ' ', @lat_min, '))'
        ),
        geo_point
    )
    HAVING distance_km <= radius_km
    ORDER BY distance_km
    LIMIT max_results;
END$$
DELIMITER ;

CALL find_nearby_locations(116.404, 39.915, 100, 10);

4. Storing Unstructured Geo Data

-- Table with POINT and JSON columns
CREATE TABLE real_estate (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    location POINT NOT NULL SRID 4326,   -- geographic point
    properties JSON,                     -- dynamic attributes
    SPATIAL INDEX idx_location (location),
    INDEX idx_properties ((CAST(properties->>'$.price' AS UNSIGNED)))
);

-- Sample inserts
INSERT INTO real_estate (title, location, properties) VALUES
('市中心公寓', ST_GeomFromText('POINT(116.407 39.904)', 4326),
 '{"price":5000000,"area":85.5,"rooms":2,"features":["地铁","学区"],"contact":{"phone":"13800138000","agent":"张经理"}}'),
('郊区别墅', ST_GeomFromText('POINT(116.287 40.042)', 4326),
 '{"price":12000000,"area":350,"rooms":5,"features":["花园","车库","游泳池"],"contact":{"phone":"13900139000","agent":"王经理"}}');

-- Complex query combining spatial and JSON criteria
SELECT
    title,
    ST_X(location) AS longitude,
    ST_Y(location) AS latitude,
    properties->>'$.price' AS price,
    properties->>'$.area' AS area,
    JSON_LENGTH(properties->'$.features') AS feature_count
FROM real_estate
WHERE MBRContains(
        ST_GeomFromText('POLYGON((116.3 39.9, 116.5 39.9, 116.5 40.1, 116.3 40.1, 116.3 39.9))', 4326),
        location
    )
  AND CAST(properties->>'$.price' AS UNSIGNED) BETWEEN 3000000 AND 8000000
  AND JSON_CONTAINS(properties->'$.features', '"地铁"')
ORDER BY CAST(properties->>'$.price' AS UNSIGNED);

-- Generate GeoJSON FeatureCollection from the table
SELECT JSON_OBJECT(
    'type', 'FeatureCollection',
    'features', JSON_ARRAYAGG(
        JSON_OBJECT(
            'type', 'Feature',
            'geometry', ST_AsGeoJSON(location),
            'properties', JSON_OBJECT(
                'title', title,
                'price', properties->>'$.price',
                'area', properties->>'$.area'
            )
        )
    )
) AS geojson
FROM real_estate
WHERE ST_Distance_Sphere(
        location,
        ST_GeomFromText('POINT(116.407 39.904)', 4326)
    ) < 20000;  -- within 20 km

MySQL's JSON and spatial data types together provide powerful capabilities for handling unstructured and geographic information. Proper use of indexes, partitioning, and generated columns can dramatically improve performance, and MySQL 8.0+ adds many advanced features.