2

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?

Improve this question

1 Answer 1

Reset to default
4

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.