Storing and querying database entries based on location

In this post I’m going to walk you through how I created a solution to storing, and looking up database entries that had location data attached to them. I will be using PostgreSQL with PostGIS extension installed.

Having the PostGIS extension installed is required. PostGIS offers significant advantages for more advanced geospatial operations and complex queries.

Set the scene

Lets assume we have an in-person events application. Each event will require some location based data attached to it, in our case that is going to be the longitude and latitude for the events meeting point.

Create the necessary database tables

For this example we’ll create a table within our database called events.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    latitude FLOAT NOT NULL,
    longitude FLOAT NOT NULL
);

SQL

Floats are commonly used for storing latitude and longitude values because they can represent a wide range of decimal numbers, making them suitable for the precision required in our example.

Create some events in our table

Now lets go ahead and create some example events so our table is populated with some test data. I’m going to use some famous landmarks but use whatever you want.

INSERT INTO events (name, latitude, longitude)
VALUES 
    ('Eiffel Tower, Paris, France', 48.8584, 2.2945),
    ('Buckingham Palace, London, UK', 51.5014, -0.1419),
    ('Colosseum, Rome, Italy', 41.8902, 12.4922);

SQL

Querying our event data

OK great, we’ve got some test data. Now we can write a query to look up events in a specific radius (which we will define) of some given coordinates and hopefully get some data back.

SELECT id
FROM "events"
WHERE ST_DWithin(
    ST_MakePoint(longitude, latitude)::geography,
    ST_MakePoint(-0.143040, 51.499040)::geography,
    15000
);

SQL

This will look up events within a 15km radius of the coordinates

Longitude: -0.143040

Latitude: 51.499040

And if everything is working correctly this should return the ID for the event entry Buckingham Palace.

Let me know

This is a simple, contrived example of how to use PostgreSQL with PostGIS to query data based on coordinates. If there’s any bugs, errors or improvements feel free to comment letting me know.

Code by Tom