GEOGRAPHY

BETA FEATUREPrerequisites:

• 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
• ST_Touches
• ST_Overlaps
• ST_Makevalid
• ST_IsValid
• ST_IsValidReason
• ST_Crosses

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.

3. Bug in ST_IsValidReason:

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

   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]'