Create Postgis Table Column Type Geometry From Another Text Column In Same Table

0
votes

I'm trying to understand how to use Postgis in a situation I've run into. I have a table like below:

location_name | geom | geography
-----------------------------------
loc_1         | 'MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))' | 
loc_2         | 'MULTIPOLYGON(((0 0,3 0,3 2,0 3,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))' | 
loc_3         | 'MULTIPOLYGON(((0 0,1 0,5 6,0 3,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))' | 
loc_4         | 'MULTIPOLYGON(((0 0,6 0,1 4,0 5,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))' | 
loc_5         | 'MULTIPOLYGON(((0 0,1 0,6 8,0 1,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))' |

Where location is a string, column geom is a text representation of a POSTGIS Polygon or Multipolygon, and column geography is an empty column type geography.

I'm trying to use ST_GeomFromText to populate the geography column with geography type elements converted from the geom string. I'm not very good with SQL and I've tried some commands so far something like this so far that hasn't worked:

UPDATE table SET geography = ST_GeomFromText(geom);

Is it possible to populate the geography column with geography typed versions of the corresponding goem column element?

1
sqlpostgresqlpostgispolygon
Worked for me. What error did you get? - jjanes
try with ST_GeogFromText. The query you show works if the text coordinates are in lat-long. If they are in a projected coordinate system, you would have to load the geometry, set the SRID, transform the SRID to 4326 and then cast to geography (though the current querry would give you a warning, not an error) - JGH
I've run into this error using the query above: ERROR: parse error - invalid geometry HINT: "" <-- parse error at position 1 within geometry - JMV12
so you can filter out empty/null geometries first - JGH

1 Answers

0
votes

I was able to find an answer after some digging around and testing. The issue was that there were empty strings in the text geometry column. A SQL command to add a geography column and populate it based off the text column would look like this:

ALTER TABLE table ADD geography GEOMETRY;
UPDATE table SET geography = ST_GeomFromText(geom) WHERE geom!='' AND geom IS NOT NULL;