Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - Spatial Data Support » Spatial data

 

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

You can add spatial data to a table by using a Well Known Text file (WKT) that contains text that can be used to load spatial data into a database and be represented as geometry.

Prérequis

The privileges required to load data depend on the -gl server option. If the -gl option is set to ALL, one of the following must be true:

  • you are the owner of the table
  • you have LOAD privilege on the table
  • you have the LOAD ANY TABLE system privilege
  • you have the ALTER ANY TABLE system privilege

If the -gl option is set to DBA, you must the LOAD ANY TABLE or ALTER ANY TABLE system privilege.

If the -gl option is set to NONE, LOAD TABLE is not permitted.

When loading from a file on a client computer:

  • READ CLIENT FILE privilege is also required.

  • Read privileges are required on the directory being read from.

  • The allow_read_client_file database option must be enabled.

  • The read_client_file secure feature must be enabled.

 Load spatial data from a WKT file
  1. Create a file that contains spatial data in WKT format that you can load into the database.

    The file can be in any format supported by the LOAD TABLE statement.

  2. In Interactive SQL, connect to your database.

  3. Create a table and load the data from the file into using a statement similar to the following:

    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 16\\Samples\\wktgeometries.csv' DELIMITED BY ',';

    The data is loaded into the table.

Résultat

The spatial data is successfully loaded from the WKT file.

Suivant

You can view the data in Interactive SQL using the Spatial Viewer.

Exemple

  1. Save the following text in a text file named wktgeometries.csv.

    The following text contains a group of geometries, defined in WKT.



    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)))"
    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))"
  2. In Interactive SQL, connect to the sample database (demo.db).

  3. Create a table called SA_WKT and load the data from wktgeometries.csv into it. 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 16\\Samples\\wktgeometries.csv' DELIMITED BY ',';

    The data is loaded into the table.

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

  5. In the Spatial Viewer, execute the following statement 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. You can create a file of WKT data containing one of each supported spatial data type, stored in individual columns.

    Copy the following code into 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 by executing the following statements. 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 16\\Samples\\wktgeometries2.csv' DELIMITED BY ',';

    The data is loaded into the table.

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

    SELECT * FROM SA_WKT2;

    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, 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.

 See also