1-- 2-- From examples in chapter 1.12.1 of 3-- "Spatial Topology and Network Data Models" (Oracle manual) 4-- 5-- Modified to use postgis-based topology model. 6-- Loads the whole topology represented in Figure 1-1 of the 7-- manual, creates TopoGeometry objects and associations. 8-- 9 10--ORA-------------------------------- 11--ORA---- Main steps for using the topology data model with a topology 12--ORA---- built from edge, node, and face data 13--ORA-------------------------------- 14--ORA---- ... 15--ORA---- 7. Query the data. 16--ORA---- 8. Optionally, edit data using the PL/SQL or Java API. 17 18BEGIN; 19 20-- 7. Query the data. 21SELECT a.feature_name, id(a.feature) as tg_id, 22 ST_AsText(topology.Geometry(a.feature)) as geom 23FROM features.land_parcels a; 24 25-- Query not in original example --strk; 26SELECT a.feature_name, id(a.feature) as tg_id, 27 ST_AsText(topology.Geometry(a.feature)) as geom 28FROM features.traffic_signs a; 29 30-- Query not in original example --strk; 31SELECT a.feature_name, id(a.feature) as tg_id, 32 ST_AsText(topology.Geometry(a.feature)) as geom 33FROM features.city_streets a; 34 35-- Query hierarchical feautures 36SELECT feature_name, ST_AsText(topology.geometry(feature)) 37FROM features.big_signs; 38 39SELECT feature_name,ST_AsText(topology.geometry(feature)) 40FROM features.big_streets; 41 42SELECT feature_name,ST_AsText(topology.geometry(feature)) 43FROM features.big_parcels; 44 45--NOTYET-- 46--NOTYET--/* Window is city_streets */ 47--NOTYET--SELECT a.feature_name, b.feature_name 48--NOTYET-- FROM city_streets b, 49--NOTYET-- land_parcels a 50--NOTYET-- WHERE b.feature_name like 'R%' AND 51--NOTYET-- sdo_anyinteract(a.feature, b.feature) = 'TRUE' 52--NOTYET-- ORDER BY b.feature_name, a.feature_name; 53--NOTYET-- 54--NOTYET---- Find all streets that have any interaction with land parcel P3. 55--NOTYET---- (Should return only R1.) 56--NOTYET--SELECT c.feature_name FROM city_streets c, land_parcels l 57--NOTYET-- WHERE l.feature_name = 'P3' AND 58--NOTYET-- SDO_ANYINTERACT (c.feature, l.feature) = 'TRUE'; 59--NOTYET-- 60--NOTYET---- Find all land parcels that have any interaction with traffic sign S1. 61--NOTYET---- (Should return P1 and P2.) 62--NOTYET--SELECT l.feature_name FROM land_parcels l, traffic_signs t 63--NOTYET-- WHERE t.feature_name = 'S1' AND 64--NOTYET-- SDO_ANYINTERACT (l.feature, t.feature) = 'TRUE'; 65--NOTYET-- 66--NOTYET---- Get the geometry for land parcel P1. 67--NOTYET--SELECT l.feature_name, l.feature.get_geometry() 68--NOTYET-- FROM land_parcels l WHERE l.feature_name = 'P1'; 69--NOTYET-- 70--NOTYET---- Get the boundary of face with face_id 3. 71--NOTYET--SELECT topology.GET_FACE_BOUNDARY('CITY_DATA', 3) FROM DUAL; 72--NOTYET-- 73--NOTYET---- Get the topological elements for land parcel P2. 74--NOTYET---- CITY_DATA layer, land parcels (tg_ layer_id = 1), parcel P2 (tg_id = 2) 75--NOTYET--SELECT topology.GET_TOPO_OBJECTS('CITY_DATA', 1, 2) FROM DUAL; 76--NOTYET-- 77--NOTYET-- 78 79END; 80 81