NQUERY mode again

J. Parapar listario at GMAIL.COM
Thu Jun 29 08:01:48 EDT 2006


Hi Dave.

Have you taken a look at the PostgreSQL log?  The SQL queries
generated by MapServer can help you to determine a working way to
write the DATA expression.

Best regards,
Jorge

2006/6/29, DjwStone <D.Stone at ed.ac.uk>:
> I've collected some more evidence in connection with this problem. This has
> mostly been prompted by (1) a suspicion that the 5-table join might be the
> cause, and (2) the fear that the 'using unique...' clause could be.
>
> Experiment 1: Remove the joins completely from the layer definition so it
> reads:
>
>     DATA          "geom from (SELECT locationref, placename, qualifyingname,
> locationtype, locationgridref, parishref, religiousorder, geom, oid FROM
> loccoords WHERE placename='%locname%') as foo using unique oid using
> srid=27700"
>
> Result 1:
> The layer displays OK, with 'locname' substituting and filtering as
> expected. Clicking the map at the layer symbol caused the query template to
> display with attributes from all the locations named 'locname'
>
> Experiment 2: Re-introduce 2 joins to model a many-to-many relationship
> between locations and dedications; note the addition also of the composite
> key 'myuid' to use with 'using unique':
>
>     DATA          "geom from (SELECT d.dedicationref, dedicationtype,
> dedsaintsnamed, c.locationref, placename, qualifyingname, locationtype,
> religiousorder, geom, c.oid, c.locationref||d.dedicationref as myuid  FROM
> (dedication d INNER JOIN reftolocn r ON d.dedicationref = r.dedicationref)
> INNER JOIN loccoords c ON r.locationref = c.locationref WHERE
> placename='%locname%') as foo using unique myuid using srid=27700"
>
> Result 2:
> The layer displays OK as before. However, 'querying the map' produces a
> completely blank query template (I posted this problem on MAPSERVER-USERS a
> while back).
>
> Experiment 3: Remove the 'using unique myuid' from the DATA definition for
> Expt.2; this was the only resolution to the blank template problem found so
> far.
>
>     DATA          "geom from (SELECT d.dedicationref, dedicationtype,
> dedsaintsnamed, c.locationref, placename, qualifyingname, locationtype,
> religiousorder, geom, c.oid, c.locationref||d.dedicationref as myuid  FROM
> (dedication d INNER JOIN reftolocn r ON d.dedicationref = r.dedicationref)
> INNER JOIN loccoords c ON r.locationref = c.locationref WHERE
> placename='%locname%') as foo using srid=27700"
>
> Result 3:
> The layer displays OK. Querying the map produces the right NUMBER of
> results, but their CONTENT is incorrect. For example, when %locname% is set
> to 'Leith', the database shows 5 dedications at 3 locations called Leith (1
> location has 3 dedications). The query template, however, lists 5 sets of
> results each with the correct location attributes, but for the location with
> 3 dedications, all 3 show the same dedication attributes:
>
> Result no.: 3
> Location Ref.: LO/JD/605
> Location Type: Chapel
> Place-name: Leith
> Dedication Type: Church dedication
> Dedication Ref.: DE/JD/693
>
> Result no.: 4
> Location Ref.: LO/JD/605
> Location Type: Chapel
> Place-name: Leith
> Dedication Type: Church dedication
> Dedication Ref.: DE/JD/693   -------------------- should be DE/JD/1584
>
> What this appears to show is that NQUERY isn't handling the join results
> correctly (or I'm not setting up the environment correctly). Either way, any
> help would be most gratefully received.
>
> Cheers,
>
> Dave
> --
> View this message in context: http://www.nabble.com/NQUERY-mode-again-tf1848174.html#a5100344
> Sent from the Mapserver - User forum at Nabble.com.
>



More information about the mapserver-users mailing list