2

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?

3

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.

  • So, how would that look like as a query? Would it be an update query?The table is location and the column is geometry, i'd rather keep the column the same rather than creating a new column. Alternatively is there anyway to specify it as geometry when importing the csv file since I will be doing this a lot? – Spatial Digger Apr 20 '17 at 16:47
  • Please see my edits above. – MappaGnosis Apr 20 '17 at 18:07
  • Ok, nearly working, now it gets complicated. Added the geom column ALTER TABLE locations ADD COLUMN geom geometry(POINT); UPDATE locations SET geom = ST_GeomFromGeoJSON(geometry); ERROR: Geometry type (Polygon) does not match column type (Point) Turns out the geometry column containing the JSON has point and polygon data - the data was supplied by a third party - so I guess I need to have a where geometry = POINT? – Spatial Digger Apr 20 '17 at 19:01
  • Going through the data it is mostly point data, there are some polygons and linestrings. – Spatial Digger Apr 20 '17 at 19:04
1

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); 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.