Oracle 12c Spatial calculating count of points from fixed point

Question

I need to answer the following question using Oracle Spatial in Oracle 12c. How many people live within x kilometer radius from a hospital?

Does anyone know how to do this?

Setup is: 2 x tables. table.People with sdo_geometry type and table.Hospital also with sdo_geometry column.

What has not worked is the following example

SELECT count(people)
FROM
( SELECT ppl.people
 FROM table.people ppl,
 table.hospital hosp,
 WHERE sdo_nn (
 ppl.loc_geo_loc,hosp.loc_geo,
 'sdo_batch_size=10 unit=km',1) = 'TRUE')
);

Also tried to create table from below. Idea was to then do a count on the created table. This didn't work.

SELECT ppl.people
 FROM table.people ppl,
 table.hospital hosp,
 WHERE sdo_nn (
 ppl.loc_geo_loc,hosp.loc_geo,
 'sdo_batch_size=10 unit=km',1) = 'TRUE')

• Mapperz: How is your data setup in Oracle?, please add more details and explain what you have tried.
• roeloffx: Thx for the reply,

Answer 1

Use the SDO_WITHIN_DISTANCE operator, like this:

SELECT count(*)
FROM table.people ppl,
 table.hospital hosp
WHERE sdo_within_distance (ppl.loc_geo_loc,hosp.loc_geo, 'distance=2 unit=km') = 'TRUE';

This returns the total count of all people that are within 2 km of any hospital.

To find out how many people are within 2 km of an hospital, and the name of each hospital, do this:

SELECT count(*), hosp.name
FROM table.people ppl,
 table.hospital hosp
WHERE sdo_within_distance (ppl.loc_geo_loc,hosp.loc_geo, 'distance=2 unit=km') = 'TRUE'
GROUP BY hosp.name;

And to find the details about all people within 2km of a selected hospital, do this:

SELECT ppl.*
FROM table.people ppl,
 table.hospital hosp
WHERE sdo_within_distance (ppl.loc_geo_loc,hosp.loc_geo, 'distance=2 unit=km') = 'TRUE'
AND hosp.id = 42;

That is how you find objects that are within a set distance of others. The SDO_NN operator does something else: use it to find the closest object(s) to another object - irrespective of distance.

• roeloffx: Thank you for this. Jip this is the logical way to do it but... you second SELECT ppl.* executes and returns quite quick. The first SELECT count() runs so long that I have to eventually kill the session. I have no idea why. Is there a Cartesian join created when using the count() function? Is it falling over because of the amount of data in this join?
• Albert Godfrind: That first query matches all people with all hospitals. How many of each do you have ? Also can you show the query plan that query uses ? And do you have a spatial index on the PEOPLE table ?
• Albert Godfrind: Assuming you do have a spatial index on the PEOPLE table, this query will perform a spatial search into the PEOPLE table for each row you have in the HOSPITAL table. But if you do not have one, then yes, this will produce a cartesian join - meaning that each HOSPITAL will be matched against each PEOPLE. Doing spatial calculations is very expensive in terms of CPU use, especially if the shapes use geographic coordinates (long/lat ). The spatial index avoids doing this cartesian join, and also provides internal optimizations that restrict the need to do full computations.
• Albert Godfrind: You say that the first query is slow, but the second one (with the GROUP BY) runs fast ? That is odd, since both are really doing the same processing, except that one breaks down the count per hospital. But it would be nice to also see the query plan for the second query.