intersection? of two vector shapefiles

[Jon Polacheck]

I have a shapefile of the 8830 election precincts in Texas as of the 2016 election.  I also have shapefiles of the Texas US House districts and the Texas Senate and House districts.  I want to generate tables that match the election precincts with the districts that they are contained in.

Using the functions available (Within, Contains, Touches, etc) all see to either produce too many rows, or too few.  One problem I identified is when an election precinct is matched with a district because they share a border, although the election precinct is in another district.  As far as I know, no election precincts cross any district border.

I got this far using qgis and spatiallite.  I'm a newbie at GIS.  This is my first project.  Apologies if I have left out any critical information. Any suggestions would be most welcome.

[a.fu...@lqt.it]

Hi Jon,

first of all a (pedantic) clarification: asking about the
"intersection of two shapefiles" is conceptually wrong and
someway misleading.

the Shapefile simply is a file format between many others;
the real object of interest is the GeoSpatial dataset it
contains. and you could eventually represent the same
identical dataset in many other alternative formats as e.g.
GML, GeoJSON or in some binary format internally adopted
be the most popular Spatial DBMSes (Oracle, PostGIS,
SpatiaLite itself etc).

Don't confuse the content and the container, they are
different things; and the real object of interest always
is the content, i.e. the Spatial Dataset.

coming to your specific question: Within, Contains, Touches
and alike simply are convenience wrappers based on the
standard DE-9IM model.
all them evaluate the spatial relationship between two
geometries, but they strongly differ in the criteria
applied on Boundary, Interior and Exterior relations.

further readings:
https://en.wikipedia.org/wiki/DE-9IM
http://edndoc.esri.com/arcsde/9.0/general_topics/understand_spatial_relations.htm
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=about+DE-9IM

quick hint:
===========
if I correctly understand all precincts are always
expected to be precisely contained within a single
district.

if this assumption is correct, then the right Spatial
operator you should use is ST_Covers() (or ST_CoveredBy)

bye Sandro

[mj10777]

In the next version of Spatialite will support ISO Topology, which will help to resolve this problem.

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=ISO+Topology

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=topo-intro

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=topo-intermediate

----

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

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=topo-intermediate

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:

https://git.osgeo.org/gogs/rttopo/librttopo

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

[Jon Polacheck]

I'm running Windows 10.  I have spatialite.exe (cli), spatialte-gui and the qspatialte plugin installed in qgis.  None seem to support the CreateTopology function.  Advice?

[a.fu...@lqt.it]

On Sat, 17 Jun 2017 08:47:58 -0700 (PDT), Jon Polacheck wrote:
> I'm running Windows 10.  I have spatialite.exe (cli), spatialte-gui
> and the qspatialte plugin installed in qgis.  None seem to support
> the CreateTopology function.  Advice?
>

Hi Jon,

Topology still is an experimental feature: the release cycle of
the first "stable" version fully supporting Topology (4.4.5)
will start in next weeks and is expected to complete during
this summer.

A nowadays outdated experimental/testing version for Windows
platforms supporting an earlier implementaion of Topology is
available from here:
http://www.gaia-gis.it/gaia-sins/windows-bin-x86-test/
http://www.gaia-gis.it/gaia-sins/windows-bin-amd64-test/

note: if you are really interested into early testing of
bleeding edge technologies Windows is the worst platform
you can adopt, because all new developments and related
tests just support Linux only during their active
development cycle.
executables for Windows platforms are usually delivered
only during the final steps of the development cycle when
the new code base is reputed stable enough to be publicly
released also on "alien/exotic" platforms.

bye Sandro

[Jon Polacheck]

Actually, I'm not interested in early testing of bleeding edge technologies, although I am comfortable with linux.  

All I really want to do is extract the tabular data from the shapefiles I have.  I would be quite satisfied if I could do it with the software I currently have access to;

SpatiaLite version ..: 2.3.1 Supported Extensions:

- 'VirtualShape' [direct Shapefile access]

- 'VirtualText [direct CSV/TXT access]

- 'VirtualNetwork [Dijkstra shortest path]

- 'RTree' [Spatial Index - R*Tree]

- 'MbrCache' [Spatial Index - MBR cache]

- 'VirtualFDO' [FDO-OGR interoperability]

- 'SpatiaLite' [Spatial SQL - OGC]

PROJ.4 version ......: Rel. 4.6.1, 21 August 2008

GEOS version ........: 3.1.1-CAPI-1.6.0

3.6.16

spacialite-gui

qgis w/qspacialte plugin

I can compile a different version of spacialite on windows or linux, if that is necessary.

On Sunday, June 11, 2017 at 10:10:44 PM UTC-5, Jon Polacheck wrote:

[a.fu...@lqt.it]

On Sat, 17 Jun 2017 12:28:50 -0700 (PDT), Jon Polacheck wrote:
> Actually, I'm not interested in early testing of bleeding edge
> technologies, although I am comfortable with linux.  
> All I really want to do is extract the tabular data from the
> shapefiles I have.  I would be quite satisfied if I could do it with
> the software I currently have access to;
>
> SpatiaLite version ..: 2.3.1 Supported Extensions:
> - 'VirtualShape' [direct Shapefile access]
> - 'VirtualText [direct CSV/TXT access]
> - 'VirtualNetwork [Dijkstra shortest path]
> - 'RTree' [Spatial Index - R*Tree]
> - 'MbrCache' [Spatial Index - MBR cache]
> - 'VirtualFDO' [FDO-OGR interoperability]
> - 'SpatiaLite' [Spatial SQL - OGC]
> PROJ.4 version ......: Rel. 4.6.1, 21 August 2008
> GEOS version ........: 3.1.1-CAPI-1.6.0
>

Hi Jon,

spatialite 2.3.1 is definitely stone dead by a very long time;
it was released on July 2009 (height years ago), and in the
meanwhile more then ten subsequent updated versions were
released; ather obviously each one of them introduced
several important new features and eliminated a lot of bugs
emerged in previous versions.
the same obviously happened to all depending libraries
such as PROJ.4 and GEOS.

what you are currently using isn't SpatiaLite (as currently
intended today); it's more like the granny of SpatiaLite.

note: the "official" home of the SpatiaLite project is here:

http://www.gaia-gis.it/gaia-sins/

you can easily download more recent versions of the
pre-built Windows binaries from there.

> I can compile a different version of spacialite on windows or linux,
> if that is necessary.
>

if building on Linux or Windows isn't a problem you could
very easily build your own executables, this including the
current "development" branch.
you just have to download the latest versions of the sources
from the Fossil repository.

you'll find detailed instruction covering all related topics
on the above mentioned home page.

bye Sandro

[mj10777]

Also:  the newer versions (since 3.0) offer a tool called 'spatialite_convert' to convert the 2.* versions to the present 4.*

spatialite_convert --version 4 your.db

This will also insure that software such as gdal and QGIS will be capable to write to the Databases

- version 2 Databases are mostly only supported as read only

Mark

[Micha Silver]

It's worth noting that if you put "spatialite download" into google, the top (few) links all point to 2.3.x
Scrolling down the list, you have to pass 2.0 and 1.0 before getting to the link current version on the gaia-sins page.

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

-- 
Micha Silver
cell: +972-523-665918

[mj10777]

On Sunday, 18 June 2017 15:48:25 UTC+2, Micha wrote:

It's worth noting that if you put "spatialite download" into google, the top (few) links all point to 2.3.x
Scrolling down the list, you have to pass 2.0 and 1.0 before getting to the link current version on the gaia-sins page.

This is, unfortunately, very true. 

Just before the issue of the Spatialite 4.0.0 version, the main Sql-Syntax pages was switched to a 'sql-lastest' Syntax

- which will always show the sql-syntax of the latest (released) version

-- this seems to work correctly

Maybe adding 'download' to the

<meta name="keywords" content=" free software,open source, download,SpatiaLite, "

to the http://www.gaia-gis.it/gaia-sins/ page might help.

Sandro: also with replacing 'RasterLite' with 'RasterLite2'

also in:

https://www.gaia-gis.it/spatialite-2.3/

change:

<title>SpatiaLite download page</title>

to 

<title>SpatiaLite 2.3 info page</title>

(and any other versions that may exist)

may assist google to 'forget' these pages.

Mark

[a.fu...@lqt.it]

On Sun, 18 Jun 2017 16:48:19 +0300, Micha Silver wrote:
> It's worth noting that if you put "spatialite download" into
> google, the top (few) links all point to 2.3.x
> Scrolling down the list, you have to pass 2.0 and 1.0 before getting
> to the link current version on the gaia-sins page.
>

Hi Micha,

yes, this is indisputably true.

Anyway all that obsolete web pages start at the very beginning
with an eye-catching orange panel reporting that this is an
obsolete and outdated version and providing the links pointing
to the currently active URLs.

Please see:
http://www.gaia-gis.it/spatialite-2.3.1/index.html

I was naively expecting that such a striking message should be
enough to warn the users pointing them in the right direction,
but as the practical experience demonstrates too many hurried
users go straight to the "precompiled binaries" link completely
ignoring the warning message.

bye Sandro