Challenges with Location Data(Coordinates) Storage and Retrieval

[PuneetSharma52]

During development, we encountered difficulties in storing and retrieving location coordinates as attributes. A thorough investigation revealed an absence of suitable attributes within the Lucid Model. To address this, the geojson library was integrated from npm, enabling the creation of a Point attribute. This approach facilitated the execution of raw PostGIS queries on the stored data.

The primary objective is to efficiently retrieve a list of users within a specified radius (10 km) of a given user's coordinates. This information will be retrieved or fetched through API endpoints to display nearby users.

Numerous attempts were made to achieve this functionality using various attribute types, including string and geometry, but without success. Guidance is required on effectively modeling location data within the application, including considerations for database migrations and controller implementation.

Migration

Model

Controller

And also in addition when we are manually seeding the data in the pgsql we are unable to seed it in the location column as showing below for reference.

[tavaresgerson]

Hi bro,

I'm participating in a project based on coordinates and other proximity calculations between points and polygons and my solution was:

1. Leave the database only to store the data (coordinates) instead of performing the calculations from the database
2. Any calculations were performed in the application layer with @turf.
3. And I also divided the execution of the Adonisjs application into a cluster (PM2) and it was the best choice

The performance became excellent when the database reached 11.2 GB and the client stopped complaining :-D

[PuneetSharma52]

That's great,
In my case i have used raw SQL postgis query that was enough to convert and store the coordinates in the WKT form it was working fine , I also mitigate the SQL injection in that but outpassing the arguments, but in two update queries it was not working, I will attach that query here, It will be helpful if you can go throw it and provide me solution to mitigate SQL injection in that query 🫡

Raw SQL query

await db.rawQuery(
UPDATE locations SET location = ST_GeogFromText('SRID=4326;POINT(${longitude} ${latitude})')::geography WHERE customer_id = ?,
[customerId]
)

[tavaresgerson]

I think this solves it:

const sql = "UPDATE locations SET location = ST_GeogFromText('SRID=4326;POINT(?, ?)')::geography WHERE customer_id = ?"
await db.rawQuery(sql, [longitude, latitude, customerId])

[PuneetSharma52]

I tried this method but it was not working.🙃

[tavaresgerson]

Parsing the float values ​​would prevent arbitrary SQL injection:

const sql = `UPDATE locations SET location = ST_GeogFromText('SRID=4326;POINT(${Number.parseInt(longitude)} ${Number.parseInt(latitude)})')::geography WHERE customer_id = ${Number.parseInt(customerId)}`

await db.rawQuery(sql)

Check if it is in accordance with the document: https://postgis.net/docs/ST_GeogFromText.html

Those tubes between the coordinates can't be understood, however just add 😄

[PuneetSharma52]

Ya i have used this thing:-

const [latitude, longitude] = locationString
      .split(' ')
      .map((coord: string) => Number.parseFloat(coord))
    const location = await Location.create({
      id: uuid(),
      customerId,
      type,
    })
    await location.save()
    try {
      await db.rawQuery(
        `UPDATE locations SET location = ST_GeogFromText('SRID=4326;POINT(${longitude} ${latitude})')::geography WHERE customer_id = ?`,
        [customerId]
      )
      return response.status(200).send({ message: 'Location updated successfully' })
    } catch (error) {
      return response.status(500).send({ message: 'Error updating location' })
    }

i think that's the reason it was showing the errors when i tried to outpass it like -> (?, ?) 🤔