I assume that there is a physical Oracle table that sees insert/update events as the REST service fires.
And that those events aren't a complete truncate/reload.
If so I would create a before insert/update trigger and construct an sde.st_geometry object from the X,y columns. No indexes should exist before the initial load; create them after the load. As ...
Try Explode Lines tool (in Processing Toolbox > Vector geometry) to extract segments out of your red line layer.
Then open the Layer Properties > Label of the produced Exploded layer and give an expression:
You need the labels, not the exploded line segments. To hide the Exploded line ...
The easiest way I found to get the bounding box from a geometry that crosses the antimeridian using OGR/GDAL is by using the gdal.VectorTranslate() function, which is the Python binding for OGR2OGR. If you pass the -lco WRITE_BBOX=YES
option as a parameter, the output file will contain the correct bounding box.
from osgeo import ogr, gdal
You can use Aggregatortransformer.
Be careful what mode you choose. To illustrate, I have 5 polygons in entry. If I choose :
Geometry - Assemble One Level : The result is one feature (merging of the 5).
Geometry - Assemble Hierarchy : The result is five features. I specify the mergefield (here FID), I have 5 unique values so I will keep the 5 geometries.
Number 1 will have least overhead. Based on map extent used most often set smallest grid slightly smaller than this map extent zoomed to. Set next middle grid 3 times this value. Set largest grid three times the middle grid value. This works like a spatial index explained here:
While PostGIS uses Well-Known Binary internally, you'd be better off explicitly casting the PostGIS geometry to Well-Known Text or Binary.
b.objectid AS oid
FROM cd.points a
JOIN sw.risk_polygons b ON sde.st_intersects(a.shape, sde.st_geometry(ST_AsText(b.shape), 4326)) = 1
ORDER BY a.objectid;
GeoPackages are SQLite databases with a specific structure. You cannot just read geometries as "random" sequential bytes from a SQLite database, there might be fragmentation or similar.
If you want to do it low-level and without one of the fine libraries others suggested, the pure Python standard library way would be to use sqlite3 to open the file and then ...
TOAD writes out the meta data of Oracles ST_GEOMETRY UDT implementation for ESRIs SDE; it's essentially a list of geometric predicates describing the geometry, and the vertex array that builds the geometry type, held as BLOB:
<ENTITY>, -- geometry type (POINT, LINESTRING, ...)
<NUMPTS>, -- number of points