LeaseControl: Viewing Tenements on a Map

This blog post explains how to connect LeaseControl data to third-party GIS software so that you can see your tenements on a geographical map.

Some of our tenement manager clients have been asking for a spatial component to LeaseControl and we are happy to report that there does currently exist a way to view your LeaseControl data spatially.

This recipe will help you create a link between your LeaseControl data and a third-party GIS platform, which will allow you to use the information contained within LeaseControl within your GIS program.

Step 1

First, we will need to actively connect QGIS to SQL and the LeaseControl database.

To do so, we need to set up an ODBC (Open Database Connectivity) connection.

Type ODBC into the Microsoft Windows search bar and select the ODBC Data Sources application.

We have used the 32-bit version for this example.

The application will appear:

Select Add, and SQL Server Native Client 11.0:

Then Finish, which will open another dialog box:

Please populate with the relevant details.

Note – Name and Description is up to you what you refer to it as, however, the server will have to be correct.

Press Next, and again on the next page (no changes are required here).

Then modify the default database to the relevant one – tick and use drop-down:

Please note you will need to select the database that you want pointing to QGIS.

Press Next, then Finish.

Then test the Data Source:

The test should complete successfully:

Hit Ok, then Ok again and close the ODBC Data Source Administrator.

Step 2

Next, we will need to get our shapefile into the MSSQL Server back end.

This will allow LeaseControl to sync its information with the tenement layer, which will then link to GQIS.

QGIS must be installed with the OSGEO4W package.

This can be done from the below link:

https://qgis.org/en/site/forusers/download.html

Once it is installed, type osgeo4w into the search bar and select the OSGeo4W Shell.

The application will open:

To load the shapefile to SQL, the below command needs to be run.

Preferably in the same folder as where your shapefiles are located, otherwise the full path must be specified in the command.

Tip: to change drive – type in your required drive plus colon and hit enter.

Further Tip: to access a certain folder – type CD<space>folder name and hit enter.

Copy and paste the command once the required folder with the shape files is found.

ogr2ogr -f “MSSQLSpatial” “MSSQL:server=YourServerHere;database=LC_DemoV180;trusted_connection=yes;” “CurrentTenements.shp” -overwrite -SkipFailures -nln “LMS_TENSPL_TenementSpatial” -lco UPLOAD_GEOM_FORMAT=wkt PRECISION=NO -progress

Where:

MSSQL:server=YourServerHere is the server name (this will need to be correct and will be independent to you, the preceding is just an example and will not be your server name!)

database=LC_DemoV180 is the database name (again this will need to be correct and will be independent to you)

“CurrentTenements.shp” is the shapefile to be loaded to mssqlserver (this will be whatever you have named the shapefile)

“LMS_TENSPL_TenementSpatial” is the name of the layer (this will be whatever you wish to name the layer)

Once the command has run as above (done – the blank spaces are where your server name and layer name would go respectively) – the shapefile will have imported to SQL.

Please note however, the shapefile is a simple point of time load and will not update automatically.

If changes are required to the file, you will need to load a new file each time – unlike the LeaseControl connection, which only needs to be done once.

Step 3

Finally, to view your data in QGIS!

You will need to open QGIS and go to the Layer menu, and select Data Source Manager:

Select MSSQL, then create a new Connection, by selecting New.

Fill in the appropriate Host details and add a Connection name.

Connection Name will be whatever you wish to call it.

The host will be your server name (must be correct and will be the same as what you entered previously in earlier steps).

Select List Databases to display your database options – please select the appropriate one (once again this must be correct and will be the same as what you entered previously in earlier steps).

Press OK, then Connect (after selecting the appropriate connection from the drop down), to get a list of database objects which can be added to QGIS. Select the ‘LMS_TENSPL_TenementSpatial’ object (layer) loaded in Step 2 (selection in blue), and press Add:

The layer will be displayed in QGIS:

From there, right click and select Properties for the shapefile you imported in the Layers window, e.g LMS_TENSPL_TenementSpatial

Select Joins and click Add (the little green plus sign) and configure like the below screenshot.

Once you hit OK, then Apply and OK again, the tables will have joined.

We are effectively matching on the join field and the target field, i.e. on tenement IDs in order to populate the information from LeaseControl to QGIS.

However please be aware that depending on the nomenclature used between the two tables, you may find that leases will not correspond as they are named slightly differently in one version or the other. Please feel free to amend these fields to suit as required.

Step 5

So, after all this what can we see here? – right-clicking on the layer will allow you to bring up the attribute table which will display LeaseControl information aligned with your imported data.

More importantly, you can display the information visually by applying labels to your tenement outlines for instance.

Need to know next year’s rent with a spatial aspect? Can do!

Output showing the annual rent payments due for leases on a map.