[GIS] Loading CSV file into PostGIS

coordinate systemcsvpostgis

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.

Best Answer

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.

Related Question