First Steps in Spatial Data смотреть онлайн

DOAG 20211118 edited. Version 1 of presentation.

Errata:
1. Using spatial operators
==========================
Make sure to use the SDO_RELATE operator(and variants) in the WHERE clause of spatial queries. This uses the spatial index to select matching candidates before performing the full spatial calculations only on those shapes that could not be matched directly:

Slide 32:
--------
With a as (
SELECT a.activity_id, a.activity_date, a.activity_name
, a.distance_km
, SDO_GEOM.RELATE(a.geom,'anyinteract',g.geom,25) geom_relate
FROM activities a
, my_geometries g
WHERE a.activity_type = 'Ride'
and g.geom_id = 2 /*Swains World Route*/
)
Select *
From a
Where geom_relate = 'TRUE'
Order by activity_date

Becomes this:

SELECT a.activity_id, a.activity_date, a.activity_name
, a.distance_km
FROM activities a
, my_geometries g
WHERE a.activity_type = 'Ride'
AND g.geom_id = 2 /*Swains World Route*/
AND SDO_ANYINTERACT(a.geom, g.geom) = 'TRUE'
ORDER BY activity_date

Slide 34:
---------
and SDO_GEOM.RELATE(a.geom,'anyinteract',g.geom,g.tol) = 'TRUE' /*activity has relation to reference geometry*/

becomes

and SDO_ANYINTERACT(a.geom,g.geom) = 'TRUE' /*activity has relation to reference geometry*/

Slide 52:
---------

and SDO_GEOM.RELATE(a.mbr,'anyinteract',m.mbr) = 'TRUE' and SDO_GEOM.RELATE(a.geom,'anyinteract',m.geom) = 'TRUE'

becomes

and SDO_ANYINTERACT(a.geom,m.geom) = 'TRUE'


2. Loading spatial data
=======================
Slides 9-27
-----------
Loading GPX data. This is easier to do using GDAL:

ogr2ogr -f OCI OCI:scott/tiger@spatialdb: ./swainsworldbox.gpx routes -nln gps_routes -overwrite
ogr2ogr -f OCI OCI:scott/tiger@spatialdb: ./swainsworldbox.gpx route_points -nln gps_points -overwrite

or converting the GPX files to GeoJSON then uploading using spatial studio

ogr2ogr -f GeoJSON gpx_routes.json ./swainsworldbox.gpx routes
ogr2ogr -f GeoJSON gpx_points.json ./swainsworldbox.gpx route_points


Slides 48/49:
-------------

Loading shapefiles is better achieved using GDAL (ogr2ogr) or using spatial studio



3. Coordinate systems
=====================
Slide 41:
---------
"Cannot compare geometries of different co- ordinate systems."
Only for functions (like SDO_GEOM.DISTANCE(), SDO_GEOM.RELATE() ...). But operators handle different coordinate systems. For example:

SDO_ANYINTERACT(a.geom,g.geom) = 'TRUE'

- a.geom is an activity geometry (coming from the GPX files) and is therefore in long/lat WGS84
- g.geom is an area: this can be in 27700 (British National Grid). It will be automatically transformed to WGS84 during the query.

But it is better to convert all geometries to WGS84.

In the demonstration: Search for Nuremburg didn't work because I misspelled it!