PostGIS: Geo queries
PostGIS is a Postgres extension that allows you to interact with Geo data within Postgres. You can sort your data by geographic location, get data within certain geographic boundaries, and do much more with it.
Overview
While you may be able to store simple lat/long geographic coordinates as a set of decimals, it does not scale very well when you try to query through a large data set. PostGIS comes with special data types that are efficient, and indexable for high scalability.
The additional data types that PostGIS provides include Point, Polygon, Linestring, and many more to represent different types of geographical data. In this guide, we will mainly focus on how to interact with Point type, which represents a single set of latitude and longitude. If you are interested in digging deeper, you can learn more about different data types on the data management section of PostGIS docs.
Enable the extension
You can get started with PostGIS by enabling the PostGIS extension in your Supabase dashboard.
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "postgis" and enable the extension.
Examples
Now that we are ready to get started with PostGIS, let’s create a table and see how we can utilize PostGIS for some typical use cases. Let’s imagine we are creating a simple restaurant-searching app.
Let’s create our table. Each row represents a restaurant with its location stored in location column as a Point type.
_10create table if not exists public.restaurants (_10 id int generated by default as identity primary key,_10 name text not null,_10 location geography(POINT) not null_10);
We can then set a spatial index on the location column of this table.
_10create index restaurants_geo_index_10 on public.restaurants_10 using GIST (location);
Inserting data
You can insert geographical data through SQL or through our API.
Restaurants
| id | name | location |
|---|---|---|
| 1 | Supa Burger | lat: 40.807416, long: -73.946823 |
| 2 | Supa Pizza | lat: 40.807475, long: -73.94581 |
| 3 | Supa Taco | lat: 40.80629, long: -73.945826 |
Notice the order in which you pass the latitude and longitude. Longitude comes first, and is because longitude represents the x-axis of the location. Another thing to watch for is when inserting data from the client library, there is no comma between the two values, just a single space.
At this point, if you go into your Supabase dashboard and look at the data, you will notice that the value of the location column looks something like this.
_100101000020E6100000A4DFBE0E9C91614044FAEDEBC0494240
We can query the restaurants table directly, but it will return the location column in the format you see above.
We will create database functions so that we can use the st_y() and st_x() function to convert it back to lat and long floating values.
Order by distance
Sorting datasets from closest to farthest, sometimes called nearest-neighbor sort, is a very common use case in Geo-queries. PostGIS can handle it very easily with the use of the <-> operator. <-> operator returns the two-dimensional distance between two geometries and will utilize the spatial index when used within order by clause. You can create the following database function to sort the restaurants from closest to farthest by passing the current locations as parameters.
_10create or replace function nearby_restaurants(lat float, long float)_10returns table (id public.restaurants.id%TYPE, name public.restaurants.name%TYPE, lat float, long float, dist_meters float)_10language sql_10as $$_10 select id, name, st_y(location::geometry) as lat, st_x(location::geometry) as long, st_distance(location, st_point(long, lat)::geography) as dist_meters_10 from public.restaurants_10 order by location <-> st_point(long, lat)::geography;_10$$;
You can call this function from your client using rpc() like this:
_10const { data, error } = await supabase.rpc('nearby_restaurants', {_10 lat: 40.807313,_10 long: -73.946713,_10})
Finding all data points within a bounding box
When you are working on a map-based application where the user scrolls through your map, you might want to load the data that lies within the bounding box of the map every time your users scroll. PostGIS can return the rows that are within the bounding box just by supplying the bottom left and the top right coordinates. Let’s look at what the function would look like:
_10create or replace function restaurants_in_view(min_lat float, min_long float, max_lat float, max_long float)_10returns table (id public.restaurants.id%TYPE, name public.restaurants.name%TYPE, lat float, long float)_10language sql_10as $$_10 select id, name, st_y(location::geometry) as lat, st_x(location::geometry) as long_10 from public.restaurants_10 where location && ST_SetSRID(ST_MakeBox2D(ST_Point(min_long, min_lat), ST_Point(max_long, max_lat)), 4326)_10$$;
The && operator used in the where statement here returns a boolean of whether the bounding box of the two geometries intersect or not. We are basically creating a bounding box from the two points and finding those points that fall under the bounding box. We are also utilizing a few different PostGIS functions:
- ST_MakeBox2D: Creates a 2-dimensional box from two points.
- ST_SetSRID: Sets the SRID, which is an identifier of what coordinate system to use for the geometry. 4326 is the standard longitude and latitude coordinate system.
You can call this function from your client using rpc() like this:
_10const { data, error } = await supabase.rpc('restaurants_in_view', {_10 min_lat: 40.807,_10 min_long: -73.946,_10 max_lat: 40.808,_10 max_long: -73.945,_10})