Prologue to Oracle Spatial Using Public Data

Presentation Transcript

Prologue to Oracle Spatial Using Public Data Richard L. Flores Isinglass, Inc. pleides100@yahoo.com

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, generally as DBA.

Reading Material Clearly composed with superb, downloadable cases for you to work through. Covers most elements of Oracle Spatial.

Reading Material Wide extending points on the FREE obtaining of information and devices for the preparing of geographic information. Awesome tips and knowledge on the field of Electronic Cartography.

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

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

Geogaphic Data Processing Data Acquisition Sources Global Positioning Systems (GPS) Published and Unpublished Free and Commercial Formats Vector: shapefiles, DLG, DXF, NTF, and so on. Raster: TIFF, GIF, and so forth

Geogaphic Data Processing Preliminary Data Processing May Involve Digitization of paper maps Vectorization of raster pictures Transformation of Mapping Coordinates Geocoding: figuring of geographic directions from road addresses.

Geogaphic Data Processing Data Storage and Retrieval For Oracle Storage Use of Oracle's Geographic Data Model Database plan for the mix of spatial and non-spatial information SQL and SQL*Loader for stacking information in Oracle. Prophet's "shapefile" to "SDO" utility. Recovery It all comes down to SQL utilizing Oracle Spatial put away systems.

Geogaphic Data Processing Display, Analysis, and Interaction. Relatively few free projects that work with Oracle Spatial specifically, sadly. Numerous business programs for showing and intuitively questioning and altering Oracle Spatial information, for example, those from ESRI, Intergraph, MapInfo, Autodesk, eSpatial, and Acquis.

Cartography 101 The Shape of the Earth: Some Facts To precisely put a point on the earth, the general state of the earth must be known. These shape models are called, datums . The earth is not level. 1 The earth is not an impeccable circle. The world's general shape is best portrayed by a hypothetical surface called a geoid . For mapping, geoids are approximated by reference ellipsoids . 1 Assertions of the Flat Earth Society in any case.

Cartography 101 The Shape of the Earth: North American Datum Older maps utilized NAD27 Newer maps utilize the more precise NAD83 made utilizing satellites and advanced electronic gear. Overall GPS utilize a datum good to NAD83 called WGS84 The directions for a point on the earth can fluctuate fundamentally relying upon the datum utilized.

Cartography 101 Spatial Reference Systems SRS are facilitate based frameworks used to find geological elements. Prophet Geographic SRS Types Geodetic : Latitude & Longitude Projected Coordinate Systems : X & Y where globe is scientifically "anticipated" onto a level guide.

Cartography 101 Why are SRS vital? Amid examination, if every spatial dat is not in the same Spatial Reference System, the geographic layers won't overlay and the investigation will be in blunder.

Cartography 101 Why are SRS vital?

Cartography 101 Why are SRS essential? Amid examination, if an unseemly Spatial Reference System is picked, you may bring unsuitable mutilation into your maps.

Cartography 101 Why are SRS critical? In the event that you were keen on utilizing a guide to gauge arrive zones, you would likely not utilize this Spatial Reference System.

Cartography 101 Geodetic Coordinate System Latitude/Longitude communicated in Degrees/Minutes/Seconds or Decimal Degrees Parallels: Lines of scope 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 request, Longitude/Latitude

Cartography 101 Geodetic Coordinate System

Cartography 101 Projected Coordinate Systems

Cartography 101 State Plane Coordinate Systems Each state has characterized SPCS zones which are mainstream for nearby and state-sized regions due to their precision. In General: "Tall" States, for example, NM utilize the Universal Transverse Mercator Projection. "Wide" States, for example, TX utilize Lambert Confromal Conic Projection. California Zone III, 0403 Texas South Central Zone, 4204

Cartography 101 Projected Coordinate Systems Select guide projection for show in view of: How you mean to utilize the information The size, area, and introduction of the territory of intrigue The metric property of the guide you wish to protect: Shape Area Distance Direction Scale See erg.usgs.gov/isb/bars/MapProjections/projections.html

Oracle Products Oracle Locator Includes Geographic Data Model Query and Analysis utilizing the Index Engine Some progressed geometric capacities Free with the Standard or Enterprise Edition

Oracle Products Oracle Spatial Option Includes All Oracle Locator features More progressed spatial capacities Location empowering geocoder Advanced components, for example, steering, organize examination, and georaster. Included cost choice with the Enterprise Edition

Oracle Spatial General "A coordinated arrangement of capacities and techniques that empowers spatial information to be put away, got to, and broke down rapidly and proficiently in an Oracle database." A construction (MDSYS) that recommends the capacity, linguistic structure, and semantics of upheld geometric information sorts. A spatial ordering system An arrangement of administrators and capacities for performing range of-intrigue inquiries, spatial join questions, and other spatial analyis operations. Authoritative utilities.

Basic Spatial Elements Point Line String N-Point Polygon Oracle Spatial Some Definitions There are other, more perplexing components, including accumulations of at least one components called Geometries . A Layer is gathering of Geometries having comparative traits such a state limits, streets, or streams.

Alabama Alaska Arizona Arkansas Oracle Spatial More on Geometries and Layers Table, STATES = LAYER A GEOMETRY (layout) of a state is in a solitary section of each line. A GEOMETRY may contain more than one component.

Oracle Spatial by Example Scenario You wish to open an upscale delight salon in focal Contra Costa region, California, taking into account wealthier, more established ladies. You might want to be near a noteworthy avenue for straightforward entry. You would prefer not to be excessively near any contenders.

Oracle Spatial by Example Identify Types and Sources of Data Needed to Support Decision Competitors: Internet Search Engine Demographic (Age, Gender, Income): U.S. Registration Bureau Roads: U.S. Geographical Survey

Oracle Spatial by Example Competitor Data: Table CREATE TABLE excellence (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), area MDSYS.SDO_GEOMETRY);

Oracle Spatial by Example Competitor Data: Spatial Metadata INSERT INTO user_sdo_geom_metadata VALUES ('BEAUTY', - Geometry Table 'Area', - 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) Datum:WGS84 );

Oracle Spatial by Example Competitor Data: Spatial Index CREATE INDEX beauty_spatial_idx ON magnificence (area) INDEXTYPE IS MDSYS.SPATIAL_INDEX ; R-Tree Index These are not at all like customary Oracle records and exceptional strides must be brought with their organization.

Oracle Spatial by Example Competitor 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 rundown of contenders and their locations from Search Engine. While exceptionally valuable, it doesn't give any specifically mappable information.

Oracle Spatial by Example Competitor Data: Geocoding The Geocoder will Standardize Address Name and, Using a database with the directions and road locations of every crossing point, Interpolate the area of the given address. Prophet Spatial Option geocoder: included cost Third gathering offers spatial database used to compute the directions

Oracle Spatial by Example Competitor Data: Geocoding Solution: Use Perl Program against web geocoding site. #!/usr/nearby/receptacle/perl # simplest_xmlrpc.pl utilize XMLRPC::Lite; utilize Data::Dumper; utilize strict; utilize notices; my $where = move @ARGV or kick the bucket "Usage: $0 \"1 Main St, Anytown, K