将postgresql the_geom函数添加到php插入

Question

I am trying to make an import script from csv into post_gis, everything imports until I add the_geom column. I cannot figure where to place the st_geomfromtext('POINT( VARIABLE ,27700)') and keep getting errors to do with the quotes, so I know I am doing something wrong with the placement of them.

This script (full version) was created following this John Boy Tutorial

 $sql ="INSERT INTO teams_tbl (team_id, name,  the_geom) VALUES 
            ( 
                '".addslashes($data[0])."', 
                '".addslashes($data[1])."', 
                'st_geomfromtext('POINT(".addslashes($data[2])."27700)')'

          ) 
        "; 

Answer 1

To help you get over the quotes placement problem I suggest using prepared statements:

// Prepare a query for execution
$result = pg_prepare(
    $dbconn,
    "my_query",
    'INSERT INTO teams_tbl (team_id, name, the_geom) '
    . 'VALUES ($1, $2, st_geomfromtext($3))');

// Prepare the string to be inserted as the_geom
$the_geom = "POINT(" . $data[2]. ", 27700)";

// Execute the prepared query using your variables.
// Note that it is not necessary to escape them
$result = pg_execute($dbconn, "my_query", $data[0], $data[1], $the_geom);

You can execute the same prepared query later with different parameters which can be very useful inside a loop.

Note: I do not know what kind of data is stored in your $data variable, but in this case you can be sure that the problem will not be the quotes, it will be whatever is in your data.