GIS features in MariaDB and MySQL

Video thumbnail (Frame 0) Video thumbnail (Frame 809) Video thumbnail (Frame 1996) Video thumbnail (Frame 4206) Video thumbnail (Frame 8575) Video thumbnail (Frame 12376) Video thumbnail (Frame 14828) Video thumbnail (Frame 16741) Video thumbnail (Frame 17665) Video thumbnail (Frame 21410) Video thumbnail (Frame 22894) Video thumbnail (Frame 23896) Video thumbnail (Frame 27359) Video thumbnail (Frame 29063) Video thumbnail (Frame 30381) Video thumbnail (Frame 35650) Video thumbnail (Frame 39814) Video thumbnail (Frame 42444) Video thumbnail (Frame 47459) Video thumbnail (Frame 48705) Video thumbnail (Frame 52045) Video thumbnail (Frame 53725) Video thumbnail (Frame 57465)
Video in TIB AV-Portal: GIS features in MariaDB and MySQL

Formal Metadata

GIS features in MariaDB and MySQL
What has happened in recent years ...
Title of Series
CC Attribution 4.0 International:
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.
Release Date

Content Metadata

Subject Area
MySQL originally only had very rudimentary GIS support. Both MariaDB and recent MySQL releases have improved quite a bit on this front though. We will look at the improvements and at the differences between the two extended implementations.
Keywords OSGeo & OSM

Related Material

Video is cited by the following resource
Computer animation Meeting/Interview
Slide rule Computer animation Quicksort Form (programming)
Point (geometry) Area Addition Polygon Set (mathematics) Database Two-dimensional space Line (geometry) Type theory Process (computing) Computer animation Ring (mathematics) String (computer science) Object (grammar) Data type Geometry
Area Point (geometry) Theory of relativity Length Multiplication sign Polygon Shared memory Line (geometry) Mereology Rectangle Subset Category of being Computer animation Ring (mathematics) Personal digital assistant Term (mathematics) Network topology String (computer science) Single-precision floating-point format Touch typing Table (information) Form (programming) Geometry
Point (geometry) Slide rule Functional (mathematics) Shape (magazine) Number Pi Simplex algorithm String (computer science) Operator (mathematics) Negative number Cuboid Software testing Data conversion Extension (kinesiology) Area Inheritance (object-oriented programming) Polygon Electronic mailing list Database Line (geometry) Rectangle X-ray computed tomography Value-added network Subject indexing Computer animation Query language Order (biology) Statement (computer science) Quicksort Procedural programming Table (information) Geometry
Boss Corporation Trail Implementation Data storage device Sound effect Rectangle Subject indexing Arithmetic mean Computer animation Query language Cuboid Table (information) Resultant Geometry
Slide rule Standard deviation Information Projective plane Set (mathematics) Numbering scheme Database Rectangle Limit (category theory) Revision control Computer animation Computer configuration Calculation Quicksort Table (information) Geometry
Axiom of choice Trail Implementation Software developer Database Database transaction Insertion loss Subject indexing Type theory Computer animation Speech synthesis Library (computing) Geometry
Point (geometry) Implementation Information File format Euler angles Electronic mailing list Data storage device Sampling (statistics) Planning Database 3 (number) Insertion loss Client (computing) Binary file Type theory Population density Computer animation Auditory masking Data conversion Data type Form (programming)
Presentation of a group Computer animation Calculation Operator (mathematics) Forcing (mathematics) Projective plane Lipschitz-Stetigkeit Rectangle Distance Number Geometry
Addition Computer animation Open source State of matter Binary code Resultant Form (programming) Formal language
Point (geometry) Functional (mathematics) Implementation Server (computing) Correspondence (mathematics) Multiplication sign Set (mathematics) Client (computing) Distance Rule of inference Field (computer science) Subset Revision control Semiconductor memory Cuboid Electronic visual display Extension (kinesiology) Physical system Standard deviation Distribution (mathematics) Structural load Data storage device Bit Database Line (geometry) Subject indexing Computer animation Data type Reading (process)
Point (geometry) Slide rule Implementation Functional (mathematics) Building Greatest element Computer file Transformation (genetics) Plotter Multiplication sign Student's t-test Regular graph Mereology Number Web 2.0 Facebook Entropie <Informationstheorie> Authorization Software testing Social class Form (programming) Scripting language Projective plane Polygon Data storage device Sampling (statistics) Bit Total S.A. Line (geometry) Subject indexing Type theory Computer animation Personal digital assistant Procedural programming Table (information) Data type Geometry
Point (geometry) Implementation Computer file 1 (number) Regular graph Order of magnitude Computer programming 2 (number) Number Different (Kate Ryan album) Cuboid Software testing Selectivity (electronic) Information security Form (programming) File format Data storage device Voltmeter Planning Database transaction Rectangle Subject indexing Computer animation Query language Writing Geometry
Overhead (computing) Computer file Client (computing) Menu (computing) Order of magnitude Computer programming Subset Mechanism design Computer configuration Hypermedia Lie group Extension (kinesiology) Metropolitan area network Plug-in (computing) Installation art Standard deviation Focus (optics) Validity (statistics) Binary code Database Subject indexing Message passing Process (computing) Computer animation Table (information) Library (computing)
Presentation of a group Statistics Mapping File format Multiplication sign Workstation <Musikinstrument> Archaeological field survey Control flow Database Disk read-and-write head Graph coloring Word Computer animation Meeting/Interview Personal digital assistant Computer configuration Reading (process) Form (programming)
Computer animation
OK so the next talk of of AP mood uh you want presented as the new guess features and Maria Debian bias the appositives talk last year also was a great talker fold I'm really uh looking forward to the stalker again at it's already about you give
speeches but about all these
features there's not been that may change fast you so it's general the coverage
so all of those speak introduction
slide didn't into these that so quickly and show my name is sort of the landform Bielefeld here in Germany and have been working for several years in my school support apartment and now switched over to the review the support apartment an the of going to use the form the slides for what's written and that's what we have is printer you for example for why yesterday's talk but today it is all about what's treatment but about more general gives things so
for this 1st introduction hall or you'll features and databases work he's in geospatial processing we usually have all the a few different types of usually two-dimensional data types simplest 1 is obviously the point is that we have find sparks regional borders we have polygons that describe or closed areas and we have and databases especially general geometry type that can contain all of these and in addition to the simple types we have also types that can contain multiple objects so for points we can have collection type of multipoint that can contain 1 or more points same 4 lines that's multiline strings is not a polygon that is a bit special because not only can have multiple polygons inside you can also have a polygon we them explore Valene's solid so like a ring that has altered border and in the border and then there's also the general geometry collection that can contain an arbitrary set of all of the previous types
so this looks like this so we have on the left side simple things points lines strings polygons all right so I have collections multipoint that's what aligned strings with a polygon usable put it on the right side of the left side of the polygon with the interior so the actual Coregonus only the ring you the on so we can process all the simple times book we also have some additional properties of these from the trees or obviously you the coordinates where the future is 4 lines we go another term in the length of the line form area we before pulling on we can calculate the area covered by the polygon a general for lines check whether if it is close to a lot on the go and calculate the bounding rectangle so the rectangular to look just large enough to fully fit all the data and and then the next thing you might be interested in is not only a single geometry or geometry collection but also spatial relationships between 2 or more geometries so for these we have simplified follow this subset of relation like 1 tributary is contained in another 1 fully it may share the border but it is fully inside all the major totally disjoint not sharing and that may be equal what's possible the they they may intersect so only a part of what is inside the other always they may overlap so the for Over the years the more case than intersect as overlap allow also want is fully so the intersection always just partially overlap and again have touches and that only share borders or corner points the OntoCAT have within that is just the opposite of complaints so we take about this 1st geometry the blue 1 2nd but and here the 2nd is contained by the first one here the first one is inside the 2nd 1 if you swap but then it would be opposite the other 1 which contains b and so on so let's look all these use actually implemented in SQL the firstly need
to create tables that have this kind of talk a
long for this you see the 1st great statement here that is sort was originally implemented in Moscow so you just have geometry as a native type and like any other column we can just say we create a column named shape the supply geometry and they can add a spatial index that parents so official open the standard simplex is a bit different as in most databases these types of negative but are the edit body extensions that come to always change deposit so this 1 work as the father does know what geometry is so that the use of operands create the table 1st of all the regular columns in the and then there's a special sport procedure that geometry column but you can use to then say OK if a table name to test pie database then test and in their table named T 2 and to this I want to add geometry because column shape In the 2nd step have been really be any we support both of Blix's starting with UDB 10 1 so we can use the simple native phase that only we support of only musket and REDD support or you could do it in a more general way that also would work for example for post all the spatial extension to a like then to insert spatial data the simplest way is to use the Van all text format and then convert that to a geometry using this conversion function so we have examples here 4 point 4 point you just give it's true coordinates for aligned string you put in the list of all the points that make up the line and for polygon you also have the list of points that make up the polygon and the last point has to be the same as the 1st 1 so that you have a closed mind the if you want to clear a special features can again
use for example if you want to query of points that under certain rectangular area just creates a polygon with this lecture rectangle then you can query In the example table above all the spectators of you the number of points that are within this rectangle so we have that spatial relationships for function In this talk on the previous slide that checks whether 1 thing is contained in another the and then we just count what comes out you can also do this in those morecomplex we're using more sophisticated spatial relationships what you have 2 tables while I'm having points of interest in Germany and 1 having areas in Germany now I want to have all the points of interest the couples boxes and so if I have to order polygonal Bielefeld as supporter shape you want to have all of the postbox points that are contained in the area of the the so we can make as much for complicated if you want but the use of the basic operations usually need then you go from there so quick overview hall or spatial features in my skill that in really be emerged started it all
started in 2004 reduces masker followed 1 but at the 1st implementation of the pure features but for spatial relationships it did not so support true relationships it only always operated from below bounding rectangles geometry I have a picture for the teachers to show what good means and they had support for spatial indexes but only in the myosin Storage Engines so that uh a choose whether you want to have a spatial index to speed things up or the you wanted to have full transactional well as it features that we would have to use in the DB tables would lose track will feature to have indexes this has changed recently we get to the later don't we have these features to although for but it has not seen much adoption is the feature that was pretty small especially due to the fact that the boss based on bounding rectangles and I think it all over the years i've been working for my school support we have for customer issues virtually about his features nobody else was using it so if this is the effect
of only checking relationships for bonding rectangle so this is the result of the previous query for post boxes in Bielefeld when you go by bounding rectangles you also get some resolves the outside of the city borders and when you to spatial relationships then you really only get all the features that are really contained within the borders so this 1 performs much better this uses to resolve the unitary so and for quite a
while nothing at all happened and then In 2011 Maria did the 5 . 3 came out and so that was the 1st release the picture lifted these bounding rectangle limitations and really operated untrue spatial relationships so that was the 1st version that
really could do this set of just taking it like this and 2 years later
in my school 5 to 6 Oracle were called up on than what 0 also supported true spatial relationships on still on both sides who are not really seeing much options maybe because it had been sort of fuses for so long so them in the 2 years ago in 2015 was really be kind 1 the you put some more effort in and almost few very small details that are missing but we could for all practical concerns got fully OpenGIS standard-compliant so we for example added to some special information scheme on our tables that required of standard so that you have information scheme of tables that give information of all those tables and columns that use gives features in your database we added gold standard-compliant clicks to add the geometry columns so we've seen on the previous slide the so not all standard-compliant use a skill that work to the and we added principled support for spatial relationship but these the so the only store and compare the specialists such about these values we don't really based calculations so the world still flat no matter what spatial relationship idea what projection use but at least that complaints if you want to in surge the Mercado data in a table that is meant for latitude longitude protection and funds were and unbound
Mars before after observing also a few more hours to speeches but also they changed from using the all homegrown implementation of spatial relationship tracks and geometry type implementation and switch to using the booze geometry library and we also added support for spatial indexes into the so you didn't have to make the choice anymore but we wanted to have faster Gisela queries based on spatial indexes or transactions a database that we could have problems so the 2nd 1 is pretty big thing the first one is something that only you developers have to care about it has changed little behavior all the features set on the secure use of now
this year with Mary to better not to be switched from using the park on extending the implementation of the new DB back to using the Oracle my of the the engine for Moscow 5 . 7 put our approaches on top and that also gave us the loss of spatial index feature of was the 1st 7 so we now also have this song where you also don't have to decide anymore with the you want have 1 or the other on some of
the features we have been planning to do the have not really delivered anything sometimes it's not even really started on it to confess his youth thinking about adding support for the 3rd column of lot of the full 3 D feature set out to at least be able to store for DSC sample attitudes information in density yeah our we have plans to switch to optimize which from using floating point the floating point so all data types in the underlying implementation to actually also provide the high precision the mask features that we also have 4 decimal and not numeric types so if you really really really really cautious about not having on a catalyst then you would could use that features 1 that feature once it actually is implemented and we have been thinking of all putting support for the the conversion all-phone known in text form at loss of unknown binary format into the actual trouble native format used by Maria DB to support that was already on the client side so that he can do the passing on the client side already and can to foster imports into the database or that matters home also shown in what I give this talk last year and that was the featureless Just purity and meanwhile the out of 10 to major release nothing he's from these from this feature list made it in there to use also intended 3 that is coming soon that also has a different fuel that will not have and the relevant just improvement has and possibly call them 1 my skills like I don't really
know because Oracle is not that open a Baltimore but often that you the only thing I know is that they have been working on adding true spatial relationships and projection support but we gave a short presentation of all that on force them fossils earlier this year the and that means that finally the world In my skull will not be flat anymore but they have geometries that really know that you're diversity is the of the of perfect all over actually lips so so that can have true distance calculations would just giving 2 coordinates and it doesn't mean that it does take in account the latitude and longitude does not just going to pretend that the broadest is stretched out on a rectangle so just 1 small feature that have revealed the smaller number but it's a pretty big things if you really need this kind of operations and thank
and search-and-rescue money we that we have 1 more thing in tended to not only that the hospital and sustain DB we also know how could you adjacent supported from what in addition to learn known text of the non binary so can now 0 paras do you Jason and put that low special column right away you can also get results futurist form of that is usually much better supported by the of the languages and tools that the spillover non-text quality states so muscular you you obviously almost the only open source databases that support as this
also obviously posters through the PostGIS extension each of these worried about all the most powerful solution on this compared to or implementation he's adding much more of functions features than just
required by the open just under so we only have the standards of a functions supposed to say there's a lot of extra stuff we don't support that on the funny thing about it is is not covered by the same license as PostgreSQL self but post this is under the GPL artists as masker memory it'd be out to so the usual license should also between reading be Vice guy Postman's is not happening in this field if you want to go for s no matter if it's last to be opposed to this you always have display what the rules of the GPL of the installation of the PostGIS extension used to be a bit tricky you have to you 1st install the actual extent among when you have 2 important special explicitly Alfonso at at all move required functions and data types that has changed in recent or post was versions don't expect them exactly but somewhere in the line 9 point something reasons our knowledge as they create extension post is amenable the background knows what to do to solve everything is needed so it used to be complicated tho it's simply just 1 you need to execute but you need executed for every single database in the post server that is supposed to have supported datatypes whereas all this data out of the box and I also posted obviously has a much larger user base other thing that's rightfully so forward it's much larger feature set and then there's also spatial extension to rescue called spatial light that also owns will only provide a subset of the induced feature set it has support for spatial indexes but you can't simply use a spatial index whereas but only you wire native Client I take the but it's not very often seen in the wild what it is for example a useful way use for internal storage which is on the the correspondence DB features always there you don't have to spit that explicitly load extension and well you can only do they will support for 2 years as that come from the time she really want to have binary as small as possible but so would only be interested for over a distance for work at very small embedded systems so for all the distribution package all the vendor packages the binary all acute to support of the box so
get that has obviously the advantage you don't need to have an extra complements you don't have to install anything else and starting from was the 1st of 6 scenario to be fast 3 both mostly opened as a competitive back from Brooklyn compatible and starting this where you tend not 1 we're for all practical purposes of fully compliant we use the 2 types of 1st of citizens just same as the stony bits of the script seen only create stable slide earlier they all support for using the OpenGeo as common ways to columns with special procedure later but there's also a native way to just use them as a 1st class citizen types because the use the the plot much functionality beyond what opened years requires still strictly two-dimensional on really beside no of support for projections and transformations everything is considered flat world oracle the change that in my relates only whenever that comes out the data types are available in all storage and tools for the storage engine little geometry columns just plop just storage retrieve it but it doesn't guarantee that these may which in their text or the state of but when it comes to Unix's indexes need to have an understanding of the data contained so indexed implementations or it shouldn't only my eyes on Knowl in my eyes on and that you B but not in all the others specialized storage and use of the word but in the top ordered these storage and engine not in the rocks storage engine that Facebook's is working on the thing that's not much of problem but also to confess that optimizes the true clever of all the use of spatial indexes in most cases can only other use spatial index or regular index on table are not post have some numbers and let In the 2nd so that brings us to the next topic the performance on and for this
on our turf some you wrote test sample I have patched will always entropy pgs import tool that is used to import OpenStreetMap data into PostGIS I extend the right so that they can also old populates minus feral or spatial tables on I didn't import all those and extract for all of Germany not the whole world because of take so long to get along with Germany is 1 of discovered countries novels and so there's a lot of data on the web so these are the numbers for last year at that time they had the 8 milieu and points of interest 11 million long lines 28 million polygons from the bottom part of Germany down to a single buildings and the total of data size of roughly 70 in the gigabytes when imported on the actually Beck last year we gave the talk for the 1st time I have good doll just as the author in the form of my students aren't my eyes on a new DB years table files on this works over here I have to confess I haven't checked whether the songs to but their and functional I will add a note to the slots before Apple and with updated you for the so what's the performance
regarding for the most simple query has be out in the example slides earlier we just want to have all the points in a certain rectangle and these are roughly you go coordinates forward Bielefeld again in the all and it doesn't matter how many of the it's only a difference of about 1 % the no these unaltered regular latitude longitude coordinates and what is the 0 and 2 pgs cruel tool uses the Google Mercato or form of that so it's usually used for all these them government API debased or use the same format as Google Maps this so so crew that just checks for points in a rectangle using a spatial index 100 conceived bit lower than those used by it's in the same order of magnitude and specialized has no number here because without spatial index it took forever and with spatial index the spatial light does not support a scale so that's so I wouldn't bother to write test program for that and we're back to your question so it is faster when not having a spatial index on my eyes and only be using my eyes on it doesn't only have to do a little sequentially a file but when using special indexes is really reading it's about the same speed as minus some of this in the DP and so for the more complex example where we have the following both boxes view the folks here again so not using rectangle about another geometry then we have with just the beginning the spatial index all and Georgian geometry columns it takes volts 60 seconds those muted in Moscow about 40 seconds and posters so that's about almost equal can specialize on the slightest cut off there's a question mark here because this book that instead of men having uh and the an extra index on the city name that means both contrasted with they the bigger difference between the interview must you need about 2nd and suppose is the only needs support two-tenths of a 2nd so that it shows that the post to streets would post that optimizes the better about doing so query plans but it still roughly an order of magnitude so as not too long go difference yeah and security
asked you to be in Moscow of I don't have a big difference to it's really minimal the implementations are so similar that it doesn't really matter and inserting data so doing became ports use obviously faster on Mars on because it does not have to take any curable both from the transactional also fity it just has 1 big data file of just from its to the and of unity B has to some more work and so this store and the the book select performance relies on community be using index he's so similar love and there is no significant difference at all so there's no reason to use
lies on you only use it for the reason of being able to having a spatial index the think also them inserting large amounts of data lies and spatial light perform roughly the same as boasts all really just using flat tables depends to the end and you and posters post was also perform roughly the same as they both have the same kind of transactional were but with PostgreSQL PostGIS you have the the advantage that you can use the copy mechanisms that allows you to feed in another data directly without having them on a scale in support and we don't really have the same in musket man you to be sorted so that is also low data on Moscow Maria DB but that does not have will read from standard in feature so you would have to write to a file 1st and it also has more passing overhead on processing Vernon binary over non text formats that's what I talked about the library extension of the client so it earlier if you could already convert data into the internal form of on the client side you could do much faster imports brief seems simple selects similarly good all greedy Debian posters both stresses slightly faster but not really significantly on the phone unfortunately it changes with previous get more complex and the to combine more indexes under did really compared index performs a spatial light as requires menu programming for decree the instead of using SQL
and so in summary have all the OpenGIS required features now performance could be variabilities think acceptable on so if you have a long Moskowa Marie UTP installations already and only 1 2 at quote to specific features this is not a valid option but if she is this your primary focus then maybe you still better off with using PostGIS with its switcher features but if you for example have all of large media that your purpose installation these are supposed to revert on both mosquito and post press but most glorious stone so if you bump who was very BBS use certain plug-ins you have the option not to use these features in the same database that's a valid option yeah and not
use actually all I wanted to talk about and now we have time for questions the
the pack of them before forget it so that the what is only is that's what is on the USB sticks to give me just the just picture also the but in a survey of Maria to use the 6 here and we don't have any idea what's on that that their free to take now aren't we also have toys that I don't know how to operate out of here we can talk about that in the break and all of the and it would some of those of the presentation of origin so OK thank head of statistic and other person and 1 hint about the spatial lives because um uh and the the new uh words here will form of the package uh the upon you think like the again so maybe you don't read uh station lights off and a few read to you technicians moral you it's usually use under the hood and rock or as an exchange format for years I think it's also quite a good thing to change the date of 3 new databases of the more where the devices and they have the form maybe even 2 years working you data from your past with red color totally forgot about mobile devices and its specialized assume that this case of the present can then you think the of a year but this is still 1 of you wants to have a printed map of their hometown of some of the region of the world we have the option of you the lunch break but you will that you