Problem with search_frame in my query!

[Jure Demsar]

We have the PARC table with 400,000 land parcels and the PIPE table with 20,000 pipes.

You will find below a simple query that finds all the parcels intersecting with the pipes buffer of 10 meters.

For the one-pipe buffer (id = 155) and search_frame = pi.geometry the query result is correct.

With search_frame = pa.geometry the result is INcorrect.

Unfortunately, there is no chance of finding results for ALL pipes.

Please suggest on how to reconstruct the query (Spatialite_gui 1.6.0).

SELECT pa.parc_id, pa.geometry 

FROM PIPE AS pi, PARC AS pa

WHERE  pi.id = '155'

and ST_Intersects(buffer(pi.geometry, 10), pa.geometry)

AND pi.ROWID IN

(

SELECT ROWID FROM SpatialIndex

WHERE f_table_name = 'pipe'

AND search_frame = pi.geometry

)

group by pa.pk_uid

[mj10777]

On Tuesday, 6 October 2020 at 19:58:51 UTC+2 jdem...@gmail.com wrote:

We have the PARC table with 400,000 land parcels and the PIPE table with 20,000 pipes.

You will find below a simple query that finds all the parcels intersecting with the pipes buffer of 10 meters.

For the one-pipe buffer (id = 155) and search_frame = pi.geometry the query result is correct.

With search_frame = pa.geometry the result is INcorrect.

Unfortunately, there is no chance of finding results for ALL pipes.

Please suggest on how to reconstruct the query (Spatialite_gui 1.6.0).

If I am reading the sql correctly, you already have your result with the  ST_Intersects, so the SpatialIndex call is useless. 

The goal of the SpatialIndex is to filter OUT PARK records that are not needed. 

This must come first within the WHERE statement, since the SpatialIndex is swift and the ST_Intesect command slow and should only be done for likely candidates. 

AND pa.ROWID IN

( // find parcels that contain the BoundingBox of the pipe + buffer

 SELECT ROWID FROM SpatialIndex

 WHERE 

  f_table_name = 'park' AND

  search_frame = ST_Buffer(pi.geometry, 10)

) 

AND

( // executed only for parcels that contains the pipe

 ST_Intersects(ST_Buffer(pi.geometry, 10), pa.geometry)

) 

group by pa.pk_uid

SELECT pa.parc_id, pa.geometry 

FROM PIPE AS pi, PARC AS pa

WHERE  pi.id = '155'

and ST_Intersects(buffer(pi.geometry, 10), pa.geometry)

AND pi.ROWID IN

(

SELECT ROWID FROM SpatialIndex

WHERE f_table_name = 'pipe'

AND search_frame = pi.geometry

)

group by pa.pk_uid

Note: the ST_Buffer may not be needed, replace with BoundingBox(pi. geometry)? 

See SpatialIndex sample here:

https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.04.html#topic_SpatialIndex_as_BoundingBox 

Mark

[mj10777]

'search_frame' only uses the BoundingBox of a geometry, so there is no need to supply the command (only the geometry). 

[Jure Demsar]

Mark, thanks for a quick respond!

If I reorder my query, as you proposed, it returns an empty ResultSet!

I am worried about running the query through all the 20,000 pipes from the PIPE table.

Any idea?

SELECT pa.parc_id, pa.geometry 

FROM PIPE AS pi, PARC AS pa

WHERE  pi.id = '155'         

AND pa.ROWID IN

( 

 SELECT ROWID FROM SpatialIndex

 WHERE 

  f_table_name = 'park' AND

  search_frame = ST_Buffer(pi.geometry, 10)

) 

AND

(

 ST_Intersects(ST_Buffer(pi.geometry, 10), pa.geometry)

) 

group by pa.pk_uid

I shall study the example recipe#16a in cookbook again.

Regards, Jure

[mj10777]

On Wednesday, 7 October 2020 at 13:40:17 UTC+2 jdem...@gmail.com wrote:

Mark, thanks for a quick respond!

If I reorder my query, as you proposed, it returns an empty ResultSet!

I am worried about running the query through all the 20,000 pipes from the PIPE table.

Any idea?

SELECT pa.parc_id, pa.geometry 

FROM PIPE AS pi, PARC AS pa

WHERE  pi.id = '155'         

AND pa.ROWID IN

( 

 SELECT ROWID FROM SpatialIndex

 WHERE 

  f_table_name = 'park' AND

You hopefully didn't repeat my spelling error? 

[Jure Demsar]

I was able to figure out what was wrong.

Again created PARC and PIPE tables and ran AddGeometryColumn and CreateSpatialIndex command.

Now results seems to be correct.

The query now investigates the reduced data set (90,000 parcels and 7,000 pipes) and it takes 35 seconds to find out 

25.000 parcels within 10 meters buffer. And 5 minutes to find 44,000 parcels within 100 meters buffer.

How long will it take for about  400,000 land parcels (POLYGON) and 20,000 pipes (LINESTRING) with 100 meters buffer?

Is there any kind of approch to reduce the computing time?

My query looks like this now:

SELECT pa.id, pa.geometry FROM PIPE AS pi, PARC AS pa

WHERE  pa.ROWID IN

( 

 SELECT ROWID FROM SpatialIndex

 WHERE 

  f_table_name = 'parc' AND

  search_frame = ST_Buffer(pi.geometry, 10)

) 

AND

(

 ST_Intersects(ST_Buffer(pi.geometry, 10), pa.geometry)

)

Regards, Jure

[mj10777]

On Thursday, 8 October 2020 at 16:54:50 UTC+2 jdem...@gmail.com wrote:

I was able to figure out what was wrong.

Again created PARC and PIPE tables and ran AddGeometryColumn and CreateSpatialIndex command.

Now results seems to be correct.

The query now investigates the reduced data set (90,000 parcels and 7,000 pipes) and it takes 35 seconds to find out 

25.000 parcels within 10 meters buffer. And 5 minutes to find 44,000 parcels within 100 meters buffer.

How long will it take for about  400,000 land parcels (POLYGON) and 20,000 pipes (LINESTRING) with 100 meters buffer?

Is there any kind of approch to reduce the computing time?

My query looks like this now:

SELECT pa.id, pa.geometry FROM PIPE AS pi, PARC AS pa

WHERE  pa.ROWID IN

( 

 SELECT ROWID FROM SpatialIndex

 WHERE 

  f_table_name = 'parc' AND

  search_frame = ST_Buffer(pi.geometry, 10)

 This St_Buffer may be a bottleneck here. It is a costly command, depending on the geometry. Check if it needed at all or with the BoundingBox of pi. geometry. 

Unfortunately, there is no chance of finding results for ALL pipes.I

[Jure Demsar]

Hi, Mark!

I set the serach frame as:

search_frame = ST_Buffer(ST_Envelope(pi.geometry, 10))

I must buffer the envelope for 100 meters to find all parcels at both ends of the polyline. 

But the computing time is the same as with the previous case.

You wrote: "Unfortunately, there is no chance of finding results for ALL pipes."

Is that means that spatialite can not solve the problem finding the parcels which are under 20,000 pipes (table PIPE) 

inside 400,000 parcels (table PARC)?

Regards, Jure!