Inserting Basic Data into a Table
Now that we know about basic text representations of points, lines, and polygons, and how to create tables, we can use a function to start inserting data into the spatial columns of our new tables.
To insert data, we need three things:
1. A table with a spatial column
We have the
shape column in our tables, from
Creating a Table (see page
Creating a Table).
2. A spatial feature text representation
3. A function to convert the text into its binary representation of the expected feature type. In this case, we wrap our WKT with the POINTFROMTEXT() function.
The following example inserts into the location_points table the point -121 52. The POINTFROMTEXT function converts the WKT representation of the point into its binary representation.
INSERT INTO location_points VALUES (1, POINTFROMTEXT('POINT(-121 52)'));
We can do the same with our LINESTRING and POLYGON examples. Note the change in function name, and that POLYGONs use a double set of braces in their text representation:
INSERT INTO location_lines VALUES (1, LINEFROMTEXT('LINESTRING(-121 52, -0.59 51.51)'));
INSERT INTO location_polygons VALUES (1, POLYFROMTEXT('POLYGON((1 1, 1 10, 10 10, 10 1, 1 1))'));
Now you have three tables, each with one feature in them.
To see that your data is indeed in those tables, try:
SELECT shape FROM location_lines;
You will see the binary representation of the geometry, like this:
+--------------------------------+
|shape |
+--------------------------------+
|\001\000 1\000\000\000)\000\000\000\001\003\000\000\000\001\000\000\000\005\000\000\000\000\000\000\000\00@^└βz\024«G┴I@βz\024«GβΓıβz\024«G┴I@βz\024«GβΓı\000\000\000\000\000\00J@\000\000\000\000\000@^└\000\000\000\000\000\00J@\000\000\000\000\000^└βz\024«G┴I@\001\002\000\000\000\002\000\000\000\000\000\000\000\00@^└\000\000\000\000\000\000J@βz\024«G┴I@\024«G┴I@\000\000|
+--------------------------------+
An application that understood Well Known Binary (WKB) format would be able to use the data directly. But we must wrap the shape field with the ASTEXT() function to see the geometry in WKT format:
SELECT ASTEXT(shape) FROM location_lines;
Returns:
+-------------------------------+
|col2 |
+-------------------------------+
|LINESTRING (-121 52, -0.59 51.5099999999999998|
--------------------------------+
Most of the spatial functions (other than the ...FROMTEXT() ones used in this tutorial) expect the data to be in the internal binary format. So you will likely use the ...FROMTEXT() and ASTEXT() functions regularly for investigating your data and results.