1

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?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.