GEOGRAPHY ​

BETA FEATURE
Prerequisites:

• To use the GEOGRAPHY type and its associated functions, you must enable the feature by setting the enable_geospatial configuration parameter to ON.

Warning: This is a beta feature that:

• Is subject to change
• May cause system instability
• May have incomplete functionality

The GEOGRAPHY data type stores geospatial data representing points, lines, and polygons on the Earth's surface. It uses a geodetic coordinate system (latitude/longitude) with SRID 4326 (WGS84). Calculations may approximate or fully account for the Earth's curvature, depending on whether the spherical or spheroidal model is used.

Spherical vs. Spheroidal Earth Models ​

When using GEOGRAPHY functions, it's important to understand which Earth model they employ. The spherical model assumes the Earth is a perfect sphere, which can lead to distance calculation errors of up to 0.3%, particularly over distances greater than 100 kilometers. This occurs because the Earth is actually a spheroid, not a perfect sphere. Functions like ST_Distance and ST_Length allow you to choose between models using the use_spheroid parameter. Other functions without a similar parameter use the spherical model unless stated otherwise.

Functions Using Planar Projection ​

Some functions are not working natively in spherical coordinates, but instead cast to planar geometry. As a result, they may fail to return correct results for objects with very large extents that cannot be cleanly converted to a planar representation.

For example, the ST_Buffer(geography,distance) function transforms the geography object into a “best” planar projection, buffers it, and then transforms it back to spherical geography. If there is no “best” projection (the object is too large), the operation can fail or return a malformed buffer.

The following functions use planar projection:

• ST_Buffer
• ST_Intersection
• ST_Difference
• ST_Union
• ST_SimplifyPreserveTopology

Input Formats ​

Geography data can be input using two standard formats:

Well-Known Text (WKT) ​

WKT is a text-based format representing geographic shapes. Coordinates must be specified as longitude/latitude pairs.

Well-Known Binary (WKB) ​

WKB is a binary representation of geographic data that can be input using hexadecimal strings prefixed with \x. Refer to st_asbinary for more details about casting and conversion functions.

Examples ​

CREATE TABLE locations (
    id INTEGER,
    location GEOGRAPHY
);

-- Insert using WKT format
INSERT INTO locations VALUES 
    (1, 'POINT(-122.27652 37.80574)');

-- Insert using WKB format
INSERT INTO locations VALUES 
    (2, '\x0101000000282cf180b2915ec01e8a027d22e74240'::VARBINARY);

-- Both formats produce the same GEOGRAPHY object
SELECT * FROM locations;
--  id |          location          
-- ----+----------------------------
--   1 | POINT(-122.27652 37.80574)
--   2 | POINT(-122.27652 37.80574)

-- Query distance between two points (in meters)
SELECT ST_Distance(
    'POINT(-122.27652 37.80574)'::GEOGRAPHY,
    'POINT(-122.27645 37.80577)'::GEOGRAPHY
);
-- 7.0062797239034

Known Limitations ​

1. SRID Support:

   • The only supported SRID is 4326 (WGS84).

2. Type Support:

   • Only the GEOGRAPHY type is supported.
   • The GEOMETRY type is not available.

3. Size Limitations:

   • The maximum object size is 64KB.

Known Bugs ​

1. Function Support:

   • Some spatial functions are not implemented.
   • Attempting to use unimplemented functions returns a "Not implemented" error.
   • Refer to the function reference for available operations.

2. Invalid Geometry Behavior:

   • Current behavior for invalid geometries may change in future releases.
   • NaN coordinate values produce undefined behavior.

3. Bug in ST_IsValidReason:

   • The problematic location is returned in meters instead of degrees.
   sql

   SELECT ST_IsValidReason('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))'::GEOGRAPHY);
   -- Returns 'Self-intersection[277506.144723604 110648.741845236]'
   -- Instead of 'Self-intersection[1 1]'

4. Missing Default Values:

   • Default values are missing in ST_Perimeter and ST_Rotate.