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.

• ApiNotActivatedMapError

• Google API REQUEST_DENIED [This API project is not authorized to use this API]

• Geocoding Service: You must enable Billing on the Google Cloud Project

• RefererNotAllowedMapError

References: 

• https://developers.google.com/maps/documentation/javascript/tutorial
• https://developers.google.com/maps/documentation/javascript/error-messages
• https://developers.google.com/maps/documentation/geocoding/intro

Related Post:

• Integrating Oracle APEX with Google Maps | Spot the Location by Providing Address/Location
• Integrating Oracle APEX with Google Maps | Find Your Location
• Reverse Geocoding
• Integrating Oracle APEX with Google Maps | Spot the Location & Get the Address by Providing Latitude and Longitude