2 posts tagged with "wkt"

With SQL Server now in public preview inside of Microsoft Fabric, I was keen to investigate it's spatial data functions. Whilst the Event House has provided spatial data processing inside of Fabric since the outset with KQL database, I was excited when the SQL engine arrived as it's a more familiar environment to those working with spatial data. It also opens up the door for doing realtime processing of that data based on interactions in a Power BI report.

Fabric SQL has strong support for geospatial datatypes and functions opening up a wide range of operations such nearest neighbours, distance calculations, boundary intersections etc etc.

In this example I wanted to keep things simple and show properties within a selected distance from selected postboxes. The postboxes would be selected by a Power BI slicer, as would the distance required. Then on making these selections, SQL server would then find the properties within those search radiuses.

First of all I needed some data to use, so I extracted from OpenStreetMap the locations of all the postboxes in Buckinghamshire in the UK using the QuickOSM plugin for QGIS. I also did the same for all the buildings in Buckinghamshire. There's only partial data for building outlines in Buckinghamshire, a better datasource for these would have probably been Ordnance Survey data. I saved these two separate data extracts in CSV format with WKT geometry.

So with my data ready, I headed over to Microsoft Fabric, and was quickly and easily able to create SQL database in just 2 clicks. Importing the data was also straightforward using DataFlows Gen 2 to upload the CSV file into OneDrive and import into 2 tables within the database using the familiar PowerQuery interface. The only downside here is that PowerQuery can't create fields of type geography so the WKT data is added as a text field. But this is quickly remedied once the data is loaded.

I also added spatial indexes on the columns containing the spatial data to improve performance.

With my data loaded I was now ready to create a function to to perform the spatial searches when triggered from Power BI. I opted to use a table function as I can then parameterize the search criteria using PowerQuery parameters - I've not been successful in achieving this with stored procedures.

From Power BI, I pass in a list of comma separated postbox references and the distance in meters to search. The first thing we need to do is process that list of postboxes:

WITH RefList AS (
    SELECT TRIM(value) AS ref FROM STRING_SPLIT(@Ref, ',')
)

With that done we can use the STDistance spatial function to calculate the distance between the buildings and the selected postboxes, and return those within the specified distance:

-- Return buildings where the distance from the postboxes is less than @Distance
SELECT 
    b.full_id, 
    b.geom.STAsText() AS WKT, 
    b.building,
    'building' AS itemtype
FROM dbo.buildings AS b INNER JOIN dbo.postboxes AS p ON p.ref IN (select ref from RefList)
WHERE b.geom.STDistance(p.geom) <= @Distance

As well as showing the matched properties in the results, I also wanted to show the locations of the postboxes, so we use a UNION ALL to add the postbox locations into the results:

UNION ALL

-- Return a points for the postbox locations
SELECT 
    full_id,
    p2.geom.STAsText() AS WKT,
    '' AS building,
    'origin' AS itemtype
FROM dbo.postboxes AS p2
WHERE p2.ref IN (select ref from RefList)

And then finally I also wanted to return the search radius to show on the map:

UNION ALL

-- Return the perimeter of the search areas as polygons
SELECT
    p3.ref + 'perimeter' AS full_id,    
    p3.geom.STBuffer(@Distance).STAsText() AS WKT,
    '' AS building,
    'search_perimeter' AS itemtype
FROM dbo.postboxes AS p3
WHERE p3.ref IN (select ref from RefList)

Here's the complete function:

ALTER FUNCTION dbo.fn_GetBuildingsWithinDistance
(
    @Ref      nvarchar (4000),
    @Distance FLOAT
)
RETURNS TABLE
AS
    RETURN
    (

    WITH RefList AS (
        SELECT TRIM(value) AS ref FROM STRING_SPLIT(@Ref, ',')
    )

    -- Return buildings where the distance from the postboxes is less than @Distance
    SELECT 
        b.full_id, 
        b.geom.STAsText() AS WKT, 
        b.building,
        'building' AS itemtype
    FROM dbo.buildings AS b INNER JOIN dbo.postboxes AS p ON p.ref IN (select ref from RefList)
    WHERE b.geom.STDistance(p.geom) <= @Distance

    UNION ALL

   -- Return a points for the postbox locations
    SELECT 
        full_id,
        p2.geom.STAsText() AS WKT,
        '' AS building,
        'origin' AS itemtype
    FROM dbo.postboxes AS p2
    WHERE p2.ref IN (select ref from RefList)

    UNION ALL

    -- Return the perimeter of the search areas as polygons
    SELECT
        p3.ref + 'perimeter' AS full_id,    
        p3.geom.STBuffer(@Distance).STAsText() AS WKT,
        '' AS building,
        'search_perimeter' AS itemtype
    FROM dbo.postboxes AS p3
    WHERE p3.ref IN (select ref from RefList)
);

This is all the setup required within Fabric SQL, so now over to Power BI Desktop.

I was able to connect to Fabric SQL in the same way as I'd connect to any SQL DB, so that was easy, and I simply imported the postbox table as import mode into my Power BI model. I was't worried about any geometry here, just the reference numbers, although in real life, I'd have imported additional reference data for improved searching and selection.

Now to bring in the building search results. I next made to parameters in PowerQuery used to pass the selected postboxes and the search distance.

Now we're ready to create the connection to the table function. As we want the searching to be done interactively, we need to ensure the connection to the table function is made using Direct Query. This means that the SQL query is executed and new results returned every time we interact with the slicers (or other visuals) in the report.

After making the initial connection, I then edited the query in the Advanced Editor. This is because we need to add a little code to handle the multi-selection of postboxes. There's likely a better approach for this, but this seems to work well. First of all I handle a single value vs multiple, and then create a comma separated text string with the selected values:

Note we're using the parameters postbox_ref and range that I configured earlier.

Here's the M code:

let
    // Normalize postbox_ref to a list if it's a single string
    normalizedList = if Value.Is(postbox_ref, type list) then postbox_ref else {postbox_ref},
    
    // Combine into a single comma-separated string
    commaSeparated = Text.Combine(normalizedList, ","),

    Source = Sql.Database("yourdatabaseservergoeshere.database.fabric.microsoft.com", "spatialsql-314d84ad-21da-438c-9caf-76f51b7259d9"),
    dbo_fn_GetBuildingsWithinDistance = Source{[Schema="dbo",Item="fn_GetBuildingsWithinDistance"]}[Data],
    #"Invoked Functiondbo_fn_GetBuildingsWithinDistance1" = dbo_fn_GetBuildingsWithinDistance(commaSeparated, range)
in
    #"Invoked Functiondbo_fn_GetBuildingsWithinDistance1"

That's the data connections sorted. Now over to Power BI's report editor. The queries should load in all the postbox references into a table, and the table function should be called using the default values defined in the PowerQuery parameters.

We now need to set up the interactivity part of the report. So I added a slicer based on the post code reference.

But this won't currently change how the table function is called - we need to link up this to the Power Query function. We do this in the Power BI Model View.

Selecting the postcode reference field on the right, shows the properties panel. We need expand the Advanced section and in the "Bind to parameter" section, select our PowerQuery postbox_ref parameter. And then also enable Multi-Select.

This then binds this field to the PowerQuery parameter. When we select an item in the slicer now, that value is passed into the SQL query.

We also need to do something similar for the distance selection. For this I created a numeric range parameter:

This then provides us with a table of numbers, and a slicer, but we need to bind it to the PowerQuery parameter before we can use it. So back in the Model view, bind it to the range parameter a we did for the postbox_ref parameter, but this time don't enable Multi-select:

And now we're ready to display the results, which of course I used Icon Map Pro for.

The field configuration is straight forward, I add the full_id field into the ID field, and the geom field into the Image / WKT field:

Then in formatting settings for Icon Map Pro, in the Data Layers I just enabled WKT / GeoJson (from data):

And set up some conditional formatting for the buildings vs the postboxes and search radius:

eg:

Here's a video showing it in action:

During the recent 30 Day Map Challenge I published a report with many of Europe's railway routes, which gained a lot of interest. I thought I'd see whether the same thing was possible for bus routes.

I'd sourced the data for the railway networks from OpenStreetMap, but it seems OpenStreetMap doesn't have sufficient data for bus routes - it's either inconsistent or partially populated.

However, I found that the Department for Transport has a Bus Open Data Site. This seems a great source of data, and I've only had chance to scratch the surface. I've started with timetable data. There's a lot of it! For starters, buses stop a lot more than trains, so there are a lot more stops, and the coordinates between each stop along the routes are detailed, as again, roads have many more turns (and roundabouts!) than railway lines.

The data downloads are large, so I've focussed on just one operator for now, the GoAhead group and downloaded their timetable data for the North East. This report represents all of their routes. A Power BI slicer enables you to pick a route, or alternatively you can search for a specific stop and all the routes that service that stop will be displayed. The chosen stop is highlighted in red.

Hovering over a stop with the mouse cursor will display the timetable information for all the buses that service that stop.

There are complexities, and nuances in the data that I haven't represented in the report, but it was a good proof of concept to see what could be achieved with bus data in a few hours. I see there's an API to retrieve the realtime locations of buses, so I'm looking forward to an excuse to explore that!