You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I have been using MySQL (MariaDB to be specific) for storing shapely geometries. All went well until the customer wanted to slightly change the position of one point.
Since I am familiar with the capabilities of GIS software, I just logged into a database and opened layer via Layer - Add vector layer. I moved the point and thought everything was fine. However, SQLAlchemy did not return any object after this change. Record with my point deleted (this was SQLAlchemy doing).
I investigated a bit more and as for the database, produced wkb seems to be correct. I am able to translate it to the geometry_wkt column and back using ST_AsText/ST_GeomFromText in pure SQL. All in db.
So the issue comes probably from Python drivers, that cannot read point geometry generated with QGIS. I receive error
shapely.errors.GEOSException: ParseException: Unknown WKB type 535
I would normally skip this issue with running scripts with "to WKT" transformation, however, we have our system built on SQLAlchemy and I cannot easily change how it behaves.
Shapely 2.0.1 QGIS 3.32.3-Lima GEOS in shapely seems to be newer version compare to one in QGIS.
It all seems like QGIS is adding some extra data that cannot be understood by Python libraries.
Here is my code:
import pandas as pd
import geopandas as gpd
from shapely import wkb
from modules.db_config import *
import mysql.connector as mariadb
from sqlalchemy import create_engine
class DataBaseManager():
def __init__(self):
try:
self.conn = mariadb.connect(user=user, password=password, host=host, port=port, database=database)
self.cursor = self.conn.cursor(buffered=True)
except mariadb.Error as error:
if error.errno == 2003:
raise RuntimeError(
"Cannot connect to do DB, pls make sure you use VPN connection!")
else:
print(error)
connection_string = f"mysql+mysqldb://{user}:{password}@{host}:{port}/{database}"
self.engine = create_engine(connection_string, pool_size=10, max_overflow=20)
self.data_table = ""
self.database = database
def __enter__(self):
return self
def __exit__(self, exc_type, exc_val, exc_tb):
if self.conn:
self.conn.close()
self.engine.dispose()
def get_sql_to_df(self, sql, params):
with self.engine.connect() as connection:
df = pd.read_sql_query(sql, connection, params=params)
return df
def get_drone_calc_positions(self, race_unique_id):
sql = "SELECT * FROM DroneCalcPosition WHERE race_unique_id=%s"
df = self.get_sql_to_df(sql, params=(race_unique_id,))
return df
def get_drone_calc_positions_as_gpd(self, race_unique_id):
df = self.get_drone_calc_positions(race_unique_id)
df['geometry'] = df['geometry'].apply(wkb.loads)
gdf = gpd.GeoDataFrame(df, geometry='geometry')
gdf.crs = "EPSG:4326"
return gdf
if __name__ == "__main__":
dbmngr = DataBaseManager()
x = dbmngr.get_drone_calc_positions_as_gpd(91)
print(x)
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
I have a quite complex issue.
I have been using MySQL (MariaDB to be specific) for storing shapely geometries. All went well until the customer wanted to slightly change the position of one point.
Since I am familiar with the capabilities of GIS software, I just logged into a database and opened layer via Layer - Add vector layer. I moved the point and thought everything was fine. However, SQLAlchemy did not return any object after this change. Record with my point deleted (this was SQLAlchemy doing).
I investigated a bit more and as for the database, produced wkb seems to be correct. I am able to translate it to the geometry_wkt column and back using ST_AsText/ST_GeomFromText in pure SQL. All in db.
So the issue comes probably from Python drivers, that cannot read point geometry generated with QGIS. I receive error
shapely.errors.GEOSException: ParseException: Unknown WKB type 535
I would normally skip this issue with running scripts with "to WKT" transformation, however, we have our system built on SQLAlchemy and I cannot easily change how it behaves.
Shapely 2.0.1 QGIS 3.32.3-Lima GEOS in shapely seems to be newer version compare to one in QGIS.
It all seems like QGIS is adding some extra data that cannot be understood by Python libraries.
Here is my code:
Beta Was this translation helpful? Give feedback.
All reactions