Implementing Proximity Search with Geohash and Spatial4j in MySQL

Background – Modern O2O services often need to find nearby points of interest (POI) such as hotels or restaurants, requiring fast "nearest" and range queries based on latitude/longitude. The article presents a database‑agnostic solution that works with any relational DB, demonstrated on MySQL.

Implementation Overview – The process is divided into four steps: (1) Search – retrieve candidates within a geographic range; (2) Filter – discard results beyond the exact distance; (3) Sort – order by distance; (4) Paginate – handle paging either in SQL or in memory.

Step 1 – Search

Two approaches are described:

Bounding‑box (interval) search – Compute a latitude/longitude rectangle around the target point using Spatial4j , then query MySQL with a BETWEEN condition on the lon and lat columns. Example code to calculate the rectangle:

double lon = 116.312528, lat = 39.983733; // device location
int radius = 1; // km
SpatialContext geo = SpatialContext.GEO;
Rectangle rectangle = geo.getDistCalc().calcBoxByDistFromPt(
    geo.makePoint(lon, lat), radius * DistanceUtils.KM_TO_DEG, geo, null);
System.out.println(rectangle.getMinX() + "-" + rectangle.getMaxX()); // longitude range
System.out.println(rectangle.getMinY() + "-" + rectangle.getMaxY()); // latitude range

The corresponding SQL uses a composite index on lon and lat :

SELECT id, name
FROM customer
WHERE (lon BETWEEN ? AND ?) AND (lat BETWEEN ? AND ?);

Geohash search – Encode latitude/longitude into a fixed‑length string (default 12 characters) using GeohashUtils.encodeLatLon . Store the hash in a geo_code column with an index, then perform a prefix LIKE query. Example table alteration and query:

CREATE TABLE `customer` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(5) NOT NULL,
  `lon` DOUBLE(9,6) NOT NULL,
  `lat` DOUBLE(8,6) NOT NULL,
  `geo_code` CHAR(12) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_geo_code` (`geo_code`)
) ENGINE=InnoDB CHARSET=utf8mb4;

SELECT id, name
FROM customer
WHERE geo_code LIKE CONCAT(?, '%');

To avoid missing points near cell borders, the article suggests querying the target cell plus its eight adjacent cells using the geohash‑java library:

GeoHash geoHash = GeoHash.withCharacterPrecision(lat, lon, 10);
System.out.println(geoHash.toBase32()); // current cell
GeoHash[] adjacent = geoHash.getAdjacent();
for (GeoHash hash : adjacent) {
    System.out.println(hash.toBase32());
}

The final SQL combines the nine prefixes with OR or, after reducing the prefix length to six characters, with an IN clause for better performance:

SELECT id, name
FROM customer
WHERE geo_code IN (?, ?, ?, ?, ?, ?, ?, ?, ?);

Step 2 – Filter – After retrieving candidates, compute the exact great‑circle distance using Spatial4j and discard any record farther than the required radius.

double lon1 = 116.3125333347639, lat1 = 39.98355521792821; // device
double lon2 = 116.312528, lat2 = 39.983733; // merchant
SpatialContext geo = SpatialContext.GEO;
double distance = geo.calcDistance(geo.makePoint(lon1, lat1), geo.makePoint(lon2, lat2)) * DistanceUtils.DEG_TO_KM;
System.out.println(distance); // km

Step 3 – Sort – Sort the filtered list in application code, e.g., Collections.sort(list, comparator) , based on the computed distance.

Step 4 – Pagination – If steps 2 and 3 are required, pagination must be performed in memory after sorting; otherwise, simple SQL LIMIT can be used.

Conclusion – For datasets under one million rows, bounding‑box search is sufficient; for larger datasets, Geohash indexing provides faster lookups. Filtering and sorting are done in the application layer, which is acceptable for typical POI densities. The article provides all necessary code snippets and references for further reading.