1

I am able to retrieve JSON from a PostgreSQL database using this command:

SELECT row_to_json(fc)
 FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
 FROM (SELECT 'Feature' As type
    , ST_AsGeoJSON(lg.geom)::json As geometry
    , row_to_json((SELECT l FROM (SELECT gid) As l
      )) As properties
   FROM s50d.aptirupatiff50dsc01042014 As lg   ) As f )  As fc;

Now I want to run this command from the PHP code like the below example:

<?php
    $db = pg_connect("host=172.31.4.67 port=5432 dbname=geoserver user=postgres password=ndem$123") or die ("cant");

    $mapa = "SELECT * FROM s50d.aptirupatiff50dsc01042014";   
    $dbquery = mysql_query($mapa,$db);
    $geojson = array( 'type' => 'FeatureCollection', 'features' => array());

    while($row = mysql_fetch_assoc($dbquery)){  
    $marker = array(
                    'type' => 'Feature',
                    'features' => array(
                        'type' => 'Feature',
                        'properties' => array(
                            'title' => "".$row[name]."",
                            'marker-color' => '#f00',
                            'marker-size' => 'small'
                            //'url' => 
                            ),
                        "geometry" => array(
                            'type' => 'Point',
                            'coordinates' => array( 
                                            $row[lat],
                                            $row[lng]
                            )
                        )
                    )
        );
    array_push($geojson['features'], $marker['features']);
    }
?>

Is it possible? If yes, please suggest related examples.

I have followed below example code:

<?php 
    $db = pg_connect("host=172.31.4.67 port=5432 dbname=geoserver user=postgres password=ndem$123") or die ("cant");
    //get query parameters with some basic protection against SQL Injection
    //$table = pg_escape_string($_GET['table']) or die('Missing required parameter: table');
    $srid = intval(isset($_GET['srid']) ? $_GET['srid'] : 4326);
    //get non-geometry column names
    $col_query = "SELECT string_agg('\"'|| column_name::text || '\"',',') FROM information_schema.columns WHERE table_name = '$table'";
    $col_query .=" AND column_name NOT IN(SELECT f_geometry_column FROM geometry_columns WHERE f_table_name = '$table')";
    $cols = $db->query($col_query)->fetch()[0];
    //get data as GeometryCollection
    $data_query = "SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features";
    $data_query .= " FROM (SELECT 'Feature' As type";
    $data_query .= ", ST_AsGeoJSON(ST_Transform(lg.geom,$srid))::json As geometry";
    $data_query .= ", row_to_json(lp) As properties";
    $data_query .=" FROM s50d.aptirupatiff50dsc01042014 As lg";
    //$data_query .= " INNER JOIN (SELECT $cols FROM \"$table\") As lp";
    $data_query .= " ON lg.id = lp.id  ) As f )  As fc";
    $data = $db->query($data_query)->fetch()[0];
    header('Content-Type: application/json');
    echo $data;
?>

I have tried this code and getting error at last line:

<?php
    //$db = pg_connect("host=172.31.4.61 port=5432 dbname=geoserver user=postgres password=ndem$123") or die ("cant");
    $db = pg_connect("host=172.31.4.61 port=5432 dbname=geoserver user=postgres password=ndem$123") or die ("cant");

    $mapa = "SELECT * FROM s50d.aptirupatiff50dsc01042014";
    $dbquery = pg_query($db,$mapa);

    //SELECT row_to_json(fc)
    //FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
    //FROM (SELECT 'Feature' As type
    //  , ST_AsGeoJSON(lg.geom)::json As geometry
    //   , row_to_json((SELECT l FROM (SELECT gid) As l
    //     )) As properties
    //  FROM s50d.aptirupatiff50dsc01042014 As lg   ) As f )  As fc;
    $srid = intval(isset($_GET['srid']) ? $_GET['srid'] : 4326);

    //get data as GeometryCollection
    $data_query = "SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features";
    $data_query .= " FROM (SELECT 'Feature' As type";
    $data_query .= ", ST_AsGeoJSON(lg.geom)::json  As geometry";
    $data_query .= ", row_to_json((SELECT l FROM (SELECT gid) As l";
    $data_query .="  )) As properties";
    $data_query .= " FROM s50d.aptirupatiff50dsc01042014 As lg   ) As f )  As fc";

    $data = $db->pg_fetch_result($data_query)->fetch()[0];
    header('Content-Type: application/json');
    echo $data;
?>

ERROR line in the above code is:

$data = $db->pg_fetch_result($data_query)->fetch()[0];
  • second example i have tried but i didnt know how to pass the (SELECT l FROM (SELECT gid) As l in it – user28536 Oct 6 '16 at 9:09
  • how i have pass $data = $db->pg_fetch_result($data_query)->fetch()[0]; – user28536 Oct 6 '16 at 11:37
  • I can able to retrive data from the query – user28536 Oct 7 '16 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.