Oracle APEX and PL/SQL: Multiple Address Marker on Google Map Fetching address from DB

The objective of this is to mark the address on the google map which can be fetched from database and connect the location using google maps polyline API

Before creating the procedure calculate the coordinates for the center location around which you need to show the markers

-- The coordinate in this line need to be updated as per your geography
   htp.print('map.setCenter(new GLatLng(51.600423,-0.338442), 7);');

Step 1 - Create a procedure for setting the marker

create or replace procedure xxtest_show_markers(p_points in varchar2) is
cursor c_klt is
   select regexp_replace (suppliername||', '||addressline1||', '||addressline2||', '||addressline3||', '||addressline4||', '||addressline5||', '||county||', '||postcode||', '||country,'(, )\1{1,}', '\1') geoloc from supplier where rownum < 25 order by id;
l_t number(3) := 0;
--l_address varchar2(1000);
l_geoloc varchar2(1000);
l_address varchar2(4000);
l_api_key varchar2(4000) := 'ABQIAAAAtPAfpd_OogwbQkJcM603nBTPeIpK1BLsYPp4-g4MsBDuj8I5YhT9s6k5GHvv156Mb6qt2YiKldX5LQ';
l_url varchar2(32000);
l_response varchar2(3200);
l_points varchar2(32000);
begin
   htp.print('<script src="http://maps.google.com/maps?file=api&v=2&sensor=true&key=ABQIAAAAtPAfpd_OogwbQkJcM603nBTPeIpK1BLsYPp4-g4MsBDuj8I5YhT9s6k5GHvv156Mb6qt2YiKldX5LQ" dirty="false"></script><script>');
   htp.print('function initialize() {');
-- htp.print('alert(window.location.host);');
   htp.print('var map = null;');
   htp.print('var geocoder = null;');
   htp.print('if (GBrowserIsCompatible()) {');
   htp.print('map = new GMap2(document.getElementById("map_canvas"));');
   htp.print('map.setCenter(new GLatLng(51.600423,-0.338442), 7);');
   htp.print('map.addControl(new GLargeMapControl());');
   htp.print('map.addControl(new GMapTypeControl());');
   htp.print('geocoder = new GClientGeocoder();');
   for r_klt in c_klt
   loop
     htp.print('geocoder.getLatLng(');
     htp.print(''''||r_klt.geoloc||''''||',');
     htp.print('function(point) {');
     htp.print('var baseIcon = new GIcon(G_DEFAULT_ICON);');
     htp.print('baseIcon.shadow = "http://www.google.com/mapfiles/shadow50.png%22;');
   --htp.print('baseIcon.shadow = "/i/pdf.png";');
     htp.print('baseIcon.iconSize = new GSize(20, 34);');
     htp.print('baseIcon.shadowSize = new GSize(37, 34);');
     htp.print('baseIcon.iconAnchor = new GPoint(9, 34);');
     htp.print('baseIcon.infoWindowAnchor = new GPoint(9, 2);');
     htp.print('var letteredIcon = new GIcon(baseIcon);');
     l_address := r_klt.geoloc;
     htp.print('letteredIcon.image = "http://www.google.com/mapfiles/marker'%7C%7Cchr(65+l_t)%7C%7C'.png%22;');
     htp.print('markerOptions = { icon:letteredIcon, title:'''||l_address||'''};');
     htp.print('var marker = new GMarker(point,markerOptions);');
     htp.print('var html = "<h1>'||r_klt.geoloc||'</h1>";');
     htp.print('GEvent.addListener(marker, "mouseover", function() { marker.openInfoWindowHtml(html); });');
     htp.print('map.addOverlay(marker);');
     htp.print('}');
     htp.print(');');
     l_t := l_t + 1;
   end loop;
   htp.print('var points = ['||p_points||'];');
   htp.print('var polyline = new GPolyline(points, ''#ff0000'', 3, 0.5);');
   htp.print('map.addOverlay(polyline);');
   htp.print('}');
   htp.print('}');
   htp.print('</script>');
   htp.print(' <body onload="initialize()" style="font-family: Arial;border: 0 none;">');
   htp.print('    <div id="map_canvas" style="width: 1200px; height: 600px"></div>');
   htp.print(' </body>');
end;

Step 2 - Create a PL/SQL Dynamic Region and copy the below code (note modify the query as per your requiremnt)

declare
l_geoloc varchar2(1000);
l_address varchar2(4000);
l_api_key varchar2(4000);
l_url varchar2(32000);
l_response varchar2(3200);
l_points varchar2(3200);
begin
for i in (select regexp_replace (suppliername||', '||addressline1||', '||addressline2||', '||addressline3||', '||addressline4||', '||addressline5||', '||county||', '||postcode||', '||country,'(, )\1{1,}', '\1') geoloc from supplier where rownum < 25 order by id)
loop
l_address := replace(i.geoloc,' ','+');
l_url := 'http://maps.google.com/maps/geo?q='||l_address||'&'||'output=csv'||'&'||'key='||l_api_key;
l_response := utl_http.request(l_url, APEX_APPLICATION.G_PROXY_SERVER);
--htp.p(l_response);
--htp.p('</br></br>');
l_points := l_points||'new GLatLng('||substr(l_response,instr(l_response,',',1,2)+1)||'), ';
end loop;
l_points := substr(l_points, 1, length(l_points)-2);
xxtest_show_markers(l_points);
end;

Step 3 - Run the page your should be able to see the markers on the google map

Oracle APEX and PL/SQL

Thursday, February 10, 2011

Multiple Address Marker on Google Map Fetching address from DB

No comments:

Post a Comment