2

I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/pp-adjacent.html

According to the tutorial, in order to find the pairs of populated places that their distance is < 1km we have to do this query

    SELECT pp1.name AS "PopulatedPlace #1",
      GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) AS "Distance (meters)",
      pp2.name AS "PopulatedPlace #2"
    FROM populated_places AS pp1,
    populated_places AS pp2
    WHERE GeodesicLength(
      MakeLine(pp1.geometry, pp2.geometry)) < 1000.0
      AND pp1.id <> pp2.id
    AND pp2.ROWID IN (
      SELECT pkid
      FROM idx_populated_places_geometry
      WHERE pkid MATCH RTreeDistWithin(
       ST_X(pp1.geometry),
       ST_Y(pp1.geometry), 0.02))
    ORDER BY 2;

Which throws an error because of no longer use of geocallback functions RTree***. I checked the update on this and this has been substituted by the use of virtual spatial index. We have to use a subquery of the form

    SELECT ROWID
    FROM SpatialIndex
    WHERE
       f_table_name = <table_name>
       AND search_frame = <some_geometry>

So i tested this

    SELECT pp1.name AS "PopulatedPlace #1",
      GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) AS "Distance (meters)",
      pp2.name AS "PopulatedPlace #2"
    FROM populated_places AS pp1,
    populated_places AS pp2
    WHERE GeodesicLength(
      MakeLine(pp1.geometry, pp2.geometry)) < 1000.0
      AND pp1.id <> pp2.id
    AND pp2.ROWID IN (
      SELECT ROWID
      FROM SpatialIndex
      WHERE
       f_table_name ='populated_places'
       AND search_frame = pp1.geometry)
    ORDER BY 2;

And the result set was awfully wrong

   PopulatedPlace #1    Distance (meters)   PopulatedPlace #2
              Ariano    0.000000             Ariano
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
              Ariano    0.000000             Ariano

Not giving me the pairs of places that their distance is <1km. Am I using a wrong spatial index subquery? What i'm searching for is how to use properly the subquery to spatial index in order to obtain a number of rows of the table populated_places that satisfy a condition (pairs of places that are <1km away of each other in the above example)

NOTE: populated_places.geometry is of type POINT-2D and of srid=4236.

UPDATE: In case I do this :

    SELECT pp1.name AS "PopulatedPlace #1",
      GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) AS "Distance(meters)",
      pp2.name AS "PopulatedPlace #2"
    FROM populated_places AS pp1,
     populated_places AS pp2
    WHERE GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) < 10000.0
    AND pp1.id <> pp2.id
    AND pp2.id IN (234,235,236,237)

I get correct results but only for distances <10km. It seems as if there are not distances<1km which is false.

  • what exactly is unclear about this question?? – mort32 May 26 '15 at 15:39
  • are you interested in virtual spatial index? or distance function on geometry? you ask 2 vague questions. How do they have to do with the title. You should understand that you are trying to give an "answerer" all the information they need to answer. How was the result set "awfully wrong? It is difficult to get used to the question format here but it works really well once you ask questions so they can be answered. Most people won't donate hrs of time to recreate your scenario. hope this helps. – Brad Nesom May 26 '15 at 15:47
  • For sure i was not interested in the mathematical notion and background of distance function. Excuse me for that, english is not my mother tongue. What i'm searching for is how to use properly the subquery to spatial index in order to obtain a number of rows of the table populated_places that satisfy a condition (pairs of places that are <1km away of each other in the above example). – mort32 May 26 '15 at 15:57
  • Does your query give right result without the subquery to spatial index? – user30184 May 26 '15 at 17:15
  • @mort32 please do not add comments to explain. Edit the original question with more information. – Brad Nesom May 26 '15 at 17:17
3

The way that Spatialite handles spatial indexes has changed since the cookbook was written (it's now a few years old), and instead of R-Tree it uses the virtual spatial index. Your revised statement was close to being correct, but you didn't indicate the distance in which you wanted to select the points. You had:

 WHERE
   f_table_name ='populated_places'
   AND search_frame = pp1.geometry)

Which is only going to give you places that have overlapping points. Try this:

WHERE
   f_table_name ='populated_places'
   AND search_frame = buffer(pp1.geometry,0.02))

To get all places within 1000 meters of each other. I'm not sure if this is the most efficient way of doing this - but I tried it on the data set and it worked. (I happened to have done this tutorial a few years ago and still had the data close at hand). If you were working with polygons, the search frame would be the bounding box of the geometry of the polygon. But since you are working with points (and points within the same layer) you have to provide some input that indicates the search frame, the distance around the point.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.