The difference between Geography and Geometry

Before we dive too deep, you might want to check out these resources:

Geometry Data Type

In SQL, the GEOMETRY data type represents data in a two-dimensional Cartesian plane. It can store points, lines, and polygons, as well as more complex shapes like circles and rectangles.

The GEOGRAPHY data type is similar to GEOMETRY, but it represents data in a round-earth coordinate system. This makes it better suited for storing data that covers large areas or tracks movement across the surface of the earth.

One key difference between the two data types is the way they store and manipulate spatial data. GEOMETRY uses a flat Cartesian plane, which can be useful for tasks like measuring distance and performing spatial analysis.

However, the earth is not flat, so GEOGRAPHY uses a more complex coordinate system to more accurately represent data on the earth’s surface. This can be helpful when working with data that needs to account for the earth’s curvature.

The Earth is a sphere

A great circle is a circle on the surface of a sphere that has the same diameter as the sphere and passes through its center. Great circles represent the shortest distance between two points on the surface of the globe.

One example of a great circle is the equator, which is the circle formed by the intersection of the earth’s surface with a plane perpendicular to its axis of rotation. Other examples include the lines of longitude (or meridians), which run from the north pole to the south pole

Using the GEOGRAPHY data type in SQL can be an advantage when dealing with distances across a sphere because it uses a round-earth coordinate system that takes into account the curvature of the earth. This can be more accurate than using the GEOMETRY data type, which uses a flat Cartesian plane to represent spatial data.

For example, if you want to calculate the distance between two points on the earth’s surface, using the GEOGRAPHY data type can give you a more accurate result than using the GEOMETRY data type. You can use the ST_Distance method to calculate the distance between two GEOGRAPHY objects, and it will return the shortest distance between the objects along the surface of the earth.

Converting Between Geometry and Geography data types

You can convert between GEOMETRY and GEOGRAPHY data types in SQL. There are a few different ways to do this, depending on the specific requirements of your data and the tools you are using.

One common way to convert between GEOMETRY and GEOGRAPHY is to use the ST_GeomFromText and ST_GeographyFromText functions in SQL. These functions allow you to convert a string representation of a geometry or geography object into the corresponding data type.

For example, you could convert a GEOMETRY point object to a GEOGRAPHY point object like this:

SELECT ST_GeographyFromText(ST_AsText(geometry_point_column))
FROM your_table;

You can also use the ST_GeomFromWKB and ST_GeographyFromWKB functions to convert between GEOMETRY and GEOGRAPHY data types using well-known binary (WKB) representations of the objects.

It’s also possible to use other functions and methods to convert between GEOMETRY and GEOGRAPHY data types, depending on the specific tools and libraries you are using. For example, you might be able to use spatial functions provided by your database management system, or you could use a third-party library like GDAL (Geospatial Data Abstraction Library) to perform the conversion.

Once GDAL is installed, you can use the ogr2ogr utility to convert between GEOMETRY and GEOGRAPHY data types. ogr2ogr is a command-line utility that allows you to perform various operations on vector data, including converting between different data formats and spatial reference systems.

To use ogr2ogr to convert between GEOMETRY and GEOGRAPHY data types, you will need to specify the input and output data sources, as well as the desired data type for the output. For example, to convert a GEOMETRY column in a PostgreSQL table to a GEOGRAPHY column, you could use a command like this:

ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myuser password=mypassword dbname=mydb" \
  -sql "SELECT ST_GeographyFromText(ST_AsText(geometry_column)) AS geography_column FROM mytable" \
  -nln mytable_geography

This command uses ogr2ogr to connect to a PostgreSQL database, execute a SQL query that converts the geometry_column to GEOGRAPHY, and store the result in a new table called mytable_geography.

Recommend Podcast episodes related to PostGIS and Spatial SQL