Test Spatialite spatial index queries

spatialite_spatial_index_test.sql

-- Spatialite queries test (cached result, total records: 2,178,275)

 
.timer on
.header on
.separator \t
.mode list
 
 
-- Seems table scan - slowest
-- (Django makes this form of query)
-- COUNT(*)
-- 5
-- CPU Time: user 0.414527 sys 2.977768
SELECT COUNT(*)
FROM location_ip2locationlitedb5    
WHERE id < 30000
AND WITHIN(
    location_ip2locationlitedb5.coord_latlng,
    GeomFromText("POLYGON ((-123.1460000000000008 36.4579999999999984, -121.1460000000000008 36.4579999999999984, -121.1460000000000008 38.4579999999999984, -123.1460000000000008 38.4579999999999984, -123.1460000000000008 36.4579999999999984))",4326)
);
 
-- Same with above
-- COUNT(*)    area
-- 5   
-- CPU Time: user 0.391106 sys 2.837311
SELECT COUNT(*), GeomFromText("POLYGON ((-123.1460000000000008 36.4579999999999984, -121.1460000000000008 36.4579999999999984, -121.1460000000000008 38.4579999999999984, -123.1460000000000008 38.4579999999999984, -123.1460000000000008 36.4579999999999984))",4326) as area
FROM location_ip2locationlitedb5
WHERE id < 30000
AND WITHIN(
    location_ip2locationlitedb5.coord_latlng,
    area
);


-- from here, full count

-- Just geom into FROM clause
-- COUNT(*)
-- 68907
-- CPU Time: user 3.771116 sys 2.843178
SELECT COUNT(*)
FROM 
    location_ip2locationlitedb5 l,
    (SELECT GeomFromText("POLYGON ((-123.1460000000000008 36.4579999999999984, -121.1460000000000008 36.4579999999999984, -121.1460000000000008 38.4579999999999984, -123.1460000000000008 38.4579999999999984, -123.1460000000000008 36.4579999999999984))",4326) as area) a
WHERE WITHIN(
    l.coord_latlng,
    a.area
);

 
-- Seems using index, fastest
-- COUNT(*)
-- 68907
-- CPU Time: user 0.145015 sys 0.044895
SELECT COUNT(*) FROM location_ip2locationlitedb5
WHERE ROWID IN (
    SELECT pkid FROM idx_location_ip2locationlitedb5_coord_latlng
    WHERE xmin > -123.1460000000000008 AND xmax < -121.1460000000000008
      AND ymin > 36.4579999999999984 AND ymax < 38.4579999999999984
);
 
-- Faster
-- COUNT(*)
-- 68907
-- CPU Time: user 0.311416 sys 0.046112
SELECT COUNT(*)
FROM
    location_ip2locationlitedb5 l,
    (SELECT GeomFromText("POLYGON ((-123.1460000000000008 36.4579999999999984, -121.1460000000000008 36.4579999999999984, -121.1460000000000008 38.4579999999999984, -123.1460000000000008 38.4579999999999984, -123.1460000000000008 36.4579999999999984))",4326) as area) s
WHERE
    WITHIN(
        l.coord_latlng, s.area
    )
AND l.ROWID IN (
    SELECT ROWID FROM SpatialIndex
    WHERE f_table_name='location_ip2locationlitedb5'
    AND f_geometry_column='coord_latlng'
    AND search_frame = s.area
);

-- Using X, Y method
-- COUNT(*)
-- 68907
-- CPU Time: user 2.152307 sys 0.514628
SELECT COUNT(*) FROM location_ip2locationlitedb5
WHERE 
X(coord_latlng) > -123.1460000000000008 AND X(coord_latlng) < -121.1460000000000008
AND Y(coord_latlng) > 36.4579999999999984 AND Y(coord_latlng) < 38.4579999999999984
;
 
-- Seems to use index, but little slower
-- COUNT(*)    area
-- 68907   
-- CPU Time: user 1.647494 sys 4.501523
SELECT COUNT(*), GeomFromText("POLYGON ((-123.1460000000000008 36.4579999999999984, -121.1460000000000008 36.4579999999999984, -121.1460000000000008 38.4579999999999984, -123.1460000000000008 38.4579999999999984, -123.1460000000000008 36.4579999999999984))",4326) as area
FROM location_ip2locationlitedb5
WHERE
    WITHIN(
        coord_latlng,
        area
    )
AND ROWID IN (
    SELECT ROWID FROM SpatialIndex
    WHERE f_table_name='location_ip2locationlitedb5'
    AND f_geometry_column='coord_latlng'
    AND search_frame = area
);
 

-- Just use float index
-- COUNT(*)
-- 68907
-- CPU Time: user 0.567073 sys 0.457566
SELECT COUNT(*) FROM location_ip2locationlitedb5
WHERE 
    longitude > -123.1460000000000008 and 
    longitude < -121.1460000000000008 and 
    latitude > 36.4579999999999984 and 
    latitude < 38.4579999999999984
;


-- use PtDistWithin, by meters
-- COUNT(*)
-- 67535
-- CPU Time: user 2.275908 sys 0.472775
SELECT COUNT(*)
FROM
    location_ip2locationlitedb5 l,
    (SELECT MakePoint(-122.1460000000000008, 37.4579999999999984, 4326) as point) s
WHERE
    PtDistWithin(
        l.coord_latlng, s.point, 100000  -- 100km
    );

-- use PtDistWithin, by meters. taking little more time than above
-- COUNT(*)
-- 67535
-- CPU Time: user 2.855052 sys 0.516057
SELECT COUNT(*)
FROM
    location_ip2locationlitedb5
WHERE
    PtDistWithin(
        coord_latlng, 
        MakePoint(-122.1460000000000008, 37.4579999999999984, 4326),
        100000  -- 100km
    );

-- use PtDistWithin with SpatialIndex
-- COUNT(*)
-- 67469
-- CPU Time: user 0.217240 sys 0.044238
SELECT COUNT(*)
FROM
    location_ip2locationlitedb5 l,
    (SELECT MakePoint(-122.1460000000000008, 37.4579999999999984, 4326) as point) s,
    (SELECT MakeCircle("POLYGON ((-123.1460000000000008 36.4579999999999984, -121.1460000000000008 36.4579999999999984, -121.1460000000000008 38.4579999999999984, -123.1460000000000008 38.4579999999999984, -123.1460000000000008 36.4579999999999984))",4326) as area) a
WHERE
    PtDistWithin(
        l.coord_latlng, s.point, 100000  -- 100km
    )
AND l.ROWID IN (
    SELECT ROWID FROM SpatialIndex
    WHERE f_table_name='location_ip2locationlitedb5'
    AND f_geometry_column='coord_latlng'
    AND search_frame = a.area
);

-- use MakeCircularSector, WITHIN
-- COUNT(*)
-- 67692
-- CPU Time: user 3.857318 sys 0.990327
SELECT COUNT(*)
FROM
    location_ip2locationlitedb5 l,
    (SELECT MakeCircularSector(-122.1460000000000008, 37.4579999999999984, 1, 0, 359.999999999999, 4326) as area) a
WHERE WITHIN(
        coord_latlng,
        a.area
    );

-- use MakeCircularSector, WITHIN, SpatialIndex
-- COUNT(*)
-- 67692
-- CPU Time: user 0.407509 sys 0.054294
SELECT COUNT(*)
FROM
    location_ip2locationlitedb5 l,
    (SELECT MakeCircularSector(-122.1460000000000008, 37.4579999999999984, 1, 0, 359.999999999999, 4326) as area) a
WHERE WITHIN(
        coord_latlng,
        a.area
    )
AND l.ROWID IN (
    SELECT ROWID FROM SpatialIndex
    WHERE f_table_name='location_ip2locationlitedb5'
    AND f_geometry_column='coord_latlng'
    AND search_frame = a.area
);