1

I am trying to convert a geo column into Polygons in Python with SQLite3. I imported the file into Python and geo rows look like following first row:

geo[0] = b'\x00\x01$i\x00\x00@\xd9\x94\x8b\xd6<\x1bAb\xda7\xb6]\xb1QA\xf0\xf7\x8b\x19UC\x1bA\x9c\xde\xc5\r\xc3\xb1QA|\x03\x00\x00\x00\x01\x00\x00\x00\x06\x00\x00\x00Hlw\xef-C\x1bA\x9c\xde\xc5\r\xc3\xb1QA\xf0\xf7\x8b\x19UC\x1bAv\xc0u)^\xb1QA\xbcw\xd4\x88\xf1<\x1bAb\xda7\xb6]\xb1QA\xa5\xdc}n\xd7<\x1bA\x84.\xe1r\xbe\xb1QA@\xd9\x94\x8b\xd6<\x1bA\xce\x8eT\xef\xc1\xb1QAHlw\xef-C\x1bA\x9c\xde\xc5\r\xc3\xb1QA\xfe'

When I use ST_AsText(GEO) in spatialite_gui, it makes the following conversion:

POLYGON((446667.483854 4638476.215202, 446677.274948 4638072.647812, 446268.383623 4638070.847159, 446261.857902 4638457.794994, 446261.63631 4638471.739536, 446667.483854 4638476.215202))

However, I could not achieve the same thing in Python using SQLite3 because it says there is no such function. Then, I started to look at alternative solutions and found this method: wkb.loads(parcel.the_geom, hex=True) with shapely package here. Yet, this also did not work and produced the following error:

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-120-96c04363008d> in <module>
      1 from shapely import wkb
      2 
----> 3 shp = wkb.loads(data[0][-1], hex=True)
      4 print(shp)

~/anaconda3/lib/python3.7/site-packages/shapely/wkb.py in loads(data, hex)
     13     reader = WKBReader(lgeos)
     14     if hex:
---> 15         return reader.read_hex(data)
     16     else:
     17         return reader.read(data)


~/anaconda3/lib/python3.7/site-packages/shapely/geos.py in read_hex(self, data)
    438         """Returns geometry from WKB hex"""
    439         if sys.version_info[0] >= 3:
--> 440             data = data.encode('ascii')
    441         geom = self._lgeos.GEOSWKBReader_readHEX(
    442             self._reader, c_char_p(data), c_size_t(len(data)))


AttributeError: 'bytes' object has no attribute 'encode'

How can I resolve this issue?

Vince
  • 20,017
  • 15
  • 45
  • 64
tcokyasar
  • 121
  • 3
  • You can use SpatiaLite functions with Python as well. You need the mod_spatialite module and all the dependencies that mod_spatialite requires. There are Windows binaries available at https://www.gaia-gis.it/gaia-sins/index.html. Then you can open the SQLite connection and load mod_spatialite as an extension. – user30184 Oct 15 '20 at 09:14
  • I am a Mac user. Do you have any alternative solution? – tcokyasar Oct 15 '20 at 13:29
  • Try to find compiled binaries for Mac or instructions for compiling them yourself. This is very old answer but perhaps it gives some hints https://stackoverflow.com/questions/46303949/how-to-load-spatialite-sqlite-extension-on-macos. Try to find at least version 4.3. – user30184 Oct 15 '20 at 13:37
  • 1
    Found my answer here: https://gis.stackexchange.com/questions/184850/how-to-use-spatialite-functions-in-a-python-script Thanks for guidance! – tcokyasar Oct 15 '20 at 13:59
  • Hello tcokyasar. I have a similar problem: With python on Mac OS, connecting to a n extern database with sql alchemy, I have a blob geometry with the same format as yours, but i didn't find how to decode it. Can you share your solution? Thanks a lot – ZDidier Jun 06 '21 at 15:06
  • I think I had to use a Windows machine, eventually. The solution was importing connR.execute('SELECT load_extension("mod_spatialite")') and connR.execute('SELECT InitSpatialMetaData(1);'). As I remember, I found these files online and put them into my working directory. – tcokyasar Jun 07 '21 at 18:56

0 Answers0