MySQL 5.7中POINT与GEOMETRYCOLLECTION的距离计算问题

Got it, let's work through this MySQL 5.7 problem step by step. You've got two core issues here: handling GEOMETRYCOLLECTION with distance functions, and figuring out what unit ST_DISTANCE returns. Let's break this down.

First: What's the unit of ST_DISTANCE? #

When working with WGS84 (SRID 4326, the standard GPS coordinate system), ST_DISTANCE returns the difference in degrees between two geometries. This isn't directly useful for real-world distance calculations because 1 degree of longitude varies in actual distance depending on latitude (it's ~111km at the equator but shrinks to 0 at the poles). So we need a way to calculate actual spherical distance, which brings us to the ST_DISTANCE_SPHERE limitation.

Fixing the GEOMETRYCOLLECTION compatibility issue #

Since ST_DISTANCE_SPHERE only supports POINT and MULTIPOINT, we need to extract the closest point from your GEOMETRYCOLLECTION to the target POINT first. MySQL 5.7 has ST_ClosestPoint(g1, g2) which does exactly that—it returns the point on g1 that's nearest to g2, regardless of whether g1 is a POINT, POLYGON, or mixed collection.

Applying this to your example data #

Here's how to test this with your sample geometries:

-- Your original geometry collection and target point (simplified @p1 to a straight POINT since it only contains one)
SET @g1 = ST_GeomFromText('GEOMETRYCOLLECTION(POINT (-156.366489591715 66.913750389327),POLYGON ((-156.357608905242 66.906958164897, -156.360302383363 66.9066027336476, -156.361997104194 66.9067073607308, -156.363616093774 66.9066368440642, -156.365477697938 66.9065867326059, -156.368127298976 66.9065970034393, -156.370061891681 66.9066888794808, -156.37182258022 66.9068547305222, -156.373286981259 66.9070724523969, -156.374390675008 66.9072952721882, -156.376359777088 66.9077681138541, -156.377706173961 66.9080113180204, -156.379222192708 66.9081328753119, -156.380729601039 66.9081591586452, -156.382562289578 66.9081211961453, -156.387571662487 66.9099676951007, -156.389320598943 66.9125180930134, -156.389291120818 66.9145787836353, -156.384722634367 66.9167899596735, -156.37955035 66.9195246586276, -156.372520662511 66.9209119638337, -156.360432280238 66.9215118034161, -156.355776993787 66.9203754471679, -156.34906598338 66.9180659711298, -156.347941981299 66.9174007836309, -156.346853913592 66.9167568252985, -156.34605399901 66.9158971169665, -156.346982815675 66.9151925950926, -156.346794497967 66.9144321773854, -156.345642955261 66.9140107294695, -156.343831364638 66.9136152003034, -156.342996512556 66.9130307378043, -156.343113243806 66.9123137492637, -156.343498096931 66.9119029992644, -156.344661664637 66.9111819440571, -156.345080786511 66.9105884961414, -156.345524286511 66.9099605023924, -156.347168040675 66.9098486503092, -156.348952756297 66.9096090419763, -156.348689200048 66.9089614565606, -156.349495732338 66.908706844061, -156.350786711503 66.9082992794783, -156.352211271917 66.9083472388533, -156.353952768789 66.90829894302, -156.355389368787 66.9082072242701, -156.356512531285 66.9079768284371, -156.356677961493 66.9078075857291, -156.356422527119 66.907644261771, -156.355901372953 66.9072802273965, -156.357608905242 66.906958164897)))');
SET @p1 = ST_GeomFromText('POINT (-156.342840017 66.9320439348)');

-- Get the closest point on the collection to your target point
SET @closest_point = ST_ClosestPoint(@g1, @p1);

-- Calculate spherical distance in meters
SELECT ST_DISTANCE_SPHERE(@closest_point, @p1) AS distance_in_meters;

Querying your table for points within 5km #

Assuming your table is named point_data with an id column and a geom column of type POINT, use this query to filter points within 5km of your collection:

SET @target_collection = ST_GeomFromText('GEOMETRYCOLLECTION(...)'); -- Replace with your full collection text

SELECT id, geom
FROM point_data
WHERE ST_DISTANCE_SPHERE(
    ST_ClosestPoint(@target_collection, geom),
    geom
) <= 5000; -- 5000 meters = 5 kilometers

Key notes for MySQL 5.7 #

• Ensure your geometries use the WGS84 coordinate system (SRID 4326). If they don't, use ST_SetSRID() to set it: ST_SetSRID(@target_collection, 4326).
• ST_ClosestPoint works with all geometry types, so it handles mixed POINT/POLYGON collections seamlessly.
• ST_DISTANCE_SPHERE returns distance in meters, which aligns perfectly with your 5km threshold (just use 5000 as the cutoff).

内容的提问来源于stack exchange，提问作者Maddys