In the next version of Spatialite will support ISO Topology, which will help to resolve this problem.
----
Under the conditions that:
- The '8830 election precincts' are considered precise
- each 'election precinct' is a valid POLYGON (i.e. closed LINESTRING)
- that the POLYGONs do not overlap
- the outer borders of the 'election precincts' form the border of the 'House district'
The 'House district' may not be so precise as the lower level 'election precinct' areas.
So one goal would (re-)create as set of 'House district' borders, that correspond to the precise 'election precinct' borders.
---
The process would be:
- create a Topology based on the 'election precinct' areas [CreateTopology]
- import the precise ''election precinct'' [TopoGeo_FromGeoTable] into the Topology
- create a Topology-Layer base on the less precise 'House district' [TopoGeo_CreateTopoLayer]
- export the Topology-Layer to a SpatialTable using the precise 'election precinct' borders found [TopoGeo_ExportTopoLayer]
Notes:
- TopoGeo_CreateTopoLayer: the routine will compare the 'less' precise geometries, looking for common geometries in the 'precise' collection in the Topology
-- in this case the outer borders of the 'election precinct' that form the 'House district', everything else will be ignored
Look for This magnified detail directly compares the ISTAT own geometries (displayed in pink color) and the Topology geometries (blue color).'
- to get an idea what it tries to do
---
Assuming, therefore, that you have imported your source into a Spatialite-Database and that you have the present developer version of Spatialite
- compiled with '--enable-rttopo'
Using the rttopo library that can be found here:
A sql-script could look like this:
Create the Topology:
SELECT DateTime('now','localtime'),"-I-> CreateTopology topo-name[topology_admin_segments] using srid[3068]";
SELECT CreateTopology
( --topo-name to create
'topology_election precincts',
-- srid of geometries used in topo-name
3068,
-- tolerance [default=0]
0,
-- has_z [default=0 2D XY dimentions, else 1=3D XYZ]
0
);
Attach your external source:
- containing the precise 'election precinct' borders
SELECT DateTime('now','localtime'),"-I-> ATTACH DATABASE berlin_admin_geometries.db as db_import";
ATTACH DATABASE "../source_db/berlin_admin_geometries.db" AS db_import;
Import the precise ''election precinct'' borders
- the non-geometry fields will also be imported as meta-data, that will later can be exported
Note: SpatialViews are NOT supported as input, only SpatialTables
Sandro: why is this so???
SELECT TopoGeo_FromGeoTable
( --topo-name to use
'topology_election precincts',
-- db-prefex (of an ATTACHed Database, if NULL=MAIN/local-table]
'db_import',
-- table-name of external (non-topo) TABLE
'election precincts',
-- column-name of external TABLE (NULL can be used of ONLY 1 Geometry, otherwise column-name of geometry]
'precincts_borders',
-- lines-max-points [default=-1, split-line after positive point number]
NULL,
-- lines-max-length [default=-1, split-line after positive length [units of srid] number]
NULL,
-- tolerance [default=0]
0
);
Check if there were any errors, which if exist must be resolved !
SELECT DateTime('now','localtime'),"-I-> ST_ValidateTopoGeo topo-name[topology_admin_segments]";
SELECT ST_ValidateTopoGeo('topology_admin_segments');
SELECT DateTime('now','localtime'),"-I-> SELECT * FROM TEMP.topology_admin_segments_validate_topogeo [should ONLY return errors]";
SELECT * FROM TEMP.topology_admin_segments_validate_topogeo;
If no errors occur:
Create a Topology-Layer of the 'House district' borders
- where many, but not all, of its geometries will correspond to the Topology Geometries
-- even when they are NOT an exact match (point for point), the attempt will be made to say: this is what is intended
- the non-geometry fields will also be imported as meta-data, that will later be exported
Note: SpatialViews are supported as input, not only SpatialTables
SELECT TopoGeo_CreateTopoLayer
(--topo-name to use
'topology_election precincts',
-- db-prefex (of an ATTACHed Database, if NULL=MAIN/local-table]
'db_import',
-- table-name of external (non-topo) TABLE
'house_districts_texas',
-- column-name of external TABLE (NULL can be used of ONLY 1 Geometry, otherwise column-name of geometry]
'district_borders',
--topo-layer-name to create
'house_districts_texas',
--is-view
1
);
Now for the final result:
Export the Topology-Layer
- which will contain the same Meta-Data as was imported
-- but with the more precise geometries contained in the Topology
SELECT TopoGeo_ExportTopoLayer
( --topo-name to use
'topology_election precincts',
--topo-layer-name to use
'house_districts_texas',
-- table-name of export (non-topo) TABLE to create
'export_house_districts_texas',
-- with-spatial-index
1,
-- create-only [0=fill [default] ; 1=do not fill]
0
);
---
The created SpatialTable : 'export_house_districts_texas' can now be used to:
- create a new Topology for the House districts
or
be used to list all of the 'election precincts' that are within a single house_district, since there are no longer any overlapping.
Hope this helps,
Mark