1

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. – ThingumaBob Sep 10 at 10:04
1

Using example from here...

Your input example:

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

Create a point converter, using Custom Type Formatting:

const asPoint = p => ({
    toPostgres: () => pgp.as.format('ST_MakePoint(${lat}, ${lng})', 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]);

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.