回复： How to make the left join to use spatial index？

[可为 <mw...@qq.com.INVALID>]

Hello expert，
   When I use sedona sql to excecute join query, Can I build spatial index firstly mannully, rather than let it build index automaticlly?Because I want to reuse this spatial index, instead of recreating the spatial index every time when executing multiple sql queries.
   Another problem，the fllowing sql also trigger "build index on the fly"  two times.


The sql
 val sql1 = "select a.id as aid , a.geom as ageom,b.id as bid,b.geom as bgeom from targetfeature a , inputfeature b where " +  "st_intersects(ST_GeomFromWKB(a.geom), ST_GeomFromWKB(b.geom))" +
"union all select a.id as aid ,null as ageom, null as bid , null as bgeom from targetfeature a where" +
  "   a.id not in(select a.id from targetfeature a , inputfeature b where st_intersects(ST_GeomFromWKB(a.geom), ST_GeomFromWKB(b.geom)))  "The Log：
WARN JoinQuery: UseIndex is true, but no index exists. Will build index on the fly.


可为
mwhei@qq.com



 




------------------ 原始邮件 ------------------
发件人:                                                                                                                        "Jia Yu"                                                                                    <jiayu@apache.org&gt;;
发送时间:&nbsp;2023年2月6日(星期一) 下午4:25
收件人:&nbsp;"dev"<dev@sedona.apache.org&gt;;"可为"<mwhei@qq.com&gt;;

主题:&nbsp;Re: How to make the left join to use spatial index？



Hi,

1. Sedona left join does not utilize the Sedona optimized spatial join algorithm, which subsequently means it will not use spatial indexes.
2. One possible alternative is to use Sedona indexed Broadcast join which supports all sorts of join types. See&nbsp;https://github.com/apache/sedona/pull/711


Thanks,
Jia


On Sun, Feb 5, 2023 at 9:30 PM 可为 <mwhei@qq.com.invalid&gt; wrote:

Hello expert，
 &amp;nbsp; &amp;nbsp; 
 &amp;nbsp; &amp;nbsp; How to make the left join to use spatial index？
 
 e.g.val sql = "select&nbsp; a.id as aid,b.id as bid&nbsp; from targetfeature a LEFT OUTER JOIN inputfeature b&nbsp; &nbsp;on&nbsp; ST_Intersects(ST_GeomFromWKB(a.geom), ST_GeomFromWKB(b.geom)) " 
 
 
 
 Thanks.