Hi all.
Based on OpenStreetMap coastline data, there are water polygon shapefiles available at:
https://osmdata.openstreetmap.de/data/water-polygons.html
Size is around 1Gb.
It took my laptop about 10 mins to load this data into a Spatialite database using .loadshp and the resulting database is about 1.2Gb.
My requirement is to test if a point is in the sea or not. I can do a crude query of:
SELECT
pk_uid, x, y
FROM
spt_WaterPolygons
WHERE
ST_Contains( wpolygon, MakePoint( 0.0, 0.0 ) );
And this returns in about 0.7seconds.
Then I add a spatial query and run the following query:
SELECT
pk_uid, x, y
FROM spt_WaterPolygons
WHERE
ROWID IN
(
--Is the given point within the water polygon
SELECT ROWID FROM SpatialIndex WHERE
(
( f_table_name = 'spt_WaterPolygons') AND
( search_frame = MakePoint( 177.342, -1.532) )
)
);
And this returns just about instantly. The timer registers 0.000.
This is utterly amazing and exceeds my expectations completely.
Fantastic. Thank you.
Best, Chris