linked tables and spatial queries

[Micha Silver]

Dylan Beaudette wrote:

> On Sunday 29 July 2007 06:23, you wrote:
>   
>> On Thu, 28 Jun 2007 18:50:47 -0700, Dylan Beaudette
>>
>> <dylan.beaudette at GMAIL.COM> wrote:
>>     
>>> I was in need of this type of functionality, and found it through two
>>> methods:
>>>
>>> 1. define a query template in your mapfile, and have it redirect the user
>>> to a PHP page which can do related DB lookups and presentation. You can
>>> pass along mapserver query results on the URL.
>>>
>>> 2. use something like ka-map and adjust how the query system displays the
>>> results in the left-hand side panel. I modified the query functions such
>>> that all searching and presentation was done via postgres+PHP.
>>>
>>> let me know if you need the details.
>>>       
>> Hi Dylan:
>>     
>
> Hi! Glad to hear that you have made some progress.
>
>   
>> I'm working on a new site (displaying water quality for farmers around our
>>     
>
...{clipped]...
>
>   
>> When I use the query button in the ka-map interface, what I get back (in
>> the left panel) is :
>>
>> Layer Name:
>> Wells
>> results: 1
>>     
>
> Right --  I had the same thing happen... details below.
>
>   
>> So it looks like the querying is working in general, just not retrieving
>> the columns of data. Can you give me any pointers? I'd be glad to try to
>> rewrite the map_query.php stuff in ka-map if necessary, but what should
>> that script do exactly? The option to just write a query template in php is
>> tempting but ka-map seems to be ignoring that TEMPLATE file.
>>     
>
> Since I am using the kaExplorer interface, the files you might be interested 
> in are in the folder tools/kaExplorer, specifically startUp.js . However, the 
> query system uses the file map_query_float.php
>
> I made my own version of this file, essentially removing much of what kamap 
> does by default :
>
> right after the lines:
> //print common header to the floating query results box:
> // echo "<b>Query Results:</b><br>";
>
> ... put your stuff here. spatial queries (or attribute) are simple to write in 
> postgis- so I just made some functions which query tables based on either a 
> coordinate pair -- which this PHP function has access to ($coords) -- or a 
> bounding box, pass them to postgis, and return an associative array as the 
> result. loop through the result printing an HTML table -- there is an example 
> in the code -- and there you have it!
>
> Good luck,
>
> Dylan
>
>   
Thanks for the reply. That's what I was looking for.

BTW, after some more fiddling, here's what I've found regarding  
ka-map's built in querying system:
1- You must enter "queryable" "true" (obviously) in the METADATA section 
of each LAYER  that you want to query. This point is mentioned in the 
ka-map wiki :  http://ka-map.ominiverdi.org/wiki/index.php/Query

2- You must have all queryable layers in their own GROUP. This I learned 
from your post:
http://lists.maptools.org/pipermail/ka-map-users/2006-October/002298.html

3- I got query results ONLY when I added a "fields" line in the METADATA 
section as so:
"fields" "name:Name,drill_dept:Depth,elevation:Elevation,aquifer_na:Aquifer"
It's in the format: "fields"  "column_1:alias_1,column_2:alias_2"
Then the aliases happily appear in ka-map's left hand result window, 
with data values underneath.

And one additional important piece of information that I gleaned from an 
obscure post somewhere: My DATA points to a view in postgresql. The view 
is a join of the geom table and the data table. (I'd assume many people 
work this way.) In order for the query to work when pointing to a 
postgresql view, you MUST have a column in the view named 'oid' with 
unique values. AND the view should be entered into the geometry_columns 
table in postgis.

It took me a while to collect all these bits and pieces, so I'm trying 
to post this to mapserver-users also. My first question never made it to 
the list; hope this gets thru to possibly aid others.

Warm regards,
Micha


-- 
Micha Silver
Arava Development Co
+972-8-6592270