9.3 Querying Spatial Data in a Property Graph
Oracle Spatial and Graph geospatial query functions can be applied to spatial data in a property graph. This topic provides some examples.
Note that a query based on spatial information can be combined with navigation and pattern matching.
The following example finds entities (vertices) that are within a specified distance (here, 1 mile) of a location (point geometry).
SQL> -- use SDO_WITHIN_DISTANCE to filter vertices
SQL> select vid, k, t, v
from testvt$
where sdo_within_distance(mdsys.opg_apis.get_geometry_from_v_t_cols(v, t),
mdsys.sdo_geometry(2001, 8307, mdsys.sdo_point_type(-122.23, 37.56, null), null, null),
'distance=1 unit=mile') = 'TRUE'
order by vid, k;
The output and execution plan may include the following. Notice that a newly created domain indexTESTVTXGEO$ is used in the execution.
100 geoloc 20 -122.230 37.560 101 geoloc 20 -122.231 37.561 .. ... ... ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18176 | 2 (50)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | 1 | 18176 | 2 (50)| 00:00:01 | | | Q1,01 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | 1 | 18176 | 2 (50)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 1 | 18176 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 5 | PX SEND RANGE | :TQ10000 | 1 | 18176 | 1 (0)| 00:00:01 | | | Q1,00 | P->P | RANGE | | 6 | PX PARTITION HASH ALL | | 1 | 18176 | 1 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | | |* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TESTVT$ | 1 | 18176 | 1 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | |* 8 | DOMAIN INDEX (SEL: 0.000000 %) | TESTVTXGEO$ | | | 1 (0)| 00:00:01 | | | Q1,00 | | | ---------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - filter(INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V")) 8 - access("MDSYS"."SDO_WITHIN_DISTANCE"("OPG_APIS"."GET_GEOMETRY_FROM_V_T_COLS"("V","T"),"MDSYS"."SDO_GEOMETRY"(2001,8307,"MDSYS"."SDO_P OINT_TYPE"((-122.23),37.56,NULL),NULL,NULL),'distance=1 unit=mile')='TRUE')
The following example sorts entities (vertices) based on their distance from a location.
-- Sort based on distance in miles
SQL> select vid, dist from (
select vid, k, t, v,
sdo_geom.sdo_distance(mdsys.opg_apis.get_geometry_from_v_t_cols(v, t),
mdsys.sdo_geometry(2001, 8307, mdsys.sdo_point_type(-122.23, 37.56, null), null, null), 1.0, 'unit=mile') dist
from testvt$
where t = 20
) order by dist asc
;
The output and execution plan may include the following.
... 101 .088148935 102 .385863422 103 .773127682 104 1.2068052 105 1.64421947 200 2.08301065 ... ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 15062 | 1366 (1)| 00:00:01 | | | | 1 | SORT ORDER BY | | 1 | 15062 | 1366 (1)| 00:00:01 | | | | 2 | PARTITION HASH ALL| | 1 | 15062 | 1365 (1)| 00:00:01 | 1 | 8 | |* 3 | TABLE ACCESS FULL| TESTVT$ | 1 | 15062 | 1365 (1)| 00:00:01 | 1 | 8 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T"=20 AND INTERNAL_FUNCTION("V"))
Parent topic: Spatial Support in Property Graphs