Download to read offline
Uploaded byJim Czuprynski
Charge Me Up! Using Oracle ML, Analytics, and APEX For Finding Optimal Charge Points
This document discusses using Oracle ML, analytics, and APEX to find optimal locations for electric vehicle charging stations. It begins by introducing the presenter and providing background on electric vehicles and charger terminology. It then outlines the challenge of ensuring adequate charging infrastructure, especially in rural areas. The document demonstrates how to use APEX's new native map region to visualize existing chargers and traffic hotspots. It shows how spatial ML and analytics can resolve duplicate geopoints and score potential new charger locations. The results are overlaid on interactive maps. Future plans include expanding the optimal placement model and developing funding models.
Charge Me Up! Using Oracle ML, Analytics, and APEX For Finding Optimal Charge Points
- 1. Charge Me Up! UsingOracle ML, Analytics, and APEX For Finding Optimal Charge Points Jim Czuprynski @JimTheWhyGuy Zero Defect Computing, Inc.
- 2. Who Am I,and What Am I Doing Here? ➢E-mail me at jim@jimthewhyguy.com ➢Follow me on Twitter (@JimTheWhyGuy) ➢Connect with me on LinkedIn (Jim Czuprynski) Traveler & public speaker Summers: Wisconsin Winters: Illinois Cyclist XC skier Avid amateur bird watcher Oldest dude in martial arts class
- 3. Jim Czuprynski Liron Amitzi https://www.beyondtechskills.com Thepodcast that talks about everything tech – except tech.TM
- 4. EVs: Coming toa Garage Near You Soon Ford F-150 Lightning has received over 200,000 orders so far GM and Honda are aggressively expanding into EV market New solid-state battery technology coming shortly Electricity demand will likely at least double as EVs are adopted What will happen to gas stations? How will governments fund road upkeep without revenue from gas taxes?
- 5. EV Chargers: Terminologyand Capacity Term / Acronym Charging Voltage Description Level 1 Charger 120V Delivers slow “trickle” charge (3–5 miles of range per hour); OK for most PHEVs, but not BEVs Level 2 Charger 208V – 240V Delivers moderate charging (12–80 miles of range per hour); preferred by most BEV owners for overnight home charging Level 3 Charger 400V – 900V Delivers extremely fast charging (3 – 20 miles of range per minute); also known as DC Fast Chargers (DCFCs) or SuperChargers Source: https://www.forbes.com/wheels/advice/ev-charging-levels/
- 6. The Conundrum ofElectric Vehicle (EV) Adoption Even in 2022, several larger towns already have rapid charging stations in case my EV’s battery is running low A normal drive to our Wisconsin cabin is leisurely & passes through larger towns and smaller villages …
- 7. The Conundrum ofElectric Vehicle (EV) Adoption Even in 2022, several larger towns already have rapid charging stations in case my EV’s battery is running low A normal drive to our Wisconsin cabin is leisurely & passes through larger towns and smaller villages … … but what happens if I suddenly need to drive to my older sister’s home in Chetek, WI in an emergency? I might be forced to re-route significantly just to have an option in case I didn’t have time to charge my EV’s battery Rural areas of northern and western Wisconsin currently offer very few EV charging options
- 8. You Start Coding.Meanwhile, I’ll Go Ask Our Users For Requirements Identify where charging stations are already located in Wisconsin, including public vs. private access Create a simple-to-use mobile-capable app that lets EV drivers quickly find the nearest charging station to their current or target location Figure out which potential locations for new charging stations should be placed along key travel routes, as well as within metropolitan & rural areas
- 9. Native Map Regions:New In APEX 21.1 APEX 21.1 offers a new Native Map region that lets you build maps to visualize location data on a map, without any plug-in required! • Five different layer types: Point, Line, Polygon, Heat Map, and Extruded (3D) Polygon • All background maps are sourced directly from the Oracle Maps Cloud • Fully interactive map features, including Drag, Zoom, and ToolTips • Plot map features from: • Simple numeric columns (Longitude and Latitude) • SDO_GEOMETRY datatype • GeoJSON objects • Leverages Oracle Spatial features, including Spatial Indexes and Coordinate Transformations (if they are available in your data model) • Visualize data from SQL queries against local database, REST-Enabled SQL, or from REST Data Sources • No API Key required!
- 10. Using Native MapRegions (1) Select the new Map Region …
- 11. Using Native MapRegions (1) Select the new Map Region … … name the new Page …
- 12. Using Native MapRegions (1) Select the new Map Region … … name the new Page … … and set up the new page’s Navigation entry
- 13. Using Native MapRegions (3) Choose a target table …
- 14. Using Native MapRegions (3) Choose a target table … … pick either a column with an SDO_GEOMETRY datatype, or select a pair of Longitude / Latitude columns for map positioning Note the option to add a Faceted Search region for the new page
- 15. Using Native MapRegions (3) Just about any SQL query can be the basis of a Map Region Note we can overlay multiple layers for the same Map Region
- 16. Using Native MapRegions (3) Just about any SQL query can be the basis of a Map Region Note we can overlay multiple layers for the same Map Region The Native Map region offers excellent control over the appearance, navigation, and initial focus of the map that’s displayed
- 17. Results: A (Not-So-)Simple Map We can also see details about each point’s location … … and use these mapping tools to drill down further into the map, reposition its center, and even measure distances between points
- 18. Using Heat Mapsto Display Traffic Intensity (1) Create a new page for Traffic Heat Mapping
- 19. Using Heat Mapsto Display Traffic Intensity (1) Create a new page for Traffic Heat Mapping Supply a query with appropriate columns and GIS features
- 20. Using Heat Mapsto Display Traffic Intensity (1) Create a new page for Traffic Heat Mapping Supply a query with appropriate columns and GIS features Specify where the traffic hot spots are located, as well as their intensity
- 21. Using Heat Mapsto Display Traffic Intensity (2) Here’s the results: A map of typical weekend traffic during a Wisconsin summer … … or, as dey say in Cheeseland: Oh, ya, hey – it’s all dem FIBs comin’ up from Illinois that’s causin’ all deese traffic jams!
- 22. Using Heat Mapsto Display Traffic Intensity (3) Drilling into the heat map shows greater levels of detail, including the “hottest” hot spots based on specific traffic monitoring points
- 23. Using Heat Mapsto Display Traffic Intensity (3) Drilling into the heat map shows greater levels of detail, including the “hottest” hot spots based on specific traffic monitoring points We can choose from myriad arrays of color schemes to highlight “heat” as desired
- 24. Which Potential ChargingStations Satisfy Underserved Locations? For each location that’s part of a selected subset of key traffic hotspots: Locate all potential charging locations … … that are within a specified distance (1 KM) … … but that currently have no charging station within 1 KM
- 25. Leveraging Spatial ML& Analytics To Resolve Duplicate GeoPoints (1) CREATE TABLE wtfc.deduplicated_locations ( tid NUMBER ,rid1 VARCHAR2(24) ,rid2 VARCHAR2(24) ) TABLESPACE data; A table with these mandatory columns … BEGIN SDO_SAM.COLOCATED_REFERENCE_FEATURES( theme_tablename => 'CHARGER_STATIONS' ,theme_colname => 'CS_GEOLOCATION' ,theme_predicate => NULL ,tablename => 'POTENTIAL_LOCATIONS' ,colname => 'PL_GEOLOCATION' ,ref_predicate => NULL ,dst_spec => 'distance=100 unit=M' ,result_tablename => 'DEDUPLICATED_LOCATIONS' ,commit_interval => 100 ); END; / … will capture any potentially duplicate locations within a 100- meter threshold of each other via this bit of Spatial ML magic
- 26. Leveraging Spatial ML& Analytics To Resolve Duplicate GeoPoints (2) SELECT cs_name || ': ' || cs_address || ', ' || cs_city || ', ‘ || cs_state_abbr || ' ' || cs_zip_code AS actual_location ,pl_name || ': ' || pl_address || ', ' || pl_city || ', ‘ || pl_state_abbr || ' ' || pl_zip_code AS potential_location FROM wtfc.deduplicated_locations XR ,wtfc.charger_stations CS ,wtfc.potential_locations PL WHERE CS.rowid = XR.RID2 AND PL.rowid = XR.RID1; This query lets us evaluate the resulting geographic deduplication
- 27. Leveraging Spatial ML& Analytics To Resolve Duplicate GeoPoints (2) SELECT cs_name || ': ' || cs_address || ', ' || cs_city || ', ‘ || cs_state_abbr || ' ' || cs_zip_code AS actual_location ,pl_name || ': ' || pl_address || ', ' || pl_city || ', ‘ || pl_state_abbr || ' ' || pl_zip_code AS potential_location FROM wtfc.deduplicated_locations XR ,wtfc.charger_stations CS ,wtfc.potential_locations PL WHERE CS.rowid = XR.RID2 AND PL.rowid = XR.RID1; This query lets us evaluate the resulting geographic deduplication Note that deduplication happens based on the locations’ geometry, not their reported addresses
- 28. Finding Under-Served TrafficHot Spots: Infrastructure CREATE TABLE wtfc.charger_location_scoring ( tid NUMBER ,rid1 VARCHAR2(24) ,rid2 VARCHAR2(24) ) TABLESPACE data; CREATE TABLE wtfc.charger_universe ( id NUMBER(6,0) NOT NULL ,type VARCHAR2(15) NOT NULL ,geolocation SDO_GEOMETRY ) TABLESPACE data; Create two tables: One to store nearby location attribute ROWIDs, and one to hold the universe of existing and potential charger sites
- 29. Finding Under-Served TrafficHot Spots: Infrastructure CREATE TABLE wtfc.charger_location_scoring ( tid NUMBER ,rid1 VARCHAR2(24) ,rid2 VARCHAR2(24) ) TABLESPACE data; CREATE TABLE wtfc.charger_universe ( id NUMBER(6,0) NOT NULL ,type VARCHAR2(15) NOT NULL ,geolocation SDO_GEOMETRY ) TABLESPACE data; Create two tables: One to store nearby location attribute ROWIDs, and one to hold the universe of existing and potential charger sites INSERT INTO user_sdo_geom_metadata VALUES ( 'CHARGER_UNIVERSE' ,'GEOLOCATION' ,SDO_DIM_ARRAY( SDO_DIM_ELEMENT('Longitude', -180, 180, 0.5) ,SDO_DIM_ELEMENT('Latitude', -90, 90, 0.5) ) ,8307); COMMIT; CREATE INDEX wtfc.charger_universe_spidx ON wtfc.charger_universe(geolocation) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2; Set up spatial index for all entries in the CHARGER_UNIVERSE table INSERT INTO wtfc.charger_universe SELECT cs_id, 'A' as cs_geolocation FROM wtfc.charger_stations; INSERT INTO wtfc.charger_universe SELECT pl_id, 'P', pl_geolocation FROM wtfc.potential_locations WHERE pl_type <> 'D'; COMMIT; Finally, add all existing chargers and all non- duplicate candidate locations into CHARGER_UNIVERSE
- 30. Finding Under-Served TrafficHot Spots: Using Map Layers Each map layer contains a different piece of the puzzle: existing chargers, high-volume traffic spots, and the resulting best potential spots for new chargers Based on average daily traffic counts captured at specific traffic monitoring points, we can apply spatial algorithms for optimal placement of new chargers
- 31. Finding Under-Served TrafficHot Spots: Automatic Reoptimization Increasing the minimum traffic volume threshold … … automatically triggers recalculation of the universe of potential EV chargers based on that new minimum
- 32. Plans for FutureDevelopment Build ML model handling cost factors for different charger types (L2 vs. L3) for optimal locations near traffic hotspots Expand optimal charger placement model to include support for long-haul EV trucking Build funding models to account for USA Build Back Better (BBB) Justice40 initiatives for equitable placement of EV charging infrastructure
- 33. Public Data Sourcesand Additional Reference Material As Biden plans EV charger rollout, location questions take the fore: https://www.smartcitiesdive.com/news/as-biden-plans-ev-charger-rollout-location-questions-take-the-fore/619466/ National Electric Vehicle Infrastructure (NEVI) Formula Program: https://www.transportation.gov/briefing-room/president-biden-usdot-and-usdoe-announce-5-billion-over-five-years-national-ev State of Wisconsin County Mapping Resources: https://data-wi-dnr.opendata.arcgis.com/ USA Tax Information by State: https://www.irs.gov/statistics/soi-tax-stats-individual-income-tax-statistics-2019-zip-code-data-soi Ford may have just changed our electric-vehicle future: https://www.washingtonpost.com/opinions/2022/05/17/ford-f150-electric-pickup-transforms-american-car-culture/ PJM releases road map for creating ‘grid of the future’ to handle coming renewables, storage wave: https://www.utilitydive.com/news/pjm-grid-future-report-energy-transition/623630/
- 34. Useful Resources andDocumentation APEX 21.1 Native Map Regions: https://docs.oracle.com/en/database/oracle/application-express/21.1/htmdb/creating-maps.html Carsten Czarski Article on APEX Native Map Region: http://www.oraworld.org/fileadmin/documents/26-ORAWORLD.pdf Oracle Spatial Analysis and Mining Concepts: https://docs.oracle.com/en/database/oracle/oracle-database/19/spatl/spatial-analysis-mining.html