Doing geo location queries with strapi and postgis

Questions and Answers Strapi Backend
1
System Information
  • Strapi Version: 4.17.1
  • Operating System: Docker
  • Database: PostGIS
  • Node Version: v20.10.0
  • NPM Version: 10.2.4
  • Yarn Version: N/A

Hi folks!

I’m new in the backend development and I have some concerns about the best way to deal with some of my queries. My application has a filter to search for nearby locations, so for this, I’m using PostGIS to do the query.

I have the collection addresses and I added a new “find” to the addresses controller, like I added below. My idea is not to mess with all my find queries, so I check if the query has the filters lat, lon, and distance and do a strapi.db.connection.raw query.

I found some problems for now, none of them is breaking anything, but I think I can improve it.

Doing this raw query, I lose some of strapi features, like relations. So what I did to fix that is get all the ID from my addresses and use findMany to find all the entities in the collection with this ID and populate with all relations that I need. This worked, but can I do something better?

Another problem is that I lose the pagination. I’m missing something?

And there is another way to do geolocation queries with strapi? I found some comments in this forum and on the stack overflow, but none of them has enough information about how I can create this query/filter.
Some of them use mongodb, others use knex to create the query. I’m open for other ways to do this. :slight_smile:

  async find(ctx) {
    const lon = ctx.query.filters['lon']
    const lat = ctx.query.filters['lat']
    const distance = ctx.query.filters['distance']
    if (lon && lat && distance) {
      const req = await strapi.db.connection.raw(`
        SELECT id
        FROM addresses 
        WHERE ST_DWithin(ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography,
        ST_MakePoint(${lon}, ${lat})::geography, ${distance})`);

      const data = await strapi.entityService.findMany('api::address.address', {
        filters: {
          id: {
            $in: req.rows.map((row) => row.id)
          },
        },
        populate: *
      });
      return ctx.send({
        data,
        meta: {}
      });
    }
    const response = await super.find(ctx);
    return response;
  },

The request from my frontend (using the strapi nuxt module).

find('addresses', {
    populate: '*',
    filters: {
      lat: mapLat.value,
      lon: mapLon.value,
      distance: distance.value.toFixed(),
    }
  });
2

I found a partial solution by myself. So if anyone found this post, the solution is to use the this.transformReponse.

I found this solution there: node.js - Entity Service VS super.find() VS Query Engine API, what is the differences? - Stack Overflow

like this:

    const lon = ctx.query.filters['lon']
    const lat = ctx.query.filters['lat']
    const distance = ctx.query.filters['distance']

    if (lon && lat && distance) {
      // And updated version of the query, I improved it a bit since the last post
      // This query will return all addresses IDs that are within the distance of the given coordinates
      const req = await strapi.db.connection.raw(`
        SELECT id
        FROM addresses 
        WHERE ST_DWithin(
        ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326)),
        ST_Transform(ST_SetSRID(ST_MakePoint(${lon}, ${lat}), 4326)), ${distance})
      `);
      // Get your start and limit from the query
      const { start, limit } = ctx.query.filters;

      // This query will return all data from address that match the IDs from the previous query
      const entityData = await strapi.entityService.findMany('api::address.address', {
        filters: {
          id: {
            // Here I'm querying the IDs from the previous query
            $in: req.rows.map((row) => row.id)
          },
        },
        // Here you can popylate the fields you want, like any other filter
        populate: ['*'],
        // For the pagination you need to pass the start and limit
        // source: https://docs.strapi.io/dev-docs/api/entity-service/order-pagination#pagination
        start,
        limit
      });
      // This will transofrm into a response that match others strapi responses.
      const data = await this.transformResponse(entityData);

      return {
        data,
        meta: {
          // Here you can create your own pagination
          pagination: {
            // You need to convert the start and limit to numbers
            // I don't know why this is converted to string, but this works on the filter
            start: Number(start),
            limit: Number(limit),
            // source: https://www.restack.io/docs/strapi-knowledge-strapi-findmany-pagination-guide
            total: req.rows.length
          }
        }
      };
    }

Before this fix my response was like this:

[
{
  id: 15,
  (... address content)
  createdAt: '2024-02-05T17:10:01.237Z',
  updatedAt: '2024-02-05T17:10:01.237Z',
  my_relation: [
    {
      id: 16,
      (...relation content)
      createdAt: '2024-02-05T17:10:01.256Z',
      updatedAt: '2024-02-05T17:10:01.275Z',
      publishedAt: '2024-02-05T17:10:01.254Z'
    }
    (...)
  ]
(...)
]

Now it is like this:

[
  {
    "id": 15,
    "attributes": {
      (... address content)
      "createdAt": "2024-02-05T17:10:01.237Z",
      "updatedAt": "2024-02-05T17:10:01.237Z",
      "reviews": {
        "data": [
          {
            "id": 16,
            "attributes": {
              (...relation content)
              "createdAt": "2024-02-05T17:10:01.256Z",
              "updatedAt": "2024-02-05T17:10:01.275Z",
              "publishedAt": "2024-02-05T17:10:01.254Z"
            }
          },
        ]
      }
    }
  }
]

But I still have one problem, my response is like this:

{
  data: {
    data: [ // Here is the problem
      [Object], [Object],
      [Object], [Object],
      [Object], [Object],
      [Object], [Object],
      [Object]
    ],
    meta: {}
  },
  meta: { pagination: { start: 0, limit: 9, total: 64 } }
}

I can’t do this on strapi because it’s just break. xD

      return {
        data: data.data,
      }