PostGIS Geometry Creation – How to Create from Latitude/Longitude Array in Table

postgispostgresqlsql

My question is similar to Creating geometry from lat/lon in table using PostGIS? , but I need to convert set or array of LON & LAT in PostgreSQL into geometry value.

For example, I can convert single LON & LAT to geometry point using below code (using PostgreSQL command) : 

SET geometry = ST_GeomFromText('POINT(' || lon || ' ' || lat || ')',4326);

What if I have array of LON & LAT like this lon1,lon2,lon3,lat1,lat2,lat3 and I want to combine those coordinates to geometry values which will produce a long polygon shape instead of a single point shape?

Best Answer

It's a little nasty to store coordinates that way...

However, more importantly, creating actual Polygons from points require some pre-conditions to be met:

  • vertices (i.e. your coordinate pairs) need to be in (any) winding order (there are contrary definitions of what direction that order should take between formats; PostGIS itself doesn't care)
  • the last vertex needs to be identical with the first; a simple Polygon is usually defined as a closed LineString

Assuming that your arrays fulfill these conditions, running

UPDATE  <table> AS a
    SET <geom> = (
        SELECT ST_SetSRID(ST_MakePolygon(ST_MakeLine(ST_MakePoint(dmp.x, dmp.y))), <SRID>)
        FROM   <table> AS b,
               LATERAL UNNEST(<array>[:ARRAY_LENGTH(<array>, 1)/2], <array>[ARRAY_LENGTH(<array>, 1)/2+1:]) AS dmp(x, y)
        WHERE  a.<id> = b.<id>
    )
;

will create Polygons with a given <SRID> in column <geom> from your <array> column. If you'd rather want LineStrings, remove the wrapping ST_MakePolygon() from the query.

If your arrays do not meet these requirements, please edit and update your question with details.

Related Question