Question on queryByAttributes - multiple fields

[Cord Thomas]

Alright, this is finally what worked for me - though i guess i am unsure
of why it worked...

Again, i am working with MapServer 4.4.0 and perl SWIG Mapscript on a
Linux Machine querying spatial data maintained in PostGIS compiled against
PostgreSQL 7.4.x

Problem - query a layer using multiple field criteria - could not get results

Answer - following is code snippet (please forgive my poor perl - i have
never used it before):

  earlier in code:
  my $lo = "ME|Knox";
  ...

  my $layerName = "counties";
  my $qryFlds="stateid";
  my @loa = split(/\|/,$lo);
  my $qryVals="(stateid='" . $loa[0] . "' and (name='" . $loa[1] . "'))";

  my $mapLayer = $map->getLayerByName($layerName);
  $mapLayer->{status} = $mapscript::MS_ON;
  my $res = $mapLayer->queryByAttributes($map, $qryFlds,
                                         $qryVals, $mapscript::MS_SINGLE);
  $mapLayer->open();
  my $shapeObj = new mapscript::shapeObj($mapscript::MS_SHAPE_POLYGON);
  my $oRes = $mapLayer->getResults();
  if ($oRes->{numresults} > 0) {
    $shapeObj = $mapLayer->getFeature($oRes->getResult(0)->{shapeindex});
  }
  $mapLayer->close();
  return $shapeObj;

I battled for hours with the following:
  my $qryFlds = "stateid,name"; - this you would think is necessary - but
doesnt work.
  various permutations of the $qryVals - setting case (lower, upper),
trimming, etc... - the query was right - it was the field parameter that
was wrong.
  the extra parentheses around the two field criteria may not be
necessary, but it was all part of my vain attempt to get results...

Next - i am not sure i am using the getResults() and getFeature() quite
right, but, hey, it works.

Thank you for help Sean.



> Cord,
>
> Here's something to try (python example):
>
>      # execute queryByAttributes ...
>      results = layer.getResults()
>
> The 'results' variable will either be a reference to the
> layer's result set or be NULL if the query returned nothing.
> The reason why it returned nothing may be found in the
> mapserver error stack
>
>      if results == None:
>          error = mapscript.errorObj()
>          if error:
>               print error.code, error.message, error.routine
>
>              # continue into the stack
>                   while 1:
>                  error = error.next()
>                  if error == None: break
>                  else:
>                      print error.code, error.message, error.routine
>
> Actually, for Python/Java/Ruby, MapServer errors become language
> exceptions.  For Perl, which has no built-in exceptions, users
> have to program their own error stack inspection.
>
> Sean
>
> On Nov 30, 2004, at 9:14 AM, Cord Thomas wrote:
>
>> Sean,
>>
>> Thank you.  If you'd like participation in the howTo, let me know.  I
>> would like to contribute non-trivial examples as i put them together.
>>
>> Given your discovery - i feel there may be a bug in implementation
>> then.
>> I am unsure why my query results in getNumResults() = 0 though when
>> executed directly against the database, it returns 1 record.
>>
>> Cord
>>
>>> My responses inline ...
>>>
>>> On Nov 29, 2004, at 6:15 PM, Cord Thomas wrote:
>>>
>>>> Hey Sean
>>>>
>>>> Thank you for this definitive answer.  I have a few observations:
>>>>
>>>> 1 - as i said, if i see the query dump from mapserver when i forgot
>>>> to
>>>> give my account select permissions, i saw the following statement:
>>>>
>>>> DECLARE mycursor BINARY CURSOR FOR SELECT
>>>> stateid,name::
>>>> text,asbinary(force_collection(force_2d(wkb_geometry)),'NDR'),OID::
>>>> text
>>>> from incorpbound WHERE (stateid='ME' and name='Camden') and
>>>> (wkb_geometry
>>>> && setSRID( 'BOX3D(-125 23,-66
>>>> 50.5)'::BOX3D,find_srid('','incorpbound','wkb_geometry') ))
>>>>
>>>> this would imply mapserver is correctly parsing my query - AND - if i
>>>> try
>>>> to execute this query against postgresql, it does return results - i
>>>> get
>>>> all excited by this.
>>>>
>>>> 2 - since query parsing is not left to mapserver (we have to provide
>>>> the
>>>> exact syntax) - there is no reason MS couldnt handle multiple field
>>>> criteria.
>>>>
>>>
>>> I looked again at the code underneath mapserver queries.  Here's what
>>> goes on under the hood: the queried layer has its 'filteritem'
>>> temporarily
>>> set to the 'qitem' from the query method, and its 'filter' temporarily
>>> set to the 'qstring'.  Then the features are filtered and indexes to
>>> the
>>> features become the query result set.
>>>
>>> So yes, you can do a multi-attribute query.  For a RDBMS data source,
>>> your qstring can be almost any SQL where clause, and for shapefile or
>>> OGR sources you can do complicated mapserver expressions like
>>>
>>>     qstring = "([att1] > 0 and [att2] > 0)"
>>>
>>> I guess my querying HOWTO needs major revision already :)
>>>
>>>> 3 - i see no performance issues or other technical hurdles that would
>>>> prevent the execution by MS of this request - unless actually it is
>>>> tied
>>>> to how this information gets back into the TEMPLATE object or is used
>>>> by
>>>> LABELs - both issues i am unclear on (like why a TEMPLATE is
>>>> /required/ by
>>>> MS for queries)
>>>>
>>>
>>> The template is an artifact from the CGI mode, since that's how the
>>> results are delivered.  I'm constantly debating with other developers
>>> over eliminating this artifact from mapscript.
>>>
>>>> 4 - I understood the MS_SINGLE vs MULTIPLE - though i still need to
>>>> learn
>>>> how best to work with this (one problem i have is that a city's
>>>> boundaries
>>>> may come in more than 1 record - but i really just want 1 result -
>>>> something i have to tinker with on my end) - thanks for the
>>>> explanation.
>>>>
>>>>
>>>> I look forward to reading more on documentation and if i can help,
>>>> please
>>>> let me know.  I am working on a prototype public participation web
>>>> application and i need a feature like this to work, and work quickly
>>>> (application performance, not how quickly i need 4.4 released).  The
>>>> two
>>>> fields are indexed and i really can't imagine that saving a query and
>>>> reloading it is going to be efficient, but i look forward to possibly
>>>> hearing from Adam.  As an alternative, i could query on the most
>>>> restrictive field and then iterate over the results until i find the
>>>> value
>>>> of the less restrictive field - though ugly, i guess that is an
>>>> alternative.
>>>>
>>>
>>> Since your data is in PostgreSQL/PostGIS, you can always go directly
>>> to the database when you need.
>>>
>>> cheers,
>>> Sean
>>>
>>
>
>