[OpenLayers-Users] postgresql table to Geojson and diplay it
in openlayers
Glenn Mullett
gmullett at lidwala.com
Fri Aug 26 03:11:13 EDT 2011
Hi Javad
>>>if you have clear example in details to display lines please
introduce me
That is *exactly* what I have outlined below...this *is* how you display
a geojson feature in OL that comes from postgis - but first you need the
geojson - that is where your php comes in - it is grabbing the response
from postgis and returning it to the browser.
>>>I am new and my knowledge is not so much
...time to start experimenting with examples and reading api
documentation (all available online @ openlayers.org)
there are examples of how to display pretty much anything (kml, gml,
geojson, ArcGIS server, WMS, WFS etc etc)
>>> I dont know what you mean about the resulted geojsonstring by php
What does your php script return ? (point your browser to
http://localhost/wherever/yourscript.php ) (remember to echo the
returned rst result at the end of your php script)
It *must* return a text string that is formatted as geojson ...
i.e.
{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "id": 0, "geometry": { .... },
"properties": { .....} } ,
{ ...etc...}
]
}
(use qgis and save a shp file as geojson to see what I mean)
You need to understand what your scripts etc are doing (what php does,
how to use with postgres, how OL works etc) as well as the architecture
of the app you are trying to develop. The forum can just point you in
the right direction - not develop the app for you.
Regards
--
Glenn Mullett
BTech(Nature Cons) PGDip(UNIGIS)
H.O.D. GIS
Lidwala Consulting Engineers
email:gmullett at lidwala.com
____________________________________________________________________________________________________________________________________________________
Lidwala Consulting Engineers (SA) (Pty) Ltd email Disclaimer and
confidentiality note:
This e-mail, its attachments and any rights attaching hereto are, unless
the content clearly indicates otherwise, the property of Lidwala
Consulting Engineers (SA) (Pty) Ltd and its subsidiaries. It is
confidential, private and intended for only the addressee. Should you
not be the addressee and receive this e-mail by mistake, kindly notify
the sender, and delete this e-mail immediately. Do not disclose or use
it in any way. Views and opinions expressed in this e-mail are those of
the sender unless clearly stated as those of Lidwala Consulting
Engineers (SA) (Pty) Ltd. Lidwala Consulting Engineers (SA) (Pty) Ltd
accepts no liability for any loss or damages howsoever incurred, or
suffered, resulting or arising, from the use of this email or its
attachments. Lidwala Consulting Engineers (SA) (Pty) Ltd does not
warrant the integrity of this e-mail nor that it is free of errors,
viruses, interception or interference.
____________________________________________________________________________________________________________________________________________________
On Wed, 2011-08-24 at 21:59 +0200, javad sadidi wrote:
> hi
> I have the table and want to display it as lines in openlayer.
> I am new and my knowledge is not so much.
> I send you the php code, please examine it and say to me how can I
> diplay it as Geojson or maybe kml in openlayers.if you have clear
> example in details to display lines please introduce me.
> I dont know what you mean about the resulted geojsonstring by php .
>
>
>
> On Wed, Aug 24, 2011 at 8:00 PM, Glenn Mullett <gmullett at lidwala.com>
> wrote:
>
> 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/20110826/41643e86/attachment-0001.html
More information about the Users
mailing list