2

I have an existing table in postgres which contains a plain old posgresql polygon. I have added a new column using

SELECT AddGeometryColumn ('public','fences','geom',4326,'POLYGON',2);

I would like to populate this GIS column with the polygon data from the existing column.

The existing column is called poly and the data looks like the following:

"((-74.026142,40.636264),(-74.026142,40.636438),(-74.025818,40.636438),(-74.025818,40.636264),(-74.026142,40.636264))"

Is there a query I can run that will pull that poly field convert it to a postgis polygon and insert it into the new geom column?

  • 1
    The existing column looks like Well-Known Text (WKT) for polygons, except that it's missing the POLYGON keyword, and it has extra parentheses around coordinate pairs. If this is formed correctly, you can use ST_GeomFromText. See also regexp_replace – toms Aug 11 '15 at 20:04
3

It's not an elegant solution, but you could try to convert Polygon string representation into WKT and use ST_GeomFromText

select ST_GeomFromText(
    'POLYGON ' || 
    regexp_replace(
        '((-74.026142,40.636264),(-74.026142,40.636438),(-74.025818,40.636438),(-74.025818,40.636264),(-74.026142,40.636264))', 
        '\,(?=[0-9])', ' ', 'g') 
)
  • I ended up using something similar to this. Thanks for the suggestion. – Nathan Case Aug 12 '15 at 15:27

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.