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. 16 years working with Geographical Information Systems and Computer Mapping in the Oil & Gas Industry. 15 years working with Oracle, generally as DBA. Sporadic involvement with Oracle Spatial since late Oracle 7.

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

Reading Material Wide going points on the FREE procurement of information and apparatuses for the handling of geographic information. Extraordinary 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 forth. 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: estimation of geographic directions from road addresses.

Geogaphic Data Processing Data Storage and Retrieval For Oracle Storage Use of Oracle's Geographic Data Model Database outline for the coordination 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. Very few free projects that work with Oracle Spatial straightforwardly, lamentably. Numerous business programs for showing and intelligently 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 a flawless circle. The world's general shape is best depicted 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 complex electronic gear. Overall GPS utilize a datum good to NAD83 called WGS84 The directions for a point on the earth can change essentially contingent upon the datum utilized.

Cartography 101 Spatial Reference Systems SRS are organize based frameworks used to find land 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 essential? Amid investigation, if every single spatial dat is not in the same Spatial Reference System, the geographic layers won't overlay and the examination will be in mistake.

Cartography 101 Why are SRS imperative?

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

Cartography 101 Why are SRS vital? On the off chance that you were occupied with utilizing a guide to gauge arrive regions, 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 territories on account of their exactness. "Tall" States, for example, TX and CA utilize the Lambert Conformal Conic Projection. "Wide" States, for example, NY utilize Transverse Mercator 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 plan to utilize the information The size and area of the region of intrigue The metric property of the guide you wish to safeguard: 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 elements, for example, steering, organize investigation, and georaster. Included cost choice with the Enterprise Edition

Oracle Spatial General "An incorporated arrangement of capacities and methods that empowers spatial information to be put away, got to, and broke down rapidly and productively in an Oracle database." A construction (MDSYS) that recommends the capacity, language structure, and semantics of bolstered geometric information sorts. A spatial ordering system An arrangement of administrators and capacities for performing region of-intrigue questions, spatial join inquiries, and other spatial analyis operations. Managerial utilities.

Basic Spatial Elements Point Line String N-Point Polygon Oracle Spatial Some Definitions The are other, more unpredictable components, including accumulations of at least one components called Geometries . A Layer is accumulation of Geometries having comparable properties such a state limits, streets, or waterways.

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

Oracle Spatial by Example Scenario You wish to open an upscale wonder salon in focal Contra Costa region, California, obliging wealthier, more seasoned ladies. You might want to be near a noteworthy avenue for simple 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. Enumeration Bureau Roads: U.S. Land Survey

Oracle Spatial by Example Competitor Data: Table CREATE TABLE magnificence (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 excellence (area) INDEXTYPE IS MDSYS.SPATIAL_INDEX ; R-Tree Index These are dissimilar to standard Oracle records and uncommon 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 straightforwardly 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 convergence, Interpolate the area of the given address. Prophet Spatial Option geocoder: included cost Third gathering offers spatial database used to ascertain the directions

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