Success with testing point in the sea
ckgoo...@gmail.com
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
a.fu...@lqt.it
> Based on OpenStreetMap coastline data, there are water polygon
> shapefiles available at:
>
>
Hi Chris,
If you don't already know it, I'd like to point out another
download site that offers an impressive series of shapefiles
derived from OSM:
https://download.geofabrik.de/
> And this returns just about instantly. The timer registers 0.000.
> This is utterly amazing and exceeds my expectations completely.
> Fantastic.
>
But it's completely up to you to be able to use it intelligently,
because nothing is automatic.
It's precisely for this reason that in all my previous emails
I've always encouraged you to study and understand in depth
how it works.
It's too fundamental a tool for any GeoSpatial analysis to
settle for approximate knowledge ... but I am very pleased
to note that you are starting to find your way.
bye Sandro