[GIS] How to insert a point into PostGIS from PHP

PHPpostgis

For the following line, I received an error in my PHP page, but the query is OK when I run it from pgmyadmin.

I am trying to insert a point into my PostGIS from PHP which contains name, address and a geometric point. Here, p_id is primary key and it is an integer.

$sql = "INSERT INTO app (p_id, p_fname, p_lname, p_saddr, p_caddr, the_geom) VALUES(intval($p_id), $fname, $lname, $street, $city, ST_GeomFromText(POINT($long $lat), 4326))";
$result = pg_query($dbh, $sql);

Error:

Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "," LINE 1: ...me, p_saddr, p_caddr, the_geom) VALUES(intval(5), , , , , ST... ^ in /Applications/XAMPP/xamppfiles/htdocs/HostMobileApp/requestProcessor.php on line 66
Error in SQL query: ERROR: syntax error at or near "," LINE 1: ...me, p_saddr, p_caddr, the_geom) VALUES(intval(5), , , , , ST... ^

Best Answer

This a really a duplicate of your previous question, and my answer is the same. Don't mix SQL and WKT! Use ST_MakePoint if you need to embed parameters into an SQL statement.

$sql = 'INSERT INTO app(p_id, p_fname, p_lname, p_saddr, p_caddr, the_geom) '
 . 'VALUES(intval($p_id), $fname, $lname, $street, $city, ST_SetSRID(ST_MakePoint($long, $lat), 4326))';
$result = pg_query($dbh, $sql);

Related Solutions

[GIS] Using PostGIS trigger

There are a few issues to consider. First, have you considered using a VIEW? I.e.

CREATE OR REPLACE VIEW places_with_coods AS
  SELECT gid, ST_Y(geo::geometry) AS lat, ST_X(geo::geometry) AS lon
  FROM places;

However, if you want to use triggers, these changes will make things work. The important pieces being that NEW is the row, which can be modified and returned. Also, use ST_Y and not new.st_y. Lastly, the trigger must be a BEFORE trigger, since it modifies the record before it is stored.

CREATE OR REPLACE FUNCTION update_tg2()
RETURNS trigger AS
$$
BEGIN
  NEW.lat := ST_Y(NEW.geo::geometry);
  NEW.lon := ST_X(NEW.geo::geometry);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
DROP TRIGGER IF EXISTS triger_coords2 on places;
CREATE TRIGGER triger_coords2 BEFORE INSERT OR UPDATE ON places
  FOR EACH ROW EXECUTE PROCEDURE update_tg2();

[GIS] Loading raster in postgis via raster2pgsql

Here is a way to do it with just PHP, not tested. Two useful links: Writing binary files to postgres - https://stackoverflow.com/questions/11162695/store-blob-like-data-in-postgresql

-- this is a postgis function that converts a regular raster into a postgis raster http://postgis.net/docs/manual-dev/RT_ST_FromGDALRaster.html

You first of all need to do this with a parameterized query. What you are doing will not work:

 <?php $fcontents = file_get_contents($_FILES["fichier"]["tmp_name"]);
   $db = pg_connect('host=localhost port=5432 dbname=gisdb user=postgres password=******');         
   $sql = 'INSERT INTO eau_param_hydro(filename,rast)
        VALUES($1, ST_FromGDALRaster( ($2)::bytea ) );';
   $params = array($_FILES["fichier"]["name"], pg_escape_bytea($fcontents) );

  $result = pg_query_params($db, $sql, $params);
  pg_close();  ?>

Best Answer

Related Solutions

[GIS] Using PostGIS trigger

[GIS] Loading raster in postgis via raster2pgsql

Related Question