Скачать или смотреть Creating Polygons in PostGIS Using Geometry Points from Latitude and Longitude Coordinates

Learn how to convert geographic data points into polygons using PostGIS in PostgreSQL. This guide details step-by-step instructions for creating tables and updating them with polygon data using SQL queries.
---
This video is based on the question https://stackoverflow.com/q/62910654/ asked by the user 'mmarboeuf' ( https://stackoverflow.com/u/3737212/ ) and on the answer https://stackoverflow.com/a/62911820/ provided by the user 'Jim Macaulay' ( https://stackoverflow.com/u/4473615/ ) 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: How to create a table with geometry polygons and field_ID from another table containing field_ID and lat long point in postgis

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.
---
How to Create Polygons from Point Coordinates Using PostGIS

Have you ever found yourself needing to transform point coordinates into polygons for your geospatial data analysis? If you're working with geographic data in PostgreSQL with PostGIS and you have a table of point coordinates, you're in the right place! This guide will guide you through the process of creating a table of geometry polygons from point coordinates, specifically using data that includes latitude and longitude.

The Problem

You have a table named field_point containing the coordinates of fields on a farm; these coordinates are represented by latitude and longitude points. The challenge is to create polygons that accurately define these fields by using the point data as vertices of the polygons. The structure of the field_point table looks like this:

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

After populating the field_point table with points, the goal is to create another table called field that contains the generated polygons along with the respective field_id. Here's what the final structure of the field table should look like:

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

Collecting Point Data

First, you will need to fill in the point geometry in the field_point table using the latitude and longitude values. Use the following SQL query to achieve this:

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

With the points properly updated, your next step is to create the polygons.

The Solution: Creating Polygons from Points

Once you've defined your points in the field_point table, creating polygons based on those points is relatively straightforward with PostGIS functions. The function you will use is ST_ConvexHull, which returns the smallest polygon that can contain all provided points.

Step-by-Step Guide

Insert Polygon Data into the New Table:
You can insert the polygons into the field table by collecting points from field_point and applying the convex hull function. However, the earlier attempt you made was missing the field_id. This is crucial for linking back to the original data.

The Correct Query to Use:
Adopt the following SQL command to ensure that you capture both the field_id and the generated polygon in the new table:

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

Explanation of the Query

INSERT INTO field: This part specifies that you're filling out the field table.

SELECT field_id, ST_ConvexHull(ST_Collect(point)): Here, you’re selecting the field_id and creating a convex hull polygon from the points collected.

FROM field_point GROUP BY field_id: Finally, you group your results by field_id to ensure that each polygon corresponds correctly to each field.

Expected Outcome

Following the successful execution of this query, you should have a field table populated with rows where each row represents a polygon generated from the points associated with its respective field_id. This is the ultimate goal when transforming point coordinates into meaningful data visualizations for geographical insights.

Conclusion

Transforming point data into polygons is a common task in geospatial analysis, and with the help of PostGIS functions, it becomes significantly easier. By following the steps outlined in this post, you can effectively create a new table that not only holds the polygons but also maintains a clear connection to your original data.

If you found this guide helpful or have any questions, feel free to leave a comment or share your experiences in working with PostGIS!