How to pull lat/lng from a map canvas and update a database

Question

So if i want to pull out lat/lngs after user clicks on a map canvas (lat/lngs are pulled using any javascript API (leaflet, wax etc.) and add this to a postgresql database using a query, how do i do this?

I came across:

https://github.com/commandprompt/postgres-js

But how do you generally add values to a db on the fly?

Answer 1

I might be getting this wrong, but just to check, you aren't planning on using https://github.com/commandprompt/postgres-js on a client/user-facing website are you? It looks like it connects with a password provided, which means you'd be supplying the user with your db password!

All you need to store points that the user clicks is a backend that can insert data as Aragon said, and a simple PUT to e.g. a flask app -

Front end:

map = L.Map(...);
map.on('click', function(e) {
    $.ajax('someurl/putpoint/', {
      type: 'PUT',
      data: {
        lat: e.latlng.lat,
        lng: e.latlng.lng
      }
    });
});

Back end with e.g. http://flask.pocoo.org/ :

from flask import Flask
app = Flask(__name__)

@app.route('/someurl/putpoint/')
def putpoint():
    cursor = get_your_db_cursor()
    # NB: massively insecure accepting any random input from PUT!
    # Should use the params arg in execute() rather than format str
    cursor.execute("""
        INSERT INTO test(p_id, the_geom)
        VALUES(2, ST_GeomFromText('POINT(%s %s)', 4326));""" % (
        request.form['lng'], request.form['lat']))  

if __name__ == '__main__':
    app.run()

Answer 2

did you implement this insert function to js library you give?

PostGIS adding geomerty function:

 INSERT INTO test(p_id, the_geom)
 VALUES(2, ST_GeomFromText('POINT(10 20)', 4326));

Your postgres backend protocol insert function:

 db.prepare("INSERT INTO returning_test (val) VALUES (?) RETURNING id, val", function (sth) {
 sth.execute("text value", function(e, rs) {
 if (rs === undefined) {
 console.log("No data.");
 }
 else {
 console.log(sys.inspect(rs));
 }
 });
 });

i hope it helps you...

Answer 3

To add values to a datebase on the fly, break it up in three steps:

1) Catch the click and position in Javascript:

https://developers.google.com/maps/documentation/javascript/examples/event-simple

2) Send the position to a server using Ajax:

http://api.jquery.com/jquery.ajax/

3) Save the values in the database using PHP:

http://php.net/manual/en/function.pg-insert.php