Postcode Plotting







I have used the postcode to plot locations in a number of Apex applications. Although it’s not as function rich as other products like Google Earth/Maps or Spatial, it’s very quick and easy to do.
Example: http://apex.oracle.com/pls/otn/f?p=33715

It works by comparing the first half of the UK postcode and retrieving the X Y coordinates from a postcode database. The XY coordinates are applied to a scale and then Map Pin images are plotted onto a UK Map image using a CSS position function. I have added a feature that will ‘jog’ map pins for different items that have the same postcode. The calculations are based on Similar Triangles and the jogging feature uses Trigonometry.

Instructions can be found at:

http://apex.oracle.com/pls/otn/f?p=33715:4



There are a few configurations that need to be made to the procedure for it to work.
1. Work out the Offsets and Scale. You can see the affects of the offset and scale in the above example. It is possible to calculate the scale by cutting the scale out of you map image (if it has one) using an image editor. The size of the image in pixels can then be used to calculate the scale. I prefer to do the whole thing by trial and error.
2. Next add you Map and Map Pin images. I have used this procedure with over 40 different Map Pins named Symbol_1 …… Symbol_40, which are then dynamically assign in the procedure. A more simple method to change the Map Pin is using a DECODE or CASE in the cursor. For example:

SELECT decode(dname, ' ACCOUNTING', 'green_flag.gif', ' (RESEARCH)', 'red_flag.gif', ' SALES', 'white_flag.gif', 'g_can.gif') image_file,
location,
deptno id,
ba.post_code postcode,
(pc.x + left_offset) / left_scale x,
(top_offset -pc.y) / top_scale y
FROM dept,
postcode pc
WHERE SUBSTR(ba.post_code, 1, (instr(ba.post_code, ' ', 1, 1) -1)) = pc.postcode(+)
ORDER BY pc.postcode;


3. Configure the Cursor. Adjust the cursor so that it includes the locations that you wish to display. Above is a simple example that shows how to link your locations to the postcode table. Note the order by at the end. This is important if you want to jog Map Pins that have the same postcode.
4. Add a link. This is optional and can be removed, but it is a nice feature to link to details for that particular location.

Comments