How can we generate GeoJSON from SQL data with latlong

Question

Is there any way we can create GeoJSON from SQL server table with Latitude & Longitude Data?

Answer 1

I have used the PostGIS code with success from Bryan Mcbride's Github Page.

It also contains a MySQL version of the code and can also be used for other geometries like polygon and line.

Dissecting essential parts of the code for you:

Basically the code gets an SQL Query from the table. If your table has the lat-long pairs, you can fetch the data from the table by changing this line

$sql = 'SELECT *, public.ST_AsGeoJSON(public.ST_Transform((the_geom),4326),6) AS geojson FROM mytable';

Also , in case, this part of code can be used as template to get the lat long data via a request variable, if you pass it as an array for example.

if (isset($_GET['ll_array'])) {
//process the ll_array here
} 

Then the rest of the code changes the array format of the query result array into the typical geoJSON array format

while ($row = $rs->fetch(PDO::FETCH_ASSOC)) {

    $properties = $row;
    unset($properties['geojson']);
    unset($properties['the_geom']);

    $feature = array(
        'type' => 'Feature',
        'geometry' => json_decode($row['geojson'], true),
        'properties' => $properties
     );

    array_push($geojson['features'], $feature);
}

and sends the output as geoJSON

header('Content-type: application/json');
echo json_encode($geojson, JSON_NUMERIC_CHECK);

Answer 2

Following is a bit sample code using GeoJSON.NET https://github.com/jbattermann/GeoJSON.Net

SQL Data Loaded into dataset ds

var modelF = new List<GeoJSON.Net.Feature.Feature>();

foreach (DataRow dr in ds.Tables[0].Rows)
{
    var point = new GeoJSON.Net.Geometry.Point(new GeoJSON.Net.Geometry.GeographicPosition(Convert.ToDouble(dr["latitude"].ToString()), Convert.ToDouble(dr["longitude"].ToString())));
    var featureProperties = new Dictionary<string, object> { };

    foreach (DataColumn dataColumn in ds.Tables[0].Columns)
    {
        featureProperties.Add(dataColumn.ColumnName, dr[dataColumn].ToString());
    }
    modelF.Add(new GeoJSON.Net.Feature.Feature(point, featureProperties));
}
var fcol = new FeatureCollection(modelF);
var serializedData = JsonConvert.SerializeObject(fcol, Formatting.Indented, new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver(), NullValueHandling = NullValueHandling.Ignore });

return serializedData;

Answer 3

SQL Server 2016 has FOR JSON clause that can be used to format results as JSON. Here is an example how to convert spatial columns to GeoJSON.