Create tables with an ST_Geometry column

You can use SQL to create tables that contain an ST_Geometry column.

In this example, two tables are created. One table, sensitive_areas, stores data about schools, hospitals, and playgrounds. The ST_Geometry data type is used to store the location of the sensitive areas in a column named zone.

The other table, hazardous_sites, stores locations of hazardous waste sites as points in an ST_Geometry column named location.

Steps:
  1. Open an SQL editor and connect to your database.
  2. For SQLite, if you have not already loaded the ST_Geometry library, do so now.
  3. Use the SQL CREATE TABLE statement to create the two tables: sensitive_areas and hazardous_sites as shown here:

    These statements create the tables in Oracle or PostgreSQL.

    CREATE TABLE sensitive_areas (
     area_id integer,
     name varchar(128), 
     area_size float,
     type varchar(10),
     zone sde.st_geometry
    );
    
    CREATE TABLE hazardous_sites (
     row_id integer NOT NULL,
     site_id integer, 
     name varchar(40),
     location sde.st_geometry
    );
    

    This example creates the tables in SQLite and adds an ST_Geometry column to each table.

    CREATE TABLE sensitive_areas (
     area_id integer primary key autoincrement not null,
     name text(128), 
     area_size float,
     type text(10)
    );
    
    SELECT AddGeometryColumn(
     null,
    'sensitive_areas',
    'zone',
    4326,
    'polygon',
    'xy',
    'null'
    );
    
    CREATE TABLE hazardous_sites (
     row_id integer primary key autoincrement not null,
     site_id integer, 
     name text(40)
    );
    
    SELECT AddGeometryColumn(
     null,
    'hazardous_sites',
    'location',
    4326,
    'polygon',
    'xy',
    'null'
    );
    

Related Topics

6/19/2015