Introduction to Oracle Spatial Using Public Data - PowerPoint PPT Presentation

Loading...

PPT – Introduction to Oracle Spatial Using Public Data PowerPoint presentation | free to download - id: 260b30-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Introduction to Oracle Spatial Using Public Data

Description:

Introduction to Oracle Spatial Using Public Data – PowerPoint PPT presentation

Number of Views:323
Avg rating:3.0/5.0
Slides: 64
Provided by: richard127
Learn more at: http://oraclemagician.com
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Introduction to Oracle Spatial Using Public Data


1
Introduction to Oracle Spatial Using Public Data
  • Richard L. Flores
  • Isinglass, Inc.
  • pleides100_at_yahoo.com

2
(No Transcript)
3
About Me
  • Schooling in Geology and Business.
  • 15 years working with Geographical Information
    Systems and Computer Mapping in the Oil Gas
    Industry.
  • 16 years working with Oracle, mostly as DBA.

4
Reading Material
  • Clearly written with wonderful, downloadable
    examples for you to work through.
  • Covers most features of Oracle Spatial.

5
Reading Material
  • Wide ranging topics on the FREE acquisition of
    data and tools for the processing of geographic
    data.
  • Great tips and insight on the field of Electronic
    Cartography.

6
Agenda
  • Geographic Data Processing
  • Cartography 101
  • Oracle Spatial Products
  • Oracle Spatial Basics by Example

7
Geographic Data ProcessingThe Processing Steps
  • Data Acquisition
  • Preliminary Data Processing
  • Data Storage and Retrieval
  • Graphical Display (Visualization), Analysis, and
    Interaction.

8
Geogaphic Data ProcessingData Acquisition
  • Sources
  • Global Positioning Systems (GPS)
  • Published and Unpublished
  • Free and Commercial
  • Formats
  • Vector shapefiles, DLG, DXF, NTF, etc.
  • Raster TIFF, GIF, etc.

9
Geogaphic Data ProcessingPreliminary Data
Processing
  • May Involve
  • Digitization of paper maps
  • Vectorization of raster images
  • Transformation of Mapping Coordinates
  • Geocoding calculation of geographic coordinates
    from street addresses.

10
Geogaphic Data ProcessingData Storage and
Retrieval
  • For Oracle
  • Storage
  • Use of Oracles Geographic Data Model
  • Database design for the integration of spatial
    and non-spatial data
  • SQL and SQLLoader for loading data in Oracle.
  • Oracles shapefile to SDO utility.
  • Retrieval
  • It all comes down to SQL using Oracle Spatial
    stored procedures.

11
Geogaphic Data ProcessingDisplay, Analysis, and
Interaction.
  • Not many free programs that work with Oracle
    Spatial directly, unfortunately.
  • Many commercial programs for displaying and
    interactively querying and editing Oracle Spatial
    data such as those from ESRI, Intergraph,
    MapInfo, Autodesk, eSpatial, and Acquis.

12
Cartography 101The Shape of the Earth Some Facts
  • To accurately place a point on the earth, the
    general shape of the earth must be known. These
    shape models are called, datums.
  • The earth is not flat.1
  • The earth is not a perfect sphere.
  • The earths general shape is best described by a
    theoretical surface called a geoid.
  • For mapping, geoids are approximated by reference
    ellipsoids.

1
Assertions of the Flat Earth Society
notwithstanding.
13
Cartography 101 The Shape of the Earth North
American Datum
  • Older maps used NAD27
  • Newer maps use the more accurate NAD83 made using
    satellites and sophisticated electronic
    equipment.
  • Worldwide GPS use a datum compatible to NAD83
    called WGS84
  • The coordinates for a point on the earth can vary
    significantly depending on the datum used.

14
Cartography 101 Spatial Reference Systems
  • SRS are coordinate-based systems used to locate
    geographical entities.
  • Oracle Geographic SRS Types
  • Geodetic Latitude Longitude
  • Projected Coordinate Systems X Y where globe
    is mathematically projected onto a flat map.

15
Cartography 101Why are SRS important?
  • During analysis, if all spatial data is not in
    the same Spatial Reference System, the geographic
    layers will not overlay and the analysis will be
    in error.

16
Cartography 101Why are SRS important?
17
Cartography 101Why are SRS important?
  • During analysis, if an inappropriate Spatial
    Reference System is chosen, you may introduce
    unacceptable distortion into your maps.

18
Cartography 101Why are SRS important?
  • If you were interested in using a map to measure
    land areas, you would probably not use this
    Spatial Reference System.

19
Cartography 101Geodetic Coordinate System
  • Latitude/Longitude expressed in
    Degrees/Minutes/Seconds or Decimal Degrees
  • Parallels Lines of latitude North () South
    (-) of Equator (-90 to 90)
  • Meridians Lines of longitude East () West (-)
    of the Prime Meridian (-180 to 180)
  • Oracle Uses Decimal Degrees in the odd order,
    Longitude/Latitude

20
Cartography 101Geodetic Coordinate System
21
Cartography 101 Projected Coordinate Systems
22
Cartography 101 State Plane Coordinate Systems
  • Each state has defined SPCS zones which are
    popular for local- and state-sized areas because
    of their accuracy.
  • In General
  • Tall States, such as NM use the Universal
    Transverse Mercator Projection.
  • Wide States, such as TX use Lambert Confromal
    Conic Projection.

California Zone III, 0403
Texas South Central Zone, 4204
23
Cartography 101 Projected Coordinate Systems
  • Select map projection for display based on
  • How you intend to use the data
  • The size, location, and orientation of the area
    of interest
  • The metric property of the map you wish to
    preserve
  • Shape
  • Area
  • Distance
  • Direction
  • Scale
  • See erg.usgs.gov/isb/pubs/MapProjections/projectio
    ns.html

24
Oracle ProductsOracle Locator
  • Includes
  • Geographic Data Model
  • Query and Analysis using the Index Engine
  • Some advanced geometric functions
  • Free with the Standard or Enterprise Edition

25
Oracle ProductsOracle Spatial Option
  • Includes
  • All Oracle Locator features
  • More advanced spatial functions
  • Location enabling geocoder
  • Advanced features such as routing, network
    analysis, and georaster.
  • Added-cost option with the Enterprise Edition

26
Oracle SpatialGeneral
  • An integrated set of functions and procedures
    that enables spatial data to be stored, accessed,
    and analyzed quickly and efficiently in an Oracle
    database.
  • A schema (MDSYS) that prescribes the storage,
    syntax, and semantics of supported geometric data
    types.
  • A spatial indexing mechanism
  • A set of operators and functions for performing
    area-of-interest queries, spatial join queries,
    and other spatial analyis operations.
  • Administrative utilities.

27
Oracle SpatialSome Definitions
  • There are other, more complex elements, including
    collections of one or more elements called
    Geometries.
  • A Layer is collection of Geometries having
    similar attributes such a state boundaries,
    roads, or rivers.

28
Oracle SpatialMore on Geometries and Layers
Table, STATES LAYER
A GEOMETRY (outline) of a state is in a single
column of each row.
A GEOMETRY may contain more than one element.
29
Oracle Spatial by ExampleScenario
  • You wish to open an upscale beauty salon in
    central Contra Costa county, California, catering
    to wealthier, older women.
  • You would like to be close to a major
    thoroughfare for ease of access.
  • You dont want to be too close to any competitors.

30
Oracle Spatial by ExampleIdentify Types and
Sources of Data Needed to Support Decision
  • Competitors Internet Search Engine
  • Demographic (Age, Gender, Income) U.S. Census
    Bureau
  • Roads U.S. Geological Survey

31
Oracle Spatial by ExampleCompetitor Data Table
CREATE TABLE beauty (id
NUMBER(38), name
VARCHAR2(100), full_address
VARCHAR2(100), city_state
VARCHAR2(50),
street_number VARCHAR2(10),
street_name VARCHAR2(20),
street_type VARCHAR2(15),
street_prefix VARCHAR2(10),
street_suffix VARCHAR2(10),
city VARCHAR2(40),
state VARCHAR2(2),
postal_code VARCHAR2(16),
location MDSYS.SDO_GEOMETRY)
32
Oracle Spatial by ExampleCompetitor Data
Spatial Metadata
INSERT INTO user_sdo_geom_metadata VALUES
('BEAUTY', -- Geometry
Table 'LOCATION', --
Geometry Column SDO_DIM_ARRY (
SDO_DIM_ELEMENT ('LONGITUDE', -- Longitude
Text -180, --
Lower Boundary 180,
-- Upper Boundary
0.5), -- Tolerance
SDO_DIM_ELEMENT ('LATITUDE', -- Latitude
Text -90, --
Lower Boundary 90,
-- Upper Boundary
0.5) -- Tolerance ),
8307 -- (SRID)
DatumWGS84 )
33
Oracle Spatial by ExampleCompetitor Data
Spatial Index
CREATE INDEX beauty_spatial_idx ON beauty
(location) INDEXTYPE IS MDSYS.SPATIAL_INDEX
  • R-Tree Index
  • These are unlike regular Oracle indexes and
    special steps must be taken with their
    administration.

34
Oracle Spatial by ExampleCompetitor Data Source
Name ---------------- ID NAME FULL_ADDRESS
CITY_STATE STREET_NUMBER STREET_NAME
STREET_TYPE STREET_PREFIX STREET_SUFFIX CITY
STATE POSTAL_CODE LOCATION
Extract list of competitors and their addresses
from Search Engine.
While very useful, it doesnt provide any
directly mappable data.
35
Oracle Spatial by ExampleCompetitor Data
Geocoding
  • The Geocoder will
  • Standardize Address Name and,
  • Using a database with the coordinates and street
    addresses of each intersection,
  • Interpolate the location of the given address.
  • Oracle Spatial Option geocoder added-cost
  • Third party sells spatial database used to
    calculate the coordinates

36
Oracle Spatial by ExampleCompetitor Data
Geocoding
Solution Use Perl Program against internet
geocoding website.
!/usr/local/bin/perl simplest_xmlrpc.pl use
XMLRPCLite use DataDumper use strict use
warnings my where shift _at_ARGV or die
"Usage 0 \"1 Main St, Anytown, KS\"\n" my
result XMLRPCLite -gt proxy(
'http//rpc.geocoder.us/service/xmlrpc' ) -gt
geocode (where) -gt result print Dumper
result
From Mapping Hacks, Tips Tools for Electronic
Mapping
37
Oracle Spatial by ExampleCompetitor Data
Geocoding
simplest_xmlrpc.pl 1355 N. Main, Walnut Creek,
CA" VAR1 'number' gt '1355',
'street' gt 'Main', 'lat' gt
'37.898365', 'state' gt 'CA',
'city' gt 'Walnut Creek', 'zip' gt
'94596', 'suffix' gt '', 'long' gt
'-122.060445', 'type' gt 'St',
'prefix' gt 'N'
Name ---------------- ID NAME FULL_ADDRESS
CITY_STATE STREET_NUMBER STREET_NAME
STREET_TYPE STREET_PREFIX STREET_SUFFIX CITY
STATE POSTAL_CODE LOCATION
38
Oracle Spatial by ExampleCompetitor Data
SDO_GEOMETRY Object-Relational Type
UPDATE beauty SET location
SDO_GEOMETRY (2001, -- Geometry
Type 2-D Point 8307, --
SRID, Datum WGS84 SDO_POINT_TYPE
(-122.060445, -- Longitude
37.898365, -- Latitude NULL),
NULL, NULL )
WHERE id 430
39
Oracle Spatial by ExampleCompetitor Data Data
Display
  • eSpatial iSmart Explorer free on OTN
  • OEM Spatial Index Advisor
  • Oracle Mapviewer
  • For serious users, many commercial products.

40
Oracle Spatial by ExampleNon-Spatial Demographic
Data Table
CREATE TABLE census_data ( CENSUS_TRACT
VARCHAR2(10)NOT NULL, MED_HOUSE_INCOME
NUMBER(38), GENDER_TOTAL NUMBER(38),
FEMALE_GE_40 NUMBER(38))
41
Oracle Spatial by ExampleNon-Spatial Demographic
Data Source
42
Oracle Spatial by ExampleSpatial Census Tract
Data Source
  • www.census.gov/geo/www/cob/tr_metadata.html
  • Has geographic boundaries of Census Tracts which
    can be loaded into Oracle Spatial.
  • Choose state and ARCVIEW Shapefile format to
    download file for California. These files are
    sometimes called ESRI Shapefiles.

43
Oracle Spatial by ExampleSpatial Census Tract
Data Pre-processing
  • shp2sdo utility downloadable from Oracle will
    create SQL and SQLLoader data and control files
    for creating Spatial objects and loading
    shapefile data into Oracle Spatial.

Creates census_tracts.sql, census_tracts.ctl,
census_tracts.dat
44
Oracle Spatial by ExampleSpatial Census Tract
Data Loading census_tract.sql
DROP TABLE CENSUS_TRACTS CREATE TABLE
CENSUS_TRACTS ( AREA NUMBER,
PERIMETER NUMBER, TR06_D00_ NUMBER,
TR06_D00_I NUMBER, STATE
VARCHAR2(2), COUNTY VARCHAR2(3), TRACT
VARCHAR2(6), NAME
VARCHAR2(90), LSAD VARCHAR2(2),
LSAD_TRANS VARCHAR2(50), GEOM
MDSYS.SDO_GEOMETRY)
45
Oracle Spatial by ExampleSpatial Census Tract
Data Loading
  • In SQLPlus
  • connect spatial/spatial
  • _at_census_tracts.sql
  • Run SQLLoader
  • sqlldr spatial/spatial census_tracts
  • In SQLPlus
  • connect spatial/spatial
  • EXECUTE SDO_MIGRATE.TO_CURRENT(CENSUS_TRACTS,GE
    OM)

46
Oracle Spatial by ExampleSpatial Census Tract
Data Display
  • Census tract outlines.
  • You can CREATE TABLE SELECT AS on state06
    and county013 to get just Contra Costa county.

47
Oracle Spatial by ExampleRoad Data Source
  • seamless.usgs.gov
  • Bureau of Transportation Statistics from U.S.
    Geological Survey.
  • shapefiles

48
Oracle Spatial by ExampleRoad Data Display
49
Oracle Spatial by ExampleAnalysis Criteria
Definition
  • Within 2 miles of census tracts in which
  • The Median Household Annual Income is greater
    then 100K and
  • Over 30 of the people are women 40 years or
    older
  • Within ½ mile of a major road
  • Not within ½ mile of a competitor

50
Oracle Spatial by ExampleAnalysis Oracle
Spatial Buffers
Original Geometry Buffered Geometry
Point
Line String
Polygon
51
Oracle Spatial by ExampleAnalysis Target Census
Tract Buffer
CREATE TABLE target_tract_buffer AS SELECT
SDO_AGGR_UNION(SDOAGGRTYPE
(SDO_GEOM.SDO_BUFFER( a.geom,
-- geometry column 2.00,
-- Distance 0.5,
'arc_tolerance0.005 unitmile'), -- Units
0.5)) geom FROM census_tracts a,
census_data b WHERE b.census_tract
a.name AND b.med_house_income
gt100000 AND b.female_ge_40/b.gender_total gt
0.30 AND a.state '06'
AND a.county '013'
52
Oracle Spatial by ExampleAnalysis Target Census
Tract Buffer
53
Oracle Spatial by ExampleAnalysis Target Census
Tract Buffer
54
Oracle Spatial by ExampleAnalysis Major Road
Buffer
CREATE TABLE road_buffer AS SELECT prefix, name,
type, suffix, SDO_AGGR_UNION(
SDOAGGRTYPE( SDO_GEOM.SDO_BUFFER(
a.geom, -- geometry
column 0.50, --
Distance 0.5,
'arc_tolerance0.005 unitmile'), -- Units
0.5)) geom FROM roads a WHERE (name
'ACALANES' AND type 'AVE') OR (name
'ACALANES' AND type 'RD')
OR (name 'YGNACIO VALLEY' AND
type 'RD')
55
Oracle Spatial by ExampleAnalysis Major Road
Buffer
56
Oracle Spatial by ExampleAnalysis Competitor
Buffer
57
Oracle Spatial by ExampleAnalysis Spatial
Operations
58
Oracle Spatial by ExampleAnalysis Spatial
Operations
CREATE TABLE target_site_wocomp AS SELECT
SDO_AGGR_UNION(SDOAGGRTYPE(c.geom,0.5)) geom
FROM (SELECT SDO_GEOM.SDO_INTERSECTION(
a.geom, b.geom, 0.5) geom FROM
target_tract_buffer a,road_buffer b)
c)
59
Oracle Spatial by ExampleAnalysis Spatial
Operations
CREATE TABLE target_site AS SELECT
SDO_AGGR_UNIION(SDOAGGRTYPE(a.geom,0.5)) geom
FROM (SELECT SDO_GEOM.SDO_DIFFERENCE(
b.geom, c.geom, 0.5) geom FROM
target_sid_wocomp b,competitor_buffer c)
a -- Create spatial metadata and index for
target_site -- and target_site_wocomp after
creation.
60
Oracle Spatial by ExampleAnalysis Final Display
61
Oracle Spatial by ExampleAnalysis Final Display
Using ESRI ArcMap
62
Geospatial Data and The DatawarehouseOverlooked
Opportunity
"I have always been puzzled by the chasm
separating the data warehouse community and the
geographic information systems (GIS) community.
Very few "conventional" data warehouses exploit
their data with a map-driven approach, yet these
same data warehouses are rich with geographic
entities including addresses, point locations,
sales districts, and higher level political
geographies." -- Ralph Kimball see also,
http//www.ecommercetimes.com/story/52791.html
63
Introduction to Oracle Spatial Using Public Data
Any Questions?
About PowerShow.com