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

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 = "" +

                 "" +
                 "" +
                 "" +
                 "";
    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 ?

 
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)


Subscribe to:
Post Comments (Atom)



arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...









pyqgis - Setting layer visibility in QGIS Python API?
Im having a problem toggling the visibility of map layers in pyqgis (2.4). I have a simple interface, just the map canvas, no legend, and ba...










postgresql - How get intersection of polygons in the same table by
PostGIS?
I have a single table of over 1000 polygons in postgreSQL (with PostGIS), and I have a field "evaluation_type" that only receives ...










Create a center line between 2 polyline - QGIS
I want to create a center line for a river (polygon). I thought about simply creating a new line manually, but it would be a long and imperf...















Search This Blog



Name:
 
 
 
Address:
 
 
Type:
 
" +
                 "bar" +
                 "restaurant" +
                 "