Loading CSV file into PostGIS?

Question

A CSV file contains the coordinates of all polygons which refers a particular crop in a specific time period. Eg:

enter image description here

How I can load this csv into PostGIS.. I tried with CREATE TABLE But I don't know which variable is used to represent geo column...ie, for representing spatial coordinates.

• Lee Hachadoorian: Working on an answer, but can you please copy a few rows from the CSV and paste them in a code block in your question? That would be much more useful than the screenshot.
• wiltomap: You could probably extract and format the coordinates string in order to make it like a WKT format. Then, PostGIS includes functions to transform WKT to its geometry format. LibreOffice Calc should enable you to do this rather than writting a script...

Answer 1

You have two methods to accomplish this

1. Extract, transform, load (ETL) Script. No one knows how to create that for your data source because you haven't provided the data, just a screen shot. However, we know that in this method you must either,
   1. Process the JSON in a script and load it into a master table directly.
   2. Process the JSON in the database using a temp table. Then UPSERT or INSERT it into the master table.
2. Use a CSV FDW so you can access the CSV directly in PostgreSQL thus eliminating the need for a temp table. Arguably a better idea than the traditional ETL-#2 option.

On the JSON format in your CSV. You know it's not GeoJSON because it has a field "geodesic" which isn't in the spec. Moreover, if it has GeoJSON geometries inside (still possible) you'll still have to process the JSON with one of the above options. Lucky for you, if you cast the JSON to jsonb or load it in a temp table with a jsonb type, you can use the easy PostgreSQL JSON operators.

Answer 2

It looks like your .geo column contains the geometry. From the screenshot it looks like GeoJSON format.

Use:

ST_GeomFromGeoJSON(.geo) AS the_geom

• Evan Carroll: Unrelated to the question. Not GeoJSON.
• user52245: @Evan Carroll Think so? Wikipedia example for geojson geometry: { "type": "Polygon", "coordinates": [ [[35, 10], [45, 45], [15, 40], [10, 20], [35, 10]], [[20, 30], [35, 35], [30, 20], [20, 30]] ] } ... The .geo field looks exactly like that format. I know the whole of the CSV is not GeoJSON, but the postgis function should still be able to convert that column to geometry. If it's something else, please let me know what it is so I can update my answer.
• Evan Carroll: My bet is that it's funky and we're only seeing part of it, and it's something like Google Maps. Anyway, it's clear geojson does not support a geodesic property.
• user52245: Fair. I didn't think about the end of the string. I guess there's no knowing for sure until op posts a data example.