Generating a table & map sharing identifiers

[Bill Binko]

So, after getting nothing back but comments on my ASCII-art (mostly 
positive, amazingly :), I banged on this a while and have come up with a 
fairly simple solution.  I thought I'd post it back to the list, so it's 
below (after the problem statement)...

On Sun, 21 Aug 2005, Bill Binko wrote:

> Hi everyone,
> 
> I have a problem that I'd like some feedback on.  I'm certain it can be 
> done, but would like to know how others have solved it in the past.
> 
> I need to generate several tables with "nearest X items" to a location.  
> For example, "Nearest 5 Schools" to a location.  I then want to generate a 
> map showing the area that includes the X items and show the items on the 
> map using the id's from the table.
> 
> Here's an example:
> 
> Table:
> Map ID	|School Name	|Students | Teachers
> --------------------------------------------
> (A)	|Main St. Elem. |100	  | 10
> (B)	|Pinewood Elem. |2500	  | 20
> (C)	|Forrest Lakes  |800	  | 20
> 
> Map:
>  ------------------
> | (A)              |
> |              (B) |
> |     (C)          |
>  ------------------

So, I found that you can indeed solve this with a strictly PostGIS 
solution.  It still might not be the best, but since it works I thought 
I'd post it.

The trick is to write a PL/PGSQL function that returns a counter that is 
unique to the current session, but that doesn't involve any setup ahead of 
time (like createing a sequence).  Once you have that, you have to sort 
your shapes, and then add the counter to the results before they go back 
to mapserver.  If you add the counter and then sort, you won't get the 
results you want.

So, here's an example:

The original Layer showed the nearest 10 sinkholes to a point (if they 
were within 3 miles).  The POINT() below was actually found dynamically):

DATA "hole from (select oid, hole from FROM all_sinkholes_km \
      WHERE hole && buffer(pointfromtext('POINT(249895.731374337 1349408.0255176)', 26759),3*5280)
      ORDER BY distance(hole, pointfromtext('POINT(249895.731374337 1349408.0255176)', 26759)) 
      limit 10) as innerQ using unique oid using SRID=26759"

The code that generated the HTML table used a query with the same WHERE
clause, but selecting different info for the table.  However, it had the
same ORDER BY and LIMIT, so they were returned in the right order.

The goal was to add a column to the HTML table that had "MAP ID" as the 
header and "A", "B", etc. as the column values, and then to put "A","B", 
etc as the labels on the points returned on the map.

The DATA section was changed to this:

DATA "hole from (select oid, mapCounter('sinkhole', 'A') as label, hole 
                 FROM (select hole from all_sinkholes_km WHERE hole && 
                       buffer(pointfromtext('POINT(249895.731374337 1349408.0255176)',
				                   26759), 3*5280) 
                       ORDER BY distance(hole, pointfromtext('POINT(249895.731374337 1349408.0255176)', 26759)) 
                       limit 10) as innerQ) as outerQ using unique oid using SRID=26759"

This returns the same holes, but with a field named 'label' that has "A", 
"B", etc to use in the mapfile LABELITEM.

Here's the code to mapCounter()

CREATE OR REPLACE FUNCTION mapCounter(text, char) RETURNS text AS '
DECLARE
	seqName  ALIAS FOR $1;
	startChar ALIAS FOR $2;
	seqAvail integer;
	result text;
BEGIN
	seqAvail :=  count(*) from pg_class where relname = seqName AND pg_table_is_visible(oid);
	IF seqAvail = 0 THEN 
	   EXECUTE '' CREATE TEMPORARY SEQUENCE '' || seqName;
	END IF ;
	result := chr((ascii(startChar) - 1 + nextval(seqName))::integer);
	return result;
END;
' LANGUAGE plpgsql;

The function takes two parameters, a unique identifier that must be a 
valid PostgreSQL sequence name, and a starting character. It checks to see 
if there's already a temporary sequence of that name in the current 
session, and creates one if not, then it just returns the sequence.

I have found this very helpful for adding labels to CGI-type Mapserver 
apps, and think it would help in WMS/WFS services as well.

I hope this helps someone.

Bill