Скачать или смотреть Converting Geometry to Geography in PostGIS

Learn how to successfully create and populate a `geography` column from a `geometry` column in PostGIS. This guide provides step-by-step instructions to overcome common errors during the conversion process.
---
This video is based on the question https://stackoverflow.com/q/64539981/ asked by the user 'JMV12' ( https://stackoverflow.com/u/4296407/ ) and on the answer https://stackoverflow.com/a/64540210/ provided by the user 'JGH' ( https://stackoverflow.com/u/7635569/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: PostGIS Create Geography Column From Geometry Column

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Converting Geometry to Geography in PostGIS: A Comprehensive Guide

If you are working with spatial data in PostGIS, you may have encountered a scenario where you need to convert a geometry column to a geography column. This conversion is essential for achieving accurate calculations such as distances and areas on the Earth's surface. In this guide, we'll go through the steps to create a geography column and successfully populate it using data from an existing geometry column while addressing common pitfalls along the way.

The Problem

You have a table structured as follows:

[[See Video to Reveal this Text or Code Snippet]]

After attempting to add a new geography column using the following command:

[[See Video to Reveal this Text or Code Snippet]]

Your table now looks like this:

[[See Video to Reveal this Text or Code Snippet]]

However, when you try to populate the geography column with data from the geometry column using:

[[See Video to Reveal this Text or Code Snippet]]

You encounter the following error:

[[See Video to Reveal this Text or Code Snippet]]

This error arises because the geometry column contains MultiPolygon data, while you designated the geography column to specifically hold POINT data.

The Solution

To successfully convert and populate your geography column from the geometry column, follow these organized steps:

Step 1: Determine the Type of Geometry

Before making any alterations, correctly identify the types of geometries stored in your geometry column. This might be a POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or a mix of these types.

Step 2: Adjust the Geography Column Type Appropriately

Depending on the type of geometries in your geometry column, you may need to adjust the geography column type:

For Homogeneous Data: If all the data in your geometry column is of type MULTIPOLYGON, use the following command to create the geography column of the appropriate type:

[[See Video to Reveal this Text or Code Snippet]]

For Mixed Data: If your geometry column contains a mix of geometry types, you can create a more generic geography column without specifying the geometry type:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Populate the Geography Column

Once you have the appropriate column type, you can proceed to populate the geography column from the geometry column. Execute the following update query:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Verify Your Data

Finally, ensure that the geography column is populated correctly by running a simple select query:

[[See Video to Reveal this Text or Code Snippet]]

Check that the values in the geography column match those converted from the geometry column without any errors.

Conclusion

Converting a geometry column to a geography column in PostGIS is not only a common task but also an essential one for spatial analyses. By following the structured approach outlined in this guide, you can easily overcome errors related to type mismatches and ensure your spatial data is ready for accurate geographic operations.

With this guide, you're equipped with the knowledge to handle similar issues should they arise in the future. Happy analyzing!