 Hello, my watch makes it three o'clock, so unless it's wrong, I'm pretty sure it's three o'clock. So, as it says up there, my name's Gary, I'm part of the Fujitsu family, I'm based in Brisbane in Australia. There I work with the PostgreSQL services team, so part of what Fujitsu does is provide support and services related to the open source PostgreSQL product. It's my first time at a PG Day event, so I just want to say it's been really cool and I'm enjoying being here in Brisbane. One of the great things about PostgreSQL I've found is that if you want to know something, there's just so much information available. The quality of the documentation is just great. You go to the website, there's a lot of user groups that you can go to around the world and you can look at their websites and there's a lot of great video talks and presentations there. You go to YouTube, you've got lots of presentations and all the features and you come to these events. That's a bit of a problem because when you've got to do a talk on a Saturday afternoon, it makes it really hard coming up with something that's interesting that you can talk about. So, I thought about it for a bit and I ended up deciding to focus, to do a bit of a use case rather than delving into the technical side. It's not something that interests me, agribotics. It's like a personal project I've taken up outside of work hours but it involves PostgreSQL or PostGIS, more than PostgreSQL, I guess. And that's agribotics. It's more specifically in Australia that's based around ARPAS or Remotely Piloted Aircraft or drones in agriculture. So, I got started or interested in this area through my hobbies, which are cave diving and adventure riding. I'm pretty passionate about those. Adventure riding, I similar professed earlier that he likes cycling. I like something of an engine so I don't have to do all that physical work. That's probably why he's a bit thinner than I am. But both require the participant to be quite aware not only where they are but the terrain around them. So in the case of diving, you've got to know where your next amount of gas is that you can go and you can get some breathe from. So adventure riding, well, how far is it to your next fuel station? Actually, just out of interest, that's a cave we did in Western Australia called Coppabetic, collecting some water samples for one of the Australian universities in New South Wales and one of the national parks somewhere. So when up here in the cave diving industry, he sort of asked me, you know, could I look into mapping underwater caves and things like that. That's really got me interested in GIS systems and because I started using Postgres at work, the Postgres extension. And then sort of recently, a few months ago, a relatively new organisation I'm slightly associated with started expanding in the use of drones and surveying different things and providing different services. My interest grew from there and that's pretty much what this talks about. So look at a few of the features and functions in Postgres that are currently available because it's nothing new I'm using there but how applied them in the use of drones in that sort of agricultural area. So I'll cover a bit of an outline of what I'll cover. I'll give you a bit of background in agriculture in Australia because it's what's based around and the potential of drones in agriculture, the current capabilities which really at the moment sort of surrounds imaging. So we'll look at that and the processing of imaging. Now I'll go through a bit of an example scenario that utilises Postgres. So we'll have a little bit of a look at some JSON stuff, importing flight plans, some of the import capabilities of it using what some functions built onto or utilities built on the geospatial data abstraction library and then some of the vector and raster functions that are in there. You might, you know, I'm not going to cover a lot of features so you might not be aware of them or you might be aware of them. If you are hopefully you'll still find the talk interesting in terms of how they've been applied in this case. Okay so there's 136,000 farmers in Australia that produce enough food to feed about 80 million people. So because there's only 24 million people in Australia there's quite a bit of excess and that contributes towards an export market of about 42 billion. The related services to agriculture actually represent about 12% of GDP. So it's a pretty significant industry in Australia as identified as one of Tony Abbott's five or one of the pillars of the five pillar economy. One's servicing, manufacturing, mining and education. So I guess it's not surprising that there's a considerable investment being made in that area. Trade agreements have provided some relief over there but you know there's also a lot of uncertainty with the TransPAC agreement with the US pulling out. There's also been other stuff like falling commodity prices. The rain with climate change has affected a lot of farms over there and so it's been a bit of a bleak outlook and that's left us with a bit of a skill shortage as well because graduates coming up aren't really wanting to move in this market because it doesn't look very good. And the lower Aussie dollars costs a lot more for farmers to buy stuff into the country as well. And probably one of the most significant things is the high wastage in the supply chain. That's to make between 30% and 40% of the wastage between the farmers and us being consumers. So that's a lot that's going to either come out of the farmers pocket or out of our pockets and it's usually going to end up out of our pockets. So to address these sort of challenges the National Farmers Federation sort of worked with various organisations in Australia and they came up with a four point plan or what they called a blueprint a number of years ago to map out the direction of agriculture in Australia. They're the key points. It's only really the innovation research and development of the last one that interested me being sort of an IT and that was sort of based around securing investment and from both the private and public sectors in this. So that's resulted some funding in the area and as a result there's been a few start-up companies getting into this area of using drones and not just agriculture but a significant amount moving into agriculture as well. So drones aren't a new technology I've been around for a long time as you can see from a couple of the bullet points up there but it's really taken off recently because of the availability of hobby grade kits you know anyone can build an autonomous flying drone nowadays for sort of like $600 or something like that and I mean how many have got drones? Like I was hoping half a few put your hands up So and it's not just the kits to build the units there's free firmware available you can get something out of your crazy fly too and go in and they're modified firmware and flight controllers have got open source applications out there the ground control systems and we'll look at a few open source too so it's really accessible and cheap to get through so in terms of agriculture the opportunity of drones in Australia why do you think it might be such a big industry? Have a think about it because I'll tell you in a minute but anyway one organisation I've recently worked with Scout Aerial Media, I'll put them up there their Brisbane base so that I guess my knowledge has come in a big part from then they've allowed me to go in and see their operation how they work, how they utilise drones and allowed me to look at what they do in their operations and when I went and had a look it's really that visit that sparked my interest because I thought gee if you could use PostgreSQL there and post just there and I'll discuss a few of those features as we go on but they're a significant company they've been around for a couple of years and they're growing they've got some contracts at New South Wales monitoring sharks by drones where they are they've set up a new company they develop their own drones they build them they've created an anti-poaching Scout drone which they've just used with a new operation they've set up in Zimbabwe to stop poaching over there part of their new business so they're becoming quite successful so it's really good to go along and see a new company starting up in this area okay so I listed before some challenges why and drones are sort of being taken up to solve some of these challenges so I asked the question before why do you think drones might be a worthwhile investment in agriculture farms in Australia are generally really large so they range from the largest to about 23,500 square kilometres through to 5,500 square kilometres I must admit a few of those larger ones are more cattle farms or we call stations rather than crops there's still some very large crops over there so just to put that in perspective there's a bit of a map of Singapore Australia's largest farm on top of that would look like that so you can imagine it's quite hard covering that sort of ground on foot so drones are a great way to get around obviously that would be a struggle for today's drones anyway unless you've gone for those really expensive ones but you get the idea farms in Australia are big and they're remote and hard to get to so it's a good application generally two main types we've got the fixed wing and a multi-rotor that's one of the anti-poaching scouts that are built very useful in agricultural work because they've got long flight times for mapping farms and things which goes against the trend in other areas where the multi-rotor is more successful because it's more manoeuvrable and confined spaces and that sort of thing so current capabilities really predominant use of drones is to get better information on the plants the soil conditions and the growing conditions and getting that better information allows the farmers to make decisions at critical points during the growing cycle and of course if you can make higher efficiencies you can save money and generate profits in a small amount of time so that sort of data we're talking about flight plans we're talking about tracks, telemetry data for the sensor from the drone because we want to know where it is and then there's different payloads you can carry and most of it as I said is imaging but these are sort of I've listed up there a few of the reports that they generate and the sort of work that they do for the customers based on capturing that data so once captured the raw data needs to be processed to extract value from it and so we're going to look at a little bit of that it's normally done with a lot of different software packages and that's sort of one of the areas that the light bulb went off when I had a look that they had some applications running in the cloud with some data there they had another server sitting under the desk with a whole lot of images sitting on the file system and there was another one over there and all the data was sort of looked at individually at a single level and I was thinking if you could put all this information together in a spatial database like PostgreSQL and put it all together and relate it together you can get much more value out of that data by looking at larger data sets over a period of time and as we heard yesterday it's really what PostgreSQL is all about all the purpose of the databases organizing large amounts of data so while the current capabilities around looking at the state of crops at the moment the next generation will probably be looking at is protecting crops from harm so the precision spraying of herbicides and pesticides on the crops save money rather than just a blanket solution detecting disease identifying wildlife threats and thwarting them and then later on beyond that we should see sort of automation of the planting and feeding and harvesting of crops mechanically I came across this the other day when I was looking into it's a lettuce farm in Japan that's already fully automated but a little bit different to Australian farms that's all indoors that one okay so why PostgreSQL PostGIS it's not only good at storing lots of data and lots of different types of data I think Simon said yesterday the goal of the PostgreSQL product the community is to provide a platform for bringing together all types of data in one place and there's a lot of different types of data and information that's related together in this industry so not just that but PostgreSQL PostgreSQL's extensible nature it's provided a lot in the way capability to manage and operate on these many different data types some of the features that you know are useful is obviously the geospatial extension postures the libraries that support the based on the geospatial data abstraction libraries there's a lot of functions in there for importing and exporting all the different types of data the fact that PostgreSQL is supported by a large number of commercial and non-commercial products in terms of visualization client programs and that sort of thing is fantastic and then you've got the architecture with the different service side languages that we can put in there like C Python and Perl they bring with them a lot of power in terms of the different libraries that come with them I think there's some other talks further up in another part that I listen to on machine learning and that sort of stuff that could utilize within the database we've got our trigger and notification framework that's quite powerful for integration automation foreign data wrappers we heard about yesterday and then of course all the good features you'd expect an enterprise quality database like concurrency control actually control high availability and that sort of thing so I'll introduce a really simple scenario now of some of the things we can go through and look at some of these features the first thing I might do is look at importing what we call a mission plan so I'll have a look at the JSON-B type very briefly and then we can generate a geometry type on that that allows to do some searching we'll do something useful with that we'll import maybe a log and some sensor data in the form of some images and then do some manipulation of that data using something like we'll use a function called STMAT Algebra to change the different pixel values around in a raster and then at the end we might do some process I say might, I'm planning on doing it hopefully we will identify and export some waypoints of what we identify as some problem areas okay so mission plan it's pretty straightforward a mission plan is pretty much a set of instructions we can load into the RPAS and it tells it where to go and hopefully it will tell it to come back and land safely I said they're really cheap to make but you don't want to go crashing them so every time you go for a flight either so when we capture our data we need to be fairly consistent if we're covering an area of crop we want to make sure we capture all of it and because it's such a large area we don't intend to fly it about 60 to 100 meters to get the resolution we want and that means we need lots of photos to cover that ground so we've got to have quite a good overlap in the photos so we can stitch them together we'll look at that process and there's some good there's a good open source product for that that there was a talk on here last year I looked at online so we'll look at that so the I've got three three listed up there that's a mission planner that runs on Windows that's Q Ground Control that runs on that'll run on Ubuntu so if you're into Linux you can run that and I use that there on Windows and there's an Android version I came across that I haven't actually used that one but it seemed to work okay from what I've heard okay so just to give you a quick look I'm running Mission Planner which is one of them just so you get a bit of an idea of what a flight plan is so it's you generate a flight plan it's got a whole lot of waypoints down here and it basically tells the drone to it's got some actions telling it to trigger the camera and then there's waypoints that it needs to head to where a lat long and an altitude and then whoops and then right right down the bottom of that there'll be an instruction to return to the launch and land so this is the flight planning area and then there's another area where you're actually in flight you can receive telemetry data okay so we can save sorry so we can save our flight plan and just coincidentally this one allows us to sorry there because I practiced this earlier so we can save it in JSON and as we know PostgreSQL you probably know by now because there's been so many talks on it but since about 9.4 the JSON-B data type and Postgres has got some really good functions I mentioned user groups before I'm in the Brisbane PostgreSQL user groups and one of our members Diego did quite a good talk last year on the JSON-B data type so if you haven't looked at some of the presentations from user group go and have a look at our Brisbane one and there's some good information on there and a lot of the other user groups around the world as well so I've got a little VM that went up here and again so I've just got a shared library a shared folder there looking at and the PG-Date mission hopefully I'm just not thinking it right see if you can see in there it's just a standard JSON type and then that's good that's okay we'll jump in and create a so I've got a database called drain let's create a table let's see what the JSON-B data type there so then we can use a let me get dizzy doing this and then we can simply really load that file and using a statement like that insert into there using the values from the variable so it's just picking it up from that PG-Date mission file and that puts it in the database so it'll be a bit difficult to read I think with it like this but I'll do it anyway but you should and you can see the data there in the table so that's great it's in there we can use our JSON-B or JSON function there's a lot of them for manipulating that data but it's not going to give us our geospatial functionality that we want so that's where post just comes in so what I'll do is I'll create a view sorry about the resolution I sort of don't do many presentations one of the things I didn't factor in was the much more resolution on the screen but I guess you learn these things by doing them so a simple view that you can make up there where it's I apologise for my use of all the all of this instead of the you know the replaces instead of using a regular expression my regular expression schools are pretty bad they're pretty rusty so I've done it that way for now notice that I'm only doing it for a type 86 or 82 which is the way points that has a lat long otherwise wouldn't be storing the lat longs that end up zero zero and then if we wanted to do something like look at the centre of the geometry it's going to be skewed and weigh out so that should create a view and now we've got now we've got our geometry type I guess most people are familiar with post just so I've sort of made the assumption that everyone knows the difference between like vector types and raster types sort of a vector type geometry on there and that will enable us to so vectors are sort of use a spatial coordinate system in space and they map out a path from a certain coordinate so you have the WKB things like points and lines and polygons and so forth rasters are more of a grid so we've got pixels with bands associated with pixels that can store values and okay so now we've got some data in there we can do a useful query and do something like select using another post just function like select the distance from the centre of geometry from that flight plan that we over them from the flight planner. I've used esti mate point to set a lat long lat in the case of post just and cast it to a geography type so that I get the representation in metres rather than angles if I'd use the other type and you can see that it's 58 kilometres away that sort of thing is really useful because we can do things like if we're generating automatic invoices or quotes we can take into account what's the size or the area of this geometry to estimate our bigger batteries do we need to take along the distance that might be an invoice item for charging a customer travel expenses and that sort of thing so it's quite a bit of capability to what to what scout and I was looking at currently does okay go back to the slides so once we've gone out and flown our mission then we want to download our tracking information and other data that we've captured like images that's just a picture of a flight controller that happens to be on the drone that I have it'll store the tracking information you can export it in a GPS exchange format or a GPX file that's typically what you see on any GPS in your car or on your bike or when you're hiking and you'll see it on the web quite a lot people saying here's a track of mine and they'll supply it in a GPX format and then you can jump online jump to online service like GPX to KML and display it in Google to have a look so with this type of data there's a utility OGR to OGR it's available as post just that's built on GDAL again there's quite a lot built on these libraries you know the visualization tools that I have a look at QGI is built on it as well so there's just a slide on the two I'll just show you OGR it flows away part of GDAL of interrogating a vector based file like a GPX file and then the OGR to OGR utility it's a utility for basically converting features between different file formats and of course PostgreSQL is one of those file formats that is supported and I've just listed a couple of switches there that will name you set the column name you set the table name that it's going into and more and it's a more PostgreSQL compatible lowercase and that sort of thing so if I jump back into here and into this side so so I've got a file there a track 286 GPX so if I do the follow on it some details about it there's waypoints routes and tracks and so forth in there and then OGR to OGR utility line specified it's going into PostgreSQL some database connection details I've got a PG pass file set up so I'm only specifying the database name the file we're loading and those which as I mentioned like naming it the name of the column and making a bit more PostgreSQL compatible so that's loaded in there there's man pages away I guess available so for the man on OGR to OGR it'll give you a whole list of the different formats and things there's so many switches they're quite I mean I'm sort of just executing it once but there's a lot of it's got a lot of powerful features in there okay so by putting the tracks into our database the idea of that was to meet a requirement that if they're ordered by CASA or they get a complaint about where their flights are someone complained that the drone was flying too low over their property they wanted to be able to have proof and say well look here's all our tracks for this customer in this geographic area and we can do a search to basically say look we haven't been flying in that area unless of course we didn't load the GPX file base but it provides them a pretty good history of what they've been doing with their drones and so yeah I mean I'll carry on with a few more things but it's quite surprising that just the features and adding the putting all this data into Postgres and using some of the features that it's provided the extra functionality that it's provided with rather than just running all these separate utilities on different machines around the office okay okay so it's plans then imagery that I mentioned pretty the main thing we capture the use of imagery used to be used to be done by a satellite so this sort of technology has been around for a while I'll be talking about NDBI or a normalised difference vegetation index it's actually been around for a long time I go and I invented it in the early 1970s we're comparing data from a satellite called Landsat 1 with biomass or amount of vegetation in those areas and he came up with some formulas he had quite a few formulas that worked but this is the one he ended up choosing and publishing those values just you know NIR's near infrared light minus visible light over near infrared plus visible light and it comes up with a an index can you explain what's the effect of radiation sorry these are some bullet points of some advantages of using a drone over the images from satellites so when you use a satellite we're just referring if you've got a drone it's much close to the ground which has some advantages it means that we can get much higher resolution I think that Landsat 8 which have a look at the best resolution on that you end up with about 30 square meters per pixel by using a drone like on my drone I just use it I've modified a consumer camera like that if I'm flying that at about 80 meters with the focal point and the aperture size I'd be getting less than 2cm per pixel so it's much much better resolution cloudy days aren't so much of an issue because you can fly under the cloud however there are factors you've got to take into account because you don't want part of a crop shady in another part sunny where you're going to get differences in your readings and reflective radiation doesn't have to travel far it's just referring to those light frequencies don't have to go all the way up through the atmosphere to the satellite before they're recorded it's only going up 60 meters so it's going to be less distortion from various atmospheric considerations so I'll talk a little bit about the normalized difference vegetation index the whole theory is quite simple and it works along across the principle of healthy plants have the spongy layer built into their leaves that reflects near infrared light the healthier the plant is the more near infrared light it'll reflect if the plant's unhealthy that it won't reflect much I've been told as well that the substance works as a bit of sunscreen and protects the cell structure of the plant itself but as the spongy layers on the bottom of the the plant I'm not quite sure the reasoning behind that one so I'll have to find out okay so as I said NDVI images aren't new they've been around for a while they've been back in the 70s and used as part of the Landsat program that's been running people heard of that one it's been running for a while it's been a project that was created to look at the Earth's surface and collect information about the Earth's surface so that first satellite went up in about 1970 and then Landsat 8 went up a year or two years ago the first one had red, green, blue and near infrared with very poor resolution Landsat 8 it's got about 11 bands 2013 so it's a while ago now so NDVI lost popularity in terms of using it from satellite there's other techniques but now that we've got RPAS flying low there's been quite a renewed interest in it and use of it so usually an NDVI image because of the formula we're going to end up containing values from minus one to positive one the higher the number so if it's a one it's reflecting a lot of near infrared light so normally minus one to about one or zero is non-living material vegetation tends to be from about 0.4, 0.5 up to 0.8 but they'll represent it like this although it's a single band they'll use something like a colour map associated with the raster to represent it in various colours and then put a legend against it so you can see the readings so what you might do after going through this the issues with it because there are issues and the uptake in Australia hasn't been as quick as I thought it would be and that was partly why I started looking into this but I'll talk about that in a minute so I downloaded some images from the Landsat satellite of my area and we'll have a go at converting one of them into an NDVI image there's three main tools for dealing with rasters we talked about the vector tools before raster to PGL imports a raster file into it you can do a single file you can import multiples by specifying before we know we can break it up into a number of individual tiles or smaller rasters which we'll do a little bit later and you can create thumbnails and things like that however you can't change the format or transform it to another spatial reference system so that's what these other two functions are used for GDAL Translate and GDAL Walk so all this stuff is publicly available and you can play with it there's even some good sites out there if you want to write programs and put it up in a satellite you can get involved with that as well so this is the handbook that's available that you can download and it's a whole stack of information in there about the satellites what it looks like on board but the thing we're interested in is this table here for Landsat 8 it shows us what the 11 bands are so to be able to do our NDVI calculation we need nearer for red band which is band 5 I don't know if you can read it at the back so band 5 is nearer for red and band 4 is red alright so I'll go back into our mouse there's a GDAL info that you can run on the raster to have a look at it before I load it in I'm on the right folder so this is the folder where I've downloaded the images from the satellite and you can see it's all of the same area in the same image but they've got a suffix in there indicating the band so we're after the B4 and B5 being the red and the infrared bands so we can load that in again using our our utility Rastiff PGSQL specified our spatial reference system of 4.3.2.6 there's the file and we're going to put it into a folder and a table called LSA NIR and staging so let that load I will load up the red one as well and it's Postgres so now there I'm just going to open it up I might get a bit short on time so I might just go ahead and execute the things we'll create a table to store our raster in so it's just a plain table we'll give it a CRID so it's got an ID and then a column of type raster common convention like with geometries it's called a geom with rasters it's called Rastfinally so we've got a table we've got our table one thing interesting if I if I'd shown you the actually I'll just show you quickly in QGIS many people use QGIS it's a pretty good product so if we're accessing vector based graphics you can see the Postgresql elephant and here but if you want to access rasters you've got a book to come up to database manager and do it through there and then we can go into postures load it into staging I want to have a look at that and then load that into the canvas so it's a bit dark on there but there's the land here there's a bit of an island out here with the red but you can see I'm going to set it off from here let's take a look so as we click around in different parts you can see the different pixel values and the single band changes and they're quite high and the frequencies we get in that satellite image so if we have a look at the actual the type that we store our pixel value in the band it's a 16-bit unsigned integer which is okay for that type but when we go to our NDVI image it's got decimal values from minus 1 to 1 so it's not going to be able to store it so we need to make sure when we create a new raster from merging the other two together that we that we take that into account so we'll create a new table and we're using a it's very hard to read so creating a new table we're inserting it into that table we created into the raster and we're using the STMAT algebra which is an algebraic function like rasters or change pixel values you can do things like reclassing where you might want to say let's change this group of pixels and just make them all the same so maybe we've got different values for water and we just want to call it water or extract it we can just change it to say not a value or zero so it doesn't actually have a value in there and we'll use that a little bit but what we're going to do here is we're going to apply the NDVI formula the LSA NIR for red values is R1 and we're just joining that on our red values is R2 and then we're applying our formula so R1 which is NIR minus R2 over R1 plus R2 and the good thing you can do that sort of recalculation and some of the visualization tools but the great thing about being able to do it you know, do it automatically you can just build it into your processes it just happens that'll take a little bit of time because they're very large rasters on those images and it's going through all those pixels and applying that calculation to every pixel and there won't be a long time only probably 40 seconds or something like that it's not that good, it's not a big server that I'm running on here so so while that's running some of the things when I first looked at it I thought how accurate is this stuff because if I look out at the plants in my garden at different times of the year they've got more growth than at other times of the year and different types of plant have different growths and it's true and that's why different crops and these are from a study that I looked up when I was sort of looking into the accuracy done in the US on corn and canola over about an eight or nine year period and you can see not only are the different crop types much different but even within the crop type for the different wave bands there's different amounts of light reflected and a lot of that's just due to how much growth there is on them but what they have found is that it's consistent over the eight years so that form of that shape is the same every year for a particular area, for a particular type of tillage and this is where I see the sort of issues or the slow uptake in Australia because that's what an MDVI image looks like and people just capture the MDVI and then they're trying to go out there and they're giving it to our farmers and say here you go and it's not really of much use to them and they say well how does that help me where's the problem area and why is it a problem area and what do I do about fixing it and just scout who have been doing it properly take this and they give it to an agricultural expert or a specialist who can take the MDVI and then go further and do further research into what's the type of crop, how have you been treating it what's the time of year and they go out there and perform further soil tests and then actually turn it into a document that's useful to the farmer but as I say there's a lot of start ups going out there just here you go and it's given a bit of a bad rap over in Australia and so the take ups has been a little bit slow I think but having said that that's where the potential and post-credits is that being able to store that information over a period of time, that reference data allowing us to work out or calculate well for this crop in this region at this time of year with the rainfall we've had this year what's the standard deviation do we fit into what's an allowable range to say this crop's healthy or it's not healthy okay let's see if that thing's finished I'm still going into that okay so I decided to understand the process better myself and get into it and this is where I started playing around I decided to modify my own I actually my own camera and then flying it for a drone catch it I decided to throw that one in there this is the best way of catching these images is from a multi-spectral camera so it'll have multiple lenses and each one will catch a particular spectrum of light unlike a consumer camera and that's one that's been generated this firm's generated from some work it's done with the New South Wales police force to try and identify potentially where bodies are buried based on different light frequencies that have given off of foliage in the area so you sort of look at some of these examples and it really gets you thinking about this for things and I think Postgres is going to make a really good I hope so I'm trying to convince them to pick this thing up it's going to make a really good platform to base their work on so anyway I don't have the money to afford one of those better cameras because they're grandest for the cheapest one so I went out and bought a a cheap consumer camera for $100 off eBay and you can modify these to generate NDVI images I mean traditionally capture red, green, blue it's a red, green, blue channel and I've written in there the different frequencies of the light in nanometers and the cameras have a CCD and the back of them capture the light and those CCDs are actually capable of capturing up to about 1300 nanometers which is into that near infrared range beyond red but they have an infrared filter that sort of blocks the light going in from that point upwards so the modification I went through that's where I've taken a picture of the let's take the back of the camera apart and take the little LCD screen off and scrape away some glue that was on the screws then I was able to unscrew the sensor and get at the little infrared filter take it out and replace it with a blue filter and so by taking out the infrared filter it means that now that infrared light from 700 to 1300 nanometers can go through and capture it on CCD and by putting the blue filter in there it stops the red light going through which means I'm able to capture the infrared on channel one and channel two I just get the visible light of blue because of that part of the sensor the blue doesn't actually capture it's only the red channel that captures up to 1300 so that that gives me everything I need with a camera for about 100 bucks to go out and capture some of this data which I wouldn't suggest going into business with that sort of thing but it's good enough to go out there and play with an experiment with what I'm doing so the next step in the processing is to generate what are called orthomosaic images or ortho photos so that's quite difficult what it means is that orthomosaic is that we put all these pictures together and we want to generate an image that's to scale so we can pretty much use it like a map so we can measure the distance between a couple of features on that image and using the coordinate system of that reference system we know it's accurate like we do with a map so that's a really quite involved process there's an open-source tool called actually I've got an animation here I think they'll put it in so it's a process of like putting all the images together there's some extra things that you can do with some control points or ground control points that you can go out in the field and actually put them down, give them a known location and that'll help software like OpenDreamMap put it all together it uses, if you don't have ground control points it uses the XF data that's tagged on to the photos you take these days like the focal length and I won't pretend to know the calculation that they utilise it's some pretty clever software the guys working on it I was surprised that it was available just when I started looking around for this because there's a few commercial products available and they're very, very expensive and when I sort of looked into the type of process and they do, it's really involved not only do they generate the author mosaic they generate a lot of other types like DEMs and DTMs for terrain models cloud spot cloud models and that sort of thing and then you can take those and you can render the surface of them and make them into 3D models as well hopefully our so that's finished so I might load that up into QGOS quickly and you can see the different you can get rid of that one and then we'll load up from PostgreSQL the NBDI that we created let's see if we can put it in staging or let's put it in there so now you'll see that you can see the values are in that sort of range that we're talking about between minus 1 and 1 now ok but as I say we've generated that image but it's not really of much it's not really of much use let's get rid of that bring up the station ok that's it I've got a shot in there of the site I really recommend that open drone mac as I say that was a product that I noticed online that was demonstrated here last year I've got I don't know if I've got time I don't run that terminal recording so you can see it running and I can speed it up I'll just run it really really quickly if I can so it's ok if you spill over you've got 15 minutes then ok cool sounds good so what I left it at is that because this thing took a couple hours to run and actually downloaded a an example from one of the commercial sites so I could compare it and see how good the open source version was compared with the commercial one and it was pretty good it was pretty close but it took about one and a half days for it to actually process all the images about 1200 images so you can see basically there's a folder that you specify with the images in there and it's a Python program that you can execute and just supply a project file and I'll just speed it up so you can see the so it resizes the images using the exit data and it looks through them finding matching points to be able to stitch them together then it does some depth stuff on it and I'll speed it up some more some depth maps I won't go into detail on what it does but you get the idea that it does a lot of work to create our orthomosaic then I've got it on the file system here but I'm going to start skipping things so it's generated an orthomosaic and some other images not just that in a directory structure and what we'll do is we'll utilize that and we'll load it into Postgres and split it up into some smaller tiles and then we'll make some changes to try and find out what areas that we need to investigate further that don't meet our expectations with the reference data I don't have but maybe hopefully one day we'll be able to generate a Postgres QL so it'll end up looking like this it talks about pixel values minus one to plus one the I mentioned about I think I mentioned about plus five or plus point five and one area might represent a really good value but for a different crop type plus seven might be a really poor value so really we need that reference information so what I'll do is it's generated that orthomosaic we'll load it into Postgres QL and create a it's there I won't go through it but you can see it's in that drone folder and it's picking up a file called dmorphofoto.tif it's already an NDVI image so as I say it's quite large and it's going to break it down into smaller rasters of 200 by 200 pixels each and with this it's going to be about 4,000 images one of the reasons I'm actually doing it this way is because I don't have a lot of memory on here and if I try to load it as one whole image it would actually run out of memory on this little dm so while that's wrong thing while that's running over there we'll create a table to put it in problem area still running, it's just adding in constraints now another class that's really useful it's a bit like stmadvalgebra this class allows you to reclass rasters it's a little bit simpler to use though so here we're taking that image I've just loaded specifying the column band 1, there's only one band anyway and then I can just with the reclass what I can do is I can say all values from minus 1 to 0.4 change the pixel value to 0 anything from 0.4 to 0.6 change it to 1 and anything from 0.6 upwards to 1 make it 0 so I'm only going to have 0s and 1s in the image and those areas with 1 will be representative of areas of a crop that aren't very healthy according to our reference data so execute that, now it's done the idea of doing it this way is that I can then go through and utilise another function and post just stvaluedCount to go through and find out how many 1s in each tip there are and how many 0s in there and I can examine it and say well if there's more than 70% 1s then that's an indication of this is a problem area within the crop ideally it's not the best way of doing it by splitting up a tile into smaller tiles and looking at it like that really I didn't have time here but I'll look into sort of writing something that can do a scan and it's a bit more intelligent way of grouping together poor value pixels to identify problem areas in fields so once we've done that we can utilise a function like this which a couple more post-crash kill functions I just wanted to demonstrate this stvalued count which we'll go through and count the values of 1 and then there's another one st raster to well co-ord which enables us to actually convert the coordinates of the raster into a real real life coordinates the co-ordinate system has been used and I'm just there are a bit of scrimmage somewhere about that don't look at that bit by doing that it's going to tell us what 30% because the tile size is 200x200 4000 pixels on there I'm saying if there's any more than three-quarters of the tile is a value of 1 then flag it and that's pretty much what we get back with our co-ordinates so I really apologise about rushing through this stuff now so that's sort of the next step of what I want to get to now I've got a list of some of the functions that I've found quite useful while playing around with it I'm experimenting with images there's things like ST color mat that allows me to generate nice representations of NDVI's like we saw earlier on using by generating color mat things like ST what else is up there histograms quite good for sort of looking at the distribution of things and ST quantile for grouping ranges of pixels together so you can get sort of an idea of coverage of good foliage and bad foliage one of the sorts of things that play around ST as JPEG there's some good functions there for exporting your final images that you might want to email out or send out to your customers as part of an automated report a little bit of a rush so I thought it does that but the idea was to sort of go through I guess a lot of presentations are quite technical at this conference so I just want to do something a little bit different and say hey look here's rather than focusing on the features in the software itself here's how to utilize it outside in the real world so hopefully it might spark a bit of interest and give you some ideas about how you can go away and use some of these functions so so I guess in summary you looked at the capabilities of RPAS in agriculture typical image process and it took a really simple example and showed how to generate an image and how we can extract some data from it and we looked at a few of the features that really exist in PostgreSQL and looked at how we were able to use them so next I want to sort of be able to automate the process and capture as much data as I can and generate try and generate that useful end result with some suggestions like at this time of year for this crop type potentially if ending the i-values in this range it could be this type of pest it could be this type of diseases prevalent for those soil conditions these are the type of things you need to look into just to make the job of the ananalyst that's going out or the expert a lot easier so he doesn't have to go through such a long process it does a lot of it for him and just automating all of that so any questions on that so it's more about a bit of a hobby project I've been working on rather than a work one but it's really interesting I think it's a good way of learning learning about new features in PostgreSQL learning new things you don't always get the opportunity in your work environment so if you have a little project at home it's a great way to learn about these new features I'll no questions then that's good I hope that's because I covered the subject matter so thoroughly alright cheers then