SQL*Plus: Release 10.2.0.2.0 - Production on Tue Oct 12 13:26:45 2010 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production With the Partitioning, OLAP and Data Mining options SQL> spool lab2rupa; ************************************************************ Spatial Queries - 1 ************************************************************************ SQL> SELECT SDO_GEOM.SDO_AREA(n.geom, 0.0000005) as forest_area FROM F10C5980G30.natural n where type ='forest'; ................ 1554 rows selected. ************************************************************ Spatial Queries - 2 ************************************************************************ SQL> SELECT max(SDO_GEOM.SDO_AREA(n.geom, 0.0000005)) as largest_park_area FROM F10C5980G30.natural n where type ='park'; LARGEST_PARK_AREA ----------------- .000336765 ************************************************************ Spatial Queries - 3 ************************************************************************ SQL> select SDO_GEOM.SDO_MBR(r.geom) from F10C5980G30.roads_major r where name='Rue Saint Vincent'; SDO_GEOM.SDO_MBR(R.GEOM)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, -------------------------------------------------------------------------------- SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR AY(-72.835569, 19.938612, -72.830725, 19.939746)) ************************************************************ Spatial Queries - 4 ************************************************************************ SQL> SELECT SDO_GEOM.SDO_CENTROID(n.geom, 0.0000005) FROM F10C5980G30.natural n WHERE name = 'Parque Central'; SDO_GEOM.SDO_CENTROID(N.GEOM,0.0000005)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), -------------------------------------------------------------------------------- SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(-70.693255, 19.7976301, NULL), NULL, NUL L) SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(-68.966362, 18.4227424, NULL), NULL, NUL L) ************************************************************ Spatial Queries - 5 ************************************************************************ SQL> select r.type, sum(SDO_GEOM.SDO_LENGTH(r.geom,0.0000005)) from F10C5980G30.roads_major r group by type; TYPE SUM(SDO_GEOM.SDO_LENGTH(R.GEOM,0.0000005)) ---------------- ------------------------------------------ primary 31.051421 secondary 27.5417142 tertiary 11.6324396 ************************************************************ Spatial Join Queries - 1 ******************************************************************** Tables->> F10C5980G30.natural, F10C5980G30.natural Join->> Self-Join where (p.name is not null) and (w.name is not null) and (p.type = 'park') and (w.type = 'water') and (SDO_GEOM.SDO_DISTANCE(p.geom, w.geom, 0.00005) <= 1) Result->> Groups of the input table tuples SQL> select p.osm_id as park_OSM_id, w.osm_id as water_OSM_id from F10C5980G30.natural p, F10C5980G30.natural w where (p.name is not null) and (w.name is not null) and (p.type = 'park') and (w.type = 'water') and (SDO_GEOM.SDO_DISTANCE(p.geom, w.geom, 0.00005) <= 1); ................ 105 rows selected. ************************************************************ Spatial Join Queries - 2 ******************************************************************** Tables->> F10C5980G30.natural, F10C5980G30.natural Join->> Self-Join where (p1.name = 'Parque Central') and (p2.name = 'Parque Central') and (p1.osm_id < p2.osm_id) Result->> Not groups of the input table tuples SQL> select SDO_GEOM.SDO_UNION(p1.geom, p2.geom, 0.00005) as GeoUnion from F10C5980G30.natural p1, F10C5980G30.natural p2 where (p1.name = 'Parque Central') and (p2.name = 'Parque Central') and (p1.osm_id < p2.osm_id); GEOUNION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 13, 1003, 1), SDO _ORDINATE_ARRAY(-70.692865, 19.797784, -70.692985, 19.797848, -70.693371, 19.797 969, -70.693629, 19.797517, -70.6932, 19.797243, -70.692865, 19.797784, -68.9666 24, 18.423275, -68.966865, 18.423006, -68.966873, 18.422469, -68.966599, 18.4222 08, -68.966135, 18.4222, -68.965861, 18.422445, -68.965869, 18.423039, -68.96609 2, 18.423275, -68.966624, 18.423275)) ************************************************************ Spatial Join Queries - 3 ********************************************************************* Tables->> F10C5980G30.natural, F10C5980G30.activities Join->> a.verified='YES' and SDO_GEOM.WITHIN_DISTANCE(n.geom,1,a.geom,0.00005)='TRUE' and n.osm_id in (select n.osm_id from F10C5980G30.natural n,F10C5980G30.activities a where n.type='park' and n.name is not null and a.verified='YES' and SDO_GEOM.WITHIN_DISTANCE(n.geom,1,a.geom,0.00005)='TRUE' group by osm_id having count(*)>=10) Result->> Groups of the input table tuples SQL> select n.osm_id,a.id from F10C5980G30.natural n,F10C5980G30.activities a where a.verified='YES' and SDO_GEOM.WITHIN_DISTANCE(n.geom,1,a.geom,0.00005)='TRUE' and n.osm_id in (select n.osm_id from F10C5980G30.natural n,F10C5980G30.activities a where n.type='park' and n.name is not null and a.verified='YES' and SDO_GEOM.WITHIN_DISTANCE(n.geom,1,a.geom,0.00005)='TRUE' group by osm_id having count(*)>=10); ................ 912 rows selected. ************************************************************ Extra Credit - 4 ***************************************************************************** SQL> select r.name as longest_road from F10C5980G30.roads_major r where SDO_GEOM.SDO_LENGTH(r.geom,0.0000005) = (select max(SDO_GEOM.SDO_LENGTH(r.geom,0.0000005)) from F10C5980G30.roads_major r); LONGEST_ROAD ------------------------------------------------ cruce de rincon ************************************************************ Extra Credit - 5 ***************************************************************************** Tables->> F10C5980G30.roads_major, F10C5980G30.activities Join->> Oneway road that is closest to the activity at latitude 18.543376 and longitude -72.338883 Result->> F10C5980G30.roads_major table tuples SQL> select * from (SELECT r.name, SDO_GEOM.SDO_DISTANCE(r.geom, a.geom, 0.00005) as Distance_from_Road from F10C5980G30.roads_major r, F10C5980G30.activities a where r.oneway=1 and a.id=(SELECT id FROM F10C5980G30.activities WHERE latitude=18.543376 AND longitude=-72.338883) AND r.name IS NOT NULL order by Distance_from_Road) WHERE ROWNUM=1; NAME DISTANCE_FROM_ROAD ------------------------------------------------ ------------------ Rue Saint-Honor?? .003385267 *********************************************************************************************************************************************************** SQL> spool off;