[OpenLayers-Users] postgresql table to Geojson and diplay it in openlayers

[Glenn Mullett]

Hi Javad

What geojson string is returned by the php? - is it correctly formatted?

>>   $sql = "SELECT gid, ST_AsGeoJSON(the_geom) AS geojson,
length(the_geom) AS length FROM ".TABLE.""

I usually format my geojson with properties at this point already (in a
plpgsql function)...

the wrapper function I wrote for this:
 
CREATE OR REPLACE FUNCTION geojson_attrib(table_name character varying,
gid character varying, cs character varying, ce character varying,
schema_name character varying)
  RETURNS SETOF character varying AS
$BODY$
 select 'SELECT ''{' || $3 || '"geometry":'' ||
st_asgeojson(transform(the_geom, 900913),6) || 
		'',"type":"Feature",' || $4 || '"properties":{' 
	|| array_to_string(ARRAY(
	SELECT '"' || c.column_name || '":"''||coalesce("'|| c.column_name ||
'"::varchar,''null'')||''"'
        FROM information_schema.columns As c
            WHERE table_schema= $5 and table_name = $1 
            AND  c.column_name NOT IN('gid', 'the_geom')
    ), ',') || '},"gid":"'' || gid || ''"}'' as v FROM ' || $5 || '.' ||
$1 || ' where gid in(' || $2 || ')' as v;
$BODY$
  LANGUAGE sql VOLATILE

I use cs and ce to (comment start, comment end) remove geometries if I
only want json, so you can set them to: ''


To display in Openlayers you need to add a geojson reader:

//load the php result  into variable str using an xmlhttprequest ( I use
Ext to do this)

//geojson reader
var geojson_format = new OpenLayers.Format.GeoJSON();

//create  a vector layer
var vector_sites = new OpenLayers.Layer.Vector(); 

//add the vector to the ol map
map.addLayer(vector_sites);

// read the geojson string into the layer
vector_sites.addFeatures(geojson_format.read(str)); 

Alternatively you can use GeoExt and just load the php result into a
FeatureStore that you point to a vector layer

    var  store = new GeoExt.data.FeatureStore({
        layer: vector_layer,
        fields:fields,
        proxy: new GeoExt.data.ProtocolProxy({
            protocol: new OpenLayers.Protocol.HTTP({
                url: store_url,
               format: new OpenLayers.Format.GeoJSON()
            })
        }),
        
        autoLoad: true
    });

Regards


Glenn Mullett
BTech(Nature Cons) PGDip(UNIGIS)
H.O.D. GIS
Lidwala Consulting Engineers
email:gmullett at lidwala.com

___________________________________________________________________________________________________________________________________________________
On Wed, 2011-08-24 at 19:32 +0200, javad sadidi wrote:

> hi 
> I have a table in postgresql that it has topology and I want to
> display it as line in openlayers.
> I use php to connect and convert my data to Geojson. this the
> codes.but I can not  get the result.who can help me.where is wrong in
> my codes,this is the code.
> 
> 
> <?php
> 
>    // Database connection settings
>    define("PG_DB"  , "tehran");
>    define("PG_HOST", "localhost"); 
>    define("PG_USER", "postgres");
>    define("PG_PORT", "5432"); 
>    define("TABLE", "close");
> 
>   
> ?>
> 
> <?php
> // Connect to database
>    $dbcon = pg_connect("dbname=".PG_DB." host=".PG_HOST."
> user=".PG_USER);
>    $sql = "SELECT gid, ST_AsGeoJSON(the_geom) AS geojson,
> length(the_geom) AS length FROM ".TABLE.""
> 
>    // Perform database query
>    $query = pg_query($dbcon,$sql); 
>    
> ?>
> 
> <?php
> 
>    // Return route as GeoJSON
>    $geojson = array(
>       'type'      => 'FeatureCollection',
>       'features'  => array()
>    ); 
>   
>    // Add edges to GeoJSON array
>    while($edge=pg_fetch_assoc($query)) {  
> 
>       $feature = array(
>          'type' => 'Feature',
>          'geometry' => json_decode($edge['geojson'], true),
>          'crs' => array(
>             'type' => 'EPSG',
>             'properties' => array('code' => '4326')
>          ),
>          'properties' => array(
>             'id' => $edge['id'],
>             'length' => $edge['length']
>          )
>       );
>       
>       // Add feature array to feature collection array
>       array_push($geojson['features'], $feature);
>    }
> 
>    // Close database connection
>    pg_close($dbcon);
> 
>    // Return routing result
>    header('Content-type: application/json',true);
>    echo json_encode($geojson);
>    
> ?>
> 
> 
> _______________________________________________
> Users mailing list
> Users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/openlayers-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/openlayers-users/attachments/20110824/72c61d01/attachment-0001.html