[Mapserver-users] Re: OT: Db Logic for Address Query

[pkishor_98 <pkishor@geoanalytics.com>]

Chip,


> I'm building an address location tool using TIGER data as a base. I
have all
> of the attribute data in a DB. When I execute the query, I return the
> shapeID, and use this to process the address in MapServer.
> 
> I'm trying to figure out the logic for doing an address query. In TIGER
> data, addresses are broken out into:
> 
>   Right and Left Ranges (TO FROM, 4 fields)
>   Prefix
>   Name
>   Suffix
>   Type
> 
> So the address:
> 
>   3645 East Washington Avenue North
> 
> would be comprised of data from 5 fields. Right now I have my
front-end set
> up so that the user has to enter each component separately (i.e.
there's a
> field for range, prefix, name, etc.). Yet, I've seen on other address
> location tools the ability to enter this entire string into one field.
> 
> How does a query like that work? How would one parse out what should
be in
> each field in order to execute the SQL correctly?
> 

one word... regexps.

I hate those multiple fields breaking an address into its component on
the front-end... they are ugly looking. Here is what I did... I wrote
a set of regular expressions that parsed out the various parts of
whatever was entered in the search box. In my case there was the added
complication that one could search by an address, or by tax id, or by
owner name. So, I had to figure out on the back end which of the above
three was the user trying to look for. Having decided that, I would
parse it out into its components and do a match. There would always be
a little text like so... "not finding what you are looking for? try
the advanced search" which would then open up the ugly many fields
entry page.

You can use regexps in both Perl and PHP (and just about any other
language). Below is the function I wrote in PHP to first determine
whether the searchterm was a tax id or an address or an owner name,
and then break it into its component parts. Hth.

//{{{ determineSearchType($searchterm). Find out what we are searching for
function determineSearchType($searchterm) {
  $dir = "n(orth)?|e(ast)?|w(est)?|s(outh)?";
  $sfx =
"st(r|reet)?|av(e|enue)?|dr(ive)?|la?ne?|c(our)?t|hwy|r(oa)?d|pl(ace)?|cir(cle)?|way";
  $qdt = "nw|ne|sw|se";
  
  if (preg_match("/^(\d+|$dir|\d+\s+\D|$sfx)\s*$/i", $searchterm)) {
    $ret = array( "searchtype" => "unknown" );
  } else {
    if (preg_match("/(\d{2}-\d{2}-\d{3}-\d{3}(-\d{4})*)/i",
$searchterm, $ar)) {
      $ret = array( "searchtype" => "tax_id", "tax_id" => $ar[1] );
    } else if
(preg_match("/^(\d+)\s+([0-9a-z]+)\s+($sfx)(\.|,|\.,)?\s+($qdt)\s*$/i",
$searchterm, $ar)) { // 123 Randal St NW
      $ret = array( "searchtype" => "address", "number" => $ar[1],
"street" => $ar[2], "suffix" => $ar[3], "quadrant" => $ar[12] );
    } else if
(preg_match("/^(\d+)\s+($dir)\s+([0-9a-z]+)\s+($sfx)\s*$/i",
$searchterm, $ar)) {            // 123 S Randal St
      $ret = array( "searchtype" => "address", "number" => $ar[1],
"direction" => $ar[2], "street" => $ar[7], "suffix" => $ar[8] );
    } else if (preg_match("/^(\d+)\s+($dir)\s+([a-z]+)\s*$/i",
$searchterm, $ar)) {                        // 123 S Randal
      $ret = array( "searchtype" => "address", "number" => $ar[1],
"direction" => $ar[2], "street" => $ar[7] );
    }  else if (preg_match("/^($dir)\s+([a-z]+)\s+($sfx)\s*$/i",
$searchterm, $ar)) {                      // S Randal St
      $ret = array( "searchtype" => "address", "direction" => $ar[1],
"street" => $ar[6], "suffix" => $ar[7] );
    } else if (preg_match("/^(\d+)\s+([a-z]+)\s*$/i", $searchterm,
$ar)) {                                 // 123 Randal
      $ret = array( "searchtype" => "address", "number" => $ar[1],
"street" => $ar[2] );
    } else if (preg_match("/^(\d+)\s+([a-z]+)\s+($sfx)\s*$/i",
$searchterm, $ar)) {                        // 123 Randal St
      $ret = array( "searchtype" => "address", "number" => $ar[1],
"street" => $ar[2], "suffix" => $ar[3] );
    } else if (preg_match("/^([a-z]+)\s+($sfx)\s*$/i", $searchterm,
$ar)) {                                // Randal St
      $ret = array( "searchtype" => "address", "street" => $ar[1],
"suffix" => $ar[2] );
    } else if (preg_match("/^([a-z]+)\s+([a-z]+)\s*$/i", $searchterm,
$ar)) {                              // Randal Schwartz
      $ret = array( "searchtype" => "owner", "owner_name" => "$ar[1]
$ar[2]" );
    } else if (preg_match("/^([a-z]+)\s*([a-z]*)$/i", $searchterm,
$ar)) {                                 // Randal
      $ret = array( "searchtype" => "owner", "owner_name" => $ar[1] );
    } else {
      $ret = array( "searchtype" => "unknown" );
    }
  }
  $foo = array($ret, $ar);
  //array_push($foo, $ret);
  //array_push($foo, $ar);
  return $foo;
  //return $ret;
} //}}}