Success with testing point in the sea

11 views
Skip to first unread message

ckgoo...@gmail.com

unread,
Aug 7, 2024, 6:05:43 PMAug 7
to spatiali...@googlegroups.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

unread,
Aug 8, 2024, 10:54:48 AMAug 8
to spatiali...@googlegroups.com
On Wed, 7 Aug 2024 20:48:59 +0100, ckgoo...@gmail.com wrote:
> Based on OpenStreetMap coastline data, there are water polygon
> shapefiles available at:
>
> https://osmdata.openstreetmap.de/data/water-polygons.html [1]
>

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.
>

The SpatialIndex based on R*Tree is truly impressive in speed.
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

Reply all
Reply to author
Forward
0 new messages