Finding point closest to point on near feature?

The logic of the Spatialite process will be:

1) Find the road that is closest to the reference point with

select 1.ID,2.ID,Min(Distance(1.geometry,2.geometry);

2) Find the closest point of the closest road that was just identified

select ClosestPoint(1.geometry,2.geometry) from 1,2 where 1.ID=x and 2.ID=y;

3) Tune the subquery to make the search from the spatial b-tree index of Spatialite so that it does not drop correct hits from the resultset but it still gives speed-up for the query.

Edit: Test procedure and results to prove that it is possible

It is often better to do the job step-by-step by using temporary tables. I made a line layer with 1602 features and point layer with 294 points and made a simple test.

First step is to create a table that select the closest road from each point and saves the result into a new table "mindist". Point IDs and line IDs are saved into the table for the next step. This is the slow part and spatial index would make the query faster.

create table mindist as
select "p"."ogc_fid"  pid, 
"l"."ogc_fid"  lid, 
min(Distance("p"."geometry","l"."geometry")) as distance
from "poi" p, "lin" l
group by pid
order by pid;

The next step is to go through the temporary table and select for each row the closest point from the geometry of the road to the point geometry. Points and lines are found with the IDs and because they are primary keys in the main tables the query will be fast.

create table closest_point as
select m.rowid as pid, lid, 
closestpoint(l.geometry,p.geometry)as geometry
from mindist m, lin l, poi p
where m.pid=p.ogc_fid
and m.lid=l.ogc_fid
group by m.rowid;

This image shows the results. Lines are the original line layer (motorways), red dots are the original points (villages) and stars show the closest point on a motorway from the villages. Results look visually good to me.