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'
   );