By David Crowther
Question:
When I try to import data into my spatial database QGIS is throwing an error and the import fails – what is causing this and how can it be resolved?
Answer:
QGIS is a great tool for opening your source spatial data e.g. Esri Shapefile, MapInfo Tab, KML, GML, Esri Geodatabase, GeoPackage and importing those files easily into your destination spatial database.
By making a simple Database connection in QGIS e.g. a connection to a SQL Server Database, users can then simply drag the source file into the relevant database and QGIS will us the GDAL tools behind the scenes to run the import for you!
Depending on the size of the source GIS file, the import can be completed within seconds!
Other advantages of using QGIS to import your GIS files is that it will automatically create the required Spatial Index for your database table for you.
It will also automatically update the Geometry_Columns metadata table in your destination database, making the connection between QGIS and the Database as seamless as possible.
However, spatial databases don’t always accept the spatial format of your source data, which means when you try to import the GIS file QGIS will throw an error message and the process will fail.
The above example is caused because the source data came from an Esri geodatabase where the geometry type of MultiSurface(CurvePolygon) is valid. However, when QGIS tried to import this into SQL Spatial it is a non-supported geometry type and so it fails.
What can we do to resolve this?
Essentially the rejected records will need their geometry types altering into a format that SQL accepts e.g. Polygon or MultiPolygon objects before you can import them. Changing geometry type isn’t difficult but how do you identify those records?
Well, when working in a spatial database you can easily view the Well-Known Text (WKT) to show the geometry type of each record. In SQL for example if we select records from our dataset, it will show the Geometry type as a series of unique values, which again doesn’t allow us to know their geometry types.
However, if you choose to EDIT the same table in SQL, it will now reveal the Well-Known Text (WKT) for each record.
This means when working with your spatial data in a database we can very quickly see and then select certain types of geometry.
Can we do this in QGIS?
Not simply – if we browse the source dataset the geometry field is not shown and as such we cannot determine the geometry type.
Are there any Plugin tools for QGIS that allow us to easily view or extract the WKT geometry types?….. yes, the Get WKT plugin!
Once installed you can select a single feature in your source data and then choose the Get WKT tool which then opens a window to reveal the WKT version of the geometry for that feature.
That’s great, but what if we want to identify which of our records from a dataset of 100 plus records has the invalid geometry type e.g. MultiSurface(CurvePolygon)?.... we definitely don’t want to have to interrogate each feature one by one!
Well, here is a simple way to extract that WKT value into your table.
Using the Field Calculator tool, you can either update an existing or new field with the WKT value using the syntax below.
In the syntax window, will simply need to insert the GEOMETRY function called – geom_to_wkt – and then afterwards in brackets choose the function called - $geometry.
The syntax of the update statement will read - geom_to_wkt( $geometry )
Once ran, the Field Calculator either updates an existing or creates a new field and extracts the WKT for the geometry of each record, and if you then order the records by this new WKT field you can identity the records that have invalid geometry types.
So, if you are interested in knowing the WKT version of records in your QGIS datasets, you now have two great tools to help you!