My first spatial index query
ckgoo...@gmail.com
Hi all. I think I now have a successful spatial query.
My database is quite simple with a single spt_Cities table with columns name, country and location where location is a point geometry.
It contains about 8,400 cities downloaded from Wikidata.
I created a spatial index on the location column.
The following statement retrieves cities within a circle of radius 1 which I believe is 1 degree of a point in the northwest of the United Kingdom.
SELECT
name, country,
ROUND( ST_Distance( MakePoint( -3, 55.0, 4326 ), location ), 2 ) AS distance
FROM spt_Cities
WHERE
spt_Cities.ROWID IN
(
--Is the city location within a bounding circle
SELECT ROWID FROM SpatialIndex WHERE
(
( f_table_name = 'spt_Cities') AND
( search_frame = MakeCircle( -3, 55.0, 1, 4326 ) )
)
) ORDER BY distance;
This returns me 3 cities which seem correct to me and it takes about 0.01seconds to execute on a fairly decent Windows laptop.
Do people agree this is the right approach for what I am trying to do?
Presumably I am going to be subject to the circle being distorted the further nearer the poles I go.
Next things for me to work on are testing it on a larger dataset and then using parameters so I can pass in a position and radius to search for.
Any thoughts anyone has welcome.
Best, Chris
a.fu...@lqt.it
congratulations: you've finally found the right path.
Now making your query parametric so that it can be easily
reused by a C++ function is very simple:
SELECT name, country,
ROUND( ST_Distance( MakePoint(?, ?, 4326 ), location ), 2 ) AS
distance
FROM spt_Cities
WHERE ROWID IN (
ORDER BY distance;
your C++ function will then simply have to reset the
sqlite3_stmt object, bind the parameter values and
finally execute the SQL statement.
something like this:
------------
sqlite3_stmt *stmt;
int ret;
double longitude;
double latitude;
double radius;
sqlite3_reset(stmt);
sqlite3_clear_bindings(stmt);
sqlite3_bind_double(stmt, 1, longitude);
sqlite3_bind_double(stmt, 2, latitude);
sqlite3_bind_double(stmt, 3, longitude);
sqlite3_bind_double(stmt, 4, latitude);
sqlite3_bind_double(stmt, 5, radius);
ret = sqlite3_step(stmt);
if (ret == SQLITE_ROW)
{
// process the resultset
}
-----------------
Just a side note:
ST_Distance( point, location ), 2 )
Why are you passing 2 as the third arg ?
The real effect is that you'll get a linear measurement
of the distance calculated with great accuracy using
geodetic formulas.
Which is a great thing, but I'm not entirely sure
that's your intention.
-------------
a little tip to improve this query.
Now that the search radius for the SpatiaIndex has
become a parameter, your C++ function your C++ function
might set different values depending on the latitude.
A small radius is fine near the equator, but when
you go towards the poles it may be useful to
progressively increase the radius to try to
compensate for the deformations.
Try doing some tests to see if you can get an
optimal calibration.
bye Sandro