Map - and GIS analysis

There was a posting at CAD.DE regarding a specific task to be accomplished by using Map.

Here is a short description:

A drawing file contains two different sets of point features.

point feature set 1 - blocks, including an ID (block attribute), labelled as "b1"
point feature set 2 - points, including an ID (Map object data table) labelled as "s1"

Here is a screenshot:

The task was to create a list where each point of feature class "s1" is linked to the closest point in feature class "b1".

Several suggestions were made how to solve the issue with Map - but either it involved laborious manual work or wouldn't always return the closest point. One suggestion was to use a spatial database to get the desired result.

I then decided to try whether that would be possible without installing any software and using only freely available tools.

Here is a short description of what I did using SQLite and Spatialite:

(1) export both point data sets (including block attributes and Map object data table) to SQLite using _mapexport

(2) download spatialite_gui.exe (http://www.gaia-gis.it/gaia-sins/index.html) - doesn't require an installation

(3) in spatial_lite connect to SQLite file
        Spatialite recognises the Map/FDO SQLite data model and geometry columns. There are accessible as "virtual tables" meaning that they cannot  be modified. It also seems that you cannot run spatial queries on the virtual tables - at least I didn't work for me. But I'm not experienced with spatial lite and might have just done something wrong.

(4) copy data from virtual tables to normal SQLite tables using CTAS (create table as select)
        With spatialite you need to create a geometry column in a very special way. First you have to create the table, afterwards you add the geometry column using the following statement: SELECT AddGeometryColumn(...). As I created my two tables with CTAS I did not get "proper" geometry columns. But for the task in question it didn't matter. The only drawback I noticed with my approach was that I couldn't export to SHP. This requires proper geometry columns.

(5) looking around I found some SQL statements for SQLite for similar tasks, I tried and played around a bit and found the following statement to return correct results:

SELECT
s1.featid as sn_id, b1.nr as b_nr, min(ST_Distance(s1.geom, b1.geom))  as dist, x(s1.geom) as xgeom , y(s1.geom) as ygeom
FROM b1, s1
group by s1.featid
ORDER BY s1.featid ;

(6) I created a new table using the select statement above

(7) to perform a visual check I wanted to load the result into Map again. As I had modified the SQLite database - by adding tables - Map refused to connect to the file. Anyway - even I had been able to load the file into Map it wouldn't have recognized the spatial_lite geometry column as Map/FDO and spatialite use different structures to store geometries. I therefore exported the table as CSV file.

(8) Now as I thought it would take too long to convert the CSV file into a spatial feature class using Map I just loaded the CSV file into QGIS and exported it as SHP data set which I then connected to in Map.

(9) overlaying the SHP file and the original drawing file I could visually check the correctness of the result.