<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.30.3">
</HEAD>
<BODY>
Hi Javad<BR>
<BR>
What geojson string is returned by the php? - is it correctly formatted?<BR>
<BR>
>> $sql = "SELECT gid, ST_AsGeoJSON(the_geom) AS geojson, length(the_geom) AS length FROM ".TABLE.""<BR>
<BR>
I usually format my geojson with properties at this point already (in a plpgsql function)...<BR>
<BR>
the wrapper function I wrote for this:<BR>
<BR>
CREATE OR REPLACE FUNCTION geojson_attrib(table_name character varying, gid character varying, cs character varying, ce character varying, schema_name character varying)<BR>
RETURNS SETOF character varying AS<BR>
$BODY$<BR>
select 'SELECT ''{' || $3 || '"geometry":'' || st_asgeojson(transform(the_geom, 900913),6) || <BR>
'',"type":"Feature",' || $4 || '"properties":{' <BR>
|| array_to_string(ARRAY(<BR>
SELECT '"' || c.column_name || '":"''||coalesce("'|| c.column_name || '"::varchar,''null'')||''"'<BR>
FROM information_schema.columns As c<BR>
WHERE table_schema= $5 and table_name = $1 <BR>
AND c.column_name NOT IN('gid', 'the_geom')<BR>
), ',') || '},"gid":"'' || gid || ''"}'' as v FROM ' || $5 || '.' || $1 || ' where gid in(' || $2 || ')' as v;<BR>
$BODY$<BR>
LANGUAGE sql VOLATILE<BR>
<BR>
I use cs and ce to (comment start, comment end) remove geometries if I only want json, so you can set them to: ''<BR>
<BR>
<BR>
To display in Openlayers you need to add a geojson reader:<BR>
<BR>
//load the php result into variable str using an xmlhttprequest ( I use Ext to do this)<BR>
<BR>
//geojson reader<BR>
var geojson_format = new OpenLayers.Format.GeoJSON();<BR>
<BR>
//create a vector layer<BR>
var vector_sites = new OpenLayers.Layer.Vector(); <BR>
<BR>
//add the vector to the ol map<BR>
map.addLayer(vector_sites);<BR>
<BR>
// read the geojson string into the layer<BR>
vector_sites.addFeatures(geojson_format.read(str)); <BR>
<BR>
Alternatively you can use GeoExt and just load the php result into a FeatureStore that you point to a vector layer<BR>
<BR>
var store = new GeoExt.data.FeatureStore({<BR>
layer: vector_layer,<BR>
fields:fields,<BR>
proxy: new GeoExt.data.ProtocolProxy({<BR>
protocol: new OpenLayers.Protocol.HTTP({<BR>
url: store_url,<BR>
format: new OpenLayers.Format.GeoJSON()<BR>
})<BR>
}),<BR>
<BR>
autoLoad: true<BR>
});<BR>
<BR>
Regards<BR>
<BR>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
<BR>
<B>Glenn Mullett</B><BR>
<I><FONT SIZE="1"><FONT COLOR="#333333">BTech(Nature Cons) PGDip(UNIGIS)</FONT></FONT></I><BR>
<FONT SIZE="2"><FONT COLOR="#333333">H.O.D. GIS</FONT></FONT><BR>
<FONT SIZE="2"><FONT COLOR="#333333">Lidwala Consulting Engineers</FONT></FONT><BR>
<FONT SIZE="2">email:<A HREF="mailto:gmullett@lidwala.com">gmullett@lidwala.com</A></FONT><BR>
<BR>
<FONT COLOR="#808080">___________________________________________________________________________________________________________________________________________________</FONT>
</TD>
</TR>
</TABLE>
On Wed, 2011-08-24 at 19:32 +0200, javad sadidi wrote:<BR>
<BLOCKQUOTE TYPE=CITE>
hi <BR>
I have a table in postgresql that it has topology and I want to display it as line in openlayers.<BR>
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.<BR>
<BR>
<BR>
<?php<BR>
<BR>
// Database connection settings<BR>
define("PG_DB" , "tehran");<BR>
define("PG_HOST", "localhost"); <BR>
define("PG_USER", "postgres");<BR>
define("PG_PORT", "5432"); <BR>
define("TABLE", "close");<BR>
<BR>
<BR>
?><BR>
<BR>
<?php<BR>
// Connect to database<BR>
$dbcon = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER);<BR>
$sql = "SELECT gid, ST_AsGeoJSON(the_geom) AS geojson, length(the_geom) AS length FROM ".TABLE.""<BR>
<BR>
// Perform database query<BR>
$query = pg_query($dbcon,$sql); <BR>
<BR>
?><BR>
<BR>
<?php<BR>
<BR>
// Return route as GeoJSON<BR>
$geojson = array(<BR>
'type' => 'FeatureCollection',<BR>
'features' => array()<BR>
); <BR>
<BR>
// Add edges to GeoJSON array<BR>
while($edge=pg_fetch_assoc($query)) { <BR>
<BR>
$feature = array(<BR>
'type' => 'Feature',<BR>
'geometry' => json_decode($edge['geojson'], true),<BR>
'crs' => array(<BR>
'type' => 'EPSG',<BR>
'properties' => array('code' => '4326')<BR>
),<BR>
'properties' => array(<BR>
'id' => $edge['id'],<BR>
'length' => $edge['length']<BR>
)<BR>
);<BR>
<BR>
// Add feature array to feature collection array<BR>
array_push($geojson['features'], $feature);<BR>
}<BR>
<BR>
// Close database connection<BR>
pg_close($dbcon);<BR>
<BR>
// Return routing result<BR>
header('Content-type: application/json',true);<BR>
echo json_encode($geojson);<BR>
<BR>
?><BR>
<BR>
<PRE>
_______________________________________________
Users mailing list
<A HREF="mailto:Users@lists.osgeo.org">Users@lists.osgeo.org</A>
<A HREF="http://lists.osgeo.org/mailman/listinfo/openlayers-users">http://lists.osgeo.org/mailman/listinfo/openlayers-users</A>
</PRE>
</BLOCKQUOTE>
</BODY>
</HTML>