Display oracle spatial data on a leaflet map

I wrote previously about how to convert oracle spatial data into GeoJSON. In this post I will take this  a step further and display that GeoJSONdata onto a leaflet map. A note before that though: The main sdo2geojson function return every record on the spatial table as a valid GeoJSON. That’s great, but not enough if you want to display the WHOLE table as a GeoJSON Feature collection. So I created a new sdo2geojson_partial function which will return an…erm… partial (and therefore not valid) GeoJSON strings which I could then concatenate through code to a valid feature collection object. You can download the updated oracle package here

The end result of this exercise would be to create a simple web site with a leaflet map that will display an oracle spatial layer at the time of load.

So I created a simple ASP.NET web site to demonstrate this. The main.aspx page is shown below.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />

    <!-- Bootstrap -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css" />
    <link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet-0.7.1/leaflet.css" />

    <style type="text/css">
        #map {
            height: 600px;
            width: 100%;

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/js/bootstrap.min.js"></script>
    <script type="text/javascript" src="http://cdn.leafletjs.com/leaflet-0.7.1/leaflet.js"></script>
    <script src="scripts/map_functions.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            map_props = init_map(40.587914266, 22.953796219, 13);
            map = map_props[0];
        <div class="container-fluid">
            <div class="row">
                <div class="col-md-6">
                    <div class="panel panel-default">
                        <div class="panel-heading">Map</div>
                        <div class="panel-body">
                            <div id="map"></div>
                <div class="col-md-6">
                    <div class="panel panel-default">
                        <div class="panel-heading">Another panel</div>
                        <div class="panel-body">

The bulk of the above markup has to do with the layout of the page using bootstrap and the setting of the leaflet map. I won’t go into any details about them, you can find out how to create the maps through their excellent tutorials or creating web sites using bootstrap in w3schools

Notice however the link to scripts/map_functions.js. This is the bit of javascript that makes a jQuery AJAX call, to a web method which in turns call the oracle function to create the GeoJSON objects.

function init_map(lon, lat, zoomlevel) {
    map = L.map('map').setView([lon, lat], zoomlevel);
    var mapbox = L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {
        attribution: '&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors',
        id: 'examples.map-i875mjb7'

    var url = "../Services/GetGeoJsonData.asmx/getJsonFromOra";

        type: "POST",
        url: url,
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        data: "{'table_name':'v_sdo_ksim_wgs84'}",
        success: function (geodata) {
            var obj = jQuery.parseJSON(geodata.d);
            var smallIcon = L.Icon.extend({
                options: {
                    iconSize: [29, 25],
                    iconUrl: '../img/sign1.png'
            geojson = L.geoJson(obj, {
                pointToLayer: function (feature, latlng) {
                    return L.marker(latlng, { icon: new smallIcon({ iconUrl: '../img/sign.png' }) });
                onEachFeature: onEachFeature
        error: function (xmlHttpRequest, textStatus, errorThrown) {

    return [map];
function onEachFeature(feature, layer) {
    layer.bindPopup('<a href="#" class="speciallink">' + feature.properties.EVA_EVENT_DESCRIPTION) + '</a.';
function zoomToFeature(e) {

In a nutshell, the script calls through GetGeoJsonData.asmx web service, the getJsonFromOra method method (line 8) using a spatial table name as a parameter called v_sdo_ksim_wgs84 (line 15). In turn the getJsonFromOra method calls the oracle function using a spatial table name as a parameter and returns a valid GeoJSON feature collection string.

    [ScriptMethod(UseHttpGet = false, ResponseFormat = ResponseFormat.Json)]
    public string getJsonFromOra(string table_name)
        string json = string.Empty;
        string connectionString = "User Id=user;Password=pwd;Data Source=xe";
        string jprefix = "{\"type\": \"FeatureCollection\",  \"features\": [";
        OracleConnection oraCon = new OracleConnection();
        oraCon.ConnectionString = connectionString;
        string select = "select ora2geojson.sdo2geojson_partial('select *  from " + table_name + "',ROWID, shape) FROM " + table_name;
        OracleCommand oraCommand = new OracleCommand(select, oraCon);
            OracleDataReader Orareader = oraCommand.ExecuteReader();

            while (Orareader.Read())
                json = json + "{" + Orareader[0].ToString() + ",";
            json = json.TrimEnd(',');
            return jprefix + json + "]}";
        catch (Exception)

The end results is a map like the one below. The traffic signs you see are oracle spatial features.


You can download the whole solution here. Remember to change the spatial table name to suit your settings.

Happy coding!

Convert Oracle Spatial data to geojson

So at some point I needed to convert Oracle Spatial data (SDO) into geojson format. I quickly came across this excellent post from SpatialDB Advisor. Which basically does exactly that. Converts SDO data into geojson. However -as he already states in his post- the function does not support attributes. So based on this original function, I created a package that will create a FULL geojson including attributes using a bit of dynamic SQL (DBMS_SQL package). And in fact, you can select which attributes by providing a select statement as a parameter. I also *think* I fixed a couple of minor bugs in the original sdo2geojson function.

You can find the package (ora2geojson.rar) here. Unzip the file and run the package header  (.pkh) and body (.pkb) files e.g.





SELECT ora2geojson.sdo2geojson('select * from v_segments_all_sdo',ROWID, shape) FROM v_segments_all_sdo;

Where v_segments_all is the spatial table or view and shape is the name of the spatial column. The ROWID is needed to collect the attributes. I am sure there are more sufficient ways to do this but this one seemed to work fine for me.

A sample usage and result is shown below: