Connect Nodes, Centroids and Voronoi Cells to POI

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:

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:

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;

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.

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.

UPDATE LONDON_POI
SET SHAPE_CENTROID =
    CASE
        WHEN SHAPE.ST_GeometryType() = 'ST_Point'
        THEN SHAPE
        ELSE SHAPE.ST_Centroid()
    END;

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.

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.

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.

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.