Convert lat-long to geom using nodejs pg-promise?

Question

I have a DB that is used as a read only postgis DB. I also have an input of lat & long to search within my db(in which obviously the points are saved in geom type - using nodejs pg-promise if it matters, but i guess it doesnt as i only need to use the correct sql query)

is there a way of easily requesting the closest node from my db to the input in the form of lat & long?

Answer 1

Using example from here...

Your input example:

const place = {
    lng: -122.2652671,
    lat: 47.30995661
};

Create a point converter, using Custom Type Formatting:

const asPoint = p => ({
    toPostgres: () => pgp.as.format('ST_MakePoint(${lng}, ${lat})', p),
    rawType: true
});

Executing the query:

await db.oneOrNone(`SELECT * FROM table ORDER BY
            ST_StartPoint(geom) <-> ST_SetSRID($1, $2) LIMIT 1;`, [asPoint(place), 4326]);