The Devtalk-section of the Merkator website is used by our developers to share milestones in projects or things they learned, … We started this series to share these posts internally, but decided that the outside world can read it too. Why not ? But remember, this blogpost is not the formal place for marketing and sales. Just enjoy our discoveries!

In a previous post we showed a proof of concept of Marlin running on QGIS. We are currently expanding on this by implementing Marlin as a plugin for MapWindow 5 (https://github.com/MapWindow/MapWindow5) a .NET open-source desktop GIS. Below is a screen shot showing the basic user interface. Although maybe not as feature-rich in comparison with other open-source GIS desktop solutions, MapWindow has the big advantage of running on .NET. This makes integrating Marlin (also using .NET) easier compared to e.g. QGIS: there’s no need for interoperability libraries, we can plug the WPF GUI directly into MapWindow. The downside is MapWindow is not as mature as QGIS and we already have had to work around a few quirks. Nothing too serious, so it won’t be long before we have a fully functional Marlin Core running on another GIS desktop client!

SqlKata

But that’s not all! Marlin does not only have an open design for the front-end, it is also open at the back-end side. The original implementation targets Oracle Database (currently 12c), but we have been working on a Microsoft SQL Server implementation in the past months. The changes to our code-base also pave the way for other database server implementations if that need would arise. We are using a small, handy library (which is nonetheless doing some heavy lifting) called SqlKata (https://sqlkata.com/). This library makes writing custom sql queries easy:

using SqlKata;
using SqlKata.Compilers;

// Create an instance of SQLServer
var compiler = new SqlServerCompiler();
var query = new Query("Users").Where("Id", 1).Where("Status", "Active");
SqlResult result = compiler.Compile(query);

string sql = result.Sql;
List<object> bindings = result.Bindings; // [ 1, "Active" ]

This would result in the following sql output:

SELECT * FROM [Users] WHERE [Id] = @p0 AND [Status] = @p1

By swapping out the compiler class with an Oracle (or MySQL, Postgres, …) compiler the vendor-specific flavour of SQL will get compiled. And for those corner cases where default ANSI sql does not have a solution, we can still use ‘raw’ sql strings:

var now = DateTime.UtcNow;
var format = "yyyy-MM-dd";
DateTime from = now.AddDays(-5).ToString(format),
         to = now.ToString(format);


var rangeQuery = new Query()
.ForOracle(q =>
    // everything written here is available to the Oracle Compiler only
    q.FromRaw("generate_series ( ?::timestamp, ?::timestamp, '1 day'::interval) dates", new[] { from, to })
    .SelectRaw("dates::date as date")
)
.ForSqlServer(q =>
    // everything written here is available to the SqlServer Compiler only
    q.WithRaw("range", @"SELECT CAST(? AS DATETIME) 'date'
        UNION ALL
        SELECT DATEADD(dd, 1, t.date) FROM range t WHERE DATEADD(dd, 1, t.date) <= ?", new[] { from, to }
    )

 .From("range")

)

Although we’re not completely there yet, we’re getting close to a fully functional Marlin on Mapwindow – that fish is growing!

Mathijs Dumon

Mathijs Dumon

Geo-ICT Consultant / Developer Marlin

Mathijs Dumon obtained his PhD in Geology at Ghent University, but aside from his love for Geology, Mathijs is also passionate about software development. After looking for a new opportunity, he joined Merkator NV/SA in September 2018 after having heard about how awesome working here is. He is currently focusing on the development of a cross-database compatible back-end for our utility network inventorisation and management software Marlin.