How do you create a table with a column of type GEOGRAPHY?
MaryJns
Posted by MaryJns
Last Updated: June 08, 2024
table geography
To create a table with a column of type GEOGRAPHY in a SQL database, you'll want to use a system that supports spatial data types. In SQL Server, PostGIS (an extension for PostgreSQL), and Oracle Spatial, you can create tables with geography data types. Here's how you can do it in these systems:
For SQL Server:
You can create a table in SQL Server with a geography column like this: 
CREATE TABLE Locations (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Location GEOGRAPHY
);
For PostgreSQL with PostGIS:
First, ensure you have the PostGIS extension enabled. Then, you can create a table as follows: 
CREATE EXTENSION postgis;  -- Run this if PostGIS is not yet enabled

CREATE TABLE Locations (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(100),
    Location GEOGRAPHY(Point, 4326)  -- WGS 84 coordinate system
);
For Oracle Spatial:
Here’s how you can create a table with a geography column in Oracle: 
CREATE TABLE Locations (
    Id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    Name VARCHAR2(100),
    Location SDO_GEOMETRY
);
(Note: With Oracle, you may need to define the SDO_GEOMETRY structure further depending on your requirements.)
Important Considerations:
1. Coordinate System: Make sure you specify the correct Coordinate Reference System (CRS) — for instance, 4326 is commonly used for WGS 84. 2. Geospatial Functions: Once you create the table, you can utilize various geospatial functions provided by your database system to insert, query, and manipulate spatial data. 3. Indexing: Consider adding a spatial index on the geography column to improve performance for spatial queries. Using these SQL commands, you can manage geographical data effectively in your applications.
Default Image