Loading Geospatial Data into SingleStoreDB
SingleStoreDBcan load geographic data (points, paths, and polygons) that are represented in
Well-Known-Text (WKT) format.
LOAD DATA statement to load the downloaded map data into the table.
Let us look at an example of how to load geographic data of different countries into SingleStoreDB.
Navigate to the Natural Earth website and download the dataset for country boundaries.
The files are downloaded in the shapefile (SHP) format, which is not supported by SingleStore. Therefore, you need to convert the files to the WKT format, as shown in the steps that follow.
Navigate to the MyGeodata Converter tool and convert the SHP file to a WKT file.
It generates a CSV file in the WKT format. The CSV file contains all the polygons, along with other data about each country. Note: SingleStoreDB does not support
Therefore, if the CSV file contains
MULTIPOLYGON, then convert it into multiple single
POLYGONs, as shown in the steps that follow.
You can visualize a
POLYGONs by using the Wicket library website.
After navigating to the website, copy the
MULTIPOLYGONset from the CSV file and paste it to the Wicket box.
Click Map It: In this example, the map displays the country
Fijiwith three polygons.
Navigate to the CSV file and separate the row containing
MULTIPOLYGONinto three individual rows, with each row containing a
Copy the other column data in the original
MULTIPOLYGONrow to each of the three
All the data is now available in the CSV file in the WKT format to load into SingleStore.
Create a table
Countries:CREATE TABLE Countries (boundary GEOGRAPHY, name_short VARCHAR(3), name VARCHAR(50),name_long VARCHAR (50), abbrev VARCHAR (10), postal VARCHAR (4),iso_a2 VARCHAR (2), iso_a3 VARCHAR(3), name_formal VARCHAR(100),SHARD KEY(name));
In this table, all polygons will be loaded into the
boundarycolumn, which is of the data type
Load the data from the CSV file to the
Countriestable:LOAD DATA INFILE '/data/natural_earth_countries_110m-1.csv'INTO TABLE Countries (boundary, @, @, @, @, @, @, @, @, @, @, name_short, @, name, name_long, abbrev, postal, name_formal, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, iso_a2, iso_a3, @, @, @, @, @, @, @ @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @, @,)FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '\n'IGNORE 1 LINES;
In the LOAD DATA syntax, the
@symbol is used to ignore unwanted columns.
FIELDS TERMINED BY ','and
OPTIONALLY ENCLOSED BY '*'clauses define the column delimiters and ensure that the commas in the polygon data are not treated as a separate field.
To see the results of loading the data, select the data from the
Countriestable:SELECT * FROM Countries ORDER BY name DESC;
You can now use this data for all Geospatial Functions.
Last modified: April 3, 2023