I have a table like this:
CREATE TABLE spatial_data (
id NUMBER PRIMARY KEY,
geometry SDO_GEOMETRY);
SDO_GEOMETRY has a field sdo_ordinates with the following type:
TYPE SDO_ORDINATE_ARRAY AS VARRAY(1048576) OF NUMBER
I can get the number of points for specified object:
select count(*)
from table(
select s.geometry.sdo_ordinates
from spatial_data s
where s.id = 12345
);
How can I get count for several objects? It's not possible to use
where s.id in (1, 2, 3, 4, 5)
And I really care about performance. Maybe PL/SQL would be the right choice?
I think that you can do it with one query:
or you can write a plsql simple function that returns the count attribute of the SDO_ORDINATE_ARRAY VARRAY:
or even nicer add a member function to SDO_GEOMETRY TYPE which return the count attribute