9

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

3
  • Does the table include a spatial data type (a geometry), or just lat/long fields? Any other 3rd party addins like ArcSDE? Can you explain your use of the "geoexplorer" tag? Apr 11, 2014 at 15:32
  • @RyanDalton : Yes table has geometry column as well. No ArcSDE. We are going to use GEOExplorer for standard service run from sql. But if we manage to directly use GeoJson then we can by pass GeoExplorer bit for lite version of our application Apr 11, 2014 at 15:37
  • You can use GeoJSON.Net. I have no experience with it. But I'm planning to work with it. I'll let you know it's usefulness when I do. Dec 18, 2014 at 15:17

3 Answers 3

4

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);
2
  • from SQL server table...
    – Magno C
    Jul 29, 2016 at 15:59
  • Should have specified MS SQL Server table in the question. The question can be interpreted as a generic one
    – arcsum
    Sep 4, 2016 at 16:22
0

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;
1
0

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.

1
  • I know, but the geography data type is date from sql 2008 (r2) so this for json path does not work for 2008 r2, 2012 etc. Apr 18, 2016 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.