I have this shapefile:
shapes.shp
in which I have imported in SpatiaLite using this command:
spatialite_tool --import --shapefile shapes --db-path db.sqlite --table shapes --charset UTF-8 --srid 27700 --coerce-2d -k
which provides me with a geometry column which seems valid:
SELECT Count(*), GeometryType("geometry"), Srid("geometry"), CoordDimension("geometry")
FROM "shapes"
GROUP BY 2, 3, 4
and the output:
Count(*) GeometryType("geometry") Srid("geometry") CoordDimension("geometry")
---------- ------------------------ ---------------- --------------------------
509282 MULTIPOLYGON 27700 XY
I then transform the geometry to SRID 4326 using the following:
UPDATE shapes SET multi_geom = TRANSFORM(geometry, 4326);
I then run the follwing:
SELECT Count(*), GeometryType("multi_geom"), Srid("multi_geom"), CoordDimension("multi_geom") from shapes GROUP by 2, 3, 4;
Which proves me with this:
Count(*) GeometryType("multi_geom") Srid("multi_geom") CoordDimension("multi_geom")
---------- -------------------------- ------------------ ----------------------------
509282 MULTIPOLYGON 4326 XY
All seems good.
Then I apply the Spatial Index via:
SELECT CreateSpatialIndex("shapes", "multi_geom");
So now I can query quite quickly any items that intersects a bounding box and it works well. The problem I now have is that I would like to display these golygons on a map using AsGeoJSON(), and though it works fine in most cases I do however have some very large polygons:
SELECT ST_NPoints(multi_geom) AS pointCount FROM shapes ORDER BY pointCount DESC limit 10;
pointCount
----------
460061
303778
248892
241198
238410
238153
205812
193641
192667
178179
Ultimately what I would like it to be able to use a form of "ST_Subdivide" function or something similar which will break up the polygons into different "rows" so that I am not sending 20MB of GeoJSON to a browser when a large polygon intersects with my bounding box. I can deal with some overlap, however some of the polygons span 10's of kilometres which in my eyes is wasted bandwidth and compute. I have been trying various different "methods" to try and achieve this to no avail, so I am certain I am missing something fundamental here.
An example of the sheer size of polygon could be seen running this query:
SELECT multi_geom
FROM shapes
WHERE ROWID IN (
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'shapes'
AND f_geometry_column = 'multi_geom'
AND search_frame = BuildMbr(
-2.022857666015625,
52.109351808806345,
-1.9955635070800781,
52.11989325575632,
4326
)
AND ST_Intersects(
multi_geom,
BuildMbr(
-2.022857666015625,
52.109351808806345,
-1.9955635070800781,
52.11989325575632,
4326
)
)
);
While in reality I would like the polygon broken up so I don't have to render it all.