Simple walk through of Spatial features in Akiban and how to do simple nearest-neighbor query.

geo.md

Enabling Akiban Geospatial Queries

The following needs to be in your server.properties file:

akserver.postgres.parserGeospatialIndexes=true

Table and Data

The table we will be using is shown below:

CREATE TABLE cities
(
  geonameid INT NOT NULL PRIMARY KEY,
  name VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci,
  asciiname VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci,
  alternatenames VARCHAR(5000) CHARACTER SET utf8 COLLATE utf8_general_ci,
  latitude DECIMAL(9,5),
  longitude DECIMAL(9,5),
  feature_class CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci,
  feature_code VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci,
  country_code CHAR(2) CHARACTER SET utf8 COLLATE utf8_general_ci,
  cc2 CHAR(60) CHARACTER SET utf8 COLLATE utf8_general_ci,
  admin1_code VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci,
  admin2_code VARCHAR(80) CHARACTER SET utf8 COLLATE utf8_general_ci,
  admin3_code VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci,
  admin4_code VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci,
  population BIGINT,
  elevation INT,
  dem INT,
  timezone VARCHAR(40) CHARACTER SET utf8 COLLATE utf8_general_ci,
  modification_date DATE
)

To create this table and load data into it, perform the following:

curl -O https://s3.amazonaws.com/akiban_public/geonames_akiban.sql.gz
gunzip geonames_akiban.sql.gz
psql -h localhost -p 15432 geo -f geonames_akiban.sql -q

The table should now contain 22920 rows.

Spatial Index

A spatial index is created as follows:

create index idx_geo on cities(z_order_lat_lon(latitude, longitude))

Point in-region Query

The following query locates cities with an elevation of at least 100 metres within 1 degree (latitude or longitude) of Boston:

select name, elevation, distance_lat_lon(latitude, longitude, 42.3583, -71.0603)
from cities
where elevation >= 100
and distance_lat_lon(latitude, longitude, 42.3583, -71.0603) <= 1

Nearest Neighbor Query

A similar query but this time we want the 10 nearest cities to Boston with an elevation of at least 100 metres. The following query gives us 10 cities that are near Boston - the 10 nearest in Z-order. Z-order is an ordering of points such that points that are close in the ordering are likely to be close in space. So the output from this query is a good, conservative approximation of the maximum radius in which we can find the 10 nearest cities to Boston with elevation >= 100.

select max(distance_lat_lon(d.latitude, d.longitude, 42.3583, -71.0603))
from
(
  select latitude, longitude
  from cities
  where elevation >= 100
  order by znear(latitude, longitude, 42.3583, -71.0603)
  limit 10
) d

We take the maximum in the list as the upper bound on our radius for searching. Then we use the point-in-region query with that value (lets says its 1.3469):

select name, elevation, distance_lat_lon(latitude, longitude, 42.3583, -71.0603)
from cities
where elevation >= 100
and distance_lat_lon(latitude, longitude, 42.3583, -71.0603) <= 1.3469