Search code examples
phppostgisgdalogr

Why can't PHP insert new geometry on PostGIS?

I'm using ogr2ogr to upload a GPX file to PostGIS.

When I do it on command line (Ubuntu 14.04), it works fine.

ogr2ogr -append -f PostgreSQL PG:"host=... dbname=... user=... password=..." 2016-04-26_15-32-01.xml

When I do it on PHP (5.5.9),

exec("ogr2ogr -append -f PostgreSQL PG:\"host=".HOST." dbname=".DATABASE." user=".USER." password=".PASSWORD."\" /var/www/html/plantas/gps/2016-04-26_15-32-01.xml 2>&1",$output,$retval);

it gives the following error:

ERROR 1: INSERT command for new feature failed.
ERROR: Geometry SRID (0) does not match column SRID (4326)

Command: INSERT INTO "waypoints" ("wkb_geometry" , "ele", "time", "name", "cmt", "desc", "sym", "type", "gpxx_waypointextension", "wptx1_waypointextension", "ctx_creationtimeextension") VALUES ('010100000000008C76C2DF4EC00100C0A13A450E40'::GEOMETRY, 594, '2015/11/16 17:22:09+00', 'ROP_2904', '08-NOV-15 9:56:51
PONTO 60', '08-NOV-15 9:56:51
PONTO 60', 'Flag, Blue', 'user', ' SymbolAndName ', ' SymbolAndName ', ' 2015-11-16T17:22:09Z ') RETURNING "ogc_fid"
ERROR 1: Terminating translation prematurely after failed
translation of layer waypoints (use -skipfailures to skip errors)

What am I missing here?

EDIT

I tried Mike T suggestion:

$linha = "ogr2ogr -append -f PostgreSQL PG:\"host=".HOST." dbname=".DATABASE." user=".USER." password=".PASSWORD."\" -s_srs EPSG:4326 /var/www/html/plantas/gps/2016-04-26_15-32-01.xml 2>&1";
// or $linha = "ogr2ogr -s_srs EPSG:4326 -append -f PostgreSQL PG:\"host=".HOST." dbname=".DATABASE." user=".USER." password=".PASSWORD."\" /var/www/html/plantas/gps/2016-04-26_15-32-01.xml 2>&1";
exec($linha,$output,$retval);

to no avail:

ERROR 1: INSERT command for new feature failed.
ERROR: Geometry SRID (0) does not match column SRID (4326)

What's really strange is that exactly the same command works on terminal. So I think PHP might have some issue, perhaps a missing library or permission?

EDIT 2

Now I'm seeing there are some installations where the user need to run

psql -d yourdatabase -f postgis.sql

In my computer I did only the

create extension postgis;
create extension postgis_topology;

Gonna see if that's the problem.

EDIT 3 - SOLVED!

I started it all again:

  1. restored a previous database from backup;
  2. create extension postgis;
  3. create extension postgis_topology;
  4. ogr2ogr from console creates additional tables (like waypoints);
  5. after this I need to grant access in those additional tables to php/apache user.
Solution

  • I started it all again:

    1. restored a previous database from backup;
    2. create extension postgis;
    3. create extension postgis_topology;
    4. ogr2ogr from console creates additional tables (like waypoints);
    5. after this I need to grant access in those additional tables to php/apache user.