CODEWITHSUNDEEP
spatialhana
Pierre F
Pierre F

Reputation: 1392

HANA Spatial: Query by Circle (Center and Radius)

Using SAP HANA Spatial, how do I query for all the points contained within a circle? Ideally, I would like to specify the latitude and longitude of the center, as well as the radius.

For instance, select all landmarks around 'Berlin - Alexanderplatz' (52.5219184 13.4132147) in a radius of 10 kilometers.

PS. To try it out:

  1. Register an account on https://account.hanatrial.ondemand.com and log in.
  2. Create a Database: Open Databases & Systems under Persistence. Click New, enter a Database ID, select HANA MDC (<trial>) as Database System, enter a SYSTEM User Password, and save.
  3. Open the Development Tools: In the overview of your database, click SAP HANA Web-based Development Workbench and log in with the SYSTEM user. Under Security, grant the application privilege sap.hana.ide::Catalog to the user SYSTEM. (Yes I know, don't do this in real life!). Open the Catalog.

  4. Create a database table: Click the button Open SQL Console (Command-Alt-C), enter the following SQL code and click the Run (F8) button.

    CREATE COLUMN TABLE "COORDINATES" ("LOCATION_NAME" VARCHAR(100), "COORDINATE" ST_POINT(0) CS_POINT);
INSERT INTO "COORDINATES" VALUES ('Berlin - Brandenburger Tor', NEW ST_Point('POINT(52.5162746 13.377704)'));
INSERT INTO "COORDINATES" VALUES ('Berlin - Gendarmenmarkt', NEW ST_Point('POINT(52.5137224 13.3926698)'));
INSERT INTO "COORDINATES" VALUES ('Paris - Tour Eiffel', NEW ST_Point('POINT(48.8583701 2.2944813)'));

  5. To query by rectangle, fire the following statement.

    SELECT LOCATION_NAME, COORDINATE.ST_ASGEOJSON() FROM COORDINATES WHERE (NEW ST_Polygon('Polygon((52 13, 52 14, 53 14, 53 13, 52 13))').ST_Contains(COORDINATE)) = 1;
  6. But, remember, I need a circle! How can I retrieve the same results?

Upvotes: 0

Views: 550

Answers (1)

Lars Br.
Lars Br.

Reputation: 10396

Wouldn't that be the case that the distance between your circle center point and your objects need to be smaller than the radius? Something like

SELECT LOCATION_NAME, COORDINATE.ST_ASGEOJSON() 
FROM COORDINATES 
WHERE 
COORDINATE.ST_Distance( NEW ST_Point('POINT(52.5162746 13.377704)')) <= 2;

Here I just put the coordinates for the Brandenburger Tor and the radius of 2 - but you should get the picture.

Upvotes: 0

Related Questions