如何根据邮编或城市名称查找并展示最近的5个地点？

Alright, let's walk through how to build this feature where users can input a zip code or city name and get the 5 closest locations from your database. Based on your table structures, here's a step-by-step solution:

需求回顾 #

• Users input either a zip code or city name
• Retrieve the 5 nearest locations from the database
• Display these locations to the user

现有数据库表详情 #

1. locations Table (~16k records) #

Stores all the locations we want to search through—core fields are locationID, name, zipcode, and city:

CREATE TABLE `locations` ( 
  `locationID` int(11) NOT NULL AUTO_INCREMENT, 
  `name` varchar(150) NOT NULL, 
  `firstname` varchar(100) DEFAULT NULL, 
  `lastname` varchar(100) DEFAULT NULL, 
  `street` varchar(100) NOT NULL, 
  `city` varchar(100) NOT NULL, 
  `state` varchar(100) NOT NULL, 
  `zipcode` varchar(10) NOT NULL, 
  `phone` varchar(20) NOT NULL, 
  `web` varchar(255) DEFAULT NULL, 
  `machine` enum('Unbekannt','Foo','Bar') DEFAULT 'Unbekannt', 
  `surface` enum('Unbekannt','Foo','Bar','') DEFAULT 'Unbekannt', 
  PRIMARY KEY (`locationID`) 
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8

2. geoData Table (~3.4M records, partitioned) #

Stores global geographic coordinates for towns/cities. Note that lat and lon are stored as integers multiplied by 10000 (e.g., 40.7128° becomes 407128):

CREATE TABLE `geoData` ( 
  `geoID` int(11) NOT NULL AUTO_INCREMENT, 
  `countryCode` char(2) NOT NULL, 
  `zipCode` varchar(20) NOT NULL, 
  `name` varchar(180) NOT NULL, 
  `state` varchar(100) NOT NULL, 
  `stateCode` varchar(20) NOT NULL, 
  `county` varchar(100) NOT NULL, 
  `countyCode` varchar(20) NOT NULL, 
  `community` varchar(100) NOT NULL, 
  `communityCode` varchar(20) NOT NULL, 
  `lat` mediumint(6) NOT NULL, 
  `lon` mediumint(6) NOT NULL, 
  PRIMARY KEY (`lon`,`lat`,`geoID`) USING BTREE, 
  KEY `geoID` (`geoID`) 
) ENGINE=InnoDB AUTO_INCREMENT=16482 DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY RANGE (lat) (
  PARTITION p0 VALUES LESS THAN (-880000) ENGINE = InnoDB,
  PARTITION p1 VALUES LESS THAN (-860000) ENGINE = InnoDB,
  -- 省略中间分区定义（为了简洁）
  PARTITION p56 VALUES LESS THAN (240000) ENGINE = InnoDB
) */;

Step-by-Step Implementation #

1. Convert User Input to Coordinates #

First, we need to turn the user's zip code/city into latitude and longitude using the geoData table. We'll prioritize zip code matches first, then fall back to city name matches:

-- Replace '@user_input' with the actual input from the user
SET @user_input = '10001';

-- Grab the target latitude/longitude (convert back to decimal by dividing by 10000)
SELECT 
  lat / 10000.0 AS target_lat, 
  lon / 10000.0 AS target_lon
INTO @target_lat, @target_lon
FROM geoData
WHERE zipCode = @user_input 
   OR name LIKE CONCAT('%', @user_input, '%')
LIMIT 1; -- Pick the first match (add country/state filters if you need to handle duplicate city names)

2. Calculate Distance & Fetch Top 5 Locations #

We'll use the Haversine formula to calculate the spherical distance between the target coordinates and each location (results in kilometers). We join locations with geoData to get each location's coordinates, then sort by distance and take the top 5:

SELECT 
  l.locationID,
  l.name,
  l.city,
  l.zipcode,
  l.street,
  l.phone,
  -- Haversine formula to calculate distance in kilometers
  6371 * 2 * ASIN(
    SQRT(
      POWER(SIN((@target_lat - (g.lat/10000.0)) * PI()/180 / 2), 2) +
      COS(@target_lat * PI()/180) * COS((g.lat/10000.0) * PI()/180) *
      POWER(SIN((@target_lon - (g.lon/10000.0)) * PI()/180 / 2), 2)
    )
  ) AS distance_km
FROM locations l
JOIN geoData g 
  ON l.zipcode = g.zipCode 
  AND l.city = g.name -- Match both zip and city to avoid mismatches (same zip, different city)
WHERE @target_lat IS NOT NULL -- Only run if we found valid coordinates
ORDER BY distance_km ASC
LIMIT 5;

3. Performance Optimizations #

Since geoData is large and locations has 16k records, here are some tweaks to speed things up:

• Add Indexes:
  • For locations: CREATE INDEX idx_loc_zip_city ON locations(zipcode, city); (speeds up the join with geoData)
  • For geoData: CREATE INDEX idx_geo_zip ON geoData(zipCode); and CREATE INDEX idx_geo_name ON geoData(name); (speeds up finding coordinates from user input)
• Filter by Coordinate Range First:
  Instead of calculating distance for every location, narrow down to locations within a 1-degree lat/lon range first (roughly 111km), then compute distances:

  -- Calculate the range around target coordinates (scaled back to integer for geoData)
  SET @min_lat = (@target_lat - 1) * 10000;
  SET @max_lat = (@target_lat + 1) * 10000;
  SET @min_lon = (@target_lon - 1) * 10000;
  SET @max_lon = (@target_lon + 1) * 10000;
  
  -- Fetch only locations within the range, then sort by distance
  SELECT 
    l.locationID,
    l.name,
    l.city,
    l.zipcode,
    l.street,
    l.phone,
    6371 * 2 * ASIN(
      SQRT(
        POWER(SIN((@target_lat - (g.lat/10000.0)) * PI()/180 / 2), 2) +
        COS(@target_lat * PI()/180) * COS((g.lat/10000.0) * PI()/180) *
        POWER(SIN((@target_lon - (g.lon/10000.0)) * PI()/180 / 2), 2)
      )
    ) AS distance_km
  FROM locations l
  JOIN geoData g 
    ON l.zipcode = g.zipCode 
    AND l.city = g.name
    AND g.lat BETWEEN @min_lat AND @max_lat
    AND g.lon BETWEEN @min_lon AND @max_lon
  WHERE @target_lat IS NOT NULL
  ORDER BY distance_km ASC
  LIMIT 5;
  

• Handle No Matches: If @target_lat is NULL (no coordinates found for user input), return a friendly message like "Could not find that location—please check your input."

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