Connect Nodes, Centroids and Voronoi Cells to POI
- How to assign
Voronoicells 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
- You have completed the tutorial Visualize a
Voronoicell using a Scalable Vector Graphic.
- You have access to the sample spatial data used in this tutorial group.
- Make sure your database instance is running before you start.
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:
Voronoicells 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_VERTICESby a column
VORONOI_CELLthat holds the
Voronoicell of the respective vertex/node as an
To enhance the existing table, execute this statement:SQLCopy
ALTER TABLE LONDON_VERTICES ADD (VORONOI_CELL ST_Geometry(32630));
Then use MERGE INTO to update the values and assign it with the respective
Voronoicell using this statement:SQLCopy
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_POIby a column
SHAPE_CENTROIDthat holds the centroid of the respective point of interest. Note, that many POI’s are polygons.
Enhance the table
LONDON_POIby another geometry with spatial reference system 32630.SQLCopy
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
LONDON_POIcontains a mixture of points and polygons. You need to copy over the points to column
SHAPE_CENTROIDand - in case of polygons - calculate their centroid and assign the resulting point to column
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.SQLCopy
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_POIwith a column
VERTEX_OSMIDand fill in the
osmidof the closest vertex.
For physically enhancing the table, you need to consider that the referenced
osmidin the table
LONDON_VERTICESis of type BIGINT.SQLCopy
ALTER TABLE LONDON_POI ADD (VERTEX_OSMID BIGINT);
You have learnt in the previous tutorial about the definition and usage of
Voronoicells. You will use the already assigned
Voronoicells to determine the closest vertex for each POI. Note, that if a POI is within the
Voronoicell 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.SQLCopy
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.
- Step 4
Which of these functions cannot help to determine if the table contains POI's as points or polygons?