MySQL or Oracle queries...

[Lowell Filak]

I'm not sure that this has been officially documented (someone please
correct me) although we use it on all our apps.
The item we found most useful is to have a field in the mysql table that
contains the shapeindex of the matching shape (just a sequential
number). That is not the only way to relate the data as you can also
relate the shapeindex to the dbf record and then relate one of the dbf
field values to the mysql table to pull further information.
There is certainly more than one way to approach this being there is
more than one interface to mapscript, however I include a perl snippet here.
Note: This may not be a 4.2 compliant example.

HTH

Lowell

  #
  # Open database connection for query.
  my $dbh = DBI->connect("DBI:mysql:db:host:port", "login", "password");
  #$dbh->trace(2);
  #
  # Prepare the sql query & run it.
  my $sth = $dbh->prepare("SELECT recno FROM housep WHERE ( addnum =
\"$addressinteger.$addressdecimal\" ) AND ( addlett = \"$addresschar\" )
AND ( roadname = \"$roadname\" )");
  $sth->execute;
  #
  # Open the address layer for query.
  my $addlayer = $imap->getLayerByName("housequery");
  my $adddata = $addlayer->{data};
  my $addshapefile = new mapscript::shapefileObj($adddata,-1);
  #
  # Create a point shape object to retrieve from layer into.
  my $addpoint = new mapscript::pointObj();
  #
  # Set a counter for the number retrieved to 0.
  # At this point there could be duplicate roadnames...
  #   For now we are going with the last match.
  my $addfound = 0;
  #
  # Set variables for the x & y of the point.
  my $addx = 0;
  my $addy = 0;
  #
  # Loop through each matching point.
  while ( @row = $sth->fetchrow_array ) {
    #
    # Grab the point by shape index.
    $addshapefile->getPoint($row[0],$addpoint);
    #
    # Grab the x & y for the point.
    $addx = $addpoint->{x};
    $addy = $addpoint->{y};
    #
    # Add this point to the count.
    $addfound = $addfound + 1;
  }
  #
  # If the address was found then we need to hand the query off to the
  # correct module.
  if ( $addfound > 0 ) {
&pic($ppn,$mode,$zmdir,$zmsize,$imgszx,$imgszy,$minx,$miny,$maxx,$maxy,$dx,$
dy,$map,$svquery,$mapext,$addx,$addy,$imap,$refer,$host);
    #
    # Return from module.
    return;
  }
   else {
    #
    # Run queries for records above & below the requested address.
    ...

The following message was sent by William Bulley <web at UMICH.EDU> on Wed,
22 Sep 2004 16:14:52 -0400.

> I am new to MapServer so please bear with me...
>
> The main MapServer home page makes reference to a feature
> of MapServer that would allow queries to a database like
> MySQL or Oracle no doubt through Perl's DBI module.
>
> My interest is not obtaining points to plot from a table
> in a database, but rather tabular or scalar information
> about a map feature (height of a building, address of a
> store, etc.) that might be selected by a MapServer user.
>
> I am no where near being able to attempt this at my level
> of MapServer knowledge today, but if it is possible to do
> this, could someone point me to a chunk of documentation
> or a WWW page for more information?
>
> I assume that something like this would need to be done
> using MapScript (Perl?) if at all.
>
> Regards,
>
> web...
>
> --
> William Bulley                     Email: web at umich.edu