Spatialite views examples

spatialite_views.sql

# spatialite views voorbeelden
# zie ook: http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/sp-view.html
##########################
### sewerage_pipe_view ###
##########################
CREATE VIEW sewerage_pipe_view AS
SELECT c.ROWID AS ROWID,
c.id AS id,
c.display_name AS display_name,
c.sewerage_type AS sewerage_type,
c.calculation_type AS calculation_type,
c.manhole_start_id AS manhole_start_id,
c.manhole_end_id AS manhole_end_id,
c.invert_level_start_point AS invert_level_start_point,
c.invert_level_end_point AS invert_level_end_point,
c.crosssection_definition_id AS crosssection_definition_id,
c.friction_type AS friction_type,
c.friction_value AS friction_value,
c.dist_calc_points AS dist_calc_points,
c.material AS material,
c.pipe_quality AS pipe_quality,
c.original_length AS original_length,
c.zoom_category AS zoom_category,
MakeLine(a.the_geom, b.the_geom) AS the_geom
FROM sewerage_pipe AS c,
sewerage_manhole AS a,
sewerage_manhole AS b
WHERE c.manhole_start_id = a.id
AND c.manhole_end_id = b.id
###########################

##############################
### output_component4_view ###
##############################
CREATE VIEW output_component4_view AS 
SELECT a.ROWID AS ROWID, 
a.bgpid AS bgpid, 
a.geometry AS geometry, 
b.bgpid AS bgpid, 
b.village_name AS village_name
FROM wmg a, output_component3 b 
WHERE a.bgpid=b.wmg_bgpid

INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column)
VALUES ('output_component4_view', 'geometry', 'ROWID', 'wmg', 'geometry');
##############################

##################
### test2_view ###
##################
CREATE VIEW test2_view AS 
SELECT 
c.ROWID AS ROWID,
c.bgpid AS bgpid, 
c.geometry AS geometry 
FROM wmg AS c

INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column)
VALUES ('test2_view', 'geometry', 'ROWID', 'wmg', 'geometry');
##################