BBS Shapefile

[MeganLowe]

Hi @DavidRoy @Gary-van-Breda , the BTO have sent me a shapefile for all current BBS routes. Is it possible to upload this to UKBMS so that all WCBS-BBS squares with routes display the route on their site details page? And if so who shall I send it to? Hoping it may be possible to load the shapefile in one go, rather than me having to draw the routes of every individual site!

The majority of BBS squares don't have routes already drawn, but some do. I'm happy for the shapefile to replace the existing routes, but only if that doesn't affect the data in any way? Thanks, Megan

[DavidRoy]

Please send the layer to me and we'll load into the system

[MeganLowe]

Thanks David, will do.

[burkmarr]

@johnvanbreda @JimBacon - could use some advice on this. On the face of it, it should be quite straight forward, but I've encountered a few problems, some of which I've overcome, but the most recent one I need advice on.

The background

• The BBS shapefile consists of linear geometry for each of around 62000 transect sections.
• Most of these transect sections are already in the warehouse under the Transect Section type, but without the linear geometry, so the aim is to update the geometry.
• Each transect section is associated with a parent of type WCBS-BSS.
• There are up to ten transect sections for each parent.
• The WCBS-BSS locations are named after the monad they occur in, e.g. SD6414.
• The transect sections are named after the parent monad and the section number, e.g. SD6414 S7

What I've done so far

• Some of the transect sections in the shapefile turned out to have null geometry, so I have removed these.
• Because there are so many transect sections, they need to be chunked into separate shapefiles of 5000 transect sections otherwise the warehouse location upload times out and I don't get to see any warning/error messages.
• In the shapefile upload I'm associating each transect section with it's parent through a column with the monad name.
• All worked fine on the first zipfile (on the dev warehouse).

Current problem In the second zipfile the import got part way through but then stopped with an error indicating that the parent (the WCBS-BBS location) was not in the database. That wouldn't be too bad if it just skipped the import of the associated transect sections and listed all the missing parents, but instead the import stops at the first problem one.

My thinking is that I could first import the parents - I'm sure that they are required. It would be difficult to identify the missing parents, so I'm thinking just upload all of them, which would just update any that already exist. My question is about the geometry. Viewing the geometry for the current WCBS-BBS locations in the warehouse, they are squares corresponding to the monad as you'd expect. To update these parents should I create a shapefile with a polygon corresponding to the monad, or can Indicia somehow work out the geometry if I do a CSV upload?

[DavidRoy]

This issue may also be relevant - https://github.com/BiologicalRecordsCentre/UKBMS-online/issues/261 also tagging @Gary-van-Breda as he's doing a lot of work on the UKBMS site at the moment

[burkmarr]

Thanks @DavidRoy - I'll take a look maybe Gary went straight to the main warehouse and I need to get them into dev.

[DavidRoy]

yes, that was what I thought for the 'missing' locations to load the geometry for

[JimBacon]

I think if you upload a csv with a Gridref column containing the monad reference then Indicia will work out the geom. You could do a one-line test file to confirm.

[burkmarr]

Thanks @JimBacon - you sent just as I was doing it! I used CSV with two columns - gr and name both with the monad. Mapped to centroid sref and name respectively and it worked.

[Gary-van-Breda]

@burkmarr Just to confirm your suspicions, I have not touched the Dev warehouse. The squares uploaded for #261 went straight into Live.

[burkmarr]

Thanks Gary. I imported them into dev warehouse but I still got the same problem - there are still some squares related to the transect sections that aren't already in the warehouse, e.g:

It's not in the dev or live warehouse.

I think I'll download the names of all the existing WCBS-BBS squares from the warehouse and write a script to reconcile that with the squares in the shapefile so I can just extract the new ones. Then I'll load these in using the CSV load before continuing with the transect sections.

[johnvanbreda]

@burkmarr when I've had to do significant processing of geometry data in the past, I've found it easier to use shp2pgsql to get the geometries into a temporary table, then write SQL that does the processing. You could in this instance create a temporary table with all the parent geometry data and then just insert the missing ones, likewise the child geometries.

[MeganLowe]

Hi @burkmarr let me know if you'd like me to check your 'extracted' BBS squares with the BTO. I was under the impression we had the full set of BBS squares, so not sure why there would be routes for ones that don't exist in the warehouse. I guess it's possible some have slipped through the net?!

[burkmarr]

Thanks @MeganLowe. When I generate a list of the squares not yet on the live warehouse I'll send them to you so that you can check them. Are there any that are not meant for public viewing?

[burkmarr]

@MeganLowe - I have that list if you want it.

[burkmarr]

@johnvanbreda - I'm persisting with the upload approach for now. I think it would also take me a while to sort out your SQL approach since I'm rather out of practice with that and the DB structure right now.

I have come across another problem but I have a potential solution I'd like to run past you.

Here's the problem:

And the reason:

The solution could be to match the parent locations on code rather than name. At the moment the codes for all the WCBS-BBS parent squares is NULL. So I could update these in pgAdmin to a combination of the square name and either the text WCBS-BBS or the id for that type, e.g. WCBS-BC-SU0805 or 5195-SU0805. Then I could regenerate the upload files to include the parent site code for the matching. What do you think?

[johnvanbreda]

@burkmarr that sounds fine from the perspective of the upload. I've not got enough knowledge of the UKBMS UI to know if the added codes will be visible to the users and might cause potential confusion - though I suppose you could always remove them again afterwards.

[MeganLowe]

@burkmarr yes please. Please can you explain what you mean by 'public viewing'? None of the BBS squares are 'public' as such, in that they shouldn't appear on the Sites Map. The only users that can view the BBS route and details are those allocated to the individual squares or admin users.

[burkmarr]

@MeganLowe - my misunderstanding; I don't really know the UKBMS site and when I need to log in I often do it as admin, so hadn't apprediated that in general users can't see them. I'll email you the square names. If you drop me an email at rburkmar@ceh.ac.uk I will reply with the squares attached.

[burkmarr]

@MeganLowe - these are in the system now. Can you check and, if al is well, close the issue? There were some transect sections in that original shapefile that had null geometry or, in one case, geometry of zero length. So I did not import these. I will email you the list of them.

[MeganLowe]

Brilliant, looks good. Thanks @burkmarr and all involved