Skip to Content

Connect Nodes, Centroids and Voronoi Cells to POI

Learn how you can enhance a table with POI's by adding columns that hold Voronoi cells, Centroids and Node references that can be used for calculating shortest paths between POI's.
You will learn
  • How to assign Voronoi cells to nodes of a transportation network
  • How to assign a centroid to a Point of Interest
  • How to add a node reference to a POI table
Created by
VijayKrishnanSR
July 26, 2021
Contributors
VijayKrishnanSR

Prerequisites

In your dataset, you have points of interest (table LONDON_POI) as well as nodes of the transportation network (table LONDON_VERTICES). Of course, there is a spatial relation between both (e.g. distance measured by ST_Distance). In this tutorial, you will learn how to assign each POI to its closest node in the transportation network. You can think of example- assigning a bar to its closest street corner.

This way, you will later be able to calculate shortest paths between points of interest. These are the steps involved:

  • Assign Voronoi cells to all Nodes
  • Assign a centroid to each Point of Interest
  • Enhance the POI table with node reference
  • Step 1

    The first thing you need to do is to enhance the table LONDON_VERTICES by a column VORONOI_CELL that holds the Voronoi cell of the respective vertex/node as an ST_Geometry.

    To enhance the existing table, execute this statement:

    SQL
    Copy
    ALTER TABLE LONDON_VERTICES ADD (VORONOI_CELL ST_Geometry(32630));
    

    Then use MERGE INTO to update the values and assign it with the respective Voronoi cell using this statement:

    SQL
    Copy
    MERGE INTO LONDON_VERTICES
    USING
    (
    	SELECT "osmid", ST_VoronoiCell(shape, 10.0) OVER () AS CELL
    	FROM LONDON_VERTICES
    ) v ON LONDON_VERTICES."osmid" = v."osmid"
    WHEN MATCHED THEN UPDATE SET LONDON_VERTICES.VORONOI_CELL = v.CELL;
    
  • Step 2

    Now you can enhance the table LONDON_POI by a column SHAPE_CENTROID that holds the centroid of the respective point of interest. Note, that many POI’s are polygons.

    Enhance the table LONDON_POI by another geometry with spatial reference system 32630.

    SQL
    Copy
    ALTER TABLE LONDON_POI ADD (SHAPE_CENTROID ST_GEOMETRY(32630));
    

    Since POI’s in the OpenStreetMap data can contain points as well as polygon (i.e. storing the shape of a house), the column SHAPE in table LONDON_POI contains a mixture of points and polygons. You need to copy over the points to column SHAPE_CENTROID and - in case of polygons - calculate their centroid and assign the resulting point to column SHAPE_CENTROID.

    Use the function ST_GeometryType(*) to determine if the record contains a point or polygon and the function ST_Centroid(*) to calculate the centroid of the latter.

    SQL
    Copy
    UPDATE LONDON_POI
    SET SHAPE_CENTROID =
        CASE
            WHEN SHAPE.ST_GeometryType() = 'ST_Point'
            THEN SHAPE
            ELSE SHAPE.ST_Centroid()
        END;
    
  • Step 3

    Now that you added the two columns, you need to connect each POI with its closest vertex in the transportation network. For this, enhance the table LONDON_POI with a column VERTEX_OSMID and fill in the osmid of the closest vertex.

    For physically enhancing the table, you need to consider that the referenced osmid in the table LONDON_VERTICES is of type BIGINT.

    SQL
    Copy
    ALTER TABLE LONDON_POI ADD (VERTEX_OSMID BIGINT);
    

    You have learnt in the previous tutorial about the definition and usage of Voronoi cells. You will use the already assigned Voronoi cells to determine the closest vertex for each POI. Note, that if a POI is within the Voronoi cell of a specific vertex, that means that this vertex is closer to the POI than any other vertex.

    With that knowledge, you can use the spatial function ST_Intersects(*) to assign a vertex to each POI in the dataset.

    SQL
    Copy
    MERGE INTO LONDON_POI lp
    USING LONDON_VERTICES lv
    ON lv.VORONOI_CELL.ST_Intersects(lp.SHAPE_CENTROID) = 1
    WHEN MATCHED THEN UPDATE SET lp.VERTEX_OSMID = lv."osmid";
    

    This picture illustrates the snapping logic.

    POI Snapping

    You have now connected each point of interest to a node/vertex in the transportation network. This will allow you to calculate shortest paths between POI’s in the following tutorials.

    In the next tutorial, learn how to prepare the data for the SAP HANA Cloud Graph Engine and how to create a Graph workspace.

  • Step 4

    Which of these functions cannot help to determine if the table contains POI's as points or polygons?

Back to top