# 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

## 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. 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.