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?

  • 1
    read through the knn tag to get familiar with the PostGIS concept of (K) Nearest Neighbor searches; this answer has an example at the bottom with the syntax you are looking for. – geozelot Sep 10 '19 at 10:04

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]);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.