[GIS] How to insert google maps lat/lng into postgis in geometry datatype

google mapsPHPpostgis

I came across:
https://developers.google.com/maps/articles/phpsqlinfo_v3
I've changed the code a bit and all process is well done for postgis database. I get the data from infobox when user clicked.

..

 var map = new google.maps.Map(document.getElementById("map_canvas"), options);
      var html = "<table>" +
                 "<tr><td>Name:</td> <td><input type='text' id='name'/> </td> </tr>" +
                 "<tr><td>Address:</td> <td><input type='text' id='address'/></td> </tr>" +
                 "<tr><td>Type:</td> <td><select id='type'>" +
                 "<option value='bar' SELECTED>bar</option>" +
                 "<option value='restaurant'>restaurant</option>" +
                 "</select> </td></tr>" +
                 "<tr><td></td><td><input type='button' value='Save & Close' onclick='saveData()'/></td></tr>";
    infowindow = new google.maps.InfoWindow({
     content: html
    });

I get the data from another php via url;

// Insert new row with user data
$query = sprintf("INSERT INTO markers " .
         " (id, name, address, lat, lng, type ) " .
         " VALUES (DEFAULT, '%s', '%s', '%s', '%s', '%s');",
         pg_escape_string($name),
         pg_escape_string($address),
         pg_escape_string($lat),
         pg_escape_string($lng),
         pg_escape_string($type));

$result = pg_query($query);

But the problem is i want to store google maps lat/lng not as float, but as geometry(propably point) data type in a column. How should i change the code or philosophy ?

Best Answer

ST_GeomFromText is the key

http://postgis.net/docs/ST_GeomFromText.html

Example

Insert into markers (name, the_geom) VALUES ('Zion National Park', ST_GeomFromText('POINT(-112.68142 37.22299)', 4326));

SRID (4326) is required.

You can as create a trigger when an event happens if updating from php

http://www.postgresql.org/docs/9.1/interactive/sql-createtrigger.html (UPDATE function recommended)

Related Question