NQUERY mode again

[DjwStone]

Still more evidence on the NQUERY mode problem:

Former experiments (see earlier posts in this thread) showed that 'using
unique <keyname>' in the DATA statement produced a blank query template,
while leaving it out gave a query template with the correct number of
'hits', but incorrect values in certain fields.

What seems to be the case is that the 'using unique <keyname>' IS VITAL to
the success of query-by-point, but the choice of key is critical. In my case
specifically, the point layer is based on a 5-table view, representing 2 m:n
relationships joined together. The schematic for this is:

A -----m:n------ B -----m:n----- C

or

A -----1:n----- X -----n:1----- B -----1:n----- Y -----n:1----- C

where A, B, and C are the base tables, and X and Y the 'intermediate'
tables. A combination of key values from X and Y uniquely identifies a row
in this view.

Unfortunately, combining numeric keys to produce a single unique value often
generates numbers too big to fit in an integer (e.g.<X key>*10000 + <Y
key>), and MapServer only appears to accept an integer column in the 'using
unique <keyname>'.

I was lucky in that I was able to construct a unique integer 'myuid' from <X
key> and <Y key> for qualifying the DATA statement:

    DATA          "geom FROM (select dedicationref, geom, stdname,
reftostconf, 		saintid, locationref, placename, deddlo, deddhi,
dedicationtype, stdesc,
                                    myuid
                        from testloc
                        where (
                            - - - assorted conditions - - -
                          ) AS foo USING UNIQUE myuid USING srid=27700"

where 'testloc' is of the form:

 SELECT <columns to display in query template>, <X key> * 100000 + <Y key>
AS myuid
   FROM A
   JOIN (B
   JOIN X ON B.dedicationref::text = X.dedicationref::text) ON
A.locationref::text = X.locationref::text
   JOIN (Y
   JOIN C ON Y.saintid::text = C.saintid::text) ON B.dedicationref::text =
Y.dedicnid::text;

Hopefully, future releases may permit a wider range of data-types for the
unique key of the spatial data-source.

Cheers,

Dave

-- 
View this message in context: http://www.nabble.com/NQUERY-mode-again-tf1848174.html#a5380134
Sent from the Mapserver - User forum at Nabble.com.