cioh 1 oracel spatial - inocar bd1/cioh oracle... · • primero compara geometrías simplificadas...

Oracle Logo

Tecnologías Espaciales de ORACLETecnologías Espaciales de ORACLE

Carlos Niño

Carlos.nino@geospatial.com.coCarlos.nino@geospatial.com.co

1

TópicosTópicos

• Entender como utilizar tecnologías espacialesEntender como utilizar tecnologías espaciales de ORACLE 

• Principios Tecnológicos de manejo de• Principios Tecnológicos de manejo de información geográfica de ORACLE

C í i• Características

• Locator, Spatial, Mapviewer, Mapbuilder, LRS, 3D y más.   

Evolucion de GIS

Application

MonolithicGIS

ProprietaryFiles

DBMSTradicional

Spatial Middleware

Application

ProprietaryAPIs

DatabaseCon

funcionalidadEspacial

Map Server

Application

OpenAPIs

El pasado Hoy

Standalone MiddlewareProprietario

PlataformaInternet

DatabaseTradicional

Oracle10g Spatial

Spatial Indexing

Accesso rapidoa su data espacial

Analisis Espacial con SQL

Oracle11g Spatial - Funcionalidad

SELECT a.nombre_de_cliente, a.numero_de_telefonoFROM clientes_de_seguro aWHERE sdo_within_distance( a.geom, via_de_huracan_geom, ‘distance = 20 unit = km’) = ‘TRUE’;

Spatial Data Types

Todo dato espacial almacenado en la base de datos

Points

Polygons

Lines

f1

f2n1

n2

e1

e2e3

e4

Spatial Data Types

• Puntos• Lineas• Poligonos• Poligonos con huecos• Circulos• Arcos• Rectangulos• Elementos Compound

R‐Tree – Índice EspacialR Tree Índice Espacial

• Cual de millones de calles tiene interacción con esta región?g

• Primero compara geometrías simplificadas – o rectángulos 

• Aplica optimización interior – Solo compara coordenadas cuando es necesario 

Data Espacial en Tablas de Oracle

Fisher Circle

85th St.

Coop Court

CallesCLAVE

1

2

3

MATERIA

Asfalto

Asfalto

Asfalto

NOMBRE

Fisher Cir.

Coop Ct.

85Th St.

VIAS

4

2

2

GEOMETRIA

R-Tree – Indice Espacial

• En el indice, cada geometria esta simplificada como un rectangulo

• Mas rapido comparar rectangulos• Filtro primario solo compara simplificaciones• Filtro secundario solo compara lo resultados de un filtro

primario

SpatialLayer

PrimaryFilter

R-TreeSpatialIndex

ReducedData Set

SecondaryFilter

SpatialOperators

ResultSet

Operadores Espaciales

• Mucha funcionalidad con operadores

• Implementado como extensiones a SQL• Operadores Topologicos

– Inside Contains– Touch Disjoint– Covers Covered By– Equal Overlap Boundary

• Operadores de Distancia– Within Distance– Nearest Neighbor

Inside

500 meters

Within Distance

Analisis Espacial Con SQL

SQL> SELECT a.building_id 2> FROM base_buildings a, 3> base_buildings b 4> WHERE b.building_id = 902 5> AND MDSYS.SDO_WITHIN_DISTANCE( 6> a.Location, b.Location, 7> ‘distance=500 unit=meter’) 8> = ‘TRUE’;

Encuentra edificios a 500 metros del edificio #902

500 meters

Funciones Espaciales

• Funciones espaciales dentro del servidor

• Union, Difference, Intersect, etc.• Spatial Aggregates• Buffer• Point at bearing• To_GML• Geometry Validations• Length• Area• Distance• Etc…

Union

XOR

Intersect

Original

Difference

Buffer

Longitude/Latitude ConsideracionesWhole Earth Model (Modelo del mundo completo)

• P1 y P2 separado por 1 grado (aproximadamente 111 Km)• P3 y P4 separado por 1 grado (aproximadamente 10 Km)• Oracle Spatial puede hacer analisis directamente en dato

Longitude/Latitude

P1 P2

P3 P4

Coordinate Systems

• Soporte para el modelo del mundo completo (latitude/longitude o “whole Earth model”)

• Calculos elipticos• Calculos precisos para area y distancia (soporte para unidades)• Soporte para geometrias que cruzan los polos o el meridiano 180

• Soporte para dato proyectado• Calculos cartesianos• Soporte para muchas sistemas: UTM, State Plane, y mucho mas…

• Soporte para data que no tiene relacion al mundo, por ejemplo, sistema local (en centimetros) para piezas de un avion

• Suporte para EPSG – Oracle 10g Version 2 en adelante

Via De Un Barco Que Cruza el Meridiano 180

Oracle SpatialLinear Referencing Systems

(LRS)

Que es Linear Referencing (LRS)?

(5,10,0) (30,10,27)

(15,5,11.2) (40,5,38)

(50,15,53.8)

(55,20,60)

(45,10,44)

Usado en muchas aplicaciones de GIS, como:• transporte (red de calles)• utilities (tuberia de petroleo, lineas electricas)

Kilometro 0 Kilometro 27

Kilometro 60

LRS Concepts

(5,10,0) (30,10,27)

(15,5,11.2) (40,5,38)

(50,15,53.8)

(55,20,60)

(45,10,44)

Corte de medida 5 a 20“Dynamic Segmentation”

(53,18) queda en medida 57.52“Locate Point”

Pipeline Asset Management ExampleSystem requirements

– Linear reference each section of the pipeline and store: associated diameters locations valve locations

PIPE_ID GEOMETRY INSTALL_DATESUS1105 lrs geom 05-Feb-1997SUS1106SUS1107

lrs geomlrs geom

08-Feb-199709-Feb-1997

...

Pipeline Table

Pipeline Asset Management Example (continued)

Pipe_ID Measure Valve IDSUS1105 230.56 10SUS1105SUS1105

243.17275.84

1112

...

Section # Pipe_ID Start M. End M. Diameter203 SUS1105 230.56 243.17 0.50204205

SUS1105SUS1105

243.17275.84

275.84302.21

0.480.43

...Pipeline Section Table

Pipeline Valve Table

Note: No explicit vertices are needed to define the pipeline sections or valves

• US Airspace Boundary Crossing Application• Oracle Spatial functions to calculate intersection of flight paths and

US airspaces. • Linear Referencing to interpolate the time and altitude for entry/exit

points of US airspace.• Accurately charge foreign carriers for the amount of time in US

airspace.(Longitude, Latitude, Altitude, Time)

Actual radar blip

Computed(Lon/Lat, Alt, Time)

LRS Application – Oracle Spatial

Oracle SpatialSpatial Aggregate Functions

Spatial Aggregate Functions - Ejemplo

Genera el estado de New York, agregando condadosSELECT SDO_AGGR_UNION(sdo_aggr_type(a.geometry, 0.5)FROM countiesWHERE state = 'New York';

SDO_AGGR_CONVEXHULL

Pozos sin contaminacion

Pozos contaminados

• Imagina un elastico alrededor de pozos contaminados• Dinamicamente genera una region nueva• Mas analisis con la region nueva, por ejemplo:

• Busca empresas quimicas dentro de 10 km al limite de la region nueva

Companeros de Oracle Spatial Geospatial Tool Providers

Specialty Systems Integrators Data Providers

NAVIGATE

Oracle Spatial 10g New Features Summary

• GeoRaster data type• Persistent Topology data type• Network Data Model• Spatial Analysis and Mining• Geocoder in the database• Routing Engine

Oracle SpatialGeoRaster

Raster Data and Cell Size

Cuadriculos grandes

Cuadriculos pequenos

Raster/Vector DiferenciasVector Data Vector Coordinates Raster Data

-74.1651749, 41.339141

-74.1651749, 41.339141,-73.4284481, 40.678193,-72.9792214, 41.686228

-74.1651749, 41.339141,-74.1651749, 39.559004,-72.9792214, 39.559004,-72. 9792214, 41.339141

Grid Raster DataA Value Attribute Table (VAT) is used to map the stored numeric

values to the meaning of that valueAn example value attribute table for geological raster data

A value attribute table can also contain user-defined columns

Stored cell values

6 1 1 1 1 6611 3 8 7 9 1973 1 8 3 3 1953 3 3 9 8 1970 3 3 3 9 0190 8 8 9 9 0010 0 2 9 1 000

2 5 4 9 1 679

CELL VALUE GEOLOGICAL PERIOD 1 Quaternary 2 Tertiary 3 Paleocene-Cretaceous 4 Mesozoic 5 Gondwana 6 Early Palaeozoic 7 Proterozoic 8 Early Proterozoic 9 Archaean 0 Blank Cell (no data)

Grid Raster Data

A COLORMAP table is used to map the stored numeric values to the display characteristics of that value

An example COLORMAP table for geological raster data

6 1 1 1 1 6611 3 8 7 9 1973 1 8 3 3 1953 3 3 9 8 1970 3 3 3 9 0190 8 8 9 9 0010 0 2 9 1 000

2 5 4 9 1 679CELL VALUE Red Green Blue

1 255 255 0 2 82 123 67 3 142 230 98 4 96 121 228 5 145 231 243 6 255 51 0 7 203 188 224 8 195 135 75 9 204 102 255 0 0 0 0

Geological Map of India (Grid Raster Data)

QuaternaryTertiaryPaleocene-CretaceousMesozoicGondwanaEarly PalaeozoicLate ProterozoicEarly ProterozoicArchaean

Raster Data Concepts – (Digital Imagery)

• Digital Imagery - a specialized type of raster dataExamples include:

Satellite imagery Airborne photographs others…

Image Data

Some bands may accentuate different features

Blocking

• Index very large rasters into smaller blocks• Interleave multi-band raster

• Band sequential (BSQ)• Band interleaved by pixel (BIP)• Band interleaved by line (BIL)

4 4 blocks

Pyramids

Pyramid level 0(raw data)

Pyramid level 1

Pyramid level 2

Pyramidlevel

GeoRaster: Compression

• Natively support two industry standard compression techniques • JPEG (lossy)

– JPEG-B (abbreviated baseline JPEG format)– JPEG-F (full-format baseline JPEG format)

• DEFLATE (lossless)– (a.k.a. ZIP)

• All GeoRaster operations work on compressed/uncompressed GeoRaster objects• Automatic decompression on sub-set operations

Advanced New FeatureGeocoder• Geocoding Engine included in the database• Generates latitude/longitude (points) from address• Supports:

• International addressing standardization• Formatted and unformatted addresses• Transaction and batch capabilities

• Base data available from NAVTEQ & TeleAtlas• Navteq partners (ADCI)• Download sample data from ADCI

• Servlet model – new in 10g Release 2• Regional partners around the world, for example Gempi for

Brasil support

Advanced New FeatureRouter

• Routing algorithms included in the database• Generates driving directions• XML API• Base data available from NAVTEQ & TeleAtlas

• Download sample data from ADCI• Regional partners around the world, for example

Gempi for Brasil support

Oracle SpatialModelo Topologico

Introducion

G1

G2

G3

G4

G5

f1

f2

f3f0

n1

n2

n3

n4

n5 n6

n7

n8 n9

e1

e2

e3

e4

e5

e6e7

e8e9

e10

Modelo Objecto Modelo Topologico

Un Ejemplo Topologico

• Parcelas• Parcela 1 asociado con poligono F1 • Parcela 2 asociado con poligono F2• Las dos parcelas incluyen a segmento E3

• Rios• Rio 1 asociado con segmento E3 (y segmentos E1 y E5)

N1E5

N4N3N2E1

E4E3

E2

F2

F1

Parcela 2

Parcela 1

Rio 1

Oracle SpatialNetwork Data Model

(Modelo de Red)

Network Data Model (Modelo de Red)

• Open Data Model For Graph Analysis• Store network (graph) structure in the database• Maintains connectivity of the network• Attributes at link and node level

• Supports Network solutions (Tracing & Routing)• Transportation and Transit Solutions• Field Service, Logistics• Location based Services and Telematics

• Bio-Info Pathways (Life Sciences)• Biological Pathways• Protein-Protein Interaction

• Network Viewer

Oracle Spatial 10gSpatial Analysis and Mining

Spatial Analytic Functions

• Discovery based on Spatial Patterns • Cluster analisis (analisis agrupado)• Location prospecting (candidatos probables)

SDO_SAM.AGGREGATES_FOR_GEOMETRY: Example

SELECT SDO_SAM.AGGREGATES_FOR_GEOMETRY( 'GEOD_COUNTIES', 'GEOM', 'sum', 'totpop', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-73.943849, 40.6698,NULL), NULL, NULL), 'distance=3 unit=mile')FROM DUAL;-----------------------723570.362

% COUNTY COVERED COUNTY ST TOTPOP BY WINDOW--------- -- ------ ----------Queens NY 1951598 00.5437756Kings NY 2300664 31.0430579New York NY 1487536 00.0188785

Queens

Kings

New York(-73.943849, 40.6698)

Standards Are Important SQL/MM Methods and OGC Standards

• GET_WKB() – Get Well Known Text• GET_WKT() – Get Well Known Binary set long 500; SELECT A.GEOM.GET_WKT() FROM polygon_table a WHERE id = 1;

A.GEOM.GET_WKT() ---------------- POLYGON ((146.0 66.0, 148.0 66.0, 148.0 68.0, 146.0 68.0, 146.0 66.0))

Fully exploit and support Standards-based, IT Architecture

• Scalability - large data volume• Availability – tens of thousands of users• Security – protect sensitive location data• Performance – timely query response• Accessibility – to enterprise applications• Manageability – leverage IT resources

Local Partitioned Spatial Indexes

• Major benefits are performance and manageability of very large data sets

• EXCHANGE PARTITION INCLUDING INDEXES supported for spatial indexes too

Customer example:

• Requirement:• Ingest and maintain 2 days of weather data online• 270,000 samples every 30 seconds

• Implemented with:• 30 second partitions (5760 partitions over 2 days)• New partitions rolled on, older partitions rolled off

Transportable Tablespaces and Spatial

• Very fast way to move large spatial dataset from one instance to another

• Can also archive data in TTS format, for example, rolling partitions that age out• Spatial data - Transportable• Spatial index - Also Transports

Oracle Application Server MapViewer

Oracle Application Server 10.1.3 SOA MapViewer• Web Map Server (genera mapas para el web)

• Viene incluido en el Oracle Application Server. No tiene costo adicional.

• Integrado con Oracle Spatial y Oracle Locator• Es una manera facil para publicar mapas generados con el

dato espacial almacenado en el tipo de dato nativo espacial de Oracle

• API:• XML API• Java API• JSP Tag library• OGC WMS• API AJAX en la version MapViewer 10.1.3 SOA

Oracle Application Server MapViewerGenera Mapas al Web

Footnote

Legend

Themes

Earthquakes

Title

Display: Generate Powerful Maps

MapViewer with JDeveloper and Oracle BI Enterprise Edition

Oracle JDeveloper

Oracle BI EE

Oracle Application Server Version 10.1.3 SOA

Mapbuilder and MapViewer

MapBuilder 10.1.3 SOA

MapViewer 10.1.3 SOA - (Oracle Maps)

Oracle Locator – Sin Costo Adicional

• All Vector Data Types• Spatial Operators

• Topological• Distance

• Distance Function• SDO_UTIL package• Coordinate Transformations• Long Transactions• Table Partitioning*• Object Replication*• Oracle Label Security

Oracle Locator

Viene incluido sin costo adicional con:

• Oracle XE• Oracle Standard Edition• Oracle Standard Edition One• Oracle Enterprise Edition

Oracle Spatial

• All Locator features• GeoRaster Data Type• Topology Data Type• Network Data Model• Geocoding• Routing• Linear Referencing• Spatial functions

• aggregates• centroid, union, intersection, etc

• 3D Data type• LIDAR and TIN Data Types

• WFS-T, CSW (Web Services)

• Oracle Enterprise Edition • Lisenced Option

Aplicaciones Espaciales No Siempre Necesitan un GIS• Toda organizacion quiere reducir costos• La Tecnologia Espacial de Oracle contiene:

• Oracle Locator– Oracle Standard Edition– Oracle Enterprise Edition

• Oracle Application Server MapViewer– Java Edition– Standard Edition– Enterprise Edition

• Utilize lo que ya tiene

Oracle 11g Spatialfeatures

3D Data Management Oracle Spatial 11g

3-D Spatial Types

• 3D Types for points/lines/polygons/solids• Specialized types for large volumes of 3D point data

• POINT CLOUD - Represent scenes as a set of 3-d points obtained using laser scanners etc.

• TIN – triangulated surface representation • 3D Coordinate Systems • Address growing number of 3-d applications

• GIS, CAD/CAM• VR, Medical Applications• City Modeling

SDO_GEOMETRY for 3D Data

• Points• Lines• Simple Surfaces

• All points of a surface lie in a 3D plane• A 3 point 3D polygon is the simplest surface• A simple surface can have any polygonal shape

• Composite surfaces• has one or more connected simple surfaces• It can be closed or open• The simple surfaces in a composite surface can not cross each

other• surface of a cube is an example of a composite surface

– Cube has six simple surfaces– Each simple surface is a 3D square

(2,0,2)(4,2,2)

(4,0,4)

Y

Z

SDO_GEOMETRY for 3D Data

• Simple Solids • Solids are composed of closed surfaces• It has to have one outer surface and one or more

interior surfaces• Cube is an example of a simple solid• A pyramid is another example of a simple solid

• Composite Solids• Consists of n simple solids as a connected solid• Can be represented as a simple solid with a composite

surface• Topologically there is an equivalent simple solid, but the

composite solid representation is easier – Example: A building composed of rooms

• Simple, composite solids: • Always define a single contiguous volume

SDO_GEOMETRY for 3-D Data

• Support for multi-points, multi-lines, multi-surfaces, multi-solids• Multi-solid is different from composite solid

• Follows GML3, ISO 19107 Specifications• Export and Import support for CityGML

Some 3D SDO_GEOMETRY operations

• Spatial Indexing: R-trees• SQL Spatial Operator support

• SDO_FILTER, SDO_ANYINTERACT, SDO_WITHIN_DISTANCE, SDO_NN

• PL/SQL Functions• VALIDATE_GEOMETRY• SDO_DISTANCE, SDO_GEOM.ANYINTERACT• SDO_AREA, SDO_LENGTH, SDO_VOLUME• SDO_CS.TRANSFORM (e.g. to/from CityGML)

Specialized Types for High Density 3D Point Data

• Large volumes of point data acquired by sensors• LIDAR• Sensors used to collect data inside buildings

• Millions of points used to model a scene• SDO_GEOMETRY is not suitable for such data• POINT CLOUD data type introduced to efficiently

manage this type of point data• TIN to create triangulation of such points

POINT CLOUD in Oracle

Ordinary Oracle table with a POINT_CLOUD column Each point cloud instance is associated with a block table

...

SDO_PC object

PC_ID MORE METADATA ......BLK_TABLE

zone_id population … POINT_CLOUD – SDO_PC 1 2000 … point cloud 1 2 1500 … point cloud 2

Baltimore_PC table

Table Column:POINT_CLOUD

Pts:LOB

PC_BLK_EXTENT: SDO_GEOMETRY

BlkidObjidTABLE of SDO_PC_BLK type

OTHERATTRS…

Each Point Cloud is Blocked

Point cloud partitioned into multiple blocks for granularity of access Each block is uniquely identified by Objid and Blkid Each point in the lob can have attributes stored in an optional table

Operations• Spatial Operators

• Any spatial operator to search block extents:– ANYINTERACT– INSIDE– Within Distance– Nearest Neighbor– Etc.,

• CLIP_PC (Clip Point Cloud)• 2D or 3D query window• Returns points for any block whose extent

intersects the query window• Only points that intersect the query window are

returned.

CREATE PC Block

CLIP_PC

Point Data

PC_BLK_TABLE

Spatial Operators

Operations

TIN: Triangulated Irregular Network• Definition

• Vector-based topological data model used to represent terrain/surface• Contain a network of irregularly spaced triangles • 3D surface representation derived from irregularly spaced points• Each sample point has an x, y coordinate and a z value or surface value

8177

2226

3565

4444

6513

5632

3651

ZYXNode No

TIN in Oracle

Ordinary Oracle table with a TIN column Each TIN instance instance is associated with a block table

...

SDO_TIN object

TIN_ID MORE METADATA ......BLK_TABLE

zone_id population … TIN - SDO_TIN 1 2000 … tin 1 2 1500 … tin2

Baltimore_TIN table

Table Column:TIN

Triangles:LOB

TIN_BLK_EXTENT: SDO_GEOMETRY

BlkidObjidTABLE of SDO_TIN_BLK type

OTHERATTRS…

Each TIN is Blocked

TIN partitioned into multiple blocks for granularity of access Each block is uniquely identified by Objid and Blkid Each block has a LOB with all the triangles for that block Each point in the LOB can have attributes stored in an optional table

Operations• Spatial Operators

• Any spatial operator to search block extents:– ANYINTERACT– INSIDE– Within Distance– Nearest Neighbor– Etc.,

• CLIP_TIN (Clip TIN)• 2D or 3D query window• Returns points for any block whose extent

intersects the query window• Only triangles that intersect the query window are

returned.

CREATE TIN

CLIP_TIN

Point Data(table/point cloud)

TIN_BLK_TABLE

Spatial Operators

Operations

3D Coordinate Systems

• Vertical Coordinate System• One dimensional• Height with regard to a base source.• For example, with regard to sea level.

• Compound Coordinate System• Geographic-2D (longitude/latitude) plus a vertical

coordinate system• Projected-2D plus a vertical coordinate system

• Geographic-3D Coordinate System• Height with regard to the surface of an ellipsoid.

Spatial Feature – Web Services

• WFS 1.0 - Transactional• CSW 2.0 – Catalog Service Web

Mas Recursos...

Examples, white papers, downloads, discussion forum, sample data, customer successes, partner information, more

http://www.oracle.com/technology/products/spatial/

http://www.oracle.com/technology/products/mapviewer/

Ejemplos On lineORACLE SPATIAL

www.dondequeda.com.co

P &Preguntas & Inquietudes