Skip to Content

Determine the Distance to a Target POI

test
0 %
Determine the Distance to a Target POI
Details

Determine the Distance to a Target POI

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
QR code

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

    google maps 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.

    google maps coordinates
  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.

    Dbeaver.io preview 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;
    
Log on to answer question
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.

    Dbeaver.io Camden bar
  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;
    
Log on to answer question
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.

Log on to answer question
Step 4: Test yourself
The function ST_GeomFromText accepts these as inputs:
×

Next Steps

Back to top