Get closest points to coordinate in postgis

closestpoints.md

Create table

CREATE TABLE your_table_name (
    id SERIAL PRIMARY KEY,
    geom GEOMETRY(Point, 3857) -- Use EPSG:3857 for Web Mercator
);

add records

INSERT INTO your_table_name (name, geom)
VALUES
    ('New York City', ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326)), -- New York City (longitude, latitude)
    ('Connecticut Capital', ST_SetSRID(ST_MakePoint(-72.685093, 41.7637), 4326)), -- Connecticut's Capital (longitude, latitude)
    ('Miami', ST_SetSRID(ST_MakePoint(-80.191790, 25.7617), 4326)), -- Miami (longitude, latitude)
    ('San Francisco', ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)), -- San Francisco (longitude, latitude)
    ('Paris', ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326)), -- Paris (longitude, latitude)
    ('Lagos', ST_SetSRID(ST_MakePoint(3.3792, 6.5244), 4326)); -- Lagos (longitude, latitude)

query closest points with longitude and latitude from google maps (srid: 3857)

SELECT 
    name,
    ST_Distance(
        geom,
        ST_Transform(ST_SetSRID(ST_MakePoint(:longitude, :latitude), 4326), 3857)
    ) / 1000 AS distance
FROM 
    location
WHERE 
    ST_DWithin(
        point,
        ST_Transform(ST_SetSRID(ST_MakePoint(:longitude, :latitude), 4326), 3857),
        :radius_in_meters
    )
ORDER BY 
    geom <-> ST_Transform(ST_SetSRID(ST_MakePoint(:longitude, :latitude), 4326), 3857)
LIMIT 10;