Spatial query on table with two spatial indexes

[ckgoo...@gmail.com]

Hello.  I have a query on my table which uses a spatial index to include only polygons that contain a point.  This works fine but I’ve recently added a second geometry column storing a point geometry which I’ve also created a spatial index for.

Is there a way to force my query to use the polygon index?

 

Here is my query and apologies for any poor layout:

--Return 1 if given point is in a land polygon or 0 otherwise

SELECT   CASE WHEN EXISTS (

  SELECT 1

  FROM spt_LandPolygons  AS l

  WHERE l.ROWID IN (

    SELECT ROWID FROM SpatialIndex WHERE (

      ( f_table_name = 'spt_LandPolygons' )

      AND ( search_frame = MakePoint( ?,? ) )

    )

  )

AND ST_Contains( l.lpolygon , MakePoint(  ?,? ))

  )

THEN 1 ELSE 0 END AS PointInside;

 

Best, Chris

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

On Fri, 22 Nov 2024 20:34:48 -0000, ckgoo...@gmail.com wrote:
> SELECT ROWID FROM SpatialIndex WHERE (
> ( f_table_name = 'spt_LandPolygons' )
> AND ( search_frame = MakePoint( ?,? ) )
>

> This works fine but I've recently added a second
> geometry column storing a point geometry which
> I've also created a spatial index for.
> Is there a way to force my query to use the
> polygon index?
>

Hi Chris,

normally we have a single geometry column in a t
able, and in this case it's enough to simply
pass the name of the table to the SpatialIndex
virtual interface (f_table_name).

but when the geometries become two or more this
is no longer sufficient and it becomes essential
to also specify the name of the column to which
we intend to refer (f_column_name), as in:

WHERE f_table_name = 'tbl' AND
f_geometry_column = 'geom' AND
search_frame = MakePoint(---)

the simplified form without f_geometry_column
is allowed only when there is a single geometric
column.

bye Sandro

[ckgoo...@gmail.com]

Thank you. That is perfect.
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 visit https://groups.google.com/d/msgid/spatialite-users/f45b975bc9d9d1c396af88346a7d6c70%40lqt.it.