Reprojection in PostGIS

In order to convert the Spatail data from one projection system to the other,PostGIS
provides ST_Transform(the_geom,targetSRID) function.While the first parameter holds the input geometry,the second parameter refers to the SRID number of the target projection system.The following example converts WGS84(SRID#4326) into UTM,Zone43N(SRID#32643).

Example:-

--DROP TABLE MyTable1_UTM;
CREATE TABLE MyTable1_UTM WITH (OIDS=TRUE) AS
(
SELECT ST_Transform(the_geom,32643) as the_geom FROM MyTable1_geom
)


You can find the SRID of all PostGIS supported projection systems in spatial_ref_sys
table of every spatially enabled table.

Converting NonSpatial to Spatial table in PostGres/PostGIS

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;

Importing CSV file to PostGres

1) Ensure that the CSV file is in the following format with first row containing column names.

Eg:- MyData.csv

Long,Lat,Alt
77.554428,12.912127,837
77.554428,12.91212,836
77.55442,12.912113,836
77.55442,12.912107,837
77.554413,12.912102,837
77.554405,12.912099,836
77.554405,12.912093,837
77.554398,12.912087,837

2) Create a Table with desired fields in PostGres

Eg:-

CREATE TABLE MyTable1
(
drlat double precision,
drlong double precision,
dralt double precision
)
WITH (OIDS=TRUE);
ALTER TABLE MyTable1 OWNER TO postgres

3) Use the following command to import the data from CSV file into PostGres table

COPY MyTable1 FROM E'C:\\MyData.csv' USING DELIMITERS ',' CSV HEADER;