Assuming you have at least PostgreSQL version 9.3, you can use a few JSON functions and operators to extract the relevant parts of the GeoJSON specification required by ST_GeomFromGeoJSON to create geometries.
Try the following, where you can replace the JSON in the top part:
WITH data AS (SELECT '{ "type": "FeatureCollection",
"features": [
{ "type": "Feature",
"geometry": {"type": "Point", "coordinates": [102.0, 0.5]},
"properties": {"prop0": "value0"}
},
{ "type": "Feature",
"geometry": {
"type": "LineString",
"coordinates": [
[102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0]
]
},
"properties": {
"prop0": "value0",
"prop1": 0.0
}
},
{ "type": "Feature",
"geometry": {
"type": "Polygon",
"coordinates": [
[ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0],
[100.0, 1.0], [100.0, 0.0] ]
]
},
"properties": {
"prop0": "value0",
"prop1": {"this": "that"}
}
}
]
}'::json AS fc)
SELECT
row_number() OVER () AS gid,
ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom,
feat->'properties' AS properties
FROM (
SELECT json_array_elements(fc->'features') AS feat
FROM data
) AS f;
Finds three geometries. The geom column has the geometry object, and the gid is the feature number. The ST_AsText function shows the WKT equivalent of each geometry. I've also included the properties or attributes that can be defined for each geometry, as is shown in the specification.
gid | geom | properties
-----+------------------------------------------+--------------------------------------
1 | POINT(102 0.5) | {"prop0": "value0"}
2 | LINESTRING(102 0,103 1,104 0,105 1) | { +
| | "prop0": "value0", +
| | "prop1": 0.0 +
| | }
3 | POLYGON((100 0,101 0,101 1,100 1,100 0)) | { +
| | "prop0": "value0", +
| | "prop1": {"this": "that"}+
| | }
(3 rows)
You should assign an SRID for the geometry, using ST_SetSRID.
Or if you simply need a single heterogeneous GEOMETRYCOLLECTION, you can make it compact like this:
SELECT ST_AsText(ST_Collect(ST_GeomFromGeoJSON(feat->>'geometry')))
FROM (
SELECT json_array_elements('{ ... put JSON here ... }'::json->'features') AS feat
) AS f;
GEOMETRYCOLLECTION(POINT(2565453.18267219 -3835048.65976031),LINESTRING(2727584.72197102 -3713449.19424187,2732476.69178127 -3992291.47342619),POLYGON((2442627.90254053 -3705499.95430853,2425506.00820465 -3886502.83728783,2555143.20817631 -3910962.68633909,2442627.90254053 -3705499.95430853)))
See also Creating GeoJSON Feature Collections with JSON and PostGIS functions from the Postgres OnLine Journal, which does the opposite.
As user30184 outlined:
It's a very common task, and there's plenty of documentation: ST_Transform.
To apply this, you need to figure out the EPSG codes of your projections.
UTM 35N probably is EPSG:32635, and your lat/lon coordinates could be anything. One of the more common ones is WGS84 as used in GPS with code EPSG:4326
The Postgis query then would be
SELECT ST_Transform(geom, 4326,32635)
If your postgis table already has the proper source SRID, you can do a simpler
SELECT ST_Transform(geom, 32635)
EDIT after you updated your question a lot:
The issue is not with ST_Transform, the issue is your query. You explicitly set all geometries to be the same. This is how UPDATE in combination with subqueries works. I really recommend you dig through the documentation a bit more to grasp this.
The solution to this issue can be found in the docs as well, by using a WHERE clause at the end. While not standard SQL, PostgreSQL allows UPDATE WHERE in order to apply subqueries to specific rows they match.
In most cases, one has a unique identifier that you can use (just hand it down from the sub queries).
In your case, depending on your data, you could use the timestamp or the lon/lat/alt itself to do so, as all these should match the same anyway.:
UPDATE info SET geom = sq.geom
FROM (
SELECT lon, lat,alt, ST_Transform(geom,32635) as geom
FROM (
SELECT lon,lat,alt, ST_setsrid(geom_v,4326) as geom
from (
select lon,lat,alt, ST_MakePoint(lon,lat,alt) as geom_v
from (
select latitude as lat, longitude as lon, altitude as alt from info order by gid asc
) as fpp
)as ftt
) as tr
) as sq
WHERE info.lon = sq.lon AND info.lat = sq.lat AND info.alt = sq.alt
However, your entire query is unnecessarily convoluted (extremely so!).
You could (and should) just use this:
UPDATE test SET geom = ST_Transform(ST_SetSRID(ST_MakePoint(lon,lat, alt),4326),32635)
to achieve the same result with a much faster and easier-to-read query.
Best Answer
It's not an elegant solution, but you could try to convert
Polygonstring representation intoWKTand useST_GeomFromText