Shared Samples

WebGIS enables powerful geospatial web applications and services that securely share your organization’s rich geospatial data, and provides tools to deeply examine spatial data and create value added products, on demand.
Showing results for 
Search instead for 
Do you mean 

PostGIS Custom Searcher for Consumer Portal

by Technical Evangelist ‎03-06-2019 05:33 AM - edited ‎03-06-2019 06:42 AM (209 Views)

Introduction

This article contains a walk-through for creating and registering additional searcher for Consumer Portal instance(s). By default, administrators have these options with default product installation:

  1. Oracle database search
  2. WMPS search

PostGIS Searcher Description

The newly added PostGIS searcher does not require any database preparation (unless specific requirements are in place) like the Oracle searcher provided with installation. Instead, it uses native PostGIS spatial functions to extract required information for positioning pins on the map, such as Lat, Lon and BBOX coordinates.

The searcher is implemented based on a sample project server-backend-custom-searcher.zip provided as part of the SDK package and with help of Implementing custom Backend searcher article.

Implementation

Visual Studio solution

Using Visual Studio 2012 or later open and examine the CustomSearcher.sln solution delivered with SDK package. It contains a sample custom searcher implementation which uses CSV files as a search source. The custom searcher consists of a class implementing a very simple ISearcher interface, requiring only PerformSearch method implementation. For the purpose of this article, all method implementations can be deleted, leaving only the CustomSearcher constructor and PerformSearch method.

Npgsql

Because we are going to work with PostgreSQL database from within .NET environment it's a good idea to utilize an existing open-source ADO.NET framework for PostgreSQL. It is called Npsql and is officially supported by PostgreSQL project. See more details here: https://www.npgsql.org/

Visual Studio makes things easier to maintain by intergrating the NuGet package manager. Using it, you can find and install Npgsql package into the solution. Also use the NuGet packages manager to remove CsvHelper library reference from the solution as it's not needed for this example.

Searcher code

The Consumer Portal communicates with Backend and expects from it following data in regard to the searching capabilities:

  • Lat, Lon - latitude and longitude coordinates of the record found
  • West, East, North, South - BBOX coordinates of the record found - they are used to control a zoom level after user clicks on a search result. Particularly useful when working with point data.
  • Text - name of the feature found
  • Type - type or other description of the feature found

It is not necessary to have all of the named fields present in the database table because some of them can be calculated on-the-fly using DB functions. Therefore, our constructor will require only following fields to be configured:

  • Connection String
  • Table Name
  • Field Name - used to display result name and also in the search query
  • Field Type
  • Geometry field

Sample CustomSearcher constructor definition:

public CustomSearcher(string connectionString, string fieldName, 
string fieldType, string geometryField, string tableName) 
{
    _connString = connectionString;
    _fieldName = fieldName;
    _fieldType = fieldType;
    _geometryField = geometryField;
    _tableName = tableName;            
}

Once we have fields required for constructing a select query, we can define the query format in the PerformSearch method (partially shown below):

public void PerformSearch(string query, PerformSearchParams performSearchParams)
{
    using (connection = new NpgsqlConnection(_connString))
    {
        connection.Open();
        var sqlCommand = string.Format("select {0},{1},{2},{3},{4},{5},{6},{7} from {8} where {9} ilike @query order by {10}",
            _fieldName, _fieldType,
            "ST_Y(ST_centroid(ST_Transform(" + _geometryField + ",4326))) as centroidy",
            "ST_X(ST_centroid(ST_Transform(" + _geometryField + ",4326))) as centroidx",
            "ST_YMin(ST_Envelope(ST_Transform(" + _geometryField + ",4326))) as bboxY1",
            "ST_XMin(ST_Envelope(ST_Transform(" + _geometryField + ",4326))) as bboxX1",
            "ST_YMax(ST_Envelope(ST_Transform(" + _geometryField + ",4326))) as bboxY2",
            "ST_XMax(ST_Envelope(ST_Transform(" + _geometryField + ",4326))) as bboxX2",
            _tableName,
            _fieldName,
            _fieldType); // let's have order by type
        
        using (var cmd = new NpgsqlCommand(sqlCommand, connection))
        {
            cmd.Parameters.AddWithValue("@query", query + "%");
            cmd.Prepare();

            using (var sqlDataReader = cmd.ExecuteReader())
                while (sqlDataReader.Read())
                {
                    var searchResultEntry = new SearchResultEntry
                    {
                        Lat = double.Parse(sqlDataReader["centroidy"].ToString()),
                        Lon = double.Parse(sqlDataReader["centroidx"].ToString()),
                        Text = sqlDataReader[_fieldName].ToString(),
                        Type = sqlDataReader[_fieldType].ToString(),
                        West = double.Parse(sqlDataReader["bboxX1"].ToString()),
                        East = double.Parse(sqlDataReader["bboxX2"].ToString()),
                        South = double.Parse(sqlDataReader["bboxY1"].ToString()),
                        North = double.Parse(sqlDataReader["bboxY2"].ToString()),
                    };
                    performSearchParams.ResultsQueue.TryAdd(searchResultEntry);

                }
        }
    }
}

The implementation uses following PostGIS specific functions:

  • ST_Y(), ST_Y() - gets X or Y coordinate of a point
  • ST_Centroid() - calculates a centroid for given geometry
  • ST_Envelope() - calculates minimum bounding rectangle for given geometry
  • ST_Transform() - transforms geometry to give coordinate system, identified by an EPSG code

Consumer Portal (as of 2018 version) requires EPSG:4326 based data in the search results. Thus this parameter is hard-coded but can be simply extracted and configured as an additional parameter in the configuration.

Custom searcher registration and configuration

Once the solution is built, we need to register and configure our searcher.

  1. Copy CustomSearcher.dll (+ .PDB for debugging) together with all 3rd-party libraries referenced, i.e. Npgsql.dll (+ .PDB) at the minimum.
  2. Paste the .DLL files into C:\Program Files\Common Files\Hexagon\Services\AdminInstances\Backend\bin folder

Searcher Registration

The new DLL assembly must get registered with the Backend service.

  1. Open Backend's web.config file and modify following <castle> section to install our custom DLL:
    <castle>
        <components/>
        <installers>
            <install type="CustomSearcher.CustomSearcherInstaller, CustomSearcher" />
        </installers>
    </castle>

Backend configuration

Starting from version 2016, all searching configurations moved from Backend's web.config into following location:
C:\ProgramData\Hexagon\Geospatial Server\Backend\Search.config

Edit this file and add a new dataset and searcher configuration. See following sample

<search>
  <datasets>
     <dataset name="PostGISServerSearch" id="pgSearch" />
  </datasets>
  <searchers>      
     <myCustomSearcher1 name="mcs1" 
            type="MyCustomSearcher" 
            dataset="pgSearch"
            connectionString="Host=localhost;Port=5432;Database=USSample;Username=***;Password=***;"
            fieldName="county_name"
            fieldType="state_name"       
            tableName="counties"
            geometryField="geometry_spa" />
    </searchers>
  </search>

The custom searcher type is identified as "MyCustomSearcher" - same name used in the CustomSearcherInstaller.cs class file of the VS project.

In the example, we will work with USSampleData (delivered with GeoMedia application) exported into PostGIS database. The search will be performed against Counties feature class and state_name attribute will be used as a search type (result description).

Registering searcher in Consumer Portal

By default, a searcher is registered through Administration Console. But because a custom solution is used, all the taks must be performed manually. It is assumed that there already exists a Consumer Portal instance.

Open <ConsumerPortal_Instance_Folder>\data\satellite_asset.json file. This file holds information about registered searchers. Replace it or merge with the following configuration, correcting the URL of the Backend service. It is suggested to remove all other searchers from the Consumer Portal configuration before testing the new searcher:

{
  "schema": "http://www.hexagongeospatial.com/webgis/jsonSchema/v0.1",
  "workspaces": [
    {
      "id": "WORKSPACE",
      "searchables": [
        "CUSTOM_SEARCHABLE"
      ],
      "activeSearches": {
        "main": [
            "CUSTOM_SEARCHABLE"
        ]
      }
    }
  ],
  "datasets": [    
    {
        "id": "CUSTOM_SEARCHABLE_DATASET",
        "service": "CUSTOM_SEARCHABLE_SERVICE",
        "searchable": true,
        "key": "pgSearch",
        "name": "PostGIS Server Search",
        "filters": []
    }
  ],
  "services": [    
    {
        "id": "CUSTOM_SEARCHABLE_SERVICE",
        "name": "SearchBackendService",
        "type": "SearchBackend",
        "url": "http://<PUT_YOUR_SERVER_NAME_HERE>/Backend"
    }
  ],
  "searchables": [
    
    {
    "id": "CUSTOM_SEARCHABLE",
    "name": "US Sample Data (PostGIS)",
    "dataset": "CUSTOM_SEARCHABLE_DATASET"
    }
  ]
}

Testing

Run the Consumer Portal instance and test the searching capabilities. If everything went smoothly, you should get search results:
ConsumerSearch1.png

Debugging

Use following hints if you need to debug the custom searcher

  • Use browser's Developer Tools (F12) > Network tab to observer the traffic between Consumer Portal and Backend service
    ConsumerSearch-debug1.png
    ---
    ConsumerSearch-debug2.png
  • Enable logging on the PostgreSQL database to see queries being sent to the database
    Example:
    2019-02-27 14:04:07 CET LOG:  execute _p1: 
    select county_name,state_name,
    ST_Y(ST_centroid(ST_Transform(geometry_spa,4326))) as centroidy,
    ST_X(ST_centroid(ST_Transform(geometry_spa,4326))) as centroidx,
    ST_YMin(ST_Envelope(ST_Transform(geometry_spa,4326))) as bboxY1,
    ST_XMin(ST_Envelope(ST_Transform(geometry_spa,4326))) as bboxX1,
    ST_YMax(ST_Envelope(ST_Transform(geometry_spa,4326))) as bboxY2,
    ST_XMax(ST_Envelope(ST_Transform(geometry_spa,4326))) as bboxX2 
    from counties where county_name ilike $1 order by state_name
  • Use Visual Studio's debugging capabilities
    • Make sure all required DLLs are put in the Backend\bin folder
    • Use Debug > Attach to Process... and find w3wp.exe process running BackendAppPool
    • Set a break point in the VS project and re-try the search request

Complete Solution

Complete Visual Studio solution can be downloaded from BitBucket

Overview
Contributors