I have a csv file Author, Name, Geometry.
The Geometry column in the csv has rows like this
{"type": "Point", "coordinates": [11.6285463, 42.4193742]}
How do I import the json as geometry?
I can import the csv to an existing table using the following:
copy locations FROM path/to/file/pleiades-locations.csv' DELIMITERS',' CSV HEADER;
I have the geometry column set as json, but no geospatial data is available when I connect with QGIS, do I have to do some conversion?
Use ST_GeomFromGeoJSON to cast your geometry from geojson to PostGIS geometry. (The converse is ST_AsGeoJSON). If more convenient, you can always add a geometry column (since you've imported everythign already) and then use the cast to set its value for each row.
Assuming you have the table imported, it will be just a normal database table with no true geometry column (in the PostGIS understanding of the term). You need to first add a geometry column (see SQL in documentation). Then do your update (asuming you have called the geometry column 'geom' and the existing json geometry column is (say) 'geomjson':
UPDATE table_name SET geom = ST_GeomFromGeoJSON(geomjson);
I'm not sure there is a way to do the import directly as per your comment below. Using ogr2ogr you can do it if the geometry is in the the form of WKT, XYZ etc or you can tell it lat and lon columns (see here). But there are no options in OGR or Postgis that I know of to directly handle the JSON on import. However, the above is very simple and you could even rename the JSON column to something else if you want to use the name 'geometry' for the geometry column (though the shortened 'geom' is a common naming convention). As your data have already been imported, this is also the quickest solution in this case.
It turned out I needed to set generic geometry! Here's my solution:
ALTER TABLE locations ALTER COLUMN geom SET DATA TYPE geometry;
UPDATE locations SET geom = ST_GeomFromGeoJSON(geometry);