Bringing benthic data to the surface - moving Marine Recorder into an open source spatial database.
CC Attribution 3.0 Germany:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Hutchison, James (Joint Nature Conservation Committee)
The Joint Nature Conservation Committee (JNCC) is one of a number of organisations that run inshore and offshore surveys to collect data on seabed biodiversity (e.g. species records and habitat types). The main storage and collation system for these data is Marine Recorder, a Microsoft Access-based database application developed by JNCC in 2002. Despite several updates since then, the system is now significantly out of date, and the lack of internet connectivity or the ability to store spatial data is a major problem. As a result of these issues, organisations in the UK that collect benthic data are investigating more up-to-date systems and there is a significant risk of data fragmentation. JNCC are currently in the process of moving all of our spatial data from a file based system into PostGIS. This talk will describe JNCC’s open-source geospatial information strategy and how we are integrating Marine Recorder into this, discussing the efficiency savings that this provides, as well as our plans for developing a full open-source, online replacement for Marine Recorder in the future.
 Okay, I would like to introduce James Hodgson on bringing Benthik data to surface, moving marine recorder into an open source spatial database. Great, thanks for coming. This is quite a similar talk to the last one, actually, a similar problem, I think. So I'm James, I work for the Joint Nature Conservation Committee in the UK, where a government body that advises on nature conservation, and this is the plan, I'm going to talk a little bit about who we are and what we do. I'm going to talk about a piece of software called Marine Recorder, then I'm going to talk about our geospatial information strategy, so how we're changing how we handle spatial data and how we're going to try and include marine recorder into that. So JNTC is a conservation body in the UK, quite a small agency, we have about 130 staff across two offices, and we do a few different things, so it's all about advising government on conservation, both in the UK and internationally, and I'm going to talk about three specific things that we do. So I'm sure you all know that the UK is devised up into four separate countries, and the UK government delegates power out to the different administrations of those countries, and they all also have their own nature conservation bodies that are responsible for nature conservation in those countries. So JNTC has a UK-wide remit, we try and provide some coordination between them by doing things like setting common monitoring standards, so when people go out to monitor the biodiversity, look at what species they have in their country, we're all measuring the same things, we can get a joined up picture for the whole of the UK. We also run surveillance schemes for a particular species, so everything from birds down to mosses and everything in between, and these are actually the schemes themselves are run by NGO partners who we work with, but we provide any government funding that they get, and we also give them some help with data management analysis and that sort of thing. And then a big part of what we do is marine conservation, so this is the UK's marine protected area network, the individual country agencies are responsible for the seabed out to 12 nautical miles, which is the dotted line that you can see just around the coast, but we lead on the seabed in the offshore area, so beyond that line, and we do things like site selection, so deciding what parts of the seabed should be protected, verification, so checking that the things that we think are there are actually there, and then monitoring. So once you've designated a protected area, making sure that the protection that you have there is working and doing what you're expected to do, and for all those things you have to actually get on a boat and go out to your site and see what's there. We don't have our own boats, but we work with a few different agencies that have one, so this one belongs to CFAS, which is another government body, it's the Centre for the Environment Fisheries and Aquaculture Science, and there are a few others that we work with too. So typically a boat like this would go out on a two or three week cruise to a site somewhere offshore and gather a few different types of data. So acoustic data, this is sonar, there are a few different forms, but they all work in basically the same way, so they send out a pulse of sound and then pick up the echoes when that bounces back off things, and the time it takes the sound to come back tells you how far away the thing is, so it gives you an idea of the depth, so you can build up a kind of 3D picture like on the far side, and the nature of the echo also tells you something about the kind of nature of the thing it's bouncing off, so you can tell whether it's a hard substrate, so rock or whether it's soft like mud. Then we also take physical samples of the bottom, so with grab sampling you drop a big piece of machinery like that one there down to the bottom, and it has jaws that close and bring up a load of mud like this, and that gets sieved through various different size meshes, which tells you a bit about the sediment type, so whether it's sort of gravel or sand or mud, and also when you sieve it you find all of the organisms that are living in the mud, and you can send them off to be identified and give you species records. Then we also do camera surveys, so this is a camera sledge on the left, we tow it along the seabed behind the boat, and it records video, which is mostly used to identify the kind of habitat, and also still photographs, so you might take a still photograph every minute of your tow, and also if you see something interesting when you're watching the video on the monitor on the boat, and those used for identifying specific species. All of that data needs to be stored somewhere, so that's where we come on to Marine Recorder. This is what looks like, it's an application that was developed in the early 2000s, so it looks a bit dated now, and it's built on Microsoft Access. So this is the front end, this runs in Access 2003 runtime, so fairly ancient in software terms, and this is what it looks like behind the scenes. So it has this back end database, this is the front end over here, we have this nice little fish icon that somebody designed for us in 2002, it doesn't look like any UK species have ever seen, but they are, then the back end, it's called the NBN data file, because the NBN trust is an NGO responsible for coordinating biodiversity recording in the UK, and the data structure in that back end file was set up to match their data structure, so that we could share data with them, but it means it's quite a complicated data structure. So then we also have the snapshot over here, which is a simplified structure, and that's produced by a snapshot tool, which is just a database that has a load of queries that pull data out of the back end and put it into that snapshot file, and it's designed to be used for easier sort of querying, in the main back end you have probably hundreds of tables with lots of foreign keys and things, whereas the snapshot is a bit simpler to actually go in and pull out some data that you want to use for analysis, and then there are some other tools for merging files together and different things, so it's basically a big collection of access databases, and it has a few issues, so it's not all that easy to use, which means that often the data doesn't get used as much as it should do. All the data entry has to go via the front end, so it has some spreadsheet input tools, but they don't work brilliantly well, and you end up, when you're entering data from a survey, quite often you end up reading it from one spreadsheet and typing it into the front end, which obviously isn't brilliantly efficient, and because it takes a long time, it also means that we often subcontract it out because we don't have time to do it ourselves, so that then costs us quite a lot of money. We're not the only people who use it, so it's used by the country agencies as well, and by some NGOs who collect C-bed data around the UK, and they each have their own back end file, some of them will have multiple back end files, and roughly twice a year, they each produce a snapshot, so the simplified format of that data, and send that to us, and we combine all of those into one UK-wide database, and that gets published on our website and also uploaded to the NBN Gateway, which is the web interface to the national biodiversity networks, biodiversity data, and we also produce some spatial layers of it, which are mostly just for our own internal use, and they're also not as efficiently produced as they could be, so it involves running queries in the database, and then usually saving those as a CSV, importing it into ArcGIS, and then converting it to a shape file, so again, some efficiency issues there, and that UK-wide database is actually a really valuable database, it's the best dataset that we have of C-bed species and habitats in the UK, but because of the format that it's in, it doesn't get used anywhere near as much as it should, so one of our main motivations for wanting to improve this software is these various frustrations and issues that we have with it, but there are a few others as well. So in June last year, Elizabeth Truss, who's the lady over there, she was the Environment Secretary for the UK at the time, she announced that DEFRA, which is the Department for the Environment, Food and Rural Affairs, so the UK Environment Department, would be opening up their data archives and making 8,000 datasets publicly available on this data.gov.uk website, and those datasets are going to be released primarily under this open government licence, which is basically equivalent to a Creative Commons attributions licence. Now, GNCC is quite a small organisation, but we actually hold quite a lot of data because of our UK-wide remit, so we'd already been working on trying to get a handle on our own data resources, and to do that, our developers have built this, which is a metadata catalogue. It's called Topcat, and it's Inspire compliant, so it has all of the same fields required by the Inspire metadata standard. And this is what an individual record looks like. As a little aside, all of the code for this is freely available on GitHub, so I wouldn't say it's a production thing that you could just take and roll out, but the code is available under an open government licence, so if you need a metadata catalogue, you're free to take it from there and adapt it for your own purposes. So when we were given this challenge of releasing all these open datasets, this metadata catalogue that we had been building became essential to our response to that, and we wanted to include marine recorder data within that. So we use R quite a lot as a general purpose scripting language within GNCC, so we developed R scripts that would pull out the metadata that we have in marine recorder. It has some metadata in there, not everything required by Inspire, so some bits we had to add manually as well, but it pulled that out, and that went into Topcat, which checks that you have everything that you need for the Inspire standard, and from there it can be exported to the data.gov.uk website. And then it also pulled out the data, so it broke that down into datasets from individual surveys, and that was written as CSVs, which then get hosted on our website with a link from the data.gov metadata. And this is a big, this is what the data.gov.uk web portal looks like, and these are some of the marine recorder datasets that are in there. This is quite a big improvement in the kind of discoverability of the data, so this has searching, you can find a dataset for a particular species and also for a particular location. You don't have to download this access database and try and dig through it to find the data that you want, but it's still not perfect. Obviously, the dataset has been split up, so you don't have a whole UK dataset anymore, and it's only there as CSVs as well, so it's not spatial data. So there are still further improvements to be made. And another motivation for wanting to improve marine recorder is some work that we were doing around changing the way that we handle spatial data as an organization as a whole. So this is what our spatial data currently looks like. We have a sort of file-based system, so these top two boxes are on network drives, and they're a mix of sort of shape files, as regiolater bases for vector data and then TIFFs for raster data. We have a set of reference datasets, which are sort of top copies of data that we use repeatedly, and they have metadata records in Topcat, and then we have working files, so things that people are using for different projects. And most of the processing happens on people's desktops, usually just in ArcGIS or sometimes with R. And this is where we're aiming to go. So moving away from the file-based system to a spatial repository based on Postgres and PostGIS that will be hosted in the Amazon Cloud. So with the current system, we have, I don't know exactly how much, certainly tens of terabytes of spatial data, probably lots of which is repeats where people have copied a layer to use their own project, so it's not the most efficient system. So moving that into a database and into the cloud will be a big improvement. And for people who want to make a map for a report or something, they'll be able to get that data via GeoServer as a WMS or WFS feed over there on the left. Yeah, your left. For people doing more sort of heavy-duty processing, there's direct access into the database as well with R and other tools, and again, we have TopCat there as the kind of data discovery tool, so that has all the metadata and links into the spatial repository. So we wanted to bring Marine Recorder into this. Marine Recorder isn't inherently spatial, but it is a database and Postgres is a database, so we thought we could just copy it directly into there. The first way we tried to do it was with ODBC, open database standards. Unfortunately, the access ODBC driver is only 32-bit, and we're using 64-bit Postgres, so that just didn't work. They wouldn't talk to each other. So we turn to our general-purpose scripting language, which is R, and actually that proved to be quite a good step in the end because in the course of moving the data into Postgres, you can also convert the Latin long fields from Marine Recorder into Postgres, Post-GIS geometries, add primary keys and spatial indexing and so on. So then we had the Marine Recorder database replicated in Post-GIS, and we wanted some kind of user interface. This was our first attempt at that, so this is very much a kind of alpha thing at the moment. But it does work. So we've done a bit of a user requirements gathering exercise just internally to find out what people would need to actually make better use of this data that we have. And a few of the things that came up were being able to just get the species or habitat records for a particular site, a particular protected area, and also being able to filter on an individual species or an individual habitat type. So this does that. It's got this middle drop-down box here. You can select a particular protected area from all of the protected areas in the UK offshore zone. And that's based on a pre-calculated query in the database. And then the species and habitat filters down at the bottom work using CQL filtering in GeoServer. And this generates a web map with just the points for the protected area that you want and the species or habitat that you want. And that web map includes a link to download a shapefile, but also a custom WMS get capabilities link which you can paste into QGIS and get your points into there that way. So that's kind of the first steps towards making this work. But this is where we ultimately want to be going. So we've already got our post-GIS database, but at the moment it's just a direct replication of the structure that we had before in Marine Recorder. So we need to do some consultation with some of the other people who use Marine Recorder to try and get, try and work out which bits that we actually need and try and simplify that structure a bit. We also have this need for an offline version. So some of the overseas territories that we work with, places like the Falklands and St. Helena don't have reliable internet access all the time. So we're looking at spatial light as an offline database option for them, file-based database. And if anybody has any experience of kind of hybrid online, offline spatial databases and syncing them with each other, we'd be very interested to hear about that. Then this will have eventually a nice shiny browser front-ends to replace the 2003 runtime version that will be easier to use. And it'll have nice automated data import routines potentially via R or some other method so that data entry is quick and easy and we don't have to subcontract, say, at a great expense. And then we'll have GeoServer as kind of already demonstrated for data dissemination and sharing. So yeah, that's where we're aiming to go. And one other thing that came up, one of the challenge is the QJS WMS and WFS interface, which is quite nice if you're used to using that sort of thing, but for less technical users who are used to being able to just pick up a shape file and drag it into ArcJS or QJS, it's a bit complicated trying to set up a connection and get the data that way. So we're wondering about the possibility of developing a simplified version of that that will be easier to use as well. So that's about it. I guess the take home message is a warning about developing your own closed source software. So this was developed initially, I think, just for JNCC's own requirements. And then other people started using it. And because it's closed source, whenever it breaks, which is quite often or whenever somebody wants a new feature, they come into us and we have to try and do something about it. So we spend lots of our time trying to fix this. And our hope is that with an open source version, hopefully we'll be able to come back to you next year's conference or probably more realistically in two or three years' time with a nice shiny version and we'll be able to report that when problems crop up, different people across the user base are able to fix them, that the data gets used better. And yeah, but it's freely accessible and used as it should be. So that's it. Any questions? Is it on? Yeah. Just for clarification, since you use our statistics and you mentioned a shiny browser front end, do you use the shiny package or is it just the term you used? It was just a term that I used, but that's a good point. And that is something that we're just beginning to look into. I've done a little bit of reading about the possibility of using shiny as a front end to us to a database. I think that's not its design purpose. I think it could maybe be done, but I think there are probably better options out there. But yes, certainly shiny is something that we're looking into and starting to use a little bit and it might well be something that we could use for data display on our website and that kind of thing. Yeah. I've got an answer for your question, how to create an interface to add WMS or WFS. In the Netherlands, we've got the PDOK, it's data for a holder of the Netherlands. And Richard Dijvervorder created a plugin to do just that. And it's really easy. It just reads some files from a website and it goes. That sounds great. Could you show me afterwards? I'd be really interested to see that. No problem. Thank you very much. Hi. As far as I understood, you're developing this new system for your, let's say, main office. But for how is your like this implementation plan for your Welsh and Scotch and Irish offices? How they are coming into this new world? Yeah, so that's a good point. At the moment, the way Marine Recorder works because it's all based on access is everybody has their own database file sitting on a hard drive somewhere in their office and then people end up emailing access databases to each other or sending them via some kind of file share and it's a bit of a nightmare. Ultimately we'd like to have this online so the main database will be in Amazon Web Services probably and ideally everybody would then just be able to have their own front end all talking to the same central database so there won't be that need for sending files back and forth every six months. The database will always have the full UK data set there ready to use. So that's where we'd like to be going. But yeah, we're still quite at a quite early stage. We haven't done all that much talking to the different country agencies yet. So that's what we're hoping. But yeah, with any of these projects, as you bring in more partners, it becomes more challenging with lots of different requirements and so on. So, Brugge is yet to be crossed there but that's the plan. Any other questions? All right, well thank you.
 Okay, I would like to introduce James Hodgson on bringing Benthik data to surface, moving  marine recorder into an open source spatial database.  Great, thanks for coming.  This is quite a similar talk to the last one, actually, a similar problem, I think.  So I'm James, I work for the Joint Nature Conservation Committee in the UK, where a  government body that advises on nature conservation, and this is the plan, I'm going to talk a  little bit about who we are and what we do.  I'm going to talk about a piece of software called Marine Recorder, then I'm going to  talk about our geospatial information strategy, so how we're changing how we handle spatial  data and how we're going to try and include marine recorder into that.  So JNTC is a conservation body in the UK, quite a small agency, we have about 130 staff  across two offices, and we do a few different things, so it's all about advising government  on conservation, both in the UK and internationally, and I'm going to talk about three specific  things that we do.  So I'm sure you all know that the UK is devised up into four separate countries, and the UK  government delegates power out to the different administrations of those countries, and they  all also have their own nature conservation bodies that are responsible for nature conservation  in those countries.  So JNTC has a UK-wide remit, we try and provide some coordination between them by doing things  like setting common monitoring standards, so when people go out to monitor the biodiversity,  look at what species they have in their country, we're all measuring the same things, we can  get a joined up picture for the whole of the UK.  We also run surveillance schemes for a particular species, so everything from birds down to mosses  and everything in between, and these are actually the schemes themselves are run by NGO partners  who we work with, but we provide any government funding that they get, and we also give them  some help with data management analysis and that sort of thing.  And then a big part of what we do is marine conservation, so this is the UK's marine protected  area network, the individual country agencies are responsible for the seabed out to 12 nautical  miles, which is the dotted line that you can see just around the coast, but we lead on  the seabed in the offshore area, so beyond that line, and we do things like site selection,  so deciding what parts of the seabed should be protected, verification, so checking that  the things that we think are there are actually there, and then monitoring.  So once you've designated a protected area, making sure that the protection that you have  there is working and doing what you're expected to do, and for all those things you have to  actually get on a boat and go out to your site and see what's there.  We don't have our own boats, but we work with a few different agencies that have one, so  this one belongs to CFAS, which is another government body, it's the Centre for the  Environment Fisheries and Aquaculture Science, and there are a few others that we work with  too.  So typically a boat like this would go out on a two or three week cruise to a site somewhere  offshore and gather a few different types of data.  So acoustic data, this is sonar, there are a few different forms, but they all work in  basically the same way, so they send out a pulse of sound and then pick up the echoes  when that bounces back off things, and the time it takes the sound to come back tells  you how far away the thing is, so it gives you an idea of the depth, so you can build  up a kind of 3D picture like on the far side, and the nature of the echo also tells you  something about the kind of nature of the thing it's bouncing off, so you can tell  whether it's a hard substrate, so rock or whether it's soft like mud.  Then we also take physical samples of the bottom, so with grab sampling you drop a big  piece of machinery like that one there down to the bottom, and it has jaws that close  and bring up a load of mud like this, and that gets sieved through various different  size meshes, which tells you a bit about the sediment type, so whether it's sort of gravel  or sand or mud, and also when you sieve it you find all of the organisms that are living  in the mud, and you can send them off to be identified and give you species records.  Then we also do camera surveys, so this is a camera sledge on the left, we tow it along  the seabed behind the boat, and it records video, which is mostly used to identify the  kind of habitat, and also still photographs, so you might take a still photograph every  minute of your tow, and also if you see something interesting when you're watching the video  on the monitor on the boat, and those used for identifying specific species.  All of that data needs to be stored somewhere, so that's where we come on to Marine Recorder.  This is what looks like, it's an application that was developed in the early 2000s, so  it looks a bit dated now, and it's built on Microsoft Access.  So this is the front end, this runs in Access 2003 runtime, so fairly ancient in software  terms, and this is what it looks like behind the scenes.  So it has this back end database, this is the front end over here, we have this nice  little fish icon that somebody designed for us in 2002, it doesn't look like any UK species  have ever seen, but they are, then the back end, it's called the NBN data file, because  the NBN trust is an NGO responsible for coordinating biodiversity recording in the UK, and the  data structure in that back end file was set up to match their data structure, so that  we could share data with them, but it means it's quite a complicated data structure.  So then we also have the snapshot over here, which is a simplified structure, and that's  produced by a snapshot tool, which is just a database that has a load of queries that  pull data out of the back end and put it into that snapshot file, and it's designed to be  used for easier sort of querying, in the main back end you have probably hundreds of tables  with lots of foreign keys and things, whereas the snapshot is a bit simpler to actually go  in and pull out some data that you want to use for analysis, and then there are some  other tools for merging files together and different things, so it's basically a big  collection of access databases, and it has a few issues, so it's not all that easy to  use, which means that often the data doesn't get used as much as it should do.  All the data entry has to go via the front end, so it has some spreadsheet input tools,  but they don't work brilliantly well, and you end up, when you're entering data from  a survey, quite often you end up reading it from one spreadsheet and typing it into the  front end, which obviously isn't brilliantly efficient, and because it takes a long time,  it also means that we often subcontract it out because we don't have time to do it ourselves,  so that then costs us quite a lot of money.  We're not the only people who use it, so it's used by the country agencies as well, and  by some NGOs who collect C-bed data around the UK, and they each have their own back  end file, some of them will have multiple back end files, and roughly twice a year,  they each produce a snapshot, so the simplified format of that data, and send that to us,  and we combine all of those into one UK-wide database, and that gets published on our website  and also uploaded to the NBN Gateway, which is the web interface to the national biodiversity  networks, biodiversity data, and we also produce some spatial layers of it, which are mostly  just for our own internal use, and they're also not as efficiently produced as they  could be, so it involves running queries in the database, and then usually saving those  as a CSV, importing it into ArcGIS, and then converting it to a shape file, so again, some  efficiency issues there, and that UK-wide database is actually a really valuable database,  it's the best dataset that we have of C-bed species and habitats in the UK, but because  of the format that it's in, it doesn't get used anywhere near as much as it should, so  one of our main motivations for wanting to improve this software is these various frustrations  and issues that we have with it, but there are a few others as well.  So in June last year, Elizabeth Truss, who's the lady over there, she was the Environment  Secretary for the UK at the time, she announced that DEFRA, which is the Department for the  Environment, Food and Rural Affairs, so the UK Environment Department, would be opening  up their data archives and making 8,000 datasets publicly available on this data.gov.uk website,  and those datasets are going to be released primarily under this open government licence,  which is basically equivalent to a Creative Commons attributions licence.  Now, GNCC is quite a small organisation, but we actually hold quite a lot of data because  of our UK-wide remit, so we'd already been working on trying to get a handle on our own  data resources, and to do that, our developers have built this, which is a metadata catalogue.  It's called Topcat, and it's Inspire compliant, so it has all of the same fields required  by the Inspire metadata standard.  And this is what an individual record looks like.  As a little aside, all of the code for this is freely available on GitHub, so I wouldn't  say it's a production thing that you could just take and roll out, but the code is available  under an open government licence, so if you need a metadata catalogue, you're free to  take it from there and adapt it for your own purposes.  So when we were given this challenge of releasing all these open datasets, this metadata catalogue  that we had been building became essential to our response to that, and we wanted to  include marine recorder data within that.  So we use R quite a lot as a general purpose scripting language within GNCC, so we developed  R scripts that would pull out the metadata that we have in marine recorder.  It has some metadata in there, not everything required by Inspire, so some bits we had to  add manually as well, but it pulled that out, and that went into Topcat, which checks that  you have everything that you need for the Inspire standard, and from there it can be  exported to the data.gov.uk website.  And then it also pulled out the data, so it broke that down into datasets from individual  surveys, and that was written as CSVs, which then get hosted on our website with a link  from the data.gov metadata.  And this is a big, this is what the data.gov.uk web portal looks like, and these are some  of the marine recorder datasets that are in there.  This is quite a big improvement in the kind of discoverability of the data, so this has  searching, you can find a dataset for a particular species and also for a particular location.  You don't have to download this access database and try and dig through it to find the data  that you want, but it's still not perfect.  Obviously, the dataset has been split up, so you don't have a whole UK dataset anymore,  and it's only there as CSVs as well, so it's not spatial data.  So there are still further improvements to be made.  And another motivation for wanting to improve marine recorder is some work that we were  doing around changing the way that we handle spatial data as an organization as a whole.  So this is what our spatial data currently looks like.  We have a sort of file-based system, so these top two boxes are on network drives, and they're  a mix of sort of shape files, as regiolater bases for vector data and then TIFFs for raster  data.  We have a set of reference datasets, which are sort of top copies of data that we use  repeatedly, and they have metadata records in Topcat, and then we have working files,  so things that people are using for different projects.  And most of the processing happens on people's desktops, usually just in ArcGIS or sometimes  with R.  And this is where we're aiming to go.  So moving away from the file-based system to a spatial repository based on Postgres  and PostGIS that will be hosted in the Amazon Cloud.  So with the current system, we have, I don't know exactly how much, certainly tens of terabytes  of spatial data, probably lots of which is repeats where people have copied a layer to  use their own project, so it's not the most efficient system.  So moving that into a database and into the cloud will be a big improvement.  And for people who want to make a map for a report or something, they'll be able to  get that data via GeoServer as a WMS or WFS feed over there on the left.  Yeah, your left.  For people doing more sort of heavy-duty processing, there's direct access into the  database as well with R and other tools, and again, we have TopCat there as the kind of  data discovery tool, so that has all the metadata and links into the spatial repository.  So we wanted to bring Marine Recorder into this.  Marine Recorder isn't inherently spatial, but it is a database and Postgres is a database,  so we thought we could just copy it directly into there.  The first way we tried to do it was with ODBC, open database standards.  Unfortunately, the access ODBC driver is only 32-bit, and we're using 64-bit Postgres,  so that just didn't work.  They wouldn't talk to each other.  So we turn to our general-purpose scripting language, which is R, and actually that proved  to be quite a good step in the end because in the course of moving the data into Postgres,  you can also convert the Latin long fields from Marine Recorder into Postgres, Post-GIS  geometries, add primary keys and spatial indexing and so on.  So then we had the Marine Recorder database replicated in Post-GIS, and we wanted some  kind of user interface.  This was our first attempt at that, so this is very much a kind of alpha thing at the  moment.  But it does work.  So we've done a bit of a user requirements gathering exercise just internally to find  out what people would need to actually make better use of this data that we have.  And a few of the things that came up were being able to just get the species or habitat  records for a particular site, a particular protected area, and also being able to filter  on an individual species or an individual habitat type.  So this does that.  It's got this middle drop-down box here.  You can select a particular protected area from all of the protected areas in the UK  offshore zone.  And that's based on a pre-calculated query in the database.  And then the species and habitat filters down at the bottom work using CQL filtering in  GeoServer.  And this generates a web map with just the points for the protected area that you want  and the species or habitat that you want.  And that web map includes a link to download a shapefile, but also a custom WMS get capabilities  link which you can paste into QGIS and get your points into there that way.  So that's kind of the first steps towards making this work.  But this is where we ultimately want to be going.  So we've already got our post-GIS database, but at the moment it's just a direct replication  of the structure that we had before in Marine Recorder.  So we need to do some consultation with some of the other people who use Marine Recorder  to try and get, try and work out which bits that we actually need and try and simplify  that structure a bit.  We also have this need for an offline version.  So some of the overseas territories that we work with, places like the Falklands and St.  Helena don't have reliable internet access all the time.  So we're looking at spatial light as an offline database option for them, file-based database.  And if anybody has any experience of kind of hybrid online, offline spatial databases  and syncing them with each other, we'd be very interested to hear about that.  Then this will have eventually a nice shiny browser front-ends to replace the 2003 runtime  version that will be easier to use.  And it'll have nice automated data import routines potentially via R or some other method so  that data entry is quick and easy and we don't have to subcontract, say, at a great expense.  And then we'll have GeoServer as kind of already demonstrated for data dissemination and sharing.  So yeah, that's where we're aiming to go.  And one other thing that came up, one of the challenge is the QJS WMS and WFS interface,  which is quite nice if you're used to using that sort of thing, but for less technical  users who are used to being able to just pick up a shape file and drag it into ArcJS or  QJS, it's a bit complicated trying to set up a connection and get the data that way.  So we're wondering about the possibility of developing a simplified version of that that  will be easier to use as well.  So that's about it.  I guess the take home message is a warning about developing your own closed source software.  So this was developed initially, I think, just for JNCC's own requirements.  And then other people started using it.  And because it's closed source, whenever it breaks, which is quite often or whenever  somebody wants a new feature, they come into us and we have to try and do something about  it.  So we spend lots of our time trying to fix this.  And our hope is that with an open source version, hopefully we'll be able to come back  to you next year's conference or probably more realistically in two or three years'  time with a nice shiny version and we'll be able to report that when problems crop up,  different people across the user base are able to fix them, that the data gets used  better.  And yeah, but it's freely accessible and used as it should be.  So that's it.  Any questions?  Is it on?  Yeah.  Just for clarification, since you use our statistics and you mentioned a shiny browser  front end, do you use the shiny package or is it just the term you used?  It was just a term that I used, but that's a good point.  And that is something that we're just beginning to look into.  I've done a little bit of reading about the possibility of using shiny as a front end  to us to a database.  I think that's not its design purpose.  I think it could maybe be done, but I think there are probably better options out there.  But yes, certainly shiny is something that we're looking into and starting to use a little  bit and it might well be something that we could use for data display on our website  and that kind of thing.  Yeah.  I've got an answer for your question, how to create an interface to add WMS or WFS.  In the Netherlands, we've got the PDOK, it's data for a holder of the Netherlands.  And Richard Dijvervorder created a plugin to do just that.  And it's really easy.  It just reads some files from a website and it goes.  That sounds great.  Could you show me afterwards?  I'd be really interested to see that.  No problem.  Thank you very much.  Hi.  As far as I understood, you're developing this new system for your, let's say, main office.  But for how is your like this implementation plan for your Welsh and Scotch and Irish offices?  How they are coming into this new world?  Yeah, so that's a good point.  At the moment, the way Marine Recorder works because it's all based on access is everybody  has their own database file sitting on a hard drive somewhere in their office and then people  end up emailing access databases to each other or sending them via some kind of file share  and it's a bit of a nightmare.  Ultimately we'd like to have this online so the main database will be in Amazon Web Services  probably and ideally everybody would then just be able to have their own front end all talking  to the same central database so there won't be that need for sending files back and forth  every six months.  The database will always have the full UK data set there ready to use.  So that's where we'd like to be going.  But yeah, we're still quite at a quite early stage.  We haven't done all that much talking to the different country agencies yet.  So that's what we're hoping.  But yeah, with any of these projects, as you bring in more partners, it becomes more challenging  with lots of different requirements and so on.  So, Brugge is yet to be crossed there but that's the plan.  Any other questions?  All right, well thank you.
foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_0_227.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_228_935.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_936_2735.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_2736_3887.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_3888_5627.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_5628_5867.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_5868_6335.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_6336_6563.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_6564_8663.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_8664_12779.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_12780_13343.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_13344_13967.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_13968_16955.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_16956_18167.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_18168_18623.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_18624_19823.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_19824_20507.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_20508_28343.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_28344_31259.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_31260_31655.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_31656_32135.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_32136_32291.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_32292_32675.png foss4g-2016-1249-bringing_benthic_data_to_the_surface_-_moving_marine_recorder_into_an_open_source_s_32676_32827.png