Introducing Spatial Types
Dolt is a SQL database with Git-style versioning. With every release of Dolt, we approach our goal of being a complete drop-in replacement for MySQL. In our latest release, we added a beta implementation of spatial types, and in this blog post I'll go over what they are and how to use them.
What are Spatial Types?
Spatial Types are a kind of data type that is used to hold geometry data. MySQL supports a subset of the OpenGIS Geometry Model, specifically the types: Geometry, Point, Linestring, Polygon, MultiPoint, MultiLinestring, MultiPolygon, and GeometryCollection.
As of this release of Dolt, we support the Geometry types Point, Linestring, and Polygon, along with a couple of the functions that use these types.
Storing U.S. Capitals
Let’s say we want to store the coordinates of the U.S. Capitals in a table. We can define the table like this:
db> create table us_state_capitals (state varchar(50) primary key, city varchar(50), coord point);
db> describe us_state_capitals;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| state | varchar(50) | NO | PRI | | |
| city | varchar(50) | YES | | | |
| coord | point | YES | | | |
+-------+-------------+------+-----+---------+-------+We can create a point to represent the coordinates using the point constructor method:
$ dolt sql -q "insert into us_state_capitals values ('alabama', 'montgomery', point(32.361667, -86.279167));"Additionally, we support the Well-Known Text (WKT) format for Geometries, so we can also create points using the st_geomfromtext function:
$ dolt sql -q "insert into us_state_capitals values ('alaska', 'juneau', st_geomfromtext('POINT(58.3 -134.416)'));"Connect-the-Dots
Suppose we are writing a Connect-the-dots book, and want to store the drawings somewhere.
We can define a table using the new polygon type for the columns.
$ dolt sql -q "create table_connect_the_dots (id int primary key, p polygon);"Then insert them all into our table
$ dolt sql -q "insert into connect_the_dots values (0, st_geomfromtext('POLYGON((1 1,1 4,3 4,3 5,4 5,4 1,1 1),(2 2,2 3,3 3,3 2,2 2))');"
$ dolt sql -q "insert into connect_the_dots values (1, st_geomfromtext('POLYGON((5 1,5 5,8 5,8 1,5 1),(6 2,6 4,7 4,7 2,6 2))');"
$ dolt sql -q "insert into connect_the_dots values (2, st_geomfromtext('POLYGON((9 1,9 2,10 2,10 4, 9 4,9 5,11 5,11 2,12 2,12 1,9 1))');"
$ dolt sql -q "insert into connect_the_dots values (3, st_geomfromtext('POLYGON((14 1,14 3,13 3,13 4,14 4,14 5,15 5,15 4,16 4,16 3,15 3,15 2,16 2,16 1,14 1))');"Plotting and filling in these polygons results in this image:
Challenge
Lastly, we also support conversions to and from Well-Known Binary (WKB) format through the methods st_aswkb and st_geomfromwkb.
If you have some extra time on your hands and are a fan of puzzles, here you go:
$ dolt sql -q "select hex(st_aswkb(p)) from bitly;"
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hex(st_aswkb(p)) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0103000000010000000D000000000000000000F03F000000000000F03F000000000000F03F00000000000000400000000000000840000000000000004000000000000008400000000000000840000000000000F03F0000000000000840000000000000F03F00000000000010400000000000000840000000000000104000000000000008400000000000001440000000000000F03F0000000000001440000000000000F03F0000000000001840000000000000104000000000000018400000000000001040000000000000F03F000000000000F03F000000000000F03F |
| 0103000000010000000D0000000000000000001440000000000000F03F000000000000144000000000000000400000000000001C4000000000000000400000000000001C40000000000000084000000000000014400000000000000840000000000000144000000000000010400000000000001C4000000000000010400000000000001C4000000000000014400000000000001440000000000000144000000000000014400000000000001840000000000000204000000000000018400000000000002040000000000000F03F0000000000001440000000000000F03F |
| 0103000000020000000B0000000000000000002240000000000000F03F00000000000022400000000000001040000000000000264000000000000010400000000000002640000000000000144000000000000022400000000000001440000000000000224000000000000018400000000000002640000000000000184000000000000026400000000000001440000000000000284000000000000014400000000000002840000000000000F03F0000000000002240000000000000F03F050000000000000000002440000000000000004000000000000024400000000000000840000000000000264000000000000008400000000000002640000000000000004000000000000024400000000000000040 |
| 010300000002000000070000000000000000002A40000000000000F03F0000000000002A400000000000001040000000000000304000000000000010400000000000003040000000000000F0BF0000000000002E40000000000000F0BF0000000000002E40000000000000F03F0000000000002A40000000000000F03F050000000000000000002C4000000000000000400000000000002C4000000000000008400000000000002E4000000000000008400000000000002E4000000000000000400000000000002C400000000000000040 |
| 0103000000010000000B0000000000000000003140000000000000F03F000000000000314000000000000018400000000000003240000000000000184000000000000032400000000000001040000000000000344000000000000010400000000000003440000000000000F03F0000000000003340000000000000F03F00000000000033400000000000000840000000000000324000000000000008400000000000003240000000000000F03F0000000000003140000000000000F03F |
| 010300000002000000070000000000000000003540000000000000084000000000000035400000000000001840000000000000384000000000000018400000000000003840000000000000F03F0000000000003740000000000000F03F0000000000003740000000000000084000000000000035400000000000000840050000000000000000003640000000000000104000000000000036400000000000001440000000000000374000000000000014400000000000003740000000000000104000000000000036400000000000001040 |
| 010300000001000000090000000000000000003940000000000000F03F000000000000394000000000000018400000000000003C4000000000000018400000000000003C4000000000000014400000000000003A4000000000000014400000000000003A4000000000000000400000000000003C4000000000000000400000000000003C40000000000000F03F0000000000003940000000000000F03F |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+Conclusion
Spatial types are a data type with a variety of uses that is now partially supported in Dolt. If you need more spatial types features, just ask! The only thing gating more spatial types in Dolt is customer interest. Download and set up Dolt to play around with Spatial Types and complete the challenge. Share your answers on our Discord.