Build an Efficient ‘Nearby Users’ Feature Using MySQL, Redis, MongoDB & GeoHash

Introduction

Yesterday a follower asked an interview question: implement a “nearby people” feature. The article discusses several solutions to help candidates answer such questions.

Principle of “Nearby People” (LBS)

The core idea is to treat the current user as the center, calculate distances to other users based on their latitude and longitude, and sort by distance.

Search users around “me”.

Calculate distance between “me” and each user.

Sort by distance and select the closest.

GeoHash Algorithm

GeoHash converts 2‑dimensional latitude/longitude into a one‑dimensional string, making proximity comparison easier. Longer strings give higher precision; similar prefixes indicate closer locations.

Example: the geohash “WX4ER” represents a rectangular area; all points inside share the same prefix.

Geohash precision table (length → width/height):

1  5009.4km  4992.6km
2  1252.3km  624.1km
3   156.5km   156km
4    39.1km   19.5km
5     4.9km    4.9km
6     1.2km   609.4m
7   152.9m  152.4m
8    38.2m    19m
9     4.8m     4.8m
10    1.2m   59.5cm
11   14.9cm  14.9cm
12    3.7cm   1.9cm

Implementation with MySQL

Design

Store user id, name, longitude, latitude in a table. To find users within a radius, first compute a bounding square using min/max longitude/latitude, query users inside the square, then filter by actual distance.

SQL Table

<code>CREATE TABLE `nearby_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;</code>

Java Example

<code>private SpatialContext spatialContext = SpatialContext.GEO;

@GetMapping("/nearby")
public String nearBySearch(@RequestParam("distance") double distance,
                           @RequestParam("userLng") double userLng,
                           @RequestParam("userLat") double userLat) {
    Rectangle rectangle = getRectangle(distance, userLng, userLat);
    List<User> users = userMapper.selectUser(rectangle.getMinX(), rectangle.getMaxX(),
                                            rectangle.getMinY(), rectangle.getMaxY());
    users = users.stream()
                 .filter(u -> getDistance(u.getLongitude(), u.getLatitude(),
                                         userLng, userLat) <= distance)
                 .collect(Collectors.toList());
    return JSON.toJSONString(users);
}
</code>

MySQL + GeoHash

Design

When inserting a user, compute a geohash string (precision chosen according to the required accuracy) and store it in a column with an index.

Table

<code>CREATE TABLE `nearby_user_geohash` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `geo_code` varchar(64) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `index_geo_hash` (`geo_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;</code>

Java Example

<code>@PostMapping("/addUser")
public boolean add(@RequestBody UserGeohash user) {
    String geoHashCode = GeohashUtils.encodeLatLon(user.getLatitude(), user.getLongitude());
    return userGeohashService.save(user.setGeoCode(geoHashCode).setCreateTime(LocalDateTime.now()));
}

@GetMapping("/nearby")
public String nearBySearch(@RequestParam("distance") double distance,
                           @RequestParam("len") int len,
                           @RequestParam("userLng") double userLng,
                           @RequestParam("userLat") double userLat) {
    GeoHash geoHash = GeoHash.withCharacterPrecision(userLat, userLng, len);
    GeoHash[] adjacent = geoHash.getAdjacent();
    QueryWrapper<UserGeohash> qw = new QueryWrapper<>();
    qw.likeRight("geo_code", geoHash.toBase32());
    for (GeoHash a : adjacent) {
        qw.or().likeRight("geo_code", a.toBase32());
    }
    List<UserGeohash> users = userGeohashService.list(qw);
    users = users.stream()
                 .filter(u -> getDistance(u.getLongitude(), u.getLatitude(),
                                         userLng, userLat) <= distance)
                 .collect(Collectors.toList());
    return JSON.toJSONString(users);
}
</code>

Redis + GeoHash

Design

Redis 3.2+ provides GEO commands based on a sorted‑set (zset). Store each user with GEOADD, then use GEORADIUS to retrieve members within a radius.

Key GEO Commands

GEOADD – add a member with longitude and latitude.

GEOPOS – get coordinates of members.

GEODIST – get distance between two members.

GEOHASH – get geohash representation.

GEORADIUS – find members within a radius.

GEORADIUSBYMEMBER – same as GEORADIUS but using an existing member as center.

Java Example

<code>@Autowired
private RedisTemplate<String, Object> redisTemplate;
private static final String KEY = "user_info";

public boolean save(User user) {
    Long flag = redisTemplate.opsForGeo().add(KEY,
        new RedisGeoCommands.GeoLocation<>(user.getName(),
            new Point(user.getLongitude(), user.getLatitude())));
    return flag != null && flag > 0;
}

public String nearBySearch(double distance, double userLng, double userLat) {
    GeoResults<RedisGeoCommands.GeoLocation<Object>> result =
        redisTemplate.opsForGeo().radius(KEY,
            new Circle(new Point(userLng, userLat),
                new Distance(distance, Metrics.KILOMETERS)),
            RedisGeoCommands.GeoRadiusCommandArgs.newGeoRadiusArgs()
                .includeDistance()
                .includeCoordinates()
                .sortAscending());
    List<User> users = new ArrayList<>();
    for (GeoResult<RedisGeoCommands.GeoLocation<Object>> r : result.getContent()) {
        users.add(new User()
            .setDistance(r.getDistance().getValue())
            .setLatitude(r.getContent().getPoint().getX())
            .setLongitude(r.getContent().getPoint().getY()));
    }
    return JSON.toJSONString(users);
}
</code>

MongoDB + 2d Index

Design

MongoDB supports 2dsphere and 2d geospatial indexes. Insert documents with a

location

array [longitude, latitude] and create a 2d index (or 2dsphere for spherical queries).

Sample Data

<code>db.hotel.insertMany([
  {name:'hotel1', location:[115.993121,28.676436]},
  {name:'hotel2', location:[116.000093,28.679402]},
  {name:'hotel3', location:[115.999967,28.679743]},
  {name:'hotel4', location:[115.995593,28.681632]},
  {name:'hotel5', location:[115.975543,28.679509]},
  {name:'hotel6', location:[115.968428,28.669368]},
  {name:'hotel7', location:[116.035262,28.677037]},
  {name:'hotel8', location:[116.024770,28.68667]},
  {name:'hotel9', location:[116.002384,28.683865]},
  {name:'hotel10', location:[116.000821,28.68129]}
]);
</code>

Index Creation

<code>db.hotel.createIndex({location:"2d"}, {bits:11111});
</code>

GeoNear Query

<code>db.hotel.aggregate({
  $geoNear:{
    near:[115.999567,28.681813],
    spherical:true,
    distanceMultiplier:6378137,
    maxDistance:2000/6378137,
    distanceField:"distance"
  }
});
</code>

Conclusion

The article emphasizes that interviewers care about breadth of knowledge. Understanding multiple backend implementations of a “nearby users” feature—MySQL, MySQL+GeoHash, Redis, MongoDB—helps candidates discuss design choices confidently and increases their chances of receiving an offer.