 My name is Norval, I'm a software engineer at Oracle. If it emits spatial and MySQL, I'm usually involved, unless it's actual math, because then there's someone from working on boost geometry. We use boost geometry as a library. And I do everything on, well, I'm involved in everything on the MySQL side. So MySQL 8 is not out yet. We have release candidate at number two out. Anything I say won't be used against me because it's still just a release candidate. The final release is coming in a couple of months. So I'm really excited about this. We have really achieved a lot. The main thing is geography. So I'm going to talk today about a little about coordinate reference systems, what we support there. Focus on geography. And I made it a thing to always talk about upgrade issues, because then no one can complain afterwards that, oh, but you didn't tell us this was hard when upgrading. So I'm going to tell you. I'm going to show a demo of some things and show live MySQL things, mostly. So let's get started. MySQL supports several spatial reference systems. Now we didn't before. This is the first version where we actually have a table of reference systems. I can show you this in MySQL. Can you read this? So we have 5108 reference systems in MySQL. We can look at one of them like this. So this is the GPS coordinate system. I'll get back to that, but just switch back to the slides for a bit. We have 5108. 5107 of them are from the EPSG data sets, the latest version of that. The final one is the abstract Cartesian coordinate system with no units on either axis SRID 0 that we have supported for multiple versions. That was the only thing that MySQL 5.7 understood. That is the root of almost all of our upgrade issues because MySQL 5.7 just ignored the SRID instead of saying, no, no, this is bad, only used 0. So if you did something else than 0 and MySQL 5.7 you're up for some funny upgrades. MySQL 8 will refuse to do computations unless it knows the definition. So in 5.7 it wouldn't be here, but here it's like, let's do a no-up more or less. It's text-geom-from-text 0.00 SRD 1, which is not defined. So this is a non-existing SRD. Yeah, I can do that. And you see the error. There's no... SRS with ID 1. So MySQL won't even create that point for you. In 5.7 it would do that, and that's as I said, the root of a lot of problems. Yeah, we have projections as well. We can show you that. SRD 3857, the Web Marketer projections. So this is Google Maps and Bing Maps and OpenSuiteMap in a lot of cases. This is, well, starting to combine a definition. The thing to notice if you want to create your own is that we don't care about these text strings or anything. We only care about these authority codes saying, yeah, this is that type of parameter. This is this and that. So the numbers and the strings are just human information. Our corner systems are, let's look at the 4326 again. One thing to notice is that it says axis latitude, axis longitude. Because this is from the EPSD. It's latitude longitude in that order. And we are standard compliant in that we follow the axis order defined in the spatial reference system. Which means MySQL will assume that when you're entering a point in this, it's latitude longitude. You can, of course, create your own to fix that problem. So create spatial reference system. Spatial. Spatial reference system. Let's just define number one. It needs a name. No one cares. MySQL doesn't care at least. And it's a definition. We can do the same thing as this one. That and we say longitude east and new axis latitude north. And we get a warning saying that this range of SREDs is reserved by EPSD. MySQL in this case, but we do it because EPSD reserves it. So please don't meddle with this, but this is a demo. I can do whatever I like. But please don't touch these ranges that we have reserved because MySQL upgrade might just overwrite your data or something like that. So now we have a spatial reference system that is just the same as the 4026, but the axis order is different. So I can show you a, for instance the length of a string in these systems. I think that's right. Let's do this in 4026. That is latitude longitude. So it means from this is at longitude zero. So this is a Greenwich Meridian from latitude zero equated to one degree north. That's the length in meters of that line. So if you mix up the axis orders, now it's longitude latitude. And we see that it's slightly longer. Of course some systems produce latitude longitude, some produce longitude latitude. And we know that. So we allow you to override it. Set the axis order to longitude latitude. So this is really a latitude longitude system, but I override it and say that this is longitude latitude input. And the formatting is really off in this small terminal, but it's the same result as we got for SRED zero, which I defined as longitude latitude. Okay. Yeah. So as I said, projections are supported. There's nothing fun about those. It's Cartesian, so we had the math already. Nothing new. Geography is a new thing. And we were told that Earth is not flat in 5.7. And instead of trying to prove that it is flat, we just decided that we should change our model. We have tried to make a rocket to prove that it's flat. Although if you want to create a flat Earth, you can do something very similar. You can create an ellipsoid that is so flat that it has an upside and downside. Because you can create any type of ellipsoid as long as the east-west axis is longer than the north-south axis. Actually, the WKT specification for ellipsoids doesn't allow you to specify anything else. It's limitation on a fallot. Not some really bad limitation because Earth is Earth. And we would calculate things on this. I can create an Earth that is really small and I will get the computation for that. Let me show you. Where was my SRE 1 there? Tethered 2, but instead of where's my spheroid? Let's make my spheroid slightly smaller. One digit less in the distance there. Sorry, I used the same name for two special reference systems. That's not allowed. Too bad the standards are strict on that. I'll do the length again. Now I can do this on SRED 2 which is much smaller Earth. And we really see that the distance now is 11 kilometers instead of 111. My school really takes into account the size of your ellipsoid. Unlike some other spatially enabled open source data database management systems that only do 4, 3, 2, 6 SREDs. We have the full thing. Any type of geometry polygons, geometry collections, anything. And we index it. So we have geographic indexes in ODB. If you are still using my isom for geographic data in my school, then drink the cooler get with your program and start using inodb. Because we don't bother with adding stuff to my isom. Use inodb. And yeah, the SRED is what decides if it's geographic or projected. There's no geog from text. There's geom from text. As I showed you earlier in this query for instance, there's geom. There's no special type for geography because the SRED just decides this. As in the standard specification, it's all geometries whether it's partition or geographic. So just put a geographic SRED in here and it's on an ellipsoid. We have a lot of functions that support geography at the moment. Especially all the spatial relations like within contains overlaps. Anything that can make use of the artery. We have length, but distance between points. The next talk is about distance I think in boost geometry. And we are waiting for boosts to support energy geometry to energy geometry distance before we add it to my school. But it's coming. So we add this piece by piece. So for instance, union intersection, that's missing at the moment. They will come. If you have a very strong need for convex hull on an ellipsoid, talk to me. I haven't heard of a real use case, but if you have it, talk to me. We can try to bump it up the priority list. But we will try to go through things as we see what people need. So we're covering the basics and then we are adding things as people the market requests. Some functions only make sense in Cartesian or geographic settings. Some functions like end points are going to not really care. So it won't take too long before we have added this to union intersection I think. Then there's convex hull. We want to see a use case as well before we spend a lot of time on it. Any questions on what we support here before I go on to the upgrade issues? Any questions on what we are able to do in Marchwell? Can you repeat that please? Clustering functions. No, we don't have any clustering arguments at the moment. Raster support. No, we don't have raster support at the moment. Only vector support. I know all these guys with satellites won't add raster support, but we don't have it yet. It's still new. We started really in 5.7 to expand this. The big thing now is geography. Please come to us with requests for futures like raster and that kind of thing. We really want to prioritize things the right way, what the community wants. We need input on that of course. We are really eager to listen to what people want and we are able to shuffle our priorities a bit to match what people actually request. Let me go to upgrade issues before I can take some questions in the end as well. Just to make sure that I have spoken about this and no one is going to complain that I didn't tell you. Because we have upgrade issues. The first and simplest one is that EBSD data sets is latitude first, longitude second. We decide to follow all these standards and recommendations that says yes, you should take that into account. We did add the manual override because we know that people will be really angry if we didn't. We saw the MySQL, let the Microsoft SQL Server thing happen. We want to try to be sound compliant, but we see a need for the manual override. The problem is that it's only available in MySQL 8 at the moment so you can't do it before you upgrade. On the good side, the storage format is still longitude first, latitude second. I know people use that in MySQL 5.7. We actually had a few functions that expected that and that storage format is exactly the same now as it used to be. As for the zero is of course the safe choice. Because that's the only thing that MySQL 5.7 actually supports. So if you say it's SRED 0, then you're safe on the upgrade. And then the bad thing, you had to recreate indexes. R3 indexes. The reason is really quite simple. It's back to the same 5.7 in yours SRED thing. You could insert geometries of different SREDs into the same column and into the same index. And 5.7 just treated that as oh yeah, it's Cartesian that's fine. I can just do math on it. But in MySQL 8 we actually care about the SRED. So suddenly you have geographic and Cartesian data in the same index and it doesn't make sense. So you actually have to enforce something there. I can show you the way we do that. Create a table in which you name geometry not null spatial index g. So I'm creating a table with one column, geometry column. It's indexed with R3 and immediately get a warning saying that we allow you to do this because we have to support kind of dump, restore upgrades. We have to be kind of SQL compatible between versions but we won't ever use this index because you haven't locked it to one specific SRED. So if I fix this by dropping it first and I can specify that this column is in SRED 4326 then it's fine. Now I can insert, it's fine. I can try with a different SRED even if it's just swapped longitude latitude it's a different SRED so I can't insert it. And this is the reason why we actually had to recreate all these indexes in 80. Luckily we can say that the user base on GS isn't that big yet. So we can actually do this because this port for property geometry and geography is very recent so we can actually do this jump now and fix this really in the beginning of our work. I want to say that if you want to learn more about MySQL in general but spatial as well, we developed this new blog at MySQLConservant.com so I know people are nagging me to blog more there and write about this so yes I will write more about the details of this and you will find stuff about how things are done in MySQL. It's slightly different from Postgres if you come from that world because standards have evolved since Postgres implemented these things. For instance the name of the table, the view we had like the name is the spatial references, not the spatial references which is in Postgres because the standards have evolved, they have changed the names they have modified things a bit, they even changed the column names for a few things. So we are based on a more recent standard than Postgres and that makes for a few differences in implementation. Yeah, are there any questions about the whole general thing? I thought about a few more on geography before I cut it off and started on upgrades. So if there are any questions now, yes it is a double precision floating point so then again it comes back to the unit you are using which is part of this definition so here for instance there's degrees and the conversion factor to radians so if you have a unit that is really small or big then you end up with precision issues but I don't think you will get a lot of precision issues if you stick to kind of normal things but yeah, it's double precision floating points, it's typically enough for most use cases. We know that because of the math involved there's a nature of things that computations are more accurate around 0,0 than at the close of the poles and at the ends of or kind of the anti meridian. That's just because of the math, the folding points and range and that kind of thing but I don't think you will ever get into problems we read later in this. We can certainly produce some weird results by specifically going in on the machine epsilon difference and trying to create problems for ourselves that's certainly possible, we have several cases of that but really later we haven't seen anything causing problems Think we have time for one more question please No? Okay, thank you for listening