Load spatial data from a Well Known Text (WKT) file

This section provides you with an overview of loading spatial data from a WKT file.

 To load spatial data from a WKT file

  1. First you create a file that contains spatial data in WKT format that you will later load into the database as follows:

    1. Open a text editor such as Notepad.

    2. The following snippet contains a group of geometries, defined in WKT. Copy the contents of the snipped to your clipboard and paste it into your text editor: 



      head,"CircularString(1.1 1.9, 1.1 2.5, 1.1 1.9)"
left iris,"Point(0.96 2.32)"
right iris,"Point(1.24 2.32)"
left eye,"MultiCurve(CircularString(0.9 2.32, 0.95 2.3, 1.0 2.32),CircularString(0.9 2.32, 0.95 2.34, 1.0 2.32))"
right eye,"MultiCurve(CircularString(1.2 2.32, 1.25 2.3, 1.3 2.32),CircularString(1.2 2.32, 1.25 2.34, 1.3 2.32))"
nose,"CircularString(1.1 2.16, 1.1 2.24, 1.1 2.16)"
mouth,"CircularString(0.9 2.10, 1.1 2.00, 1.3 2.10)"
hair,"MultiCurve(CircularString(1.1 2.5, 1.0 2.48, 0.8 2.4),CircularString(1.1 2.5, 1.0 2.52, 0.7 2.5),CircularString(1.1 2.5, 1.0 2.56, 0.9 2.6),CircularString(1.1 2.5, 1.05 2.57, 1.0 2.6))"
neck,"LineString(1.1 1.9, 1.1 1.8)"
clothes and box,"MultiSurface(((1.6 1.9, 1.9 1.9, 1.9 2.2, 1.6 2.2, 1.6 1.9)),((1.1 1.8, 0.7 1.2, 1.5 1.2, 1.1 1.8)))"
L,"MultiCurve(CircularString(1.05 1.56, 1.03 1.53, 1.05 1.50),CircularString(1.05 1.50, 1.10 1.48, 1.15 1.52),CircularString(1.15 1.52, 1.14 1.54, 1.12 1.53),CircularString(1.12 1.53, 1.06 1.42, 0.95 1.28),CircularString(0.95 1.28, 0.92 1.31, 0.95 1.34),CircularString(0.95 1.34, 1.06 1.28, 1.17 1.32))"
holes in box,"MultiPoint((1.65 1.95),(1.75 1.95),(1.85 1.95),(1.65 2.05),(1.75 2.05),(1.85 2.05),(1.65 2.15),(1.75 2.15),(1.85 2.15))"
arms and legs,"MultiLineString((0.9 1.2, 0.9 0.8),(1.3 1.2, 1.3 0.8),(0.97 1.6, 1.6 1.9),(1.23 1.6, 1.7 1.9))"
left cart wheel,"CircularString(2.05 0.8, 2.05 0.9, 2.05 0.8)"
right cart wheel,"CircularString(2.95 0.8, 2.95 0.9, 2.95 0.8)"
cart body,"Polygon((1.9 0.9, 1.9 1.0, 3.1 1.0, 3.1 0.9, 1.9 0.9))"
angular shapes on cart,"MultiPolygon(((2.18 1.0, 2.1 1.2, 2.3 1.4, 2.5 1.2, 2.35 1.0, 2.18 1.0)),((2.3 1.4, 2.57 1.6, 2.7 1.3, 2.3 1.4)))"
round shape on cart,"CurvePolygon(CompoundCurve(CircularString(2.6 1.0, 2.7 1.3, 2.8 1.0),(2.8 1.0, 2.6 1.0)))"
cart handle,"GeometryCollection(MultiCurve((2.0 1.0, 2.1 1.0),CircularString(2.0 1.0, 1.98 1.1, 1.9 1.2),CircularString(2.1 1.0, 2.08 1.1, 2.0 1.2),(1.9 1.2, 1.85 1.3),(2.0 1.2, 1.9 1.35),(1.85 1.3, 1.9 1.35)),CircularString(1.85 1.3, 1.835 1.29, 1.825 1.315),CircularString(1.9 1.35, 1.895 1.38, 1.88 1.365),LineString(1.825 1.315, 1.88 1.365))"

    3. Save the file as wktgeometries.csv.

  2. In Interactive SQL, connect to the sample database (demo.db) as user DBA, or as a member of the SYS_SPATIAL_ADMIN_ROLE group.

  3. Create a table called SA_WKT and load the data from wktgeometries.csv into it as follows. Be sure to replace the path to the .csv file with the path where you saved the file: 

    DROP TABLE IF EXISTS SA_WKT;
CREATE TABLE SA_WKT (
    description CHAR(24),
    sample_geometry ST_Geometry(SRID=1000004326)
);

LOAD TABLE SA_WKT FROM 'C:\\Documents and Settings\\All Users\\Documents\\SQL Anywhere 12\\Samples\\wktgeometries.csv' DELIMITED BY ',';

    The data is loaded into the table.

  4. In Interactive SQL, select Tools » Spatial Viewer.

  5. In the Spatial Viewer, execute the following command to see the geometries: 

    SELECT * FROM SA_WKT;
    Polygons, lines and points that together look like a person holding a square next to a wagon full of geometry shapes.

  6. Your data may have several columns of spatial data. In this next example, you create a file of WKT data containing one of each supported spatial data type, stored in individual columns.

    Copy the following code snippet to your text editor and save the file as wktgeometries2.csv: 



    "Point(0 0)",,,,,,,,,,,,,,
,"LineString(0 0, 1 1)",,,,,,,,,,,,,
,,"CircularString(0 0, 1 1, 0 0)",,,,,,,,,,,,
,,,"CompoundCurve(CircularString(0 0, 1 1, 1 0),(1 0, 0 1))",,,,,,,,,,,
,,,,"CompoundCurve(CircularString(0 0, 1 1, 1 0),(1 0, 0 1),(0 1, 0 0))",,,,,,,,,,
,,,,,"Polygon((-1 0, 1 0, 2 1, 0 3, -2 1, -1 0))",,,,,,,,,
,,,,,,"CurvePolygon(CompoundCurve(CircularString(0 0, 1 1, 1 0),(1 0, 0 0)))",,,,,,,,
,,,,,,,"CurvePolygon(CompoundCurve(CircularString(0 0, 2 1, 2 0),(2 0, 0 0)))",,,,,,,
,,,,,,,,"MultiPoint((2 0),(0 0),(3 0),(1 0))",,,,,,
,,,,,,,,,"MultiPolygon(((4 0, 4 1, 5 1, 5 0, 4 0)),((-1 0, 1 0, 2 1, 0 3, -2 1, -1 0)))",,,,,
,,,,,,,,,,"MultiSurface(((4 0, 4 1, 5 1, 5 0, 4 0)),CurvePolygon(CompoundCurve(CircularString(0 0, 2 1, 2 0),(2 0, 0 0))))",,,,
,,,,,,,,,,,"MultiLineString((2 0, 0 0),(3 0, 1 0),(-2 1, 0 4))",,,
,,,,,,,,,,,,"MultiCurve((3 2, 4 3),CircularString(0 0, 1 1, 0 0))",,
,,,,,,,,,,,,,"GeometryCollection(MultiPoint((2 0),(0 0),(3 0),(1 0)),MultiSurface(((4 0, 4 1, 5 1, 5 0, 4 0)),CurvePolygon(CompoundCurve(CircularString(0 0, 2 1, 2 0),(2 0, 0 0)))),MultiCurve((3 2, 4 3),CircularString(0 0, 1 1, 0 0)))",
,,,,,,,,,,,,,,"GeometryCollection(Point(0 0),CompoundCurve(CircularString(0 0, 1 1, 1 0),(1 0, 0 1),(0 1, 0 0)),CurvePolygon(CompoundCurve(CircularString(0 0, 2 1, 2 0),(2 0, 0 0))),MultiPoint((2 0),(0 0),(3 0),(1 0)),MultiSurface(((4 0, 4 1, 5 1, 5 0, 4 0)),CurvePolygon(CompoundCurve(CircularString(0 0, 2 1, 2 0),(2 0, 0 0)))),MultiCurve((3 2, 4 3),CircularString(0 0, 1 1, 0 0)))"

  7. Create a table called SA_WKT2 and load the data from wktgeometries2.csv into it as follows. Be sure to replace the path to the csv file with the path where you saved the file: 



    DROP TABLE IF EXISTS SA_WKT2;
CREATE TABLE SA_WKT2 (
    point          ST_Point,
    line           ST_LineString,
    circle         ST_CircularString,
    compoundcurve  ST_CompoundCurve,
    curve          ST_Curve,
    polygon1       ST_Polygon,
    curvepolygon   ST_CurvePolygon,
    surface        ST_Surface,
    multipoint     ST_MultiPoint,
    multipolygon   ST_MultiPolygon,
    multisurface   ST_MultiSurface,
    multiline      ST_MultiLineString,
    multicurve     ST_MultiCurve,
    geomcollection ST_GeomCollection,
    geometry       ST_Geometry
);

LOAD TABLE SA_WKT2 FROM 'C:\\Documents and Settings\\All Users\\Documents\\SQL Anywhere 12\\Samples\\wktgeometries2.csv' DELIMITED BY ',';

    The data is loaded into the table.

  8. In the Spatial Viewer, execute the following command to see the geometries.

    Note that you can only see one column of data at a time; you must use the Column dropdown in the Results area to view the geometries for the other columns. For example, this is the view of the geometry in the curvepolygon column:

    Image of a polygon. The top of the polygon is circular, while the bottom is flat.

  9. To view the geometries from all of the columns at once, you can execute a SELECT statement for each column and UNION ALL the results, as follows: 



    SELECT point FROM SA_WKT2
UNION ALL SELECT line FROM SA_WKT2
UNION ALL SELECT circle FROM SA_WKT2
UNION ALL SELECT compoundcurve FROM SA_WKT2
UNION ALL SELECT curve FROM SA_WKT2
UNION ALL SELECT polygon1 FROM SA_WKT2
UNION ALL SELECT curvepolygon FROM SA_WKT2
UNION ALL SELECT surface FROM SA_WKT2
UNION ALL SELECT multipoint FROM SA_WKT2
UNION ALL SELECT multipolygon FROM SA_WKT2
UNION ALL SELECT multisurface FROM SA_WKT2
UNION ALL SELECT multiline FROM SA_WKT2
UNION ALL SELECT multicurve FROM SA_WKT2
UNION ALL SELECT geomcollection FROM SA_WKT2
UNION ALL SELECT geometry FROM SA_WKT2
    An image showing all of the geometries in the SA_WKT2 column.
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0