#### Determine the Distance to a Target POI

0 %
Determine the Distance to a Target POI
Details

# Determine the Distance to a Target POI

July 26, 2021
Created by
July 26, 2021
Learn how you can select specific location points via SQL and determine the distance between two points in SAP HANA Cloud, SAP HANA database.

#### You will learn

• How to transform your location point to a Spatial Reference System
• How to select a target POI from the sample data
• How to determine the distance between two location points

## Prerequisites

In this tutorial, you will learn how to determine the distance to a target Point of Interest (POI). This includes three steps:

• Select a Location via SQL
• Select a Target POI
• Determine distance between two points

Imagine you are done working for today, step out of the office and want to go to a bar in London to grab your well-deserved home-made iced tea.

In this exercise, you will calculate the distance between your location and a POI of the type `bar` in the table `LONDON_POI`.

Step 1: Select a location via SQL

You are starting your trip at Canary Wharf in London. In this step, you will use a select statement reflecting your starting point location as type `ST_Geometry`.

1. First, you will need to find the latitude and longitude of your location. Web mapping services like Google Maps can help here. Visit maps.google.com and zoom-in to Canary Wharf in London. Right-click on the big round-about and select What’s here?.

2. You can retrieve the coordinates from the small overlay at the bottom of the window or copy and paste from the current URL.

3. Next, you need to bring these coordinates into SAP HANA Cloud, SAP HANA database. To do that, you can use the function `ST_GeomFromText`(*). This constructor for geometries expects a Well-known Text (WKT) as well as the associated spatial reference system as input. Latitude and longitude can be easily assembled to match the expected WKT string. The associated spatial reference system in this case has `id 4326 (WGS84)`.

``````SELECT ST_GeomFromText('POINT(-0.026859 51.505748)', 4326) FROM DUMMY;
``````
4. If you use a SQL editor with built-in spatial visualization, like `DBeaver`, you will be able to preview the location and double-check that it matches your previously selected location.

5. To make the following exercises more convenient, it is a good idea to transform this point to the same spatial reference system as our data from OpenStreetMap (SRS with `id 32630`). We can extract the Well-known Text representation by using the function `ST_AsWKT`(*).

``````SELECT ST_GeomFromText('POINT(-0.026859 51.505748)', 4326) .ST_Transform(32630) .ST_AsWKT() FROM DUMMY;
``````
6. In the Results Panel you should now see the transformed coordinates:

``````POINT (706327.107445 5710259.94449)
``````
7. Next, you need to select this point with Spatial Reference System `32630` by executing the following statement.

``````SELECT ST_GeomFromText('POINT (706327.107445 5710259.94449)', 32630) FROM DUMMY;
``````
Step 2: Select a target POI

Now that your starting point coordinate has been transformed and selected, you need to select the target POI from the table `LONDON_POI`.

1. Your target destination is a bar called Blues Kitchen in Camden. All points of interest are stored in the table `LONDON_POI`. You can search our POI based on field name and amenity. The latter describes the type of POI. In our case we would like to filter for value bar by executing this statement:

``````SELECT *
FROM LONDON_POI lp
WHERE LOWER("name") LIKE '%blues kitchen%' AND "amenity" = 'bar'
``````
2. You should receive 4 records in the Results panel.

3. You can use a visualization tool to see the location of these records. If you do not have a visualization tool installed, you can use online tools such as Wicket. Wicket is able to visualize geometries in WKT format in spatial reference system 4326. To obtain these geometries from SAP HANA Cloud we can use `ST_Transform`(*) in combination with `ST_AsWKT`(*).

``````SELECT "osmid", SHAPE.ST_Transform(4326).ST_AsWKT()
FROM LONDON_POI lp
WHERE LOWER("name") LIKE '%blues kitchen%' AND "amenity" = 'bar';
``````
4. When you copy over the geometries in WKT format to your (online) visualizer, you will see that two records are not located in Camden and the two other records are actually referring to the same location - one being a polygon depicting the outline of the building and the other being a point depicting the location of the building.

5. In the next step we will work with the POI with OSM ID (field `osmid`) 6274057185. Select it by executing this statement:

``````SELECT * FROM LONDON_POI lp WHERE "osmid" = 6274057185;
``````
Step 3: Determine distance between two points

Now that you have selected starting point and target point, you can determine the direct distance between your location the selected POI.

You can use the function `ST_Distance`(*) to determine the direct distance between both points:

``````SELECT SHAPE.ST_Distance(
ST_GeomFromText('POINT (706327.107445 5710259.94449)', 32630)
)
FROM LONDON_POI lp
WHERE "osmid" = 6274057185;
``````

Triggering the query above, you can see in the Results that the direct distance to reach your home-made iced tea is around 8670 meters.

You have successfully created a geometry using a `WKT` string. Furthermore, you have visualized points of interest by either using `DBeaver` or an online visualization tool like Wicket. With the spatial function `ST_Distance` you have determined the direct distance between two points.

Learn in the next tutorial how to identify a relevant area for a transportation network.

Step 4: Test yourself
The function ST_GeomFromText accepts these as inputs:
×

### Next Steps

Navigate tutorial steps