Leveraging Supabase and PostgreSQL for Distance-based Filtering and Location Data Retrieval

In this blog post, we will explore how to leverage Supabase as a backend and PostgreSQL as the underlying database to implement distance-based filtering and retrieve location data. We will also highlight the significance of incorporating geospatial functionalities using PostGIS.

Geospatial Functionalities with PostGIS

When working with geospatial data in PostgreSQL, integrating PostGIS becomes essential. PostGIS is an open-source extension to PostgreSQL that enables the storage, management, and analysis of geographic data. It provides a wide range of geospatial functions and operators that can be utilized to perform complex spatial queries and manipulations. By incorporating PostGIS into your Supabase and PostgreSQL stack, you can leverage powerful capabilities such as distance calculations, spatial indexing, geometric operations, and spatial relationships. These functionalities enable efficient distance-based filtering and location data retrieval, making it easier to implement location-aware applications and services.

Retrieving Location Data

To obtain location data based on an IP address, we can create two PostgreSQL functions: get_records_within_distance_free for the free tier (60 requests per minute) and get_records_within_distance when you have an API key from the freeipapi service.

a. get_records_within_distance_free Function (Free Tier):

CREATE OR REPLACE FUNCTION get_records_within_distance_free(ip varchar, distance numeric)
RETURNS SETOF my_table AS
$$
DECLARE
    tlongitude float;
    tlatitude float;
    response jsonb;
BEGIN
    -- HTTP GET request to retrieve the latitude and longitude
    SELECT content::jsonb INTO response FROM http_get('https://freeipapi.com/api/json/' || ip);
    tlongitude := (response -> 'longitude')::float;
    tlatitude := (response -> 'latitude')::float;
    -- Call the get_entries_within_distance function
    RETURN QUERY SELECT * FROM get_entries_within_distance(tlongitude, tlatitude, distance);
END;
$$ LANGUAGE plpgsql VOLATILE;

The get_records_within_distance_free function retrieves the latitude and longitude by making an HTTP GET request to the freeipapi API. It then calls the get_entries_within_distance function using the obtained latitude, longitude, and distance parameters.

b. get_records_within_distance Function (API Key):

CREATE OR REPLACE FUNCTION get_records_within_distance(ip varchar, distance numeric)
RETURNS SETOF my_table AS
$$
DECLARE
    tlongitude float;
    tlatitude float;
    response jsonb;
    freeipapi_key TEXT;
BEGIN
    -- Get freeipapi_key from the vault
    SELECT decrypted_secret
    INTO freeipapi_key
    FROM vault.decrypted_secrets
    WHERE name = 'freeipapi_key';
    -- HTTP GET request to retrieve the latitude and longitude
    SELECT content::jsonb INTO response
    FROM http((
        'GET',
        'https://freeipapi.com/api/json/' || ip,
        ARRAY[http_header('Authorization', 'Bearer ' || freeipapi_key)],
        'application/json'
    )::http_request);
    tlongitude := (response -> 'longitude')::float;
    tlatitude := (response -> 'latitude')::float;
    -- Call the get_entries_within_distance function
    RETURN QUERY SELECT * FROM get_entries_within_distance(tlongitude, tlatitude, distance);
END;
$$ LANGUAGE plpgsql VOLATILE;

The get_records_within_distance function is used when you have an API key from the freeipapi service. It retrieves the API key from the vault and incorporates it into the HTTP GET request to retrieve the latitude and longitude. The obtained latitude, longitude, and distance parameters are then used to call the get_entries_within_distance function.

Leveraging PostgreSQL Function for Distance-Based Filtering

In this section, we will delve into the creation of a PostgreSQL function to enable distance-based filtering, leveraging the power of the PostGIS extension. This approach allows efficient retrieval of location data within a specified radius. Implementing this functionality with Supabase and PostgreSQL empowers your application with geospatial capabilities.

Let's explore an example of the PostgreSQL function, get_entries_within_distance, designed to facilitate distance-based filtering:

CREATE OR REPLACE FUNCTION get_entries_within_distance(tlongitude float, tlatitude float, distance numeric)
RETURNS SETOF my_table AS
$$
BEGIN
  RETURN QUERY SELECT * FROM my_table 
  WHERE ST_DistanceSphere(ST_MakePoint(longitude, latitude), ST_MakePoint(tlongitude, tlatitude)) < distance::float;
END;
$$ LANGUAGE plpgsql VOLATILE;

Executing the PostgreSQL Function

To retrieve records within a specific distance from a given IP address, we can execute the appropriate PostgreSQL function using SQL queries. Here are two examples:

a. Utilizing the Free Tier Function:

SELECT * FROM get_records_within_distance_free('138.186.111.34', 900000);

b. Employing the API Key Function:

SELECT * FROM get_records_within_distance('138.186.111.34', 900000);

Both queries retrieve records that fall within a distance of 900,000 units from the geographical location associated with the IP address '138.186.111.36'.

Invoking the PostgreSQL Function from a JavaScript Client

To integrate the PostgreSQL function into your JavaScript application using Supabase's JavaScript client library, you can utilize RPC (Remote Procedure Call) to make the necessary call. Here's an optimized code snippet:

const { data: ret, error } = await supabase
  .rpc('get_records_within_distance', { ip: '138.186.111.34', distance: 900000 });
console.log(JSON.stringify(ret));

In this example, the IP address and distance parameters are passed to the get_records_within_distance function, and the response is stored in the ret variable.

Security Considerations for Accessing the freeipapi.com API

When it comes to accessing external APIs like freeipapi.com, it's crucial to prioritize security and adhere to rate limits. By utilizing the get_records_within_distance function, which retrieves the freeipapi_key from the vault, you can effectively bypass the rate limit imposed by the API, which typically allows only 60 requests per minute. However, it's essential to implement rate limit handling tailored to your specific application requirements, ensuring a seamless and efficient user experience.

Conclusion: Leveraging Supabase, PostgreSQL, and Geospatial Capabilities for Location Data Retrieval

In this blog post, we explored the powerful combination of Supabase as a backend and PostgreSQL as the underlying database to implement distance-based filtering and retrieve location data. By incorporating the robust geospatial functionalities provided by PostGIS and leveraging the freeipapi.com API, we were able to achieve accurate distance calculations and obtain precise location information.

The integration of Supabase, PostgreSQL, and external APIs empowers developers to build highly sophisticated and location-aware applications. Whether you're working on mapping services, location-based search functionality, or geospatial analytics, this comprehensive stack equips you with the necessary tools and flexibility to deliver exceptional user experiences.

We highly encourage readers to continue exploring and experimenting with Supabase, PostgreSQL, PostGIS, and other cutting-edge geospatial technologies to unlock the full potential of location data in their projects. By leveraging these state-of-the-art technologies, you can create innovative applications that seamlessly integrate location awareness and provide valuable insights to your users. Including expanding on the user tracking mechanisms.

Stay up-to-date with the official documentation of Supabase, PostgreSQL, and PostGIS to leverage the latest features, enhancements, and security best practices. As you embark on your development journey, we wish you success in creating groundbreaking location-enabled applications that make a significant impact in your industry.