By David Crowther
Question:
How do I create MapThat Search Layers which link to my PostGIS data sources?
Answer:
Creating MapThat Search Layers for data stored in SQL Server, PostGIS, Oracle or any valid data source is the same, but only the syntax of the Search query is different. Follow the example step below to successfully create a PostGIS Search Layer.
Create your Search Layer
Simply create your Search Layer as per normal. I like to take a copy of an existing layer that I know already displays within MapThat and then edit the new Layer to create a Search version.
For example here I have taken a copy of the Planning Apps PostGIS layer which I know successfully shows my Planning Apps, it has a valid THEME and a valid Information Bubble, all of which we will re-use in the Search Layer.
In the MapThat Admin Forms make the following changes:
Layer Type:
Set the Layer Type to be SearchSpatial.
Layer SQL Statement:
The key to a working PostGIS Search Layer is that the Layer SQL Statement will be different to those created for SQL or Oracle, because it is PostGIS that runs the query. Here is an example of the syntax needed to create a Search Layer that allows the user to choose from a list of Unique Values, in this case the Planning Apps ‘Progress’ values.
Select * from planningapps4326multigeom where COALESCE(progress,'') in (:progress)
Note - Because these Planning Apps are in PostGIS we need to use the COALESCE statement to find Unique Values, whereas SQL would us the IN statement.
Search Parameters:
As per normal you will also need to add the Search Parameters.
In this example we will allow the User to search via the Progress field and present the results as a LIST.
Note – PostGIS is very case sensitive, so be wary when defining the Table/View and the Column values.
Projects Searches:
Finally ensure that the new Search Layer is exposed in your Project using the Project - Project Searches Tab to add the new Search Layer.
Now within MapThat you can run the new PostGIS Planning Apps Search.
And the resultant features are shown in the map.