MapThat – Working with PostGIS Data

By David Crowther

If you are considering connecting your MapThat webGIS instance to spatial tables stored in PostGIS then here are some useful FAQ’s and Help Tips –

• Missing or Incorrect PostGIS Geometry – details how to deal with incorrect or missing geometry: https://www.cadlinecommunity.co.uk/hc/en-us/articles/212810545-MapThat-Admin-PostGIS-Layer-returns-LineString-error-message
• PostGIS 3D or 2D data – outlines that MapThat expects the spatial records in 2D format:  https://www.cadlinecommunity.co.uk/hc/en-us/articles/212188169-MapThat-Admin-PostGIS-Layer-returns-the-Input-has-Z-error-message
• Displaying X and Y Point data from PostGIS – details the column formats that MapThat will need for your X and Y values - https://www.cadlinecommunity.co.uk/hc/en-us/articles/211717085-MapThat-Admin-Forms-PostGIS-DataLayers-not-displaying

In addition you may wish to use the MapThat Drawing Tools to edit your PostGIS tables. If so, here are some extra tips and tricks to ensure that your users can manage their spatial assets via MapThat and maintain the integrity of your PostGIS geostore.

Tip 1 – Ensure your MapThat Layer ‘Unique ID’ field is the PostGIS Primary Key

If you choose your MapThat Layers Unique ID field to be a column in PostGIS which is not your Primary Key, this may mean that not all of your data is shown within the map window.

This problem is caused because there are NULL values in the field which you set as the Unique ID field for the MapThat layer, in this case the OGR_FID field.

But if you edit the MapThat layers properties and choose to use the Primary Key field as the Unique ID field, then because that field will have values for each record the map window will now show all records correctly.

Tip 2 – MapThat PostGIS Point layer does not save New or Moved records

This is linked to the above issue and resolution.

When setting up your MapThat layer ensure that you choose the Unique ID and the AutoNumber Column to be the column in your PostGIS table which is set as the Primary Key.

In the case of the TryMapThat PostGIS Schools table - Schools4326 – in PGAdmin we can see that the Primary Key for the table is the ID field.

Therefore ensure that you set the Unique ID field:

And the AutoNumber Column:

,….. to both use the ID (PostGIS Primary Key) field.

 This means that when you use the MapThat Drawing Tools to MOVE and Create a NEW School (point object), the changes and the additions will be saved and inserted into your PostGIS database. If not then you will end up with records in the PostGIS table that have NULL values in the chosen ID field (as they don’t have numbers auto generated) and this causes display issues for the layer – see tip 1.

Tip 3 – Working with NOT NULL fields

Finally, you may encounter issues when creating new features with the MapThat Drawing Tools if you have columns in your PostGIS tables that have Allow Null values set to be false.

In this example we have a planning applications table – planningapps4326 – where the AREA field has been set to NOT allow NULL values.

If you have not exposed the AREA field to the user within the MapThat Drawing tools, then the insert into the PostGIS Database will fail, as it does not accept a NULL value in the Area field.

To resolve this issue, you can either:

• Expose the Area field and ask the user to complete the value for the Area of the new feature.
• Create a Database Trigger which will auto calculate the Area of the new feature and therefore insert the value for you.
• Or set the AREA field to allow Null values.

The MapThat layer will now allow you to create and edit features within the PostGIS planning applications table.