Last updated
August 2, 2023

Postgres Geolocation: Geolocation with PostgreSQL

Elizabeth (Lizzie) Shipton

Table of Contents:

Get your free
IP Geolocation
API key now
4.8 from 1,863 votes
See why the best developers build on Abstract
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
No credit card required

Geolocation is the process of translating geographical data to a physical location on the planet. You may have heard of geolocation by looking up a device's IP address and then using that information to determine a user's zip code, city, or country.

Geolocation queries can also be run on the back end and can be very useful for doing geospatial analysis using latitude and longitude coordinates. Geospatial analysis is important for apps that use users' location information to show nearby points of interest, to connect with other nearby users, or to get distances or directions to a particular location.


Postgresql databases support geolocation queries through the use of stored geographical objects and the handy Postgis extension, a spatial database extender. In this article, we'll look at adding Postgis to a Postgresql database, loading some test data, and making location queries against that data using geospatial indexes and other special functions that allow us to work with geographical objects.

We recommend you have pgAdmin installed to do this tutorial.

Let’s send your first free
IP Geolocation
See why the best developers build on Abstract
Get your free api

Getting Started With Postgis Extension

This tutorial will not dive deeply into how to use Postgres. We'll assume a baseline knowledge of how to install and set up a basic Postgresql database. If you need help with that, we recommend checking out the docs.

Install Postgis

Once you have your Postgresql database instance up and running, installing the Postgis extension is easy. On a Mac, you can add it via Homebrew.

Restart Postgres

After you install Postgis, you'll need to restart your Postgres instance for the change to take effect. You can do that via PGAdmin, or by running the following in psql in the command line.

brew services restart postgres

Create Database

Create a normal database using PGAdmin, or by running the following command in the psql command line.

create database databasename

Enable Extension

The extension is now running on the SQL server. Next, you'll need to run the CREATE EXTENSION command that runs the Postgis extension in the SQL window. We recommend doing this via the PGAdmin interface. Connect to your SQL instance in PGAdmin and run the following command.


This creates all the needed database objects, tables, indexes, etc that Postgis gives us to work with. You can confirm that Postgis is running with the following query:

SELECT postgis_full_version();

Load Sample Data

Finally, you'll need some sample data. We'll use the data bundle from the Postgis tutorial. Connect to your PGAdmin database and right-click on your database name, then select "Restore." Browse to the location where you downloaded the sample data and select the nyc_data.backup file. This will insert data from the backup file into your database.

Next, right-click on the database name again and select "Refresh." You should now see a list of new tables and data. PGAdmin has a handy Geometry Viewer, which you can click to see the layout of the streets.

Run Complex Queries

When a database instance runs the Postgis extension, created databases will include the geometry data type, and will be geospatially aware. The geometry column is an opaque binary that requires Postgis functions to turn the data that gets stored there into usable points.

Query a Single Point

To get a single set of latitude and longitude coordinates from the geom column, run the following query:

SELECT name, ST_AsText(geom)
FROM nyc_subway_stations

The result should look something like this

"Cortlandt St" "POINT(583521.854408956 4507077.862599085)"
"Rector St" "POINT(583324.4866324601 4506805.373160211)"
"South Ferry" "POINT(583304.1823994748 4506069.654048115)"
"138th St" "POINT(590250.10594797 4518558.019924332)"
"149th St" "POINT(590454.7399891173 4519145.719617855)"

The ST_AsText function turns the opaque binary geom column into a pair of latitude and longitude points.

Finding Distance

Finding simple distances can be done by returning the length of a linestring. A linestring is a path between two points. It is represented as an ordered series of two or more points. Roads and rivers are considered linestrings.

In our sample data, we have linestring data for the streets in New York City. The distances of streets can be found by using the ST_Length function and passing it the name of a linestring (street.) For example:

SELECT ST_Length(geom)
FROM nyc_streets
WHERE name = 'Columbus Cir';

Geometry vs Geography

Let's quickly talk about the difference between geometry and geography, particularly as it applies to geospatial data analysis. Postgis draws the distinction between geometry and geography thus: geometry is flat, while geography is curved.

Small distances on the Earth (i.e. between two points in a city) can be easily calculated using cartesian geography. Longer distances, however, (for example, points in different cities or states) must include additional calculations to account for the curvature of the Earth.

Postgis provides this functionality through the geography type. This type should be used instead of the geometry type when calculating distances between countries or cities.

SELECT ST_Distance(
'SRID=4326;POINT(-118.4079 33.9434)'::geography, -- Los Angeles (LAX)
'SRID=4326;POINT(2.5559 49.0083)'::geography -- Paris (CDG)

Spatial Indexes

Spatial indexes, like regular database indexes, make querying spatial data faster and working with very large datasets more manageable. Without a spatial index, searches would require a "sequential scan" of all rows in the table. An index organizes the data into a search tree for faster processing.

To test this, let's remove the spatial index from nyc_census_blocks.

DROP INDEX nyc_census_blocks_geom_idx;

Then run the following query in PGAdmin and watch the "timing" indicator.

SELECT count(blocks.blkid)
FROM nyc_census_blocks blocks
JOIN nyc_subway_stations subways
ON ST_Contains(blocks.geom, subways.geom)

Now, the table is small, so granted, the time it takes to scan it should be quite small (around 300ms.) But let's see what happens when we add the index back again.

CREATE INDEX nyc_census_blocks_geom_idx
ON nyc_census_blocks
USING GIST (geom);

When you run the same query, the PGAdmin timing indicator should show a significantly faster processing time (somewhere around 50ms.)


In this article, we looked at how to set up a Postgresql database with Postgis and work with the geometry data type. We loaded some test data provided by the Postgis docs and used the special Postgis functions to query for geospatial data. We also used the Geography Viewer to view all the points on a map.


Does Postgres support geospatial data?

Yes. While Postgres does not have built-in support for geolocation, there are several Postgres extensions including Postgis that allow you to treat geospatial data as first-class objects in your database.

What is the data type for latitude and longitude in Postgresql?

In Postgresql, latitude and longitude are represented by the POINT data type. This is a geometry data type that is stored in the opaque binary geom column. It can be accessed by running the Postgis ST_AsText function.

Is Postgis included in Postgresql?

No, Postgis is an add-on, or extension. It is a spatial database extender that adds support for geographic objects and allows geospatial queries to be run in a Postgres database.

Elizabeth (Lizzie) Shipton
Get your free
IP Geolocation
key now
This is some text inside of a div block.
get started for free

Related Articles

Get your free
key now
IP Geolocation
4.8 from 1,863 votes
See why the best developers build on Abstract
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
No credit card required