PostGIS is a fantastic, battle-tested suite of GIS extensions for Postgres. It handles most aspects of it, and is widely used, for example in OpenStreetMap’s Mapnik. But for your project, it’s most likely overkill. In a Q and A fashion, let’s discuss reasons for (not) using it.
I need to store geographical data together with my entities. Google and StackOverflow tell me that I need PostGIS’s Geometry objects.
You’ll likely be using a very large cannon to shoot a tiny bird.
Geometry is a flexible type that can store a point, a collection of points, a shape or a collection of these.
I want points.
If it’s just a point to show a map pin, keeping it in two columns is good enough. All you need is
ALTER TABLE places ADD COLUMN longitude NUMERIC(8, 5) and then
latitude NUMERIC(8, 5).
Okay, but that has only five digits of decimal precision. Shouldn’t I use DOUBLE PRECISION instead? Or a longer NUMERIC?
No, unless you need micrometer-precise location. We could use
REAL instead, but that has a total precision of six digits, meaning that you lose precision the farther from equator/meridian zero your points are.
Lines and polygons too.
Use the built-in geometric types, and you also get circles and paths for free. Internally, they use 8-byte long floats, equivalent to
If Postgres has all these built-in, what is PostGIS for, then?
For importing and exporting industry-standard WKT/WKB format files. For managing complex shapes. For 3D geometries. For world-scale objects rather than local-scale. For cases where calculating distances and areas on a sphere vs spheroid makes a difference.
It rasterizes map tiles, also as vectors. It has a specialized spatial index. It allows querying over properties of geometric objects.
In short, it’s excellent if you’re building a dedicated map-centered database. You don’t need all of that to store just a couple of map locations.
But I do need it for querying and calculations?
There are plenty of built-ins, of which the most important is probably the containment
Can I measure the distance between two given points?
Make sure you have the
earthdistance module enabled:
CREATE EXTENSION IF NOT EXISTS earthdistance CASCADE (cascade pulls in requirements, which are just the
cube module). Then use the eponymous
earth_distance function; note that it doesn’t take coordinates directly, but its own type, which you convert to with
If using built-in Points, note that the order of
ll_to_earth’s coordinates is reversed: points are
(x, y) which corresponds to
(lng, lat). But the function takes
lat, lng. Alternatively, use the
point <@> point operator, but notice that it returns distances in some legacy units, and is not adjustable for other planet sizes.
Can I use it for indexing?
CREATE INDEX geo_index ON places USING gist(ll_to_earth(lat, lng)), then query with the cube module’s
@> containment operator. An example query to find points within a certain radius1 from a given center location:
Is there a plugin for my framework of choice?
For ActiveRecord, you likely don’t need one, as it has supported Postgres’s geometric types for years now, and you can add support for the
earth type yourself if really needed, with the Attributes API.
But there are activerecord-postgres-earthdistance and geokit-rails. For Sequel, there is the (probably unmaintained) sequel-location gem.