7

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

  • 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? – RyanDalton Apr 11 '14 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 – Pragnesh Patel Apr 11 '14 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. – Mahmoodvcs Dec 18 '14 at 15:17
2

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);

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • from SQL server table... – Magno C Jul 29 '16 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 '16 at 16:22
0

SQL Server 2016 has FOR JSON clause that can be used to format results as JSON. Here is example how to convert spatial columns to GeoJSON http://blogs.msdn.com/b/sqlserverstorageengine/archive/2016/01/05/returning-spatial-data-in-geojson-format.aspx

  • 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. – JP Hellemons Apr 18 '16 at 13:35
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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