Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Geographic Searches With Postgres's Earthdistance and Cube Extensions
#!/usr/bin/env ruby
=begin
= Geographic Searches With Postgres's Earthdistance and Cube Extensions
This program shows how to easily create a Postgres database that uses the Cube
and Earthdistance extensions to perform fast queries on geographic data.
Briefly, the problem this code solves is "show me all places within 50
kilometers of New York City."
I've written this brief guide because while the Postgres docs have all the info
you need, I couldn't easily find any tutorial-type documentation for Postgres
beginners.
First, be sure you have installed this program's dependencies:
* Postgres 9.1.x
* The "squirm" Ruby gem
* Ruby 1.9.x
Next, you must download a datafile from Geonames:
http://download.geonames.org/export/dump/US.zip
is what I'm using here.
You'll also need to create a database:
createdb geosearch_test
== Earthdistance
The Earthdistance extension adds some stored procedures which can
be used to calculate approximate distances between points on the Earth. It
assumes the Earth is a perfect sphere, so if you need very accurate
calculations, then this is not for you; take a look at PostGIS instead. But for
most websites that need to do basic proximity searches, it's sufficient.
The extension offers two means of calculating distance: with points or with
cubes. Using points is conceptually simpler but has two drawbacks: first, it
becomes less accurate the closer you get to the poles, so geographic searches in
Alaska, for example, are less accurate than in Ecuador. The second drawback is,
you can't use indexes with point-based searches. So let's stick to cubes.
So let's see how we would find all the records within 5 kilometers of New York
City:
SELECT * FROM cities
WHERE earth_box(ll_to_earth(40.71427000, -74.00597000), 50000) @> ll_to_earth(lat, lng);
The `ll_to_earth` function returns a point on the surface of the earth when
given a latitide and longitude, and the `earth_box` function will give us a
bounding box. Using the cube operator (`@>`) we select only points that fall
inside the box.
Now this is quite nice, but by itself it will perform a sequential scan on the
table. With my sample database of 100,000 rows, it takes roughly 8 seconds - too
slow to be at all useful.
Luckily Postgres's GIST index type understands how to index cube data, so we
can build an index to speed up this query significantly:
CREATE INDEX test_index ON cities USING gist (ll_to_earth(lat, lng));
Notice that what we're indexing is the return value of the function
`ll_to_earth`, and not the columns themselves.
So how much faster will the index make the query? After adding it, the query
runs in a mere 1.3 milliseconds. Now *that's* a little better!
If you'd like to get some more background on this topic, the Postgres docs are
brief but comprehensive:
http://www.postgresql.org/docs/9.1/static/earthdistance.html
=end
require "squirm"
Squirm.connect dbname: "geosearch_test", pool_size: 1
Squirm do
exec "CREATE EXTENSION IF NOT EXISTS cube"
exec "CREATE EXTENSION IF NOT EXISTS earthdistance"
exec "DROP TABLE IF EXISTS cities CASCADE"
exec %q{
CREATE TABLE cities(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
state CHAR(2) NOT NULL,
population INTEGER NOT NULL DEFAULT 0,
lat DECIMAL(11,8) NOT NULL,
lng DECIMAL(11,8) NOT NULL
)
}
exec %q{
CREATE OR REPLACE FUNCTION insert_city(_name text, _state text,
_population integer, _lat decimal, _lng decimal) RETURNS VOID AS $$
BEGIN
INSERT INTO cities (name, state, population, lat, lng)
VALUES (_name, _state, _population, _lat, _lng);
END;
$$ LANGUAGE 'PLPGSQL'
}
exec "CREATE INDEX test_index ON cities USING gist (ll_to_earth(lat, lng))"
end
File.open("US.txt", "r:utf-8") do |file|
insert_city = Squirm.procedure "insert_city"
i = 0;
file.lines.each do |line|
fields = line.strip.split("\t")
# Check "feature class" field, only include populated places.
next if fields[7] !~ /^PPL/
data = {
:name => fields[1],
:state => fields[10].upcase,
:population => fields[14].to_i,
:lat => fields[4].to_f,
:lng => fields[5].to_f,
}
i = i.next
insert_city.call(*data.values)
puts "#{i} - #{data[:name]}"
# Let's break after 100k since we don't need that many records to prove our
# concept.
break if i == 100_000
end
end
@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Aug 24, 2015

Thanks for this. Btw., should 50,000 be 5,000 in the earth_box call? If I'm not mistaken it should be in meters.

@bukowa

This comment has been minimized.

Copy link

@bukowa bukowa commented May 20, 2018

thanks 👍

@inouire

This comment has been minimized.

Copy link

@inouire inouire commented Apr 19, 2020

Thank you for the walk through, generic enough to be adapted to any situation fitting this use case

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment