Determine the Distance to a Target POI

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

   SQL Copy

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.

 ![Dbeaver.io preview location](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-smart-multi-model-3/ss-03-dbeaver-preview-location.png)

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`(*)](https://help.sap.com/viewer/bc9e455fe75541b8a248b4c09b086cf5/LATEST/en-US/7a169dff787c1014a095b86992806f14.html).

```SQL
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:

   Code Copy

POINT (706327.107445 5710259.94449)

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

   ```SQL
SELECT ST_GeomFromText('POINT (706327.107445 5710259.94449)', 32630) FROM DUMMY;

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:

   SQL Copy

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](https://arthur-e.github.io/Wicket/sandbox-gmaps3.html). 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`(*)](https://help.sap.com/viewer/bc9e455fe75541b8a248b4c09b086cf5/LATEST/en-US/e2b1e876847a47de86140071ba487881.html)  in combination with [`ST_AsWKT`(*)](https://help.sap.com/viewer/bc9e455fe75541b8a248b4c09b086cf5/LATEST/en-US/7a169dff787c1014a095b86992806f14.html).

```SQL
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:

   SQL Copy

SELECT * FROM LONDON_POI lp WHERE “osmid” = 6274057185;

</div><div class="step-validation-mount"
      data-step="2"></div>
 <div class="step-actions">
   <button class="fd-button fd-button--emphasized" data-action="mark-done" data-step="2">Done</button>
 </div>
</div>
</div>






<div id="step-3" class="tutorial-step" data-step="3">
<div class="step-header" data-action="toggle-step">
 <span class="step-check-circle"></span>
 <div class="step-header-text">
   <span class="step-label">Step 3</span>
   <span class="step-title-text">Determine distance between two points</span>
 </div>
 <span class="step-toggle-icon">+</span>
</div>
<div class="step-body" hidden>
 <hr class="step-divider" />
 <div class="step-content">

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`(*)](https://help.sap.com/viewer/bc9e455fe75541b8a248b4c09b086cf5/LATEST/en-US/7a182aa3787c101481f996e3d419c720.html) to determine the direct distance between both points:

```SQL
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.