[MAPSERVER-USERS] Joining two tables and directing multiple row results to a template

[udaysr]

Hi,

I have a question about joining two tables and viewing my multiple row
(one-to-many) results. Essentially, I am doing what is described in an
earlier post by Steven Monai on joining two tables 'opo' and 'pippo':
http://www.nabble.com/Re%3A--UMN_MAPSERVER-USERS--Join-between-Postgis-table-and-Postgresql-table-p1477848.html

I have two tables, x_addresses and x_voters. Multiple voters can reside in
the same address. I want to display points for each address, and when these
points are clicked on, I want to display all the voters who reside at this
address. Each address could have 0 or more voters associated with it. Each
voter has either 0 or 1 address. This is exactly the situation described in
the post above, and I am doing the following in my map file:

LAYER
	NAME "MyAddressPoints"	
	TYPE POINT
	STATUS OFF
# database
  CONNECTIONTYPE postgis
  CONNECTION "user=xxxx dbname=xxxx host=localhost"
  PROCESSING "CLOSE_CONNECTION=DEFER"
  DATA "the_geom from (select addr.the_geom, addr.house_number,
addr.street_info, addr.city, addr.state, addr.zip, addr.county_fips,
addr.precinct_name, addr.latitude, addr.longitude, addr.address_id, vtr.gid,
addr.voters, vtr.first_name, vtr.last_name, vtr.voter_id, vtr.status from
x_addresses as addr LEFT OUTER JOIN x_voters as vtr on
addr.address_id=vtr.address_id where addr.latitude is not null and
addr.longitude is not null) as temp using unique gid using SRID=4326"  
	MAXSCALEDENOM 400000
	PROJECTION
		"init=epsg:4326"
	END   
	TEMPLATE "templates/repeat.html"
	HEADER "templates/header.html"
	FOOTER "templates/footer.html"

My query works (as intended, I believe). The issue is that I want to be able
to display all results on double clicking on the point displayed. I think
there is an inefficiency in that points are displayed one over the other
when there are multiple voters at the same address, per this query. (Smaller
question - can that be optimized? Should I preferably be using the JOIN
tag?)

But my primary issue is that I am not able to display the details of the
multiple persons using the templates.

The repeat.html template just has table rows in it:

  <tr><td>Street</td><td>[street_info]</td></tr>
  <tr><td>City, State</td><td>[city], [state]</td></tr>
  <tr><td>Zip</td><td>[zip]</td></tr>
  <tr><td>Lat/Long</td><td>[latitude]/[longitude]</td></tr>
  <tr><td>County FIPS</td><td>[county_fips]</td></tr>
  <tr><td>PrecinctName</td><td>[precinct_name]</td></tr>
  <tr><td>Voter Count</td><td>[voters]</td></tr>
  <tr><th  colspan="2">Voter</th></tr>
  <tr><td>Voter ID</td><td>[voter_id]</td></tr>
  <tr><td>First Name</td><td>[first_name]</td></tr>
  <tr><td>Last Name</td><td>[last_name]</td></tr>
  <tr><td>Status</td><td>([status])</td></tr>

BTW, I set the mode query parameter in the URL to the map file to NQUERY.

The web application uses OpenLayers and php, and the URL accessed when
double clicking on an address point is specified in Javascript as follows:

var url =  MyAddressPoints.getFullRequestString({
                            REQUEST: "GetFeatureInfo",
                            EXCEPTIONS: "application/vnd.ogc.se_xml",
                            BBOX: MyAddressPoints.map.getExtent().toBBOX(),
                            X: e.xy.x,
                            Y: e.xy.y,
                            INFO_FORMAT: 'text/html',
                            QUERY_LAYERS: MyAddressPoints.params.LAYERS,
                            WIDTH: MyAddressPoints.map.size.w,
                            HEIGHT: MyAddressPoints.map.size.h});
            OpenLayers.loadURL(url, '', this, setHTML);


This just displays a single voter all the time in the table, instead of the
multiple I would like to display. Is there a way to  obtain multiple row
results from a single point double click and display them?

Thank you!

- Uday

-- 
View this message in context: http://www.nabble.com/Joining-two-tables-and-directing-multiple-row-results-to-a-template-tp19293398p19293398.html
Sent from the Mapserver - User mailing list archive at Nabble.com.