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" />
    <title>Test</title>

    <!-- 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%;
        }
    </style>

    <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];
        });
    </script>
</head>
<body>
    <form>
        <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>
                    </div>
                </div>
                <div class="col-md-6">
                    <div class="panel panel-default">
                        <div class="panel-heading">Another panel</div>
                        <div class="panel-body">
                            Content
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </form>
</body>
</html>

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'
    }).addTo(map);

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

    $.ajax({
        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
            });
            geojson.addTo(map);
        },
        error: function (xmlHttpRequest, textStatus, errorThrown) {
            console.log(xmlHttpRequest.responseText);
            console.log(textStatus);
            console.log(errorThrown);
        }
    })

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

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.

    [WebMethod]
    [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;
        oraCon.Open();
        string select = "select ora2geojson.sdo2geojson_partial('select *  from " + table_name + "',ROWID, shape) FROM " + table_name;
        OracleCommand oraCommand = new OracleCommand(select, oraCon);
        try
        {
            OracleDataReader Orareader = oraCommand.ExecuteReader();

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

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

image

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

Happy coding!

So how much does Greek Local Government really spend? Find out on an interactive map

diavgeia_maps_enAnd where else would a better place be, you may ask. If you head over to Diavgeia Spending Maps and after you hit the “It’s all Greek to me” button (Aren’t I a clever boy – get the pun?) you should be able to query spending data for every municipality in Greece for a time period of your choosing. Make sure to check the FAQ first.

Data is coming from the Greek Government’s “Transparency  Portal” (“Diavgeia” stands for transparency in Greek)

This has been my toy project for a bit now trying to work on it on my spare time. This is the first release. Do have a play and let me know what you think. If you want more details on how it was done, read on.

The background

Under the Greek Transparency Program initiative, beginning October 1st, 2010, all government institutions are obliged to upload their acts and decisions on the “Transparency Portal”. In fact, NO administrative act or decision is valid unless published on this portal. More info here.

challenge_accepted_barneyThe good folks at the Ministry of Administrative Reform and e-Governance who run this portal, have also provided an API so external apps can use their data. Now, although there were some external sites utilizing the API, none of them had any maps. So I raised to the challenge. In a nutshell, the app makes subsequent calls to the API, one for each municipality, for decisions and acts relating to spending data and simply adds them together. The result is the total amount spent and its the value used to color the map.

There are two -or three very important caveats here – at least in this first version:

The API will only return a maximum of 500 results per query. This means that if -for the date range you selected- the municipality has issued more than 500 decisions relating to spending, the total amount shown for this municipality will be incorrect. However, one can get around it by selecting shorter time periods. In any case the application will report on the number of results returned as well as the number of actual results for each query so you should be able to know if all data was included.

The second is more serious, and in all probability due to my ignorance of the inner workings and processes within the municipalities and their interaction with the Transparency Portal.

To begin with, some of the decisions have a zero amount next to them. And that’s despite the fact that in some cases, even the title of the particular decision states the amount (which of course is not zero). At first I thought it was some sort of bug in my code, but no, it WAS the data stupid. I haven’t got the foggiest why this is.

On the other hand, I noticed that some decisions are duplicated. Exact same title and amount but different decision id. Again, not a clue.

I do intend to try and contact the Diavgeia people to find out but till then -and its plastered all over the site- do use the results with EXTREME caution. Results and total amounts are indicative and should be used for informational purposes only!

The technical bits

Diavgeia Spending Maps was developed using Leaflet (based on the Interactive Choropleth Map example) and jQuery. Controls were based on the JQuery UI tools. Data was returned in Json format. Administrative boundaries were downloaded from geodata.gov.gr as a single shapefile and converted to GeoJson after some generalization to reduce in size.

Greek Version