Geographic / spatial query

Oracle

-- Oracle Spatial: SDO_GEOMETRY column and spatial index
CREATE INDEX location_sidx ON shop(location)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX;
-- Shops within 5 km:
SELECT name FROM shop
WHERE SDO_WITHIN_DISTANCE(location,
  SDO_GEOMETRY(2001, 4326,
    SDO_POINT_TYPE(32.85, 39.92, NULL), NULL, NULL),
  'distance=5 unit=KM') = 'TRUE';

Oracle Spatial stores geographic data in the SDO_GEOMETRY type; a USER_SDO_GEOM_METADATA entry and a spatial index are required. Operators such as SDO_WITHIN_DISTANCE, SDO_RELATE and SDO_NN answer distance/intersection/nearest-neighbor queries. SRID 4326 is the WGS84 (latitude/longitude) system.

PostgreSQL

-- PostGIS extension:
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE INDEX location_gix ON shop USING GIST (location);
-- Shops within 5 km (geography, meters):
SELECT name FROM shop
WHERE ST_DWithin(location,
  ST_SetSRID(ST_MakePoint(32.85, 39.92), 4326)::geography,
  5000);

PostGIS turns PostgreSQL into a full-featured spatial database. It provides geometry (planar) and geography (spherical) types and hundreds of ST_ functions such as ST_Distance/ST_DWithin/ST_Intersects/ST_Contains. A GiST/SP-GiST index speeds up spatial queries.

MySQL

-- Spatial column (with SRID) and SPATIAL index:
ALTER TABLE shop MODIFY location POINT NOT NULL SRID 4326;
ALTER TABLE shop ADD SPATIAL INDEX (location);
-- Compute distance (meters, geography):
SELECT name,
  ST_Distance(location,
    ST_SRID(POINT(32.85, 39.92), 4326)) AS distance
FROM shop
ORDER BY distance LIMIT 10;

MySQL (5.7+) supports OGC-compliant spatial data types (POINT, POLYGON, etc.) and ST_ functions. A SPATIAL index (R-tree) can be created only on InnoDB/MyISAM and on NOT NULL columns; for the optimizer to use it, the column must be defined with an SRID attribute (SRID 4326). With SRID 4326, ST_Distance returns the geodesic distance in meters. With POINT(x, y) + ST_SRID, x is longitude and y is latitude; for WKT input (ST_GeomFromText) with SRID 4326 the order is latitude-longitude.

SQL Server

-- geography type and spatial index:
CREATE SPATIAL INDEX location_sidx ON dbo.shop(location);
-- Shops within 5 km (STDistance returns meters):
SELECT name FROM dbo.shop
WHERE location.STDistance(
  geography::Point(39.92, 32.85, 4326)) <= 5000;

SQL Server offers two spatial types: geography (spherical, latitude/longitude) and geometry (planar). Methods such as STDistance, STIntersects and STContains answer the queries. A spatial index (grid-based) improves performance. Mind the argument order of geography::Point(latitude, longitude, SRID).

MongoDB

// 2dsphere index (for GeoJSON):
db.shop.createIndex({ location: "2dsphere" })
// Shops nearest to a given point ($near):
db.shop.find({
  location: {
    $near: {
      $geometry: { type: "Point", coordinates: [32.85, 39.92] },
      $maxDistance: 5000
    }
  }
})

MongoDB stores geographic data in GeoJSON format and runs spherical queries with a 2dsphere index. The $near (sorted by distance), $geoWithin (within an area) and $geoIntersects operators are supported. Coordinates are in [longitude, latitude] order; $maxDistance is in meters.

Redis

# Built-in geospatial commands (built on sorted sets):
GEOADD shops 32.85 39.92 "kizilay"
GEOADD shops 32.86 39.93 "cankaya"
# Search within 5 km of a point:
GEOSEARCH shops FROMLONLAT 32.85 39.92
  BYRADIUS 5 km ASC

Redis stores geographic locations in a sorted set with GEOADD (longitude latitude member). GEOSEARCH (Redis 6.2+, replacing the older GEORADIUS) returns the members within a radius or box, sorted by distance. GEODIST gives the distance between two points. This is a core feature; no module is needed.

Elasticsearch

# Map the field as geo_point:
PUT shop
{ "mappings": { "properties": { "location": { "type": "geo_point" } } } }
# Query within 5 km:
GET shop/_search
{ "query": { "geo_distance": {
  "distance": "5km",
  "location": { "lat": 39.92, "lon": 32.85 } } } }

Elasticsearch supports the geo_point (point) and geo_shape (polygon/line) field types. Filtering is done with geo_distance (radius), geo_bounding_box (box) and geo_polygon queries; sorting with geo_distance, and aggregation with geohash_grid/geotile_grid. Coordinates are given as lat/lon or as a [lon, lat] array.