• Blog posts

    Bio

    By David Crowther

    MapThat___Working_with_PostGIS_Data_-_1.PNG

    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

    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.

    MapThat___Working_with_PostGIS_Data_-_2.PNG

    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.

    MapThat___Working_with_PostGIS_Data_-_3.PNG

    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.

    MapThat___Working_with_PostGIS_Data_-_4.PNG

    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.

     MapThat___Working_with_PostGIS_Data_-_5.PNG

    Therefore ensure that you set the Unique ID field:

    MapThat___Working_with_PostGIS_Data_-_6.PNG

    And the AutoNumber Column:

    MapThat___Working_with_PostGIS_Data_-_7.PNG

    ,….. 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.

    MapThat___Working_with_PostGIS_Data_-_8.PNG

    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.

    MapThat___Working_with_PostGIS_Data_-_9.PNG

    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.

    MapThat___Working_with_PostGIS_Data_-_10.PNG

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

     

Comments

0 comments

Please sign in to leave a comment.