postgis, postgres: geometry columns

# create table (with oid option) and add needed columns
CREATE TABLE mypoints(name varchar(255), x decimal(20,8), y decimal(20,8)) with oids;

# populate columns from source text file

COPY mypoints FROM '/data/dlmysqldata/mypoints.txt';

# create geometry column named mygeom

SELECT AddGeometryColumn('public', 'mypoints', 'mygeom', 4326, 'POINT', 2);

# update  geometry column with point geometry from columns

UPDATE mypoints set mygeom=ST_GeomFromText('POINT(x y)', 4326);

ERROR:  parse error - invalid geometry

# hmmm...

UPDATE mypoints set mygeom=ST_GeomFromText('POINT(-71.0891380310059 42.3123226165771)', 4326);

# but this works!

UPDATE mypoints set mygeom=ST_GeomFromText('POINT(' || x || ' ' || y || ')', 4326);

# aha!

# create geography table; replace with correct SRID

ALTER TABLE mypoints add mygeog geography(POINT,4326);

UPDATE mypoints set mygeog=ST_GeomFromText('SRID:4326;POINT(' || x || ' ' || y || ')');

# i now get column "oid" does not exist. try to recreate table with oid

# create line table

CREATE TABLE mylines(name varchar(255), id varchar(255), x1 decimal(20,8), y1 decimal(20,8), x2 decimal(20,8), y2 decimal(20,8)) with oids;

SELECT AddGeometryColumn('public', 'mylines', 'mygeom', 4326, 'LINESTRING', 2);

UPDATE mylines set mygeom=ST_GeomFromText('LINESTRING(' || x1 || ' ' || y1 || ',' || x2 || ' ' || y2 || ')', 4326);

GRANT all ON TABLE mylines TO devuser;

# create polygon table

create table mypolys(name varchar(255), id varchar(255), x1 decimal(20,8), y1 decimal(20,8), x2 decimal(20,8), y2 decimal(20,8), x3 decimal(20,8), y3 decimal(20,8), x4 decimal(20,8), y4 decimal(20,8)) with oids;

SELECT AddGeometryColumn('public', 'mypolys', 'mygeom', 4326, 'POLYGON', 2);

UPDATE mypolys set mygeom=ST_GeomFromText('POLYGON((' || x1 || ' ' || y1 || ',' || x2 || ' ' || y2 || ',' || x3 || ' ' || y3 || ',' || x4 || ' ' || y4 || '))', 4326);

GRANT all ON TABLE mypolys TO devuser;

# be sure to grant access

# connect to your db by using \c <dbname>

# - for db access

GRANT all ON database spatial_test TO devuser;

# - for table access

GRANT all ON TABLE mypoints TO devuser;

# - for geom access

GRANT all ON TABLE geometry_columns TO devuser;

# - for spatial access

GRANT all ON table spatial_ref_sys TO devuser;