0

Not sure if I need to post this at Geo stack exchange or here. I am doing an api call with Python to a large database that has a column with + 1000 (single polygon) geojson strings. Each geojson looks like this:

{'type': 'FeatureCollection', 'features': [{'type': 'Feature', 'geometry': {'type': 'Polygon', 'coordinates': [[[111.38652, -0.441704], [111.386491, -0.441687], [111.386448, -0.441674], [111.38638, -0.441672], [111.386337, -0.441671], [111.38632, -0.441731], [111.386276, -0.44177], [111.386237, -0.441789], [111.386211, -0.44178], [111.386155, -0.441784], [111.386148, -0.441789], [111.386124, -0.441869], [111.386095, -0.441935], [111.386117, -0.441967], [111.386147, -0.441963], [111.386172, -0.441959], [111.386236, -0.441996], [111.386296, -0.441963], [111.386345, -0.441943], [111.386404, -0.441944], [111.386461, -0.441915], [111.386464, -0.441889], [111.386489, -0.441861], [111.386556, -0.441819], [111.38655, -0.441719], [111.38652, -0.441704]]]}}]}

I want to do a loop through the database to get all these strings and store each of these geojson string individually in my own SQLite/Spatialite database (so 1 geojson string per row) After that I want to be able to visualise the polygons individually or process them into 1 big geofile from the database. My question is: what is the advise how to best process these geojson strings to SQlite/Spatialite(?) I could populate them into my database as geojson strings (so as they are now), but I assume that would not make sense since you cannot visualise them afterwards. Besides, I tried to load them as strings into TEXT columns of an SQlite database but it creates errors as SQlite sees them as dictionaries. So, in WHAT DATABASE (Sqlite or Spatialite) and WHAT COLUMN TYPE I can best store them (GEOMETRY?) for further processing and HOW can I get those geojson strings in that column with python3? Do I need regex to extract the coordinates from the geojson stings and store them in the column, or is there a way to do this more easy? Any advise to help me in the right direction is appreciated.

GIStrees
  • 1
  • 1
  • 1
  • Check out [GeoAlchemy2](https://geoalchemy-2.readthedocs.io/en/latest/).Or alternatively, you could consider [MongoDB](https://docs.mongodb.com/manual/reference/geojson/). – RJ Adriaansen Aug 01 '21 at 19:29
  • Thanks. Maybe it is my lacking experience about working with Python, Geojson and Sqlite, but I find it so strange that people developed an entire library on spatial analysis (meaning Libspatialite) but there is hardly info found on how to populate the SQLite database with geographical data. I searched everywhere, but all seems to be focussed on querying data from databases or populating SQLite with normal json data (without any geo-component). Strange that this topic is not covered better by Spatialite. It seems pretty complex to load geodata to a database with Python. – GIStrees Aug 02 '21 at 10:00
  • Don't forget that SQLite is ancient, predates the rise of json and is built upon the first normal form rule that a cell should only hold one value. I would certainly go for a NoSQL option like MongoDB. – RJ Adriaansen Aug 02 '21 at 10:16

0 Answers0