2

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.

Improve this question
2
  • 4
    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.
    – Lee Hachadoorian
    Commented Feb 2, 2017 at 4:34
  • 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...
    – wiltomap
    Commented Feb 2, 2017 at 11:03

2 Answers 2

Reset to default
2

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.

Improve this answer
0

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

Use: 

ST_GeomFromGeoJSON(.geo) AS the_geom
Improve this answer
4
  • Unrelated to the question. Not GeoJSON.
    – Evan Carroll
    Commented Feb 2, 2017 at 6:11
  • 1
    @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.
    – user52245
    Commented Feb 2, 2017 at 6:17
  • 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.
    – Evan Carroll
    Commented Feb 2, 2017 at 6:33
  • 2
    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.
    – user52245
    Commented Feb 2, 2017 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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