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

▶
pins polygons and perspectives visualizing geographic
SMART_READER_LITE
LIVE PREVIEW

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

Mar 01, 2024 332 likes •1.01k views

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

slide-1
SLIDE 1

Pins Polygons & Perspectives

1

Pins Polygons and Perspectives
 Visualizing Geographic Data in APEX

Christoph Ruepprich Accenture Enkitec Group

slide-2
SLIDE 2

Pins Polygons & Perspectives

Who Am I?

  • Dad & Husband
  • Consultant @ Accenture an Enkitec Group
  • DBA/Developer
  • Fitness
  • Bass player
  • Board gamer

2

ruepprich.wordpress.com @CRuepprich cruepprich cruepprich@enkitec.com

slide-3
SLIDE 3

Pins Polygons & Perspectives

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

3

slide-4
SLIDE 4

Pins Polygons & Perspectives

What is covered?

  • Geocoding
  • Mapping APIs
  • Interacting with database
  • Pins and Polygons
  • Third party data

4

4

slide-5
SLIDE 5

Pins Polygons & Perspectives

What’s Covered

  • Geolocation
  • Map APIa
  • Rendering Maps
  • Third Party Data
  • Relationships

5

slide-6
SLIDE 6

Pins Polygons & Perspectives

First we need…

6

Data!

Turn addresses into geographic coordinates

slide-7
SLIDE 7

Pins Polygons & Perspectives

Coordinates

7

slide-8
SLIDE 8

Pins Polygons & Perspectives

Layers use Coordinates

  • Pins
  • Polygons
  • Lines
  • Routes
  • etc.

8

slide-9
SLIDE 9

Pins Polygons & Perspectives

Geocoding Webservices

  • Find coordinates from and address

9

Reverse Geocoding

  • Find address from coordinates
slide-10
SLIDE 10

Pins Polygons & Perspectives 10

Geocoding Webservices

slide-11
SLIDE 11

Pins Polygons & Perspectives

Geocoding with

  • Uses SQL!

11

https://query.yahooapis.com/v1/public/yql?q=select * from geo.placefinder where text="1400 6th Ave, Seattle, WA 98101" https://query.yahooapis.com/v1/public/yql?q=select * from geo.placefinder where text="1400 6th Ave, Seattle, WA 98101"

  • Output XML or JSON

https://query.yahooapis.com/v1/public/yql? format=json&q=select * from geo.placefinder where text="1400 6th Ave, Seattle, WA 98101" https://query.yahooapis.com/v1/public/yql? format=json&q=select * from geo.placefinder where text="1400 6th Ave, Seattle, WA 98101"

https://developer.yahoo.com/yql/guide/usage_info_limits.html

slide-12
SLIDE 12

Pins Polygons & Perspectives

Geocoding with

12

Sample XML result

slide-13
SLIDE 13

Pins Polygons & Perspectives

Geocoding with

13

  • Other queries

select * from local.search where zip='76051' and query='hair cut' select * from weather.forecast where woeid=2502265

slide-14
SLIDE 14

Pins Polygons & Perspectives

Geocoding with

  • Use maps.googleapis.com

14

http://maps.googleapis.com/maps/api/geocode/xml? sensor=false&address=1400 6th Ave, Seattle, WA 98101 http://maps.googleapis.com/maps/api/geocode/xml? sensor=false&address=1400 6th Ave, Seattle, WA 98101 json?

slide-15
SLIDE 15

Pins Polygons & Perspectives

Geocoding with

15

Sample result:

slide-16
SLIDE 16

Pins Polygons & Perspectives

Reverse Geocoding with

  • Use maps.googleapis.com

16

http://maps.googleapis.com/maps/api/geocode/xml? latlng=40.714224,-73.961452 http://maps.googleapis.com/maps/api/geocode/xml? latlng=40.714224,-73.961452 json?

slide-17
SLIDE 17

Pins Polygons & Perspectives

ReverseGeocoding with

17

slide-18
SLIDE 18

Pins Polygons & Perspectives

Geocoding with

  • Apex Plug-in
  • Creates collection
  • Results in report on collection
  • Good with PL/SQL

18

slide-19
SLIDE 19

Pins Polygons & Perspectives

Which geocoding service do I use?

  • Many other services available
  • Check cost and usage limits
  • Check response times
  • Bulk processing
  • Verify results

19

slide-20
SLIDE 20

Pins Polygons & Perspectives 20

Processing the Results

slide-21
SLIDE 21

Pins Polygons & Perspectives

What to do with the results

  • Store XML/JSON result in clob
  • Parse result and loop through arrays
  • Store results in table or collection


and/or
 Render on map

21

Basic Steps:

slide-22
SLIDE 22

Pins Polygons & Perspectives

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

22

slide-23
SLIDE 23

Pins Polygons & Perspectives

23

We have coordinates.

slide-24
SLIDE 24

Pins Polygons & Perspectives

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

24

https://developers.google.com/maps/

slide-25
SLIDE 25

Pins Polygons & Perspectives

Mapping with

  • You need
  • Javascript Library
  • API Key (optional)

25

slide-26
SLIDE 26

Pins Polygons & Perspectives

Mapping with

  • Requires Application Key (Free)
  • Extensible
  • Good Documentation / Examples

26

https://developer.yahoo.com/maps/flash/jsGettingStarted.html https://developer.yahoo.com/maps/ajax/v2start.html

slide-27
SLIDE 27

Pins Polygons & Perspectives

Mapping with

  • You need
  • Javascript Library
  • API Key

27

slide-28
SLIDE 28

Pins Polygons & Perspectives

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

28

slide-29
SLIDE 29

Pins Polygons & Perspectives

Mapping with

  • You Need
  • Plug-Ins

29

slide-30
SLIDE 30

Pins Polygons & Perspectives

Mapping with

  • Free API
  • Lightweight
  • Good for Mobile
  • Use other tile servers
  • Extensible
  • Concise coding

30

http://leafletjs.com/

slide-31
SLIDE 31

Pins Polygons & Perspectives

Mapping with

  • You need
  • JS Library
  • CSS Style Sheet

31

slide-32
SLIDE 32

Pins Polygons & Perspectives

Tile Layers with

32

http://leaflet-extras.github.io/leaflet-providers/preview/

slide-33
SLIDE 33

Pins Polygons & Perspectives

API Comparisons

  • http://robinlovelace.net/software/2014/03/05/

webmap-test.html

33

slide-34
SLIDE 34

Pins Polygons & Perspectives

How to display Javascript maps

  • HTML Region with <div>

<div id=“map” style=“min-height:500px;”>

  • Javascript
  • Include libraries
  • Renders map in div

34

slide-35
SLIDE 35

Pins Polygons & Perspectives

How to display Oracle maps

  • Map Region Plugin
  • Marker Plugins

35

slide-36
SLIDE 36

Pins Polygons & Perspectives

36

Demo Simple Map

slide-37
SLIDE 37

Pins Polygons & Perspectives

37

Interacting with Map and Database

slide-38
SLIDE 38

Pins Polygons & Perspectives

Interacting with the Database

38

slide-39
SLIDE 39

Pins Polygons & Perspectives

Data Example

39

ID Brand Latitude Longitude 1 Shell 32.34321

  • 97.398834

2 Valero 32.15534

  • 97.19984

3 Exxon 33.92234

  • 94.88223

Gas Stations

slide-40
SLIDE 40

Pins Polygons & Perspectives

Convert to JSON

40

ID Brand Latitude Longitude 1 Shell 32.34321 -97.398834 2 Valero 32.15534 -97.19984 3 Exxon 33.92234 -94.88223

Gas Stations

{ "stations":[ { "id":"1", "brand":"Shell", "lat":32.343214, "lng":-97.398834 }, { "id":"2", "brand":"Valero", "lat":32.15534, "lng":-97.19984 }, { "id":"3", "brand":"Exxon", "lat":33.92234, "lng":-94.88223 } ] }

P110_JSON Page Item:

slide-41
SLIDE 41

Pins Polygons & Perspectives

Convert to JSON with PL/SQL Loop

41

  • 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;

  • Page Process
  • On Demand Process

Store large JSON objects in collection CLOB.

slide-42
SLIDE 42

Pins Polygons & Perspectives

Loop Through JSON

42

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); }

slide-43
SLIDE 43

Pins Polygons & Perspectives

Keep Track of Markers/Polygons

43

markersArr[0].lat = 123 markersArr[0].lng = 456 markersArr[1].lat = 123 markersArr[1].lng = 456

slide-44
SLIDE 44

Pins Polygons & Perspectives

Remove Markers/Polygons

44

for (var i=0;i<markersArr.length;i++){ //remove markers to the map using map.removeLayer(markersArr[i]); }

  • Loop through markers array and remove each one from the map
slide-45
SLIDE 45

Pins Polygons & Perspectives

Add Popup

  • Add ‘on-click’ event listener to each marker

45

markersArr[0].bindPopup(‘Some html text’);

slide-46
SLIDE 46

Pins Polygons & Perspectives

Circles

  • Lat/Lng to define center
  • Radius

46

slide-47
SLIDE 47

Pins Polygons & Perspectives

Polygons

  • Use arrays
  • Add events
  • Multiple points

47

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);
slide-48
SLIDE 48

Pins Polygons & Perspectives

48

Demo Gas Stations

slide-49
SLIDE 49

Pins Polygons & Perspectives

49

Third Party Data

slide-50
SLIDE 50

Pins Polygons & Perspectives

WKT - Well Known Text

  • Polygons can have thousands of points
  • Complex geometries from 3rd party providers

50

slide-51
SLIDE 51

Pins Polygons & Perspectives

WKT - Well Known Text

51

http://en.wikipedia.org/wiki/Well-known_text

slide-52
SLIDE 52

Pins Polygons & Perspectives

WKT - Well Known Text

52

http://en.wikipedia.org/wiki/Well-known_text

slide-53
SLIDE 53

Pins Polygons & Perspectives

Sample Data

  • 177 Zip Codes
  • 792 Polygons
  • 52,060 Coordinates
  • in under 2 seconds!

53

slide-54
SLIDE 54

Pins Polygons & Perspectives

54

Demo Zip Codes

slide-55
SLIDE 55

Pins Polygons & Perspectives

55

Finding Relationships

slide-56
SLIDE 56

Pins Polygons & Perspectives

Oracle Locator/Spatial

  • Built into Oracle Database
  • Functions and procedures to handle geometric

data

  • SDO_GEOMETRY object

Not supported with locator

  • Advanced functions:
  • Difference
  • Intersection
  • Union
  • Etc.

56

slide-57
SLIDE 57

Pins Polygons & Perspectives

Using third party WKT

Steps:

  • Populate sdo_geometry column
  • Set SRID (Spatial Reference ID)
  • Set Metadata (USER_SDO_GEOM_METADATA)
  • Create Index (MDSYS.SPATIAL_INDEX)

57

slide-58
SLIDE 58

Pins Polygons & Perspectives

Convert WKT to SDO Geometry

58

WKT SDO Geometry

sdo_util.from_wktgeometry()

slide-59
SLIDE 59

Pins Polygons & Perspectives

Zip Codes Table

59

ZIP WKT GEOM 76051 MULTIPOLYGON ((( MDSYS.SDO_GEOMETRY(2001,8307,MD SYS.SDO_POINT_TYPE(-97.094171,32.93 4568,NULL),NULL,NULL) 90210 MULTIPOLYGON ((( MDSYS.SDO_GEOMETRY(… 98101 MULTIPOLYGON ((( MDSYS.SDO_GEOMETRY(…

slide-60
SLIDE 60

Pins Polygons & Perspectives

SDO_GEOMETRY

60

SDO_GEOMETRY( 2001 -- SDO_GTYPE: point ,4326 -- SDO_SRID (coordinate system) ,MDSYS.SDO_POINT_TYPE(lon, lat, NULL) -- POINT_TYPE ,NULL -- SDO_ELEM_INFO_ARRAY ,NULL -- SDO_ORDINATE_ARRAY )

  • GTYPE: Geometry types (Points,Lines,Polygons, etc.)
  • SRID: (MDSYS.CS_SRS) Coordinate system (2D,3D, Locale, etc.)
  • POINT_TYPE: (x,y,z)
  • ELEM_INFO_ARRAY: How to interpret ORDINATE_ARRAY.
  • ORDINATE_ARRAY: Array of points
slide-61
SLIDE 61

Pins Polygons & Perspectives

Calculate Distance

61

select SDO_GEOM.SDO_DISTANCE( MDSYS.SDO_GEOMETRY( 2001 , 4326 , MDSYS.SDO_POINT_TYPE(1, 1, NULL) , NULL , NULL ), MDSYS.SDO_GEOMETRY( 2001 , 4326 , MDSYS.SDO_POINT_TYPE(2, 2, NULL) , NULL , NULL ) ,1 -- within 1m tolerance ,'unit=m' -- meters ) AS dist_in_meters from dual;

slide-62
SLIDE 62

Pins Polygons & Perspectives

  • Gas Stations within 5km
  • SDO_WITHIN_DISTANCE

Calculate Distance

62

SELECT * FROM gas_stations g WHERE SDO_WITHIN_DISTANCE( g.geom , MDSYS.SDO_GEOMETRY( 2001 , 4326 , MDSYS.SDO_POINT_TYPE(- 97.078686, 32.939251, NULL) , NULL , NULL ) ,'distance = 5 unit=km' ) = 'TRUE'

Observer Point

slide-63
SLIDE 63

Pins Polygons & Perspectives

Calculate Area

63

select SDO_GEOM.SDO_AREA( SDO_GEOMETRY( 2003 -- GTYPE: two-dimensional polygon ,4326 -- SRID ,NULL -- POINT TYPE ,SDO_ELEM_INFO_ARRAY(1,1003,1) ,SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3) ) ,1) AREA from dual;

slide-64
SLIDE 64

Pins Polygons & Perspectives

Contains

64

sdo_contains(polygon.geom,point.geom) = ‘TRUE’

slide-65
SLIDE 65

Pins Polygons & Perspectives

65

Demo Contains

slide-66
SLIDE 66

Pins Polygons & Perspectives

Q&A

66

Q & A