How do you create a table with a column of type GEOMETRY?
MaryJns
Posted by MaryJns
Last Updated: June 22, 2024
table geometry
To create a table with a column of type GEOMETRY, you typically need to use a spatial database that supports geometry data types, such as PostgreSQL with the PostGIS extension, MySQL, or others. Here's how you can do it in a few different databases:
PostgreSQL with PostGIS
1. Enable PostGIS extension (if you haven't done so already): 
CREATE EXTENSION postgis;
2. Create a table with a GEOMETRY column: 
CREATE TABLE my_table (
       id SERIAL PRIMARY KEY,
       name VARCHAR(100),
       geom GEOMETRY(Point, 4326)  -- You can specify the geometry type and SRID
   );
MySQL
In MySQL, you can create a table with a GEOMETRY column as follows: 
CREATE TABLE my_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY NOT NULL
);
SQL Server
In SQL Server, you can create a table with a GEOMETRY column using the following syntax: 
CREATE TABLE my_table (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100),
    geom GEOMETRY
);
Oracle
In Oracle, if using Oracle Spatial, the syntax is slightly different: 
CREATE TABLE my_table (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100),
    geom MDSYS.SDO_GEOMETRY
);
General Notes
1. Geometry Types: When creating a GEOMETRY column, you can specify the type of geometry (e.g., Point, Polygon, LineString) if the database allows it. 2. Spatial Reference Identifier (SRID): Many spatial databases allow you to specify an SRID along with your geometry type, which indicates the coordinate system you are using. 3. Indexes: For performance reasons, you might want to create spatial indexes on geometry columns after creating the table. Always consult your specific database's documentation for the most accurate syntax and options available for geometry data types.
Default Image