Pins Polygons and Perspectives Visualizing Geographic Data in APEX - PowerPoint PPT Presentation

Pins Polygons and Perspectives 
 Visualizing Geographic Data in APEX Christoph Ruepprich Accenture Enkitec Group Pins Polygons & Perspectives 1

Who Am I? ● Dad & Husband ● Consultant @ Accenture an Enkitec Group ● DBA/Developer cruepprich@enkitec.com ● Fitness ruepprich.wordpress.com ● Bass player cruepprich ● Board gamer @CRuepprich Pins Polygons & Perspectives 2

About Accenture Enkitec Group ● Result of acquisition of Enkitec by Accenture in May 2014 ● Global systems integrator focused on the Oracle platform ● Headquartered in Dallas, Texas ● Consultants average 15+ years of Oracle experience ● ~20 Oracle ACE members on staff Pins Polygons & Perspectives 3

What is covered? ● Geocoding ● Mapping APIs ● Interacting with database ● Pins and Polygons ● Third party data Pins Polygons & Perspectives 4 4

What’s Covered ● Geolocation ● Map APIa ● Rendering Maps ● Third Party Data ● Relationships Pins Polygons & Perspectives 5

First we need … Data! Turn addresses into geographic coordinates Pins Polygons & Perspectives 6

Coordinates Pins Polygons & Perspectives 7

Layers use Coordinates ● Pins ● Polygons ● Lines ● Routes ● etc. Pins Polygons & Perspectives 8

Geocoding Webservices ● Find coordinates from and address Reverse Geocoding ● Find address from coordinates Pins Polygons & Perspectives 9

Geocoding Webservices Pins Polygons & Perspectives 10

Geocoding with ● Uses SQL! https://query.yahooapis.com/v1/public/yql?q=select * from https://query.yahooapis.com/v1/public/yql?q=select * from geo.placefinder where text="1400 6th Ave, Seattle, WA 98101" geo.placefinder where text="1400 6th Ave, Seattle, WA 98101" ● Output XML or JSON https://query.yahooapis.com/v1/public/yql? https://query.yahooapis.com/v1/public/yql? format=json &q=select * from geo.placefinder where text="1400 format=json &q=select * from geo.placefinder where text="1400 6th Ave, Seattle, WA 98101" 6th Ave, Seattle, WA 98101" https://developer.yahoo.com/yql/guide/usage_info_limits.html Pins Polygons & Perspectives 11

Geocoding with Sample XML result Pins Polygons & Perspectives 12

Geocoding with ● Other queries select * from local.search where zip='76051' and query='hair cut' select * from weather.forecast where woeid=2502265 Pins Polygons & Perspectives 13

Geocoding with ● Use maps.googleapis.com http://maps.googleapis.com/maps/api/geocode/xml? http://maps.googleapis.com/maps/api/geocode/xml? json? sensor=false&address=1400 6th Ave, Seattle, WA 98101 sensor=false&address=1400 6th Ave, Seattle, WA 98101 Pins Polygons & Perspectives 14

Geocoding with Sample result: Pins Polygons & Perspectives 15

Reverse Geocoding with ● Use maps.googleapis.com http://maps.googleapis.com/maps/api/geocode/xml? http://maps.googleapis.com/maps/api/geocode/xml? json? latlng=40.714224,-73.961452 latlng=40.714224,-73.961452 Pins Polygons & Perspectives 16

ReverseGeocoding with Pins Polygons & Perspectives 17

Geocoding with ● Apex Plug-in ● Creates collection ● Results in report on collection ● Good with PL/SQL Pins Polygons & Perspectives 18

Which geocoding service do I use? ● Many other services available ● Check cost and usage limits ● Check response times ● Bulk processing ● Verify results Pins Polygons & Perspectives 19

Processing the Results Pins Polygons & Perspectives 20

What to do with the results Basic Steps: ● Store XML/JSON result in clob ● Parse result and loop through arrays ● Store results in table or collection 
 and/or 
 Render on map Pins Polygons & Perspectives 21


 What to do with the results ● XML - Use Oracle’s built in XML functions ● Good when processing results with PL/SQL 
 ● JSON - ● Good with JavaScript ● PLJSON package (sourceforge) 
 http://sourceforge.net/projects/pljson/ 
 My Blog: http://wp.me/pRyVI-k1 Pins Polygons & Perspectives 22

We have coordinates. Pins Polygons & Perspectives 23

Mapping with ● Optional Free API Key - Get usage stats ● Extensible ● Usage Limit: 25k map loads per day 
 (don’t quote me on that) ● Excellent Documentation ● API Reference ● Examples https://developers.google.com/maps/ Pins Polygons & Perspectives 24

Mapping with ● You need ● Javascript Library ● API Key (optional) Pins Polygons & Perspectives 25

Mapping with ● Requires Application Key (Free) ● Extensible ● Good Documentation / Examples https://developer.yahoo.com/maps/ajax/v2start.html https://developer.yahoo.com/maps/flash/jsGettingStarted.html Pins Polygons & Perspectives 26

Mapping with ● You need ● Javascript Library ● API Key Pins Polygons & Perspectives 27

Mapping with ● Sample Geolocation Showcase (Apex 4.2.5) ● Mapping through Plug-Ins ● See Carsten Czarski’s Presentation: 
 Location Data for Everyone - in APEX 4.2.5. What the Sample Geolocation Showcase can do for you. 
 ODTUG Webinars Pins Polygons & Perspectives 28

Mapping with ● You Need ● Plug-Ins Pins Polygons & Perspectives 29

Mapping with ● Free API ● Lightweight ● Good for Mobile ● Use other tile servers ● Extensible ● Concise coding http://leafletjs.com/ Pins Polygons & Perspectives 30

Mapping with ● You need ● JS Library ● CSS Style Sheet Pins Polygons & Perspectives 31

Tile Layers with http://leaflet-extras.github.io/leaflet-providers/preview/ Pins Polygons & Perspectives 32

API Comparisons ● http://robinlovelace.net/software/2014/03/05/ webmap-test.html Pins Polygons & Perspectives 33

How to display Javascript maps ● HTML Region with <div> <div id=“map” style=“min-height:500px;”> � ● Javascript ● Include libraries ● Renders map in div Pins Polygons & Perspectives 34

How to display Oracle maps ● Map Region Plugin ● Marker Plugins Pins Polygons & Perspectives 35

Demo Simple Map Pins Polygons & Perspectives 36

Interacting with Map and Database Pins Polygons & Perspectives 37

Interacting with the Database Pins Polygons & Perspectives 38

Data Example Gas Stations ID Brand Latitude Longitude 1 Shell 32.34321 -97.398834 2 Valero 32.15534 -97.19984 3 Exxon 33.92234 -94.88223 Pins Polygons & Perspectives 39

Convert to JSON { "stations":[ { "id":"1", "brand":"Shell", "lat":32.343214, Gas Stations "lng":-97.398834 }, ID Brand Latitude Longitude { "id":"2", "brand":"Valero", 1 Shell 32.34321 -97.398834 "lat":32.15534, "lng":-97.19984 }, 2 Valero 32.15534 -97.19984 { "id":"3", 3 Exxon 33.92234 -94.88223 "brand":"Exxon", "lat":33.92234, "lng":-94.88223 Page Item: } ] P110_JSON } Pins Polygons & Perspectives 40

Convert to JSON with PL/SQL Loop • Page Process � • On Demand Process begin for c1 in (select NAME, LAT, LNG from gas_stations) loop - -Build JSON string … end loop; - - Store string in page item :P110_JSON := l_json; — - For On Demand Process use - - htp.p(l_json); end; Store large JSON objects in collection CLOB. Pins Polygons & Perspectives 41

Loop Through JSON var json = JSON.parse($v('P110_JSON')); //json contains “stations” object var gs = json.stations; for (var i=0;i<gs.length;i++){ var latlng = new L.LatLng(gs[i].lat ,gs[i].lng); var marker = L.marker(latlng); marker.addTo(map); markersArr.push(marker); } Pins Polygons & Perspectives 42

Keep Track of Markers/Polygons markersArr[0].lat = 123 markersArr[0].lng = 456 markersArr[1].lat = 123 markersArr[1].lng = 456 Pins Polygons & Perspectives 43

Remove Markers/Polygons • Loop through markers array and remove each one from the map for (var i=0;i<markersArr.length;i++){ //remove markers to the map using map.removeLayer(markersArr[i]); } Pins Polygons & Perspectives 44

Add Popup ● Add ‘on-click’ event listener to each marker markersArr[0].bindPopup(‘Some html text’); Pins Polygons & Perspectives 45

Circles ● Lat/Lng to define center ● Radius Pins Polygons & Perspectives 46

Polygons ● Use arrays ● Add events ● Multiple points var corners =[]; corners[0] = new L.LatLng(47.65521295468833 ,-122.41790771484375); corners[1] = new L.LatLng(47.663537612601345,-122.1734619140624); corners[2] = new L.LatLng(47.56170075451973 ,-122.18719482421874); corners[3] = new L.LatLng(47.56077405523748 ,-122.43301391601562); � polygon = L.polygon(corners); � polygon.addTo(map); Pins Polygons & Perspectives 47

Demo Gas Stations Pins Polygons & Perspectives 48

Third Party Data Pins Polygons & Perspectives 49

WKT - Well Known Text ● Polygons can have thousands of points ● Complex geometries from 3rd party providers Pins Polygons & Perspectives 50