Integrating Oracle APEX with Google Maps | Spot the Location by Providing Multiple Latitude & Longitude

To integrate Oracle APEX with Google Maps | Spot the Location by Providing Multiple Latitude & Longitude,

I have followed below steps.

Step 1: Google API Key must be obtained. Get Google API Key.

Step 2: Create required objects (Table, Sequence, Trigger).

CREATE TABLE fxgn_map_locations

(

location_id NUMBER,

location_name VARCHAR2(500),

country VARCHAR2(300),

latitude NUMBER,

longitude NUMBER,

updated_by VARCHAR2(50),

updated_on DATE,

row_version_number NUMBER

);

CREATE SEQUENCE fxgn_map_locations_s START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER fxgn_map_locations_biu BEFORE

INSERT OR

UPDATE ON fxgn_map_locations FOR EACH ROW BEGIN IF inserting

AND :NEW.location_id IS NULL THEN

SELECT fxgn_map_locations_s.nextval INTO :NEW.location_id FROM dual;

END IF;

IF (inserting OR updating) THEN

IF (:NEW.updated_on IS NULL) THEN

:NEW.updated_on := LOCALTIMESTAMP;

END IF;

IF (:NEW.updated_by IS NULL) THEN

:NEW.updated_by := NVL(wwv_flow.g_user,USER);

END IF;

:NEW.row_version_number := NVL(:OLD.row_version_number,0) + 1;

END IF;

END;

Step 3: Populate data into fxgn_map_locations. By executing below script you can load some sample data.

INSERT INTO fxgn_map_locations (location_id,location_name,country,latitude,longitude) VALUES (fxgn_map_locations_s.nextval,'Sydney','Australia',-33.86882,151.20929);

INSERT INTO fxgn_map_locations (location_id,location_name,country,latitude,longitude) VALUES (fxgn_map_locations_s.nextval,'Dubai','United Arab Enirates',25.204849,55.270782);

INSERT INTO fxgn_map_locations (location_id,location_name,country,latitude,longitude) VALUES (fxgn_map_locations_s.nextval,'Chennai','India',13.08268,80.270721);

INSERT INTO fxgn_map_locations (location_id,location_name,country,latitude,longitude) VALUES (fxgn_map_locations_s.nextval,'Boston','Uited States of America',42.360081,-71.058884);

COMMIT:

Step 4: Check the data by executing below SELECT statement.

SELECT location_id,
location_name,
country,
latitude,
longitude,
updated_by,
updated_on,
row_version_number
FROM fxgn_map_locations;

Step 5: Create PLSQL Dynamic Content region and copy and paste below PLSQL code.

DECLARE
latcentre VARCHAR2(250);
lngcentre VARCHAR2(250);
CURSOR geocur
IS
SELECT location_id AS l_id,
location_name
||','
||country AS l_desc,
latitude AS l_lat,
longitude AS l_lng
FROM fxgn_map_locations;
BEGIN
htp.print('<div id="googleMap" style="width:100%;height:400px;"></div>');
htp.print('<script>');
htp.print('function myMap() {');
htp.print('var mapCanvas = document.getElementById("googleMap");');
--My map is centered on the lat/long of Honiara, in the Solomons
htp.print('var myCenter = new google.maps.LatLng(-9.4456,159.9729);');
htp.print('var mapProp= {');
htp.print(' center: myCenter');
htp.print(' ,zoom:8');
htp.print(' };');
htp.print('var map=new google.maps.Map(mapCanvas,mapProp);');
htp.print('var markerBounds = new google.maps.LatLngBounds();');
--loop for multiple markers
FOR georec IN geocur
loop
htp.print(' var myLatLng = {
lng: ' || georec.l_lng || ',
lat: ' || georec.l_lat || '
}');
htp.print('var markerProp = { ');
htp.print(' position: myLatLng ');
htp.print(' ,title: "' || georec.l_desc || '" ');
htp.print(' };');
htp.print('var marker = new google.maps.Marker(markerProp);');
htp.print('marker.setMap(map);');
htp.print('markerBounds.extend(myLatLng);');
END loop;
htp.print('map.fitBounds(markerBounds);');
htp.print('}'); --close function myMap
htp.print('</script>');
--Add you own Google API key below
htp.print('<script src="https://maps.googleapis.com/maps/api/js? key=MY_GOOGLE_API_KEY&callback=myMap"></script>');
END;

Note: In the end of code, you can find link,

htp.print('<script src="https://maps.googleapis.com/maps/api/js? key=MY_GOOGLE_API_KEY&callback=myMap"></script>');

You must replace MY_GOOGLE_MAP_API_KEY with your valid Google MAP API Key.

Output:

That's it. Happy APExing!!!...

Faced Issues: I would like to share all the issues which i faced when i was integrating Oracle APEX with Google Maps.