Spatial-Views support

[Mark Johnson]

I have been having problems in getting Spatial-Views showing properly in geopaparazzi.

The problem was, that although they were showing up correctly in QGIS, there was no results in geopaparazzi.
- they were showing up in the Spatialite-Data list, but failed while using the Spatialindex
-- it was returning no results


SELECT ST_AsBinary(CastToXY(ST_Transform(map_linestring,4326))),karte_nummer FROM berlin_1000 WHERE ST_Intersects(map_linestring, ST_Transform(BuildMBR(13.410767,52.522871,13.417204,52.51708,4326),3068)) = 1 AND ROWID IN (SELECT ROWID FROM Spatialindex WHERE f_table_name ='berlin_1000' AND f_geometry_column = 'map_linestring' AND search_frame = ST_Transform(BuildMBR(13.410767,52.522871,13.417204,52.51708,4326),3068));

The cause was that 'ROWID' is being hard-coded in the query from our side.
- for tables this is no problem, but for views it is - because by default they have no ROWID

When a Spatial-View is created useing an alias called 'rowid' (everything MUST be in small letters with spatial-views)
- the view shows up correctly in geopaparazzi, with labels

In the long term we must add a ROW_PK field into SpatialVectorTable, which by default will be 'ROWID', but for views be replaced by the value found in
- views_geometry_columns.view_rowid
and this value must be used in
- SpatialiteUtilities.buildGeometriesInBoundsQuery
-- replacing the hard-coded 'ROWID' with the given value

Only in this way will spatial-views be shown correctly

I have uploaded to:
http://www.mj10777.de/public/download/geopaparazzi/maps_geometrys/
- maps_geometrys.db
and the sql used to create it:
create_maps_geometrys.sql

It MAY be possible that the present version of geopaparazzi can read these views
- spatialite version 3.0.1 may not work
- with the 'spatialite_views' is works correctly (using spatialite 4.1.1)

There are views in 'map_geometries' showing the extent of standard maps in berlin
- scales 1:1000 ; 1.4000 ; 1:5000 ; 1:25000 ; 1:50000 ; 1:100000
All of which reside in one table called maps_geometry
- the view show on specific scale
-- View: berlin_1000
-- will show the extend and map number of the 1:1000 maps
---> 20140320.view_scale_1000.png

Mark

[Mark Johnson]

On Thursday, 20 March 2014 13:21:27 UTC+1, Mark Johnson wrote:

I have been having problems in getting Spatial-Views showing properly in geopaparazzi.

The problem was, that although they were showing up correctly in QGIS, there was no results in geopaparazzi.
- they were showing up in the Spatialite-Data list, but failed while using the Spatialindex
-- it was returning no results


SELECT ST_AsBinary(CastToXY(ST_Transform(map_linestring,4326))),karte_nummer FROM berlin_1000 WHERE ST_Intersects(map_linestring, ST_Transform(BuildMBR(13.410767,52.522871,13.417204,52.51708,4326),3068)) = 1 AND ROWID IN (SELECT ROWID FROM Spatialindex WHERE f_table_name ='berlin_1000' AND f_geometry_column = 'map_linestring' AND search_frame = ST_Transform(BuildMBR(13.410767,52.522871,13.417204,52.51708,4326),3068));

The cause was that 'ROWID' is being hard-coded in the query from our side.
- for tables this is no problem, but for views it is - because by default they have no ROWID

When a Spatial-View is created useing an alias called 'rowid' (everything MUST be in small letters with spatial-views)
- the view shows up correctly in geopaparazzi, with labels

In the long term we must add a ROW_PK field into SpatialVectorTable, which by default will be 'ROWID', but for views be replaced by the value found in
- views_geometry_columns.view_rowid
and this value must be used in
- SpatialiteUtilities.buildGeometriesInBoundsQuery
-- replacing the hard-coded 'ROWID' with the given value

The needed changes have been made to the spatialite_views branch.

Mark