1

I have a Postgresql table named stores which contains a store's location (lat, long), and I can find near stores from a store using query. However, I can't find a query to create a 'ready'-generated table which create list of near stores for every store. This is the query I used to get list of near stores:

select mds.id, mds.store_name
    from public.store mds,
    (select latitude, longitude from public.store where id = '3f6077c0-c56b-4570-883f-4c16dc19855e') as st,
    sqrt(111.12 * (mds.latitude - st.latitude) * 111.12 * (mds.latitude - st.latitude) + (111.12 * (mds.longitude - st.longitude) * cos(st.latitude / 92.215)) * (111.12 * (mds.longitude - st.longitude) * cos(st.latitude / 92.215))) as distance
where distance <= 20
order by distance
limit 100

I can't replace the '3f6077c0-c56b-4570-883f-4c16dc19855e' by public.store.id. The table columns of store table is:

| id  | store_name  | latitude   |  longitude  |

Please help me with this request. Thank you very much.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Huy Do
  • 470
  • 1
  • 7
  • 19

1 Answers1

4

Spatial queries are better handled using the extension PostGIS. It has loads of really handy functions that make spatial queries very easy to write and to maintain. My suggestion:

Install Postgis (see this other answer)

Add a geometry column to your table, e.g.

SELECT AddGeometryColumn ('public','store','geom',4326,'POINT',2);

Create point geometries based on your latitude and longitude values:

UPDATE store SET geom = ST_MakePoint(longitude,latitude);

Index it (to make queries faster)

CREATE INDEX idx_store_geom ON store USING gist (geom);

After that, this is how a query to list the nearest neighbours of a given point would look like:

SELECT * FROM store
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(92.215,111.12),4326)

Or if you want the nearest store to each store ..

SELECT * FROM store mds,
LATERAL (SELECT store_name,ST_Distance(geom,mds.geom) FROM store
         WHERE id <> mds.id
         ORDER BY geom <-> mds.geom
         LIMIT 1) c (closest_store,distance);
  • The operator <-> stands for distance, so using it in the ORDER BY clause with LIMIT 1 selects only the record that is closest to a reference geometry.
  • 4326 stands for the spatial reference system WGS84. It might vary depending on your coordinates.

Demo: db<>fiddle

Jim Jones
  • 18,404
  • 3
  • 35
  • 44