Spatial join and spatial index

[ludovico frate]

Hi,

I have two dataset, one representing 1.200.000 points and the other 1.200.000 square cells, where each point falls inside one square (a kind of 1:1 relationship). I would like to perform a spatial join between the two datasets, using spatial index.

The query that I used to perform a simple spatial join was:

SELECT r.PK_UID as rowid, r.ID_CELLA as id_cella, r.geometry as geom, p.COD_90 as cod_90, p.COD_00 as cod_00, p.COD_08 as cod_08

FROM iuti_punti as p, iuti_reticolo as r

WHERE ST_CONTAINS (r.geometry, p.geometry) = 1

but this takes a lot of time (not sure if the process will be completed).

Thus, I've tried to implement spatial index into my query (after creating the index), but without success:

SELECT r.PK_UID as rowid, r.ID_CELLA as id_cella, r.geometry as geom,p.COD_90 as cod_90, p.COD_00 as cod_00, p.COD_08 as cod_08

FROM iuti_punti as p, iuti_reticolo as r

WHERE ST_CONTAINS (r.geometry, p.geometry) = 1

AND r.ROWID IN (

SELECT rowid FROM SpatialIndex

WHERE f_table_name = 'iuti_reticolo'

AND search_frame =  r.geometry

)

Any suggestions?

Thank you in advance,

Ludovico

[Peter Johnson]

Your search frame should be 'p' not 'r'.

ie: search_frame =  p.geometry

Also you should be able to remove the WHERE ST_CONTAINS statement, if they are squares then you shouldn't need this.

-P

--
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 post to this group, send email to spatiali...@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

[mj10777]

What is exactly the problem ?

* no results

* or slow results

For the second, try this

-- you are searching for a geometry in p, so use the p.geometry and not the r.geometry

SELECT

 r.PK_UID as rowid,

 r.ID_CELLA as id_cella,

 r.geometry as geom,

 p.COD_90 as cod_90,

 p.COD_00 as cod_00,

 p.COD_08 as cod_08

FROM iuti_punti as p, iuti_reticolo as r

WHERE

(

 -- filter out all rows that are out of the question, returning only candidates [fast]

 r.ROWID IN

 (

  SELECT rowid FROM SpatialIndex WHERE

  (

   (f_table_name = 'iuti_reticolo') AND  
   -- you are searching for a geometry in p

   (search_frame =  p.geometry)

  )

 ) AND

 -- check if candidates are contained in p.geometry [slow]

 (ST_CONTAINS (r.geometry, p.geometry) = 1)

);

for the first

* The SpatialIndex was created for 'iuti_reticolo'?

* when yes, does 'iuti_reticolo' have a second geometry?
* --> if yes: add (f_geometry_column = 'geometry')

Mark 

[mj10777]

On Thursday, 20 September 2018 10:35:21 UTC+2, Peter Johnson wrote:

Your search frame should be 'p' not 'r'.

ie: search_frame =  p.geometry

Also you should be able to remove the WHERE ST_CONTAINS statement, if they are squares then you shouldn't need this.

yes indeed, that too. But only because it is a square cells.

For a wiggly it would be needed. 

[ludovico frate]

Thank you Peter,

it works. The process have taken just 1 minute and 37 seconds, great!

[ludovico frate]

Yes, I have created it. 

* when yes, does 'iuti_reticolo' have a second geometry?

No, iuti reticolo has one column geometry! 

* --> if yes: add (f_geometry_column = 'geometry')

Thank you for the tip! 

[mj10777]

On Thursday, 20 September 2018 10:44:51 UTC+2, ludovico frate wrote:

On Thursday, 20 September 2018 10:20:31 UTC+2, ludovico frate wrote:

Hi,

I have two dataset, one representing 1.200.000 points and the other 1.200.000 square cells, where each point falls inside one square (a kind of 1:1 relationship). I would like to perform a spatial join between the two datasets, using spatial index.

The query that I used to perform a simple spatial join was:

SELECT r.PK_UID as rowid, r.ID_CELLA as id_cella, r.geometry as geom, p.COD_90 as cod_90, p.COD_00 as cod_00, p.COD_08 as cod_08

FROM iuti_punti as p, iuti_reticolo as r

WHERE ST_CONTAINS (r.geometry, p.geometry) = 1

but this takes a lot of time (not sure if the process will be completed).

Thus, I've tried to implement spatial index into my query (after creating the index), but without success:

SELECT r.PK_UID as rowid, r.ID_CELLA as id_cella, r.geometry as geom,p.COD_90 as cod_90, p.COD_00 as cod_00, p.COD_08 as cod_08

FROM iuti_punti as p, iuti_reticolo as r

WHERE ST_CONTAINS (r.geometry, p.geometry) = 1

AND r.ROWID IN (

SELECT rowid FROM SpatialIndex

WHERE f_table_name = 'iuti_reticolo'

AND search_frame =  r.geometry

)

Any suggestions?

We are in the process of re-writing 'Cookbook 3.0' to 'Cookbook 5.0'

It starts here:

https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/

Themes with SpatialIndex start here:

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

Not everything has been adapted yet, but the SpatialIndex portion should be complete.

Mark

[ludovico frate]

I have another problem.

The query works well.

I created a view form that query with the following script:

CREATE VIEW iuti_join as

SELECT r.PK_UID as rowid,
r.ID_CELLA as id_cella,
r.geometry as geom,
p.COD_90 as cod_90,
p.COD_00 as cod_00,
p.COD_08 as cod_08
FROM iuti_punti as p, iuti_reticolo as r
WHERE ST_CONTAINS (r.geometry, p.geometry) = 1

AND r.ROWID IN (
SELECT rowid FROM SpatialIndex
WHERE f_table_name = 'iuti_reticolo'

AND search_frame =  p.geometry
)

and then, I've registered the geometry

INSERT INTO views_geometry_columns VALUES
('iuti_join', 'geom', 'rowid', 'iuti_reticolo', 'geometry', 1)

I ve tried to perform a simple query on the view but is very slow. In addition, even if I'am able to see the geometry in spatialite gui, when I load the view in qgis 

through the DB manager the geometry is not recognized (even if in the table the field geom is present).

[mj10777]

On Thursday, 20 September 2018 11:28:15 UTC+2, ludovico frate wrote:

I have another problem.

The query works well.

I created a view form that query with the following script:

CREATE VIEW iuti_join as
SELECT r.PK_UID as rowid,
r.ID_CELLA as id_cella,
r.geometry as geom,
p.COD_90 as cod_90,
p.COD_00 as cod_00,
p.COD_08 as cod_08
FROM iuti_punti as p, iuti_reticolo as r
WHERE ST_CONTAINS (r.geometry, p.geometry) = 1
AND r.ROWID IN (
SELECT rowid FROM SpatialIndex
WHERE f_table_name = 'iuti_reticolo'
AND search_frame =  p.geometry
)

and then, I've registered the geometry

INSERT INTO views_geometry_columns VALUES
('iuti_join', 'geom', 'rowid', 'iuti_reticolo', 'geometry', 1)

I ve tried to perform a simple query on the view but is very slow.

You should read through the new Cookbook topic about the SpatialIndex then

In a nutshell:

* the SpatialIndex is very fast

* Spatial-Query are slow

If the first WHERE condition is not fulfilled, the second will not be called

* if not found in the SpatialIndex (the likely candidates), the ST_Contain is not needed

Therefore use the SpatialIndex first and then the slower Spatial-Command ST_Contains

* that is what is slowing you down

If the rectangles are always these grids, then the ST_Contain may not be needed.

A SpatialIndex is a BoundingBox of the min/max x and y values of the POLYGON.

* if this grid is the same as the BoundingBox, then ST_Contain is not needed

* if this grid is the same as the BoundingBox, the point is inside the BoundingBox when found in the SpatialIndex

 

In addition, even if I'am able to see the geometry in spatialite gui, when I load the view in qgis 

through the DB manager the geometry is not recognized (even if in the table the field geom is present).

Has an UpdateLayerStatistics been done on the table? 

Look at:

SELECT * FROM "vector_layers_statistics"

are there NULL values for those geometries?

Mark

[mj10777]

On Thursday, 20 September 2018 11:49:03 UTC+2, mj10777 wrote:

On Thursday, 20 September 2018 11:28:15 UTC+2, ludovico frate wrote:

I have another problem.

The query works well.

I created a view form that query with the following script:

CREATE VIEW iuti_join as
SELECT r.PK_UID as rowid,
r.ID_CELLA as id_cella,
r.geometry as geom,
p.COD_90 as cod_90,
p.COD_00 as cod_00,
p.COD_08 as cod_08
FROM iuti_punti as p, iuti_reticolo as r
WHERE ST_CONTAINS (r.geometry, p.geometry) = 1
AND r.ROWID IN (
SELECT rowid FROM SpatialIndex
WHERE f_table_name = 'iuti_reticolo'
AND search_frame =  p.geometry
)

and then, I've registered the geometry

INSERT INTO views_geometry_columns VALUES
('iuti_join', 'geom', 'rowid', 'iuti_reticolo', 'geometry', 1)

I ve tried to perform a simple query on the view but is very slow.

You should read through the new Cookbook topic about the SpatialIndex then

recipe #16a: SpatialIndex as BoundingBox

recipe #17: Railways vs Populated Places 

Look for 'What is faster and more efficient?'