Offset points in Mapserver and PostGIS

Recently, I had to find a way to offset point features that have been created using dynamic segmentation in PostGIS and display them through Mapserver.

Obviously, the points would have to be offset perpendicularly to the line. Mapserver includes an offset function through the STYLE object which works well for lines. By defining the offset as OFFSET [x] –99 the line geometry will be rendered as shifted by x SIZEUNITS perpendicular to the original line geometry. But this technique wouldn’t work for points.

So the idea was to do the offset server-side but WITHOUT changing the actual geometry as the offset was for display purposes only.

To do this I used the st_line_interpolate_point_with_offset function found here and then created a simple wrapper around it:

	pnt_geometry geometry,
	line_geometry geometry,
	offset_val double precision)
    RETURNS geometry
    LANGUAGE 'plpgsql'

    COST 100

 loc double precision;
 offset_geom geometry;
  loc:=ST_LineLocatePoint(line_geometry, pnt_geometry);
	--st_line_interpolate_point taken from:	
	offset_geom:=st_line_interpolate_point(line_geometry, loc, offset_val);
	--raise notice 'offset geom: %1', ST_AsText(offset_geom);
	return offset_geom;


Then, in the mapfile, I set the DATA statement to be:

    DATA "mh_geom from (select mh_gid, offset_point(mh_geom, 'water_pipe','wp_geom',%offsetval%) as
      mh_geom from manhole g) as
      subquery using unique mh_gid using srid=2100"

I also added a VALIDATION block to ensure the offsetval substitution variable was a number:

    "offsetval"	"-?[0-9]{0,10}"

I could then fire a mapserver/WMS request appending the offsetval as a parameter:

http://localhost/mapserver/mapserv? &offsetval=10

With an offset of 10:


Or, with an offset of –10:


Hope this helps someone. Happy coding!


Look Ma! No GIS!

Before you light’ em torches, let me tell you: Not really, no. But the following are a couple of posts I wrote on LinkedIn explaining why a “GIS-only” approach is not ideal when it comes to Linear Referencing and Transportation networks. So, for your reading pleasure:

When GIS is just not enough – Part 1

When GIS is just not enough – Part 2

And the Tech Talk video


OGC seeks public comment on proposed Geocoding API Standard

Not a moment too soon.

OGC, the standards body for anything geospatial, announced in a press release that it’s seeking public comment on a draft charter for an OGC Geocoding API Standards Working Group (SWG). For those not familiar with the OGC acronyms, this is just the first step in implementing a standard. The public comments are not for the standard itself but for the scope and deliverables of the Working Group (who will be responsible for proposing the standard).

As stated in the press release, OGC recognises that

 “There are a number of different APIs on the market for doing this, and a variety of different algorithms for translating strings into locations. The standardisation of a Geocoding API is aimed at simplifying interoperability between business intelligence, mapping, and routing tools, and the services that geocode strings, and adds the capability to easily replace services or chain them together to improve searching success rates.”

You can download the draft charter of the Geocoding SWG at Comments should be sent via email to and are due by 26 September 2017.

“Your wife might leave you if you work with open source” – No more

pros_cons_programmerThis is what an ESRI distributor told me (more or less) referring to the amount of time it takes someone to set up an interactive map web site  built around open source software.

Was meant in jest of course. He did have a point though. For anyone that is not very technical savvy, trying to create a simple website with a map and some interactivity was… let’s say, challenging the least. Compare having to install various components, editing Apache configs, copy/paste files around, searching for plugins, writing code for simple map interactions like zoom in/out, with the clean, drag & drop way of Web AppBuilder for ArcGIS.

To me, this was always the big gap in the FOSS4G ecosystem. Desktop GIS like QGIS, were not a problem any more. Download and install via a wizard. Easy. But there was never an quick (or painless) way of creating a map website.

Not until now, that is. Boundless has announced the release of their QGIS 2.14.1 distribution (1) which includes, among other goodies, the Web App Builder (currently in Beta) plugin.

Web App Builder allows you to create web apps directly from QGIS. Without writing a single line of code.

You can add different base maps or overlays:


And select the tools/functionality you want available on your site:


There are lots of other customization settings but those were the most interesting ones in my opinion. There are a few bugs (the tabbed interface is not working, doesn’t work well with different SRIDs) which result in blank pages, but all in all, I had a fully functional web site in less than 5 mins. That by itself is a huge improvement from the past!

You can download the Boundless QGIS distribution here. There is a form where you have to enter your details, etc but its definitely worthwhile.

Lots of kudos to the guys in Boundless and  I am sure lots of families will be thanking them too 😉

(1) To avoid confusion or misunderstandings as per @Mr Fahrenheit’s comment: QGIS is an open source project created and still actively been developed by an international team of contributors and not by just Boundless. This post is referring to a specific distribution/branch of QGIS which includes the WAB plugin

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="">
<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="" />
    <link rel="stylesheet" href="" />

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

    <script src=""></script>
    <script src=""></script>
    <script type="text/javascript" src=""></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 ='map').setView([lon, lat], zoomlevel);
    var mapbox = L.tileLayer('http://{s}{z}/{x}/{y}.png', {
        attribution: '&copy; <a href="">OpenStreetMap</a> contributors',
        id: ''

    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">' + + '</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!

The 10 commandments of Egoless Programming

Came across these on various sites and thought I should re-publish. The concept of “Egoless programming” was introduced by Jerry Weinberg in his 1971 seminal book “The Psychology of Computer programming”10commandment. All very useful and clever, but I doubt ALL of them doable ALL the time.  Or, are they?

  1. Understand and accept that you will make mistakes. The point is to find them early, before they make it into production. Fortunately, except for the few of us developing rocket guidance software at JPL, mistakes are rarely fatal in our industry, so we can, and should, learn, laugh, and move on.
  2. You are not your code. Remember that the entire point of a review is to find problems, and problems will be found. Don’t take it personally when one is uncovered.
  3. No matter how much “karate” you know, someone else will always know more. Such an individual can teach you some new moves if you ask. Seek and accept input from others, especially when you think it’s not needed.
  4. Don’t rewrite code without consultation. There’s a fine line between “fixing code” and “rewriting code.” Know the difference, and pursue stylistic changes within the framework of a code review, not as a lone enforcer.
  5. Treat people who know less than you with respect, deference, and patience. Nontechnical people who deal with developers on a regular basis almost universally hold the opinion that we are prima donnas at best and crybabies at worst. Don’t reinforce this stereotype with anger and impatience.
  6. The only constant in the world is change. Be open to it and accept it with a smile. Look at each change to your requirements, platform, or tool as a new challenge, not as some serious inconvenience to be fought.
  7. The only true authority stems from knowledge, not from position. Knowledge engenders authority, and authority engenders respect – so if you want respect in an egoless environment, cultivate knowledge.
  8. Fight for what you believe, but gracefully accept defeat. Understand that sometimes your ideas will be overruled. Even if you do turn out to be right, don’t take revenge or say, “I told you so” more than a few times at most, and don’t make your dearly departed idea a martyr or rallying cry.
  9. Don’t be “the guy in the room.” Don’t be the guy coding in the dark office emerging only to buy cola. The guy in the room is out of touch, out of sight, and out of control and has no place in an open, collaborative environment.
  10. Critique code instead of people – be kind to the coder, not to the code. As much as possible, make all of your comments positive and oriented to improving the code. Relate comments to local standards, program specs, increased performance, etc.

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: