How to perform geographical queries using Supabase and PostGIS

Super Bass database can store many data types but did you know that you can also store geographical data such as coordinates on a map let's look at some actual examples of what you can build with this feature you can get started with geodata on Super Bass by enabling the postgis extension from your Super Bass dashboard postgis extension gives us Advanced Data types that are suited for storing geographical data as well as provide us with many functions to sort and convert those data now let's say we are working on a restaurant search app we could start with creating a restaurants table to store information about the restaurants in the app we'll have a location column with type geography point this location column will store the latitude and longitude coordinates of each restaurant once we have a table we can insert some data into the restaurants table using SQL note that SD Point here is a function provided by pulses extension that converts latitude and longitude pair into geography type you can also insert data using the client libraries as well here's a JavaScript example now that we have some dummy data in our database let's look at how we can query them before we get started with querying though we probably want to set an index so that our queries run efficiently set exist index on the location column with the following SQL and we are set let's first look at a boundary box query a Bondo ebox query fetches all the data that I located inside a 2d boundary box this is useful when you have a map UI and you want to fetch all the data within a region that a user is viewing we can create a postgres function for this we can take the lower left and upper right corner of the bounds we want to query and pass it as the argument the magic happens inside the wire Clause using the two points provided in the argument we construct a 2d bounding box then using the double Ampersand operator we check if the location intersects with the bounding box or not the resulting query returns a table of all the restaurants that are within the 2D bounding box which we can create from our client another common scenario is to query data order by distance from a certain location ordering by distance can be useful for finding nearby restaurants in your app we can again create a postgres function to fetch the restaurants and order them by distance the function takes a latitude and longitude pair and Returns the restaurants in the closest to farthest order we can get the distance between the location of the argument and each restaurant location using SD distance function which Returns the distance in meters the magic is this double arrow symbol which is another postgis operator specifically to sort data by distance and just by calling this function from your app you can get the restaurants ordered by closest to farthest and just like that you can store and interact with zero data in your supervised database head over to superbass.com to learn more about Super Bass [Music]

Channel: Supabase

Views: 5,078

Keywords:

Id: agFsGDJxjwA

Length: 2min 54sec (174 seconds)

Published: Wed Oct 04 2023