2

I am building a simple API that also deal with geometries (store into postGIS). I am using knex-postgis to access the ST_ spatial functions in postGIS with knex.

I have used this example to insert a point and it work when its hard coded. But my lack of experience is leave me hanging, how can I make the query dynamic? I want to create a form with input for x and y value and send it to the ST_geomFromText function to save it to the geom type in the db. Is this when you would use parameters? Could someone point me in the right direction?

// insert a point
const sql1 = db.insert({
  id: 1,
  geom: st.geomFromText('Point(0 0)', 4326)
}).into('points').toString();
console.log(sql1);
// insert into "points" ("geom", "id") values 
(ST_geomFromText('Point(0 0)'), '1')

So far I have tried

router.post('/', (req, res, next) => {
  queries.create(req.body).then(poi => {
    res.json(poi[0]);
  });
});

Insert query

  create(poi) {
    const sql = db.insert(poi).returning('*').into('poi');
    return sql;
  },

In Postman I am sending this in its body

{
    "place": "test",
    "comments": "new",
    "numbers": 6,
    "geom": "st.geomFromText('Point(-71.064544 44.28787)', 4326)"
}

But get an error "Unhandled rejection error: parse error - invalid geometry" The hard coded object looks the same and is work fine.

I have a feeling that I am using the st.geomFromText wrong, but i dont I am not sure?

This is what i get if i console.log returned query

insert into "poi" ("comments", "geom", "numbers", "place") values ('new', 'st.geomFromText(''Point(-71.064544 44.28787)'', 4326)', 6, 'test')

(see how it does not change st.geom.. to st_geom..? Also the quotation marks around the ST function and the Point object is not right)

This string work when I run it in pgAdmin

insert into "poi" ("comments", "geom", "numbers", "place") values ('new', st_GeomFromText('Point(-71.064544 44.28787)', 4326), 6, 'test')

EDIT: I console.log the hardcoded version too. It does no appear to add the extra '' on the geometry value

insert into "poi" ("geom") values (ST_geomFromText('Point(-71.064544 44.28787)', 4326))

Any advice?

geogrow
  • 485
  • 2
  • 9
  • 26

1 Answers1

2

You pass coordinates from postman as string value in JSON object

"geom": "st.geomFromText('Point(-71.064544 44.28787)', 4326)"

And expect your code to transform it into a function call

st.geomFromText('Point(-71.064544 44.28787)', 4326)

What knex does in this case. It takes your string and convert it as it is string field (here you get a single quote ' replacement with double '' for Postgres to escape it).

What you can do. Pass your geometry to your API like this

"geom": "Point(-71.064544 44.28787)"

And in your route handler

create(poi) {
  const sql = db.insert({
    place: poi.place,
    comments: poi.comments,
    numbers: poi.numbers,
    geom: st.geomFromText(poi.geom, 4326)
  }).returning('*').into('poi');
  return sql;
},
coockoo
  • 2,294
  • 17
  • 26