Nearest location from JSON column

Question

We have a directory application and every directory has many branches and many location points (latitude, longitude)

The branches location are stored in the database as a JSON value column type text as follows:

{
    "locations": [
        {
            "lat": "30.021463",
            "lng": "31.074944"
        },
        {
            "lat": "30.035189",
            "lng": "31.198761"
        }
    ]
}

How to get the nearest location to the user when sending their latitude & longitude?

Technologies used

1. .Net Core
2. PostgreSQL
3. Entity Framework Core

Answer 1

If you have PostGIS installed on your Postgres server, you could try something like this

SELECT j.*,
  ST_DISTANCE(
    ST_GeogFromText('POINT(' || lng || ' ' || lat || ')'),
    ST_GeogFromText('POINT(31.08 30.025)')
    ) AS dist
FROM YourTable AS t
CROSS JOIN jsonb_to_recordset(t.data->'locations') AS j(lat int, lng int)
ORDER BY
  dist;

I strongly suggest you store geodetic information in an actual geography column.

At the very least you should normalize this JSON out to another table if you are querying it often.