I have a dataset containing GPS points I store in a table with a geometry column. I use the following code to populate my table.
CREATE TABLE STOPS_GEO (stop_id TEXT PRIMARY KEY,stop_desc TEXT,stop_place TEXT,pole_key TEXT,stop_operator TEXT,stop_lat DOUBLE,stop_lon DOUBLE);SELECT AddGeometryColumn('STOPS_GEO', 'stop_geometry', 31370, 'POINT', 2, 1);INSERT INTO STOPS_GEO (stop_id, stop_desc, stop_place, pole_key, stop_operator, stop_lat, stop_lon)SELECT stop_id, stop_desc, stop_place, ('') pole_key, stop_operator, stop_lat, stop_lonFROM STOPS_CSVWHERE stop_lat IS NOT null AND stop_lon IS NOT null;UPDATE STOPS_GEO SET stop_geometry = Transform(MakePoint(stop_lon, stop_lat, 4326), 31370);I've created a spatial-index
SELECT CreateSpatialIndex('STOPS_GEO', 'stop_geometry');But when I try to use the index as filter to narrow search with this kind of query no results are returned. Results are returned if I use a huge MBR Circle radius, but they are inconsistent.
SELECT *FROM STOPS_GEO AS S WHERE S.stop_id IN(SELECT ROWID FROM SpatialIndex WHERE f_table_name='STOPS_GEO' AND search_frame=BuildCircleMbr(X(Transform(MakePoint(4.336527, 50.835705, 4326), 31370)) ,Y(Transform(MakePoint(4.336527, 50.835705, 4326), 31370)), 10, 31370))When I have a look at the data stored in the spatial-index I've noticed the X and Y values don't add up with the position of my points.
How come the data stored in the spatial-index is not consistent with the input-data ? Where did it all go wrong ?!
أكثر...
CREATE TABLE STOPS_GEO (stop_id TEXT PRIMARY KEY,stop_desc TEXT,stop_place TEXT,pole_key TEXT,stop_operator TEXT,stop_lat DOUBLE,stop_lon DOUBLE);SELECT AddGeometryColumn('STOPS_GEO', 'stop_geometry', 31370, 'POINT', 2, 1);INSERT INTO STOPS_GEO (stop_id, stop_desc, stop_place, pole_key, stop_operator, stop_lat, stop_lon)SELECT stop_id, stop_desc, stop_place, ('') pole_key, stop_operator, stop_lat, stop_lonFROM STOPS_CSVWHERE stop_lat IS NOT null AND stop_lon IS NOT null;UPDATE STOPS_GEO SET stop_geometry = Transform(MakePoint(stop_lon, stop_lat, 4326), 31370);I've created a spatial-index
SELECT CreateSpatialIndex('STOPS_GEO', 'stop_geometry');But when I try to use the index as filter to narrow search with this kind of query no results are returned. Results are returned if I use a huge MBR Circle radius, but they are inconsistent.
SELECT *FROM STOPS_GEO AS S WHERE S.stop_id IN(SELECT ROWID FROM SpatialIndex WHERE f_table_name='STOPS_GEO' AND search_frame=BuildCircleMbr(X(Transform(MakePoint(4.336527, 50.835705, 4326), 31370)) ,Y(Transform(MakePoint(4.336527, 50.835705, 4326), 31370)), 10, 31370))When I have a look at the data stored in the spatial-index I've noticed the X and Y values don't add up with the position of my points.
How come the data stored in the spatial-index is not consistent with the input-data ? Where did it all go wrong ?!
أكثر...