MySQL查询：判断用户坐标是否在自定义绘制的地图区域内

Your current query uses a simple bounding box check, which can't accurately determine if a point is inside an irregular polygon. MySQL has built-in spatial functions (available in 5.6+) that solve this exact problem. Here's a step-by-step solution:

1. Understand the Core Issue #

The query select * from places where lat > '$lat' AND long < '$long' only filters points within a rectangular range. It can't account for the irregular shape of your custom drawn regions (like your orange area), so it will either include points outside the polygon or miss points inside it.

2. Solution: Use MySQL Spatial Functions #

We'll use ST_Contains() to check if a point lies inside a polygon, and first aggregate your places table points into valid polygon geometry.

Final Query #

Here's the query that will return the region(s) containing your user's coordinate (53.746858,-1.46513):

SELECT p.name, pl.placeId
FROM place p
JOIN (
    SELECT 
        id AS placeId,
        -- Convert grouped lat/long points into a Polygon
        ST_GeomFromText(
            CONCAT(
                'POLYGON((',
                -- Concatenate all points into WKT format, and close the polygon
                CONCAT(
                    GROUP_CONCAT(CONCAT(`long`, ' ', `lat`) SEPARATOR ','),
                    ',',
                    -- Append the first point to close the polygon
                    (SELECT CONCAT(`long`, ' ', `lat`) FROM places WHERE id = places.id LIMIT 1)
                ),
                '))'
            )
        ) AS polygon
    FROM places
    GROUP BY id
) pl ON p.id = pl.placeId
-- Check if the user's point is inside the polygon
WHERE ST_Contains(
    pl.polygon,
    ST_GeomFromText('POINT(-1.46513 53.746858)') -- Note: WKT uses "POINT(longitude latitude)"
);

Breakdown of the Query #

• Subquery to Create Polygons:

  • GROUP_CONCAT(CONCAT(long, ' ', lat) SEPARATOR ',') combines all points for a single region into a string formatted as lon1 lat1,lon2 lat2,...
  • We append the first point to the end of this string to ensure the polygon is closed (required for valid WKT Polygon format)
  • ST_GeomFromText() converts this string into a MySQL spatial Polygon object

• Point-in-Polygon Check:

  • ST_GeomFromText('POINT(-1.46513 53.746858)') converts the user's coordinate into a spatial Point object (note: WKT requires longitude first, then latitude)
  • ST_Contains(pl.polygon, user_point) returns TRUE if the point lies inside the polygon

3. Important Notes #

• MySQL Version: Ensure you're using MySQL 5.6 or newer (spatial functions are not available in older versions)
• Keyword Escaping: Your places table uses long as a column name, which is a MySQL reserved keyword. Always wrap it in backticks `long` to avoid syntax errors.
• Polygon Order: Make sure the points in your places table are ordered consistently (either clockwise or counter-clockwise) to avoid the polygon being interpreted as an "inverted" area.
• Performance: If you have a large number of regions, consider adding a spatial index to optimize the query. You could modify your places table to store pre-computed Polygon objects instead of generating them dynamically.

内容的提问来源于stack exchange，提问作者Kodr.F