I would assume that the user is familiar with PostGIS,the spatial extension to PostGres.
In this post ,we would create a Sptial Table by using geographic elements from a NonSpatial table.We would continue with the MyTable1 created in the previous post.
1) Install PostGIS extension
2) Create a geometry enabled table specifying the Spatial Reference System(SRID).In this example,POINT type geometry with WGS84 coordinate system is created.
--DROP TABLE MyTable1_geom;
CREATE TABLE MyTable1_geom
(
the_geom geometry,
CONSTRAINT "enforce_dims_the_geom" CHECK (ndims(the_geom) = 3),
CONSTRAINT "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),
CONSTRAINT "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
)
WITH (OIDS=TRUE);
ALTER TABLE MyTable1_geom OWNER TO postgres;
3) Copy data from Non-Spatial table-MyTable1
INSERT INTO MyTable1_geom(the_geom) SELECT ST_GeomFromEWKT('SRID=4326;POINT('|| drlong || ' ' || drlat || ' ' ||dralt || ')') as the_geom FROM MyTable1;
4)Check if updated in spatial table
SELECT ST_AsEWKT(the_geom) FROM MyTable1_geom;
No comments:
Post a Comment