make my Json string parsed openlayers.format.geojson

i need to change a Json string into Geojson format. right now i am using Json_encode() function to convert sql query result into json string (as given in this link JSON encode MySQL results).

i want to make this geojso开发者_StackOverflow社区n string readable by openlayers.

how can i use json _encode for this purpose ? or is there a different function which i can use for this? thanks in advance.

GeoJSON format is fairly simple so I would go ahead and loop through all raw data that your SQL query returns and build a string in GeoJSON format manually. Then you pass this string from you server side code to the client where you parse it to javascript object so that OpenLayers can consume it.

Here's example in Ruby I've done a while ago. Hope you get the idea and can do the same in PHP:

    json = "{ \"type\": \"FeatureCollection\", \"features\": ["

    layer.get_feature_count.times do |i|
        feature = layer.get_feature(i)
        json += "{\"type\":\"Feature\", \"id\":" + feature.get_fid.to_s + ", \"properties\": {"

        feature_defn = layer.get_layer_defn

        feature_defn.get_field_count.times do |j|
            field = feature_defn.get_field_defn(j)      
            json += "\"" + field.get_name_ref + "\":" + "\"" + feature.get_field(j).to_s + "\""

            if j+1 < feature_defn.get_field_count
                json += ", "
            end
        end

        json += "}, " 
        #end of properties

        geom = feature.get_geometry_ref 
        json += "\"geometry\":" + geom.export_to_json
        #end of geometry

        json += "}"

        if i+1 < layer.get_feature_count
            json += ", "
        end 
    end

    json += "]}"

(I assume from your post that you're using PHP).

If you fetch your spatial data as WKT (1), you can use the GeoJSON class(2) from the PHP version of mapfish ( this class could be used w/o mapfish ).

You'll just have to write your own adapter for this.

HTH,

• 1 http://dev.mysql.com/doc/refman/5.0/en/functions-to-convert-geometries-between-formats.html#function_astext
• 2 https://github.com/tonio/sfMapFishPlugin/tree/master/lib/GeoJSON

I've actually have been working on this same issue over the past few weeks. Important to note: I'm not using a geospatial database, just normal ol' storage for me (MySQL to be exact).

Here is how I accomplished it (without the messy concat statements):

SQL Table Description:

CREATE TABLE IF NOT EXISTS `conditions` (
  `conditionsID` int(10) unsigned NOT NULL auto_increment,
  `ICAO` varchar(4) default NULL,
  `LOCATION` varchar(50) default NULL,
  `LATITUDE` float default NULL,
  `LONGITUDE` float default NULL,
  `TEMPERATURE` float default NULL,
  `TEMPERATURE_F` float default NULL,
  `TEMPERATURE_C` float default NULL,
  `FEELS_LIKE` float default NULL,  
  PRIMARY KEY  (`conditionsID`),
  KEY `ICAO` (`ICAO`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=56274738 ;

I can then use the json_encode PHP library (first available in 5.2, better options for 5.3) to encode a json object from a PHP object. No more working with strings, instead I can work with pseudo-objects (I prefer arrays, easy to manage on the fly). 5.3 brings the option to pretty print as well.

My PHP server code:

$feature_collection = array();
$feature_collection['type'] = "FeatureCollection";
$feature_collection['features'] = array();

$con = mysql_connect($DB_URI, $DB_USER, $DB_PASS);
if (!$con) {
    $errorMessage = "Error: Could not connect to data database.  Error: " . mysql_error();
} else {
    mysql_select_db("data", $con);
    $result = mysql_query("SELECT * FROM `conditions` WHERE LATITUDE > $LAT AND LONGITUDE > $LON GROUP BY LOCATION;");

    while ($row = mysql_fetch_assoc($result)) {
        $feature_collection['features'][] = createFeature($row['conditionsID'],
                                                          $row['LATITUDE'],
                                                          $row['LONGITUDE'],
                                                          $row);
    }
}


echo json_encode($feature_collection);

function createFeature($ID, $lat, $lon, $data)
{
    unset($data["LATITUDE"]);
    unset($data["LONGITUDE"]);
    $feature = array();
    $feature["type"] = "Feature";
    $feature["id"] = $ID;

    $feature["geometry"] = array();
    $feature["geometry"]["type"] = "Point";
    $feature["geometry"]["coordinates"] = array($lon+0, $lat+0);

    $feature["properties"] = $data;
    $feature["properties"]["visible"] = "true";

    return $feature;
}

Notice the "FeatureCreate" function to allow me to build a feature quickly. Also note I add almost the entire row into the properties object. Overkill/Redundant, but makes for simpler code.

Hopefully this snippet will help. It does my job (and I'd be happy to listen to others suggestions).