The cool thing about receiving a complicated requirement is that you learn something new and it turns out to be pretty cool.
In an application I needed to be able to display ocean ports on a map along with the number of shipments shipped from that port. Here is what the map looked like once I was done:
Well, that sounds easy enough! However, the maps that come with AnyChart's anymap in Apex 4, know nothing about ocean ports. The map like the one above has cities in the map information but those cities are not located exactly where the ports are located, nor do they have the same name as the port. The image of the map is exactly as I want it, but I need a set of custom locations to display on the map.
Let's get started.....
I decided to start by creating a collection on my page that would get the information I require from the customer's shipping data. I need the list of ports and the number of shipments per port. Here is my code to create the collection.
declare
l_query varchar2(4000);
begin
IF apex_collection.collection_exists(p_collection_name=>'CHINAMAP') then
APEX_COLLECTION.DELETE_COLLECTION(
p_collection_name => 'CHINAMAP' );
END IF;
l_query := q'(select null LINK, )';
l_query := l_query || q'(port_of_load LABEL, )';
l_query := l_query || q'(count(shipment_id) SHIPMENTS )';
l_query := l_query || q'(from shipping_rep_sum )';
l_query := l_query || q'(where export_country = 'China' )';
l_query := l_query || q'(and main_carriage = 'OCEAN' )';
l_query := l_query || q'(group by port_of_load)';
apex_collection.create_collection_from_query (
p_collection_name => 'CHINAMAP',
p_query => l_query,
p_generate_md5 => 'YES');
end;
And here is the resulting collection data:
If you were trying to display the above collection data on a map of China, it wouldn't work. The majority of the port names have no associated locations on the map.
Next I created a table that stores the longitude and latitude of the ports (I looked up these figures on the internet. The table data looks like this:
So far....so good. I've got my customer data, and I have the coordinates for the locations. Now, I need to be able to supply the map with the data in the right format. The map uses xml to plot the locations. I need to pass data to the map in a format that will look like this:
<data>
<series name="Ports" type="Marker" palette="Default" >
<point name="Ningbo" x="121.55" y="29.88" >
<attributes>
<attribute name="value1">23</attribute>
</attributes>
</point>
<point name="Qingdao" x="120.3575" y="36.093" >
<attributes>
<attribute name="value1">45</attribute>
</attributes>
</point>
<point name="Shekou" x="112.90" y="21.48" >
<attributes>
<attribute name="value1">2</attribute>
</attributes>
</point>
<point name="Nhava Sheva" x="72.97" y="18.95" >
<attributes>
<attribute name="value1">2</attribute>
</attributes>
</point>
<point name="Shanghai" x="121.375" y="31.254" >
<attributes>
<attribute name="value1">167</attribute>
</attributes>
</point>
<point name="Yantian" x="116.27" y="23.60" >
<attributes>
<attribute name="value1">3</attribute>
</attributes>
</point>
<point name="Hong Kong" x="119.17" y="25.27" >
<attributes>
<attribute name="value1">1</attribute>
</attributes>
</point>
</series>
</data>
I create a hidden text area on the page. In my case, I called it P6_XML_CHINA. Then I have an on load pl/sql process to generate the xml and populate the hidden item. Here's my process code:
declare
l_data varchar2(4000);
v_xlocation varchar2(50);
v_ylocation varchar2(50);
v_port varchar2(100);
cursor get_locations is select x_location
,y_location
from port_locations
where port_name = v_port;
begin
l_data := '<data>'||chr(10);
l_data := l_data ||'<series name='||chr(34)||'Ports'||chr(34)||' type='||chr(34)||'Marker'||chr(34)||' palette='||chr(34)||'Default'||chr(34)||' >'||chr(10);
for c1 IN (select c002,c003
from apex_collections
where collection_name = 'CHINAMAP')
loop
v_port := c1.c002;
v_xlocation := null;
v_ylocation := null;
open get_locations;
fetch get_locations into v_xlocation, v_ylocation;
close get_locations;
if v_xlocation is not null
then
l_data := l_data ||'<point name='||chr(34)||c1.c002||chr(34)||' ';
l_data := l_data ||'x='||chr(34)||v_xlocation||chr(34)||' ';
l_data := l_data ||'y='||chr(34)||v_ylocation||chr(34)||' >'||chr(10);
l_data := l_data ||' <attributes>'||chr(10);
l_data := l_data ||' <attribute name='||chr(34)||'value1'||chr(34)||'>';
l_data := l_data ||c1.c003||'</attribute>'||chr(10);
l_data := l_data ||'</attributes>'||chr(10);
l_data := l_data||'</point>'||chr(10);
end if;
end loop;
l_data := l_data ||'</series>'||chr(10);
l_data := l_data ||'</data>';
:p6_xml_china := l_data;
end;
Next, I create a map region and select a map of China. I set all of the map attributes the way I want them and apply the changes. I then edit the map and select the "map xml" tab and set "Use customer xml" to 'Yes'. This displays the xml that the map will use. I need to edit the xml slightly. Scroll down until you find the reference #DATA# and replace it with a reference to the hidden item with &P6_XML_CHART. (remember the period at the end). This will tell the map to use the xml stored in the hidden item which will plot all of the custom points.
You can plot anything on a map as long as you can supply the associated longitude and the latitude and prepare the required xml.
Customer map done! Cool stuff learned!
