Let’s assume following:
You have a database of locations, for example list of all zip codes in your country including GPS positions and you would like to get nearest 10 zip codes to specific one. Or you have a GPS of your house. you have a list of all bars in your city including theirs GPS positions and you would like to get nearest 10 ones.
You can get these results quite simple with Postgres database.
I’m going to demostrate this on zip codes:
Firstly we have to load following extension into database:
CREATE EXTENSION cude;
CREATE EXTENSION earthdistance;
If you don’t have these extensions installed, you can simply intall them:
sudo apt-get install postgresql-contrib-9.1
Now let’s create the table:
CREATE TABLE zip_code (
id serial NOT NULL,
zip_code integer NOT NULL,
region character varying(100) NOT NULL,
latitude numeric(6,4) NOT NULL,
longitude numeric(6,4) NOT NULL,
CONSTRAINT zipcode_pkey PRIMARY KEY (id)
insert some data:
INSERT INTO zip_code (zip_code, region, latitude, longitude) VALUES (10200, ‘Prague 1’, 50.0833, 14.4275)
and finally create the index:
CREATE INDEX ON zip_code USING GIST (earth_coords)
Now you have the table with data and index on the column, which you will need for conditioning. But you have to do one more thing:
UPDATE zip_code SET earth_coords = ll_to_earth(latitude, longitude);
This will fill the earth_coords column with GPS positions converted into earth data type for each record in the table.
Now you have to know GPS position of the zip code which you would like get nearest zip codes to. Let’s say the GPS position is:
So you can run something like:
SELECT * FROM zipcode WHERE earth_box(ll_to_earth(50.0794, 14.43400), 5000) @ earth_coords LIMIT 10
which will select all zip codes from your current one in 5 km radius (that’s why 5000).
On my local machine (Intel i5) with 6.8k records the query takes 142ms.