A polygon crossing the international date line

[ckgoo...@gmail.com]

Hello.  I have imported sea polygons from shapefiles of seas and oceans into my database.  These shapefiles give me names of bodies of water which is why I’m interested in them.  The problem is that when I test what polygons include a point such as ( 5, 55 ) I get back the North Sea and North Atlantic as I would expect but I also get back the North Pacific and Bering Sea which isn’t correct.

When I query the MbrMinX, MbrMinY, MbrMaxX and MbrMaxY I get that the Bering Sea has a minx of -180 and a maxX of +180.  So it goes all the way around the world.

The Bering Sea is of course much smaller than this.

How can I get the results I need when polygons cross the 180 degree meridian?

Best, Chris

[a.fu...@lqt.it]

On Thu, 8 Aug 2024 22:59:11 +0100, ckgoo...@gmail.com wrote:
> How can I get the results I need when polygons cross the 180 degree
> meridian?
>

Hi Chris,

always remember that the SpatialIndex is a very efficient and very fast
filter, but that it's also very imprecise because it takes into
consideration exclusively the MBRs and not the Geometries as such.

The WHERE clause of your Spatial Queries should never be limited to
simply query the SpatialIndex.
It must also contain a function that checks whether a real intersection
exists between the two Geometries.

Example #1 (wrong)
==================
SELECT a, b, c
FROM some_table
WHERE rowid IN (
SELECT rowid FROM SpatialIndex
WHERE f_table_name = 'some_table'
AND search_frame = MakePoint(...));

Note: the inner query on the Spatial Index may contain rows
presenting an intersection between the MBRs but not between
the Geometries themselves.


Example #2 (correct)
====================
SELECT a, b, c
FROM some_table
WHERE ST_Intersects(geom, MakePoint(...)) = 1
AND rowid IN (
SELECT rowid FROM SpatialIndex
WHERE f_table_name = 'some_table'
AND search_frame = MakePoint(...));

Note: in this second case, however, ST_Intersects() will
verify the presence of a real intersection between the
two geometries, thus refining the resultset until it's
accurate.

This requires a little extra work, but the negative
impact on speed is very modest because you are operating
on a resultset that's already filtered by the SpatialIndex.

bye Sandro

[ckgoo...@gmail.com]

Hi Sandro,
In your good example, the Intersects call happens before the index filter. Does this mean the Intersect call would get evaluated first making the index filter redundant, or is the order not important?
Best, Chris

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/c4e0eee96d271d8a23b1968b6add22f5%40lqt.it.

[a.fu...@lqt.it]

On Fri, 9 Aug 2024 07:16:40 +0100, ckgoo...@gmail.com wrote:
> Hi Sandro,
> In your good example, the Intersects call happens before the index
> filter. Does this mean the Intersect call would get evaluated first
> making the index filter redundant, or is the order not important?
>

Hi Chris,

SQLite has a Query Planner (aka Query Optimizer) module that always
rewrites your query to make it as optimized as possible.

https://www.sqlite.org/optoverview.html

The order in which the various operations will be performed is
almost never the one literally defined in the query, it's
instead the one that will be decided by the Query Planner.

You can easily examine the query strategy that will effectively
be adopted by using an EXPLAIN QUERY PLAN statement, as in

EXPLAIN QUERY PLAN
SELECT * FROM my_table
WHERE var1 > 10 AND var2 < 100
ORDER BY var3;

BTW this is a very powerful tool for writing highly optimized
queryes because it makes easy identifying where bottlenecks are.

short conclusion: ST_Intersects() will be evaluated only
_AFTER_ querying the SpatialIndex.

bye Sandro

[Mark Johnson]

That should read 'should' be called after the spatialindex.

The SpatialIndex call should filter out all geometries that are out of range, so that ST_Intersects() will only be called (after 'AND') for geometries that are within the area of interest.

The Query Planner can only plan for original (buildin) sqlite functions. It doesn't have the faintest clue about external functions, such as Spatialite.

Mark

bye Sandro

[ckgoo...@gmail.com]

Hi,
This looks like it is working now. I've incorporated my query into my C++ program and I can now use my cursor keys to move around the world and get feedback on whether my current position is in the sea or not. I've also included data from IHO and repeated the query when I am in the sea to get the name of that sea.
And it looks like the execution time is about 0.1seconds. Certainly from a usability point of view this is absolutely fine.
Thanks for your help in getting me to this point.
Next stop is spatialite_osm_map to organise my land data.

Best, Chris


-----Original Message-----
From: spatiali...@googlegroups.com <spatiali...@googlegroups.com> On Behalf Of a.fu...@lqt.it

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/4948547497b94b96d06308a5c3988c4e%40lqt.it.

[a.fu...@lqt.it]

On Sat, 10 Aug 2024 08:30:39 +0100, ckgoo...@gmail.com wrote:
> Next stop is spatialite_osm_map to organise my land data.
>

Hi Chris,

know that this is the longest and most painful path of all.

I personally wouldn't even consider the idea of ​​using
spatialite_osm_map and would instead download all the
shapefiles I need directly from here:

https://download.geofabrik.de/

All datasets are selected by continent / state /
region / county, and the shapefiles are organized
by categories (landuse, natural, places, roads,
railways and so on).

I think it saves you a lot of effort (and time).

bye Sandro