Besides range slider, other types of filters can be implemented in a layer. Comprehensive filter settings can help users find POIs that are interesting easily. Therefore, if you have a large amount of POIs in your database, we strongly recommend you to provide good set of filter settings for better user experience. In this tutorial, we will provide some more explanation and sample code that help developers better understand filter settings in a layer.

Since filter settings are highly customizable, we will walk you through an example which adds some filter settings to a geo-location layer which shows diverse housing properties.

NOTE that this tutorial is only to help you understand how to enable filter settings in a layer. You are free to decide when and how you want to use them for your own purposes. 

1. Design custom filters based on the dataset

Suppose that we have a table called "POI_RealEstate" which contains information of various housing properties. For each property, the following information is provided:

As we can see from the table above, in order to quickly find the interesting properties among large dataset, we can apply the following custom filters to some of the attributes. Of course, you can define your own custom filters according to your need.

2. Define Filter settings on the publishing site

Under "Filters" tab on the layer editing page, we created the following five filter settings. For more information on filters definition, please check Edit a layer page.

Before moving on to the back end customization, let us see what a getPOIs request with defined custom filters looks like.

http://examplelayer/FourthTutorial_FilterSettings.php?lang=en&countryCode=NL&lon=4.94745254517&userId=6f85d06929d160a7c8a3cc1ab4b54b87db99f74b&developerId=4441&developerHash=1b28b7444bde90c2da52aa051a18b2d57440b795&RADIOLIST=1&CHECKBOXLIST=1%2C2&version=4.0&radius=1500&CUSTOM_SLIDER=300000&timestamp=1289231992753&lat=52.365852048&layerName=testlayerfilters&SEARCHBOX=&accuracy=100

According to the getPOIs request definition, the filters have the following values in the getPOIs request.

In the web service, we use these parameters and their values in the getPOIs request to determine which POIs should be returned.

3. Create "POI_RealEstate"

We will create a new table called "POI_RealEstate" in the database. Please check the table structure below for detailed information.

Note that besides the defined parameters for a POI, we add three new columns: Radiolist, Checkbox and Custom_Slider.

These three columns will be used in the SQL query statement for retrieving the right POIs based on the filter settings.

4. Support custom filters in the web service

Here we will provide the sample code which supports the custom filters defined in section 2. We build the new functionality on top of the sample code from the third tutorial. If you have not read it, please do so in order to understand the changes in the code better.

     1) Retrieve values of custom filter parameters in the getPOIs request

     Since we added four custom filters, we want to retrieve the values of these parameters in the getPOIs request. Therefore, we add the four parameters' names.  

     // Put needed parameter names from GetPOI request in an array called $keys.
     $keys = array( "layerName", "lat", "lon", "radius",
                            "RADIOLIST", "CHECKBOXLIST",
                            "CUSTOM_SLIDER", "SEARCHBOX" );

     2) Prepare each filter parameter for the SQL query statement

     Here we define four functions which convert the filter parameter value got from the getPOIs request into a certain value. This new value will be used in the SQL query statement which retrieves POIs that satisfy all the filter settings. These four functions are highly customizable and can be modified for your own purpose.

• Text box filter

     For text box filter, we want to implement a simple search function. This function searches for POIs whose titles contain the search term filled in the text box. In our case, POI title is the address of a property, such as firststreet 1.  In the text box, users can fill in the street name, for instance, firststreet. Then,  only POIs whose titles contain string "firststreet" will be returned. If the text box is empty, all POIs within the search range will be returned.  

     In the function defined below, we cover two cases, if a search term is filled in, the function simply returns the search term as a string. If the text box is empty, we return any string that contains a combination of numbers, letters and spaces. This is because POI title is a string which contains numbers, letters and spaces.  

// Prepare the search value which will be used in SQL statement.
// Arguments:
//   searchbox ; the value of SEARCHBOX parameter in the GetPOI request.
//
// Returns:
//   searchbox_value ; If searchbox parameter has an empty string, return a
//   string which is  a combination of numbers, letters and white spaces.
//   Otherwise, return the value of searchbox parameter.

function getSearchValue($searchbox) {
    // if $searchbox exists, prepare search value.
    if (isset($searchbox)) {
      // initiate searchbox value to be any string that consists of numbers,
      // letters and spaces.
      $searchbox_value = '[0-9a-zA-Z\s]*';
      // if $searchbox is not an empty string, return the $searchbox value.
      if (!empty($searchbox))
        $searchbox_value = $searchbox;
        
      return $searchbox_value;
    } //if
    else { // If $searchbox does not exist, throw an exception.
      throw new Exception("searchbox parameter is not passed in GetPOI request.");
    }//else
}//getSearchValue

• Radiobutton list filter

     We use Radiobutton list to let user choose properties that are on sale or for rent. In table "POI_RealEstate", we have a column called "Radiolist" and value for each property is either "sale" or "rent". However, in the getPOIs request, the value for RADIOLIST parameter is either 1 or 2. "1" refers to "for sale" and "2" refers to "for rent". In order to use these integer values in the SQL query statement, we need to convert them into the values that are stored in column "Radiolist".

     In getRadioValue function below,

• string "sale" is returned when RADIOLIST parameter value is "1";
• string "rent" is returned when RADIOLIST parameter value is "2";

// Prepare radiolist value which will be used in SQL statement. In this
// function, we convert the returned value into the ones that are stored in the
// database.
//
// Arguments:
// radiolist ; the integer value of RADIOLIST parameter in the GetPOI request.
//
// Returns:
// radio_value ; the value that can be used to construct the right SQL
// statement.
function getRadioValue($radiolist) {
  // if $radiolist exists, prepare radio_value.     
  if(isset($radiolist)) {
    $radio_value = '';
    // if $radiolist == 1, return $radio_value ="sale";
    // if $radiolist == 2, return $radio_value ="rent";
    switch ($radiolist) {
      case '1':
        $radio_value = "sale" ;
        break;
      case '2':
        $radio_value = "rent" ;
        break;        
      default:
        throw new Exception("invalid radiolist value:" . $radiolist);
      } //switch
     return $radio_value;
    }//if
    else {
      throw new Exception("radiolist parameter is not passed in GetPOI request.");
    }//else
}//getRadioValue

• Checkbox list filter

     It is possible to have multiple choices in Checkbox list filter. For instance, in this example, an user can find all properties that are either "apartment" or "house".  In the getPOIs request, the checked multiple choices are separated by commas, such as "1,2". If none of the checkboxes is selected, no POIs will be returned. In order to find POIs that fall in one of the selected options, we use bitwise operations. The idea is to add up all numbers returned in CHECKBOXLIST parameter and compare this sum value to the real value from "Checkbox" column of a POI in "POI_RealEstate" table. If the comparison result is 1, it means that this POI falls in one of the checkbox selections, therefore, it should be returned. NOTE that the checkbox value defined on the publishing site should always be the power of 2, for instance, 1,2,4,8 etc.

     In function getCheckboxValue below, we will:

• return 0, if CHECKBOXLIST parameter is empty. This will assure that no POIs are returned if no check box is selected.
• return the sum value retrieved from CHECKBOXLIST parameter.

// Prepare checkbox value which will be used in SQL statement.
// In this function, we add all the numbers in $checkboxlist parameter. If
// $checkboxlist is empty, then we return 0.
//
// Arguments:
// checkboxlist ; the value of CHECKBOXLIST parameter in the GetPOI request.
//
// Returns:
// checkbox_value ; the value that can be used to construct the right SQL
// statement.

function getCheckboxValue($checkboxlist) {
  // if $checkboxlist exists, prepare checkbox_value.     
  if(isset($checkboxlist)) {
    // Initialize returned value to be 0 if $checkboxlist is empty.
    $checkbox_value = 0;
    // If $checkboxlist is not empty, return the added value of all the numbers
    // splited by ','.
    if (!empty($checkboxlist)) {
      if (strstr($checkboxlist , ',')) {
        $checkbox_array = explode(',' , $checkboxlist);
        for($i=0; $i<count($checkbox_array); $i++)
          $checkbox_value += $checkbox_array[$i];    
      }//if
      else
        $checkbox_value = $checkboxlist;
    }//if
    return $checkbox_value;
  } //if
  else {
    throw new Exception("checkboxlist parameter is not passed in GetPOI request.");
  }//else
}//getCheckboxValue

• Custom Slider filter
  

     We use custom slider to return the maximum price for a property. In our example, we just return the value retrieved from CUSTOM_SLIDER parameter in the getPOIs request. 

// Prepare custom_slider value which will be used in SQL statement.
// In this function, we simply return the value of $customslider defined in the GetPOI request.
//
// Arguments:
// customslider ; the value of CUSTOM_SLIDER parameter in the GetPOI request.
//
// Returns:
// customslider ; the value that can be used to construct the right SQL statement.
//
function getSliderValue ($customslider) {
    // if $customslider exists, return its value.
    if(isset($customslider))
      return $customslider;
    else
      throw new Exception("custom slider parameter is not passed in GetPOI request.");
}//getSliderValue

5. Construct SQL query statement to retrieve POIs based on filter settings

In our example, we are going to modify the SQL query statement in function getHotspots to support filter settings defined before. Only the changes are highlighted (in yellow) below.

The SQL query is created to retrieve POIs which meet the criterion of filter settings in the GetPOI request. Returned geo POIs are sorted by distance and the first 50 POIs are selected.

•  The distance is caculated based on the Haversine formula.

          Note: this way of calculation is not scalable for querying large database.

•   searchbox filter, find POIs with title that contains the search term. If the searchbox is empty, all POIs are returned.
•   radiolist filter, find POIs whose value in "Radiolist" column equals to the returned value from getRadioValue function.
•   checkbox filter, find POIs which don't return 0 after comparing the value in "Checkbox" column and prepared checkbox value (from getCheckboxValue function) using Bitwise operations. If CHECKBOX parameter is empty, then no POIs are returned.
•   custom_slider filter, find POIs with value in "Custom_Slider" column that is not bigger than the returned value from getSliderValue function.

  // Use PDO::prepare() to prepare SQL statement.
  // This statement is used due to security reasons and will help prevent general SQL injection attacks.
  // ":lat1", ":lat2", ":long", ":radius", ":search", ":radiolist", ":checkbox" and ":slider"  are named parameter markers for which real values
  // will be substituted when the statement is executed.
  // $sql is returned as a PDO statement object.
  $sql = $db->prepare( '
              SELECT id,
               imageURL,
               title,
               description,
               footnote,
               lat,
               lon,
               (((acos(sin((:lat1 * pi() / 180)) * sin((lat * pi() / 180)) +
                        cos((:lat2 * pi() / 180)) * cos((lat * pi() / 180)) *
                      cos((:long  - lon) * pi() / 180))
                      ) * 180 / pi()
               )* 60 * 1.1515 * 1.609344 * 1000
               ) as distance,
               iconID,
               objectID,
               transformID
     FROM POI_RealEstate
   WHERE poiType = "geo"
      AND title REGEXP :search
      AND Radiolist = :radiolist
      AND (Checkbox & :checkbox) != 0
      AND Custom_Slider <= :slider
 HAVING distance < :radius

ORDER BY distance ASC
    LIMIT 0, 50 ' );

  // PDOStatement::bindParam() binds the named parameter markers to the
  // specified parameter values.
  $sql->bindParam(':lat1', $value['lat'], PDO::PARAM_STR);
  $sql->bindParam(':lat2', $value['lat'], PDO::PARAM_STR);
  $sql->bindParam(':long', $value['lon'], PDO::PARAM_STR);
  $sql->bindParam(':radius', $value['radius'], PDO::PARAM_INT);

  // Custom filter settings parameters. The four Get functions can be
  // customized.
  $sql->bindParam(':search', getSearchValue($value['SEARCHBOX']), PDO::PARAM_STR);
  $sql->bindParam(':radiolist', getRadioValue($value['RADIOLIST']), PDO::PARAM_STR);
  $sql->bindParam(':checkbox', getCheckboxValue($value['CHECKBOXLIST']), PDO::PARAM_INT);
  $sql->bindParam(':slider', getSliderValue($value['CUSTOM_SLIDER']), PDO::PARAM_INT);

  // Use PDO::execute() to execute the prepared statement $sql.
  $sql->execute();
  // Iterator for the response array.
  $i = 0;
  // Use fetchAll to return an array containing all of the remaining rows in
  // the result set.
  // Use PDO::FETCH_ASSOC to fetch $sql query results and return each row as an
  // array indexed by column name.
  $rawPois = $sql->fetchAll(PDO::FETCH_ASSOC);
 
  /* Process the $pois result */
  // if $rawPois array is not  empty
  if ($rawPois) {
   
    // Put each POI information into $hotspots array.
    foreach ( $rawPois as $rawPoi ) {

      $poi = array();
      $poi['id'] = $rawPoi['id'];
      $poi['imageURL'] = $rawPoi['imageURL'];
      // Get anchor object information
      $poi['anchor']['geolocation']['lat'] = changetoFloat($rawPoi['lat']);
      $poi['anchor']['geolocation']['lon'] = changetoFloat($rawPoi['lon']);
      // get text object information
      $poi['text']['title'] = $rawPoi['title'];
      $poi['text']['description'] = $rawPoi['description'];
      $poi['text']['footnote'] = $rawPoi['footnote'];
      //User function getPOiActions() to return an array of actions associated
      //with the current POI
      $poi['actions'] = getPoiActions($db, $rawPoi);
      // Get object object information if iconID is not null
      if(count($rawPoi['iconID']) != 0)
        $poi['icon'] = getIcon($db , $rawPoi['iconID']);
      // Get object object information if objectID is not null
      if(count($rawPoi['objectID']) != 0)
        $poi['object'] = getObject($db, $rawPoi['objectID']);
      // Get transform object information if transformID is not null
      if(count($rawPoi['transformID']) != 0)
        $poi['transform'] = getTransform($db, $rawPoi['transformID']);
      // Put the poi into the $hotspots array.
      $hotspots[$i] = $poi;
      $i++;
    }//foreach
  }//if
  return $hotspots;
}//getHotspots

This concludes our tutorial, feel free to download the Sample Code FifthTutorial_APIv6.zip to give it a try.