By David Crowther
In this previous blog we looked at tips for connecting MapThat with tables in PostGIS – https://www.cadlinecommunity.co.uk/hc/en-us/articles/360001713717-MapThat-Working-with-PostGIS-Data
In this new blog we will explore some more useful tips so that you can get the best out of MapThat when connecting to tables in your PostGIS Database.
1 – SEQUENCE Fields
If you wish to use MapThat to insert new records into your PostGIS Tables, ensure that you set the AUTO NUMBRER field Type to be SEQUENCE and choose the Primary Key field as the AUTO NUMBER Column.
However, if you still experience issues inserting new records, the issue is likely that the AutoNumber Column chosen in your MapThat Layer settings is not defined a SEQUENCE (Serial) field, and therefore will not auto insert the next numeric value.
If you check your PostGIS Table, any field defined as a SEQUENCE will have a DEFAULT value set e.g. as per the below, the ogr_fic field has a default value which will ensure that when new records are inserted the new record has the next incremental number assigned.
It is possible to create a SEQUENCE type field in PostGIS for a table that has this missing – https://www.cadlinecommunity.co.uk/hc/en-us/articles/360004777017-PostGIS-How-do-you-Create-a-Unique-ID-Field-
Any good spatial importer tool such as Cadline’s Spatial Importer will auto create this for you. The best tip is to choose a field name such as OGR_FID, OGR_FIC and avoid field names sauch as FID, and the SEQUENCE default should be created for you automatically upon import.
2 – Editing Tables in Other SCHEMAS
You may experience some MapThat error messages when you try to connect to or edit tables which are stored in other Schemas – i.e. non-PUBLIC Schema’s.
The best practice to follow when setting up your MapThat layers is to follow these tips:
- Layer Node_SQL – use the format - select * from schema.tablename
- Layer Geo_TableName – use the format - schema.tablename
- Layer Geo_SchemaName – use the format – schema
- Layer Attribute Edit – Info Bubbles – ensure you use the format - schema.tablename – in the Attr Table field.
Using the above options will ensure you can connect to and also edit tables in other (non-Public) PostGIS Database Schemas.