Migrating MySQL Geospatial Queries to Lucene

MySQL has improved significantly over the years on handling geospatial data. Initially, it only supported GIS storage, but not proximity search. Calculating distances by hand (or by code) was quite common. Then InnoDB spatial indices were introduced, spatial functions or proper SRID support were added. For many business applications the features offered by MySQL have been proven entirely sufficient. I’ve used it for venue geo filtering.

At first, it worked.

But over time, requirements evolved. Venue ordering became more sophisticated. Full-text relevance mixed with geospatial ranking and custom scoring. Performance started to suffer.

As it later became clear, this was just another example of me using a hammer to drive a screw. I was solving a search problem… in a database. That’s when Lucene came into the picture.

Initial geofencing and ordering in MySQL

Suppose, there is a query that fetches venues inside a geo bounding box defined by a latitude, longitude and a radius. These three parameters define the center point and radius of the area we want to search within. We also order results by the distance to the given latitude and longitude.

Note: MySQL uses POINT(x y) (WKT format), not EPSG axis order. In GIS systems, x maps to longitude and y to latitude. It’s a small detail — but getting it wrong gives you very convincing, completely incorrect results.

SELECT *
FROM venues
WHERE
  MBRContains(
    geo_bbox_polygon(:lat, :lng, :radius),
    address_location
  )
AND ST_Distance_Sphere(
    address_location,
    ST_GeomFromText(CONCAT('POINT(', :lng, ' ', :lat, ')'), 4326)
) <= :radius
ORDER BY
  ST_Distance_Sphere(
    address_location,
    ST_GeomFromText(CONCAT('POINT(', :lng, ' ', :lat, ')'), 4326)
  ),
  venue_id DESC;

DELIMITER $$

CREATE FUNCTION geo_boundingbox_polygon(
    lat DOUBLE,
    lng DOUBLE,
    radiusMeters DOUBLE
)
RETURNS POLYGON
DETERMINISTIC
BEGIN
    DECLARE earth_radius DOUBLE DEFAULT 6378137;

    DECLARE latRad DOUBLE;
    DECLARE lngRad DOUBLE;
    DECLARE angularRadius DOUBLE;

    DECLARE minLat DOUBLE;
    DECLARE maxLat DOUBLE;
    DECLARE deltaLng DOUBLE;
    DECLARE minLng DOUBLE;
    DECLARE maxLng DOUBLE;

    -- Convert to radians
    SET latRad = RADIANS(lat);
    SET lngRad = RADIANS(lng);

    SET angularRadius = radiusMeters / earth_radius;

    -- Latitude bounds
    SET minLat = latRad - angularRadius;
    SET maxLat = latRad + angularRadius;

    -- Guard for poles (cos(lat) ~ 0)
    IF ABS(COS(latRad)) < 1e-12 THEN
        SET minLng = -PI();
        SET maxLng = PI();
    ELSE
        SET deltaLng = ASIN(SIN(angularRadius) / COS(latRad));
        SET minLng = lngRad - deltaLng;
        SET maxLng = lngRad + deltaLng;
    END IF;

    RETURN ST_PolyFromText(CONCAT(
        'POLYGON((',
        DEGREES(minLng), ' ', DEGREES(minLat), ',',
        DEGREES(maxLng), ' ', DEGREES(minLat), ',',
        DEGREES(maxLng), ' ', DEGREES(maxLat), ',',
        DEGREES(minLng), ' ', DEGREES(maxLat), ',',
        DEGREES(minLng), ' ', DEGREES(minLat),
        '))'
    ), 4326);

END$$

DELIMITER ;

Issues with the MySQL approach

Firstly, even with spatial indexes, MySQL still evaluates distance per row. Indexes help narrow down candidates — but they don’t help rank them. Thus, MySQL ends up doing exactly what you don’t want at scale: running trigonometric distance calculations thousands of times per query.

Secondly, polygon queries have costs too. Functions like MBR_Contains still need to evaluate whether each candidate point actually lies inside the polygon. That means running a point-in-polygon check per row: evaluating intersections and handling geometric edge cases. At scale, it becomes expensive very quickly.

Thirdly, in practice, users don’t want only the closest results. They want the most relevant ones:

• matching their query
• near their location
• possibly influenced by some popularity score (from reviews or other ratings)

MySQL can compute each of these independently — but it has no unified ranking model. Everything is computed eagerly, row by row, and sorted afterward.

So instead of making MySQL behave like a search engine — one should use one.

Lucene geospatial approach

When indexing a location in Lucene:

document.add(new LatLonPoint("address_location", lat, lng));

you’re basically inserting that point into a BKD tree.

Just a quick detour. What is a BKD tree, you ask. A BKD tree is a data structure designed for efficient multi-dimensional search. For spatial data, it allows entire regions to be pruned quickly during queries. Here’s a great article on the BKD tree.

For a query like:

LatLonPoint.newDistanceQuery("address_location", lat, lng, radius)

Lucene doesn’t compute distance for every document. Instead, it navigates the tree, skips the regions that cannot match and only evaluates a small set of candidate points. What used to require multiple conditions and computations in SQL becomes a single geo query backed by an index.

Also, quite obviously: filtering and ranking are separate concerns. Filtering is done on LatLonPoint fields, LatLonDocValuesFieldis better for ranking. Usually, they are used together.

document.add(new LatLonPoint("address_location", lat, lng));
document.add(new LatLonDocValuesField("address_location", lat, lng));

Sorting will look like this:

Sort sort = new Sort(
  LatLonDocValuesField.newDistanceSort("address_location", lat, lng),
  SortField.FIELD_SCORE,
  // any other scoring fields that are relevant
);

Mapping MySQL Geo Queries to Lucene

Once the switching of mental models is done, the migration itself becomes surprisingly straightforward.

Every MySQL concept has a Lucene equivalent.

1. Distance Filtering

ST_Distance_Sphere(address_location, :point) < radius

LatLonPoint.newDistanceQuery("address_location", lat, lng, radius)

2. Bounding Box

MBRContains(bbox, address_location)

LatLonPoint.newBoxQuery("address_location", minLat, maxLat, minLng, maxLng)

3. Polygon Queries

ST_Contains(polygon, address_location)

LatLonPoint.newPolygonQuery("address_location", polygon)

4. Sorting by Distance

ORDER BY ST_Distance_Sphere(...)

new Sort(
  LatLonDocValuesField.newDistanceSort("address_location", lat, lon)
);

Tradeoffs: no free lunch

One of the biggest tradeoffs when moving from MySQL to Lucene is losing transactional guarantees. Good old relational databases are still excelling at keeping the data consistent. Lucene, however, is a near real-time system: a newly created venue might not appear in search immediately (indexing may take a few seconds), updates and deletes can be briefly out of sync. Strict consistency is traded for eventual consistency — with much faster reads.

Also, with the MySQL-approach data lives in one place and queries reflect the latest state, whereas, introducing Lucene means introducing a new source of truth: MySQL has the canonical data, Lucene is the search index. Keeping them in sync becomes an engineering task. In the end, query complexity converts to system design complexity. For search-heavy systems, though, this trade is usually right.

Final thoughts

This migration taught me an important lesson: just because the data lives in a database doesn’t mean every problem should be solved there. Search is a good example of a concern that benefits from its own dedicated abstraction.

So, the next time you find yourself hitting a screw with a hammer, take a step back. You might just need a screwdriver instead.