Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE store (id int, store_name text, longitude numeric, latitude numeric);
INSERT INTO store  VALUES (1,'Store 1',-4.5896,54.2835);
INSERT INTO store  VALUES (2,'Store 2',-4.4077,54.2011);
INSERT INTO store  VALUES (3,'Store 3',-4.6283,54.0804);

SELECT AddGeometryColumn ('public','store','geom',4326,'POINT',2);
UPDATE store SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);
CREATE INDEX idx_store_geom ON store USING gist (geom);

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1




addgeometrycolumn


public.store.geom SRID:4326 TYPE:POINT DIMS:2



SELECT 1



UPDATE 3



CREATE INDEX


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


id
store_name
longitude
latitude
geom


1
Store 1
-4.5896
54.2835
0101000020E6100000EBE2361AC05B12C03F355EBA49244B40

2
Store 2
-4.4077
54.2011
0101000020E61000001A51DA1B7CA111C0E09C11A5BD194B40

3
Store 3
-4.6283
54.0804
0101000020E6100000A54E4013618312C0984C158C4A0A4B40



SELECT 3


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); 


id
store_name
longitude
latitude
geom
closest_store
distance


1
Store 1
-4.5896
54.2835
0101000020E6100000EBE2361AC05B12C03F355EBA49244B40
Store 2
0.19969318966855096

2
Store 2
-4.4077
54.2011
0101000020E61000001A51DA1B7CA111C0E09C11A5BD194B40
Store 1
0.19969318966855096

3
Store 3
-4.6283
54.0804
0101000020E6100000A54E4013618312C0984C158C4A0A4B40
Store 1
0.20675420189200436



SELECT 3


CREATE TABLE store_closest AS
SELECT * FROM store
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-4.59,54.29),4326)
LIMIT 100;

SELECT * FROM store_closest;

SELECT 3




id
store_name
longitude
latitude
geom


1
Store 1
-4.5896
54.2835
0101000020E6100000EBE2361AC05B12C03F355EBA49244B40

2
Store 2
-4.4077
54.2011
0101000020E61000001A51DA1B7CA111C0E09C11A5BD194B40

3
Store 3
-4.6283
54.0804
0101000020E6100000A54E4013618312C0984C158C4A0A4B40



SELECT 3

id	store_name	longitude	latitude	geom
1	Store 1	-4.5896	54.2835	0101000020E6100000EBE2361AC05B12C03F355EBA49244B40
2	Store 2	-4.4077	54.2011	0101000020E61000001A51DA1B7CA111C0E09C11A5BD194B40
3	Store 3	-4.6283	54.0804	0101000020E6100000A54E4013618312C0984C158C4A0A4B40