To perform editing or data analysis, the layers you’re working with must reside in the database.
The Database Manager allows you to import layers into the database.
To import file into the database:
- Click Tools tab and click Database Manager icon.
- Click Import Geospatial file icon.
- Select layer you want to import (or upload it to the GIS Cloud).
- Click Select button.
- Write table name.
Note: When naming table:
- start with a letter or an underscore (_)
- use only English alphabet letters, digits and underscores
- use only lower case
- Projection – Info about layer projection
- Reproject to – If box is checked, it’s possible to reproject layer from original projection to another projection
- Encoding – character encoding is used to represent a repertoire of characters. You can choose between encoding, but note that original layer has to have UTF-8 encoding
- Geometry type – spatial data can be represented as point, line, polygon, multipoint, multiline or multipolygon. If original layer is multipoint, multiline or multipolygon type, check “Expolode geometry box since multigeometry can’t be imported in the Database
- Overwrite existing – Overwrites existing table with the same name
- Explode collections – Explodes multigeometry features to the geometry (i.e. multipolygons to polygons)
- Skip failures – Skips features that can’t be imported to the database
- Use shp2pgsql – Converts a shapefile into a series of SQL commands
Note: make sure that layer has UTF-8 encoding.
Adding Database layer to map
After importing, database layer has to be added to a map. Database is the required data source for editing geometry and performing any data editing, calculations, or analysis.
Database layer is added to a map as shown below:
- Select Add Layer from the Layer tab or Map tab.
- Select Database from the dropdown menu.
- Select a table.
- Click Select button.
Troubleshoot the errors while importing data into Database
Table name is not valid
This error indicates that name of the table is not valid. When naming a table, don’t use spaces or any special characters. Use only English alphabet letters, digits or an underscore (_). Use only lower case.
Unable to open, check if you uploaded all the neccesary files
Shapefile has a several mandatory files: .shp, .shx, .dbf and .prj. If some of them is missing, the layer won’t be imported in database. Upload all the mandatory files in File Manager and try again.
Invalid geometry, please try another type
This mesage indicated that system couldn’t recognize the layer’s geometry. Select an appropriate geometry type from the drop down list and try again. This error may appear when importing the multigeometry layers. If the layer is multigeomety (multipoint, multiline or multipolygon), select the coresponding geometry type and check ‘Explode collections’ checkbox.
Table already exists
This message indicates that a table with the same name already exists in the database. As there can’t be two tables with the same name in the database, delete or rename the table from the database or check ‘Overwrite existing’ checkbox and try again.
List of Database Manager functions
- Database Connection – Enables database connections. For cases where the connection is not established choose Connect.
Refresh – refresh table list.
Select all – select all database elements.
Unselect all – unselect all database elements.
Rename – rename selected table.
Edit fields – edit structure of selected layer
Duplicate – duplicate selected database elements.
Reset – reset metadata cache of features etc from the selected tables.
Truncate – truncate features from selected tables.
Delete – delete selected tables.
- Import Geospatial file – Tool for adding layers into a database.
- Import CSV or XLS – Tool for adding CSV or XLS files.
- Join Wizard – Tool for joining layers.
- Filter – Enables database search.
- All/Spatial/Non-Spatial – Displays all or spatial/non-spatial tables.
- Calculate – Tool for counting number of the features in the selected table.
- Quick actions – when pointing mouse above layer displays Edit and Delete option.
External PostGIS databases
It is possible to connect an external PostGIS database to GIS Cloud and manage data from the external database:
- import Shapefile or XLS/CSV table from GIS Cloud in an external database
- display a spatial table from an external database on the GIS Cloud map
- add, edit, delete features from tables stored in an external database
- delete table from an external database
- add, edit, delete attributes from tables stored in an external database
- connect Mobile Data Collection app and external database and collect data in an external database
To create new a PostGIS connection:
- Open Tools tab
- Open Database Manager
- Click New
- Set parameters and Save
- Connection name
- Host – this is presumably your IP address. This has to be a public IP address, reachable from the Internet, not a
private one (if your address is 10.x.y.z, 192.168.x.y, that won’t work).
- Port – keep the default port 5432, unless you explicitly set up your database differently
- Database name – this is the name of your database within PostgreSQL+PostGIS
- Username – this is the name of the database user that can connect to the before mentioned database. Note this option is case sensitive.
- Password – this is database password. Note that this option is case sensitive.
- SSL (Secure Socket Layer) – Set the desired level of connection security from the dropdown menu.
- Scheme – By enabling this you choose to see only public schema tables.
Note: the values in the table below serve as an example and are not the values you need to type to create your own database.
Note: GIS Cloud database connections are expected to be in the Simple Features SQL format as implemented by PostGIS version 1.5 or higher, including the following requirements:
- the geometry columns should be named wkb_geometry
- the primary key should be named ogc_fid. An auto-incrementing (or serial) column, as opposed to a regular numeric column, is used to ensure that each inserted row gets an automatically generated unique ID (FID) value.
- the tables should have primary keys set so that they can be edited
- all user tables and PostGIS tables need to have the relevant database permissions set so they can be read from and/or written to by the specified user
Note: To preform join, layer must reside in the Database.
Creating a new layer with combine attribute data from two separate layers can be done with Join Wizard.
Browsing for layers which will be included and choosing their attributes defines joining result which is saved in the database.
Join can be made by following next steps:
- Open Database Manager from the Tools tab.
- Click Join Wizard button.
- Type in the name of the layer which will be created as a result.
- Browse first and second layer out of which join will be made.
- Choose which layer will be source for geometry takeover (use as a spatial table).
- Select which columns will be added to the result.
Note: make sure that layers don’t have same column names, with the exception of the column on which join is based. It can have different or equally named column in the both layers, but it’s crucial that it has matching data, unique for each feature.