Relationship class in sqlite(spatialite)?

[Dekay]

Hi,


What I am trying to do is to basically define the relationship between
various ArcGIS shapefiles and tables that are contained in one
sqlite(spatialite extension) database.


After defining the relationship, I want to be able to navigate through
the related values in a GIS application (e.g. QGIS)


In ArcGIS-FGDB(Feature Geodatabase) set-up, I can define something
called "relationship class" which enables such capability.


So far, I figured out that I can relate two tables using the syntax
"REFERENCES" in sqlite, but it does not seem to have something similar
to relationship class explicitly.


Assuming that I completed defining the relationship between the
shapefiles using REFERENCES keyword in sqlite, is there any
visualization tool that enables me the navigation between the related
table attribute values?


I would appreciate your answer or very simple comments.


Thanks!


DK

[a.furieri]

Hi DK,

SQLite/SpatiaLite is a Relational Database:
i.e. a fully compliant implementation of
a well known and universally widespread
data model (and related syntax), formally
defined by the SQL international standard.

Relational databases and SQL aren't at
all newcomers, because they where invented
some 40 years ago (1970), and since then
both them are main "foundation pillars"
in Computing.

Attempting to explain SQL foundations in few
words is an absolutely desperate task: but you
can easily find lots of books and on-line doc,
ranging from heavy academic press to slim&quick
tutorials for beginners.

Just a couple of very basic references to
start with:
http://en.wikipedia.org/wiki/Relational_database
http://www.sqlite.org/lang.html

============================================
Quick&dirty answer:

Using SQL syntax you can JOIN two (or even more)
tables at your will: and there are several ways
to get this result.
Just a basic (and quite stupid) example:

CREATE TABLE countries (
country_code TEXT PRIMARY KEY NOT NULL,
country_name TEXT NOT NULL);

CREATE TABLE towns (
town_id INTEGER PRIMARY KEY AUTOINCREMENT,
town_name TEXT NOT NULL,
country_code TEXT NOT NULL,
CONSTRAINT fk_country_town
FOREIGN KEY (country_code)
REFERENCES countries (country_code));

INSERT INTO countries VALUES ('IT', 'Italy');
INSERT INTO countries VALUES ('FR', 'France');
INSERT INTO countries VALUES ('ES', 'Spain');

INSERT INTO towns VALUES (NULL, 'Rome', 'IT');
INSERT INTO towns VALUES (NULL, 'Paris', 'FR');
INSERT INTO towns VALUES (NULL, 'Madrid', 'ES');

SELECT t.town_id, t.town_name, c.country_name
FROM towns AS t, countries AS c
WHERE t.country_code = c.country_code;

-- or --

SELECT t.town_id, t.town_name, c.country_name
FROM towns AS t
JOIN countries as c ON
(c.country_code = t.country_code);

and you can also "staticize" (i.e. make permanent)
such JOINed query, simply creating a so called VIEW:

CREATE VIEW town_with_country AS
SELECT t.town_id AS town_id,
t.town_name AS town_name,
t.country_code AS country_code,
c.country_name AS country_name
FROM towns AS t
JOIN countries as c ON
(c.country_code = t.country_code);

SELECT * FROM town_with_country;

--------------------------------------
obviously, all this applies to Geometries
as well, because a Geometry table simply
is a common SQL table, after all.

if all this sounds too much complex to you,
you can follow a simplified and user friendly
way. Please read the following docs:
http://www.gaia-gis.it/spatialite-2.4.0/Using-Views-Basic.pdf
http://www.gaia-gis.it/spatialite-2.4.0/Using-Views-Advanced.pdf

Please note: Geometry Views are fully
supported by the QGIS spatialite data
provider.
You simply have to define the Geometry View
using spatialite-GUI, and you can then immediately
see the "combined" layer using QGIS

bye Sandro

[Andrea P.]

Hi,
the relationships of a fgdb.
Are no treu db-relations,
but instead they are rules supported and understand only from a
specific client called ArcGIS-arceditor/arcinfo.

so to have the same paradigm you must suppose to have an other client
(like qgis) to understand the rules write in
some support-tables of you sqlite db.

This is the more difference between a geodatase model and a relational-
Model.

The relational model is obviously more portable because it is usable
on every client GIS that is capable to read spatialite.

regads,
Andrea.

wrote

[Milver]

Sandro, Andrea,

Some questions related to this discussion:

- I've read there are bugs in defining VIEWS using virtual tables.
Have these been corrected?
- If it is possible to persist virtual tables' relationships using
VIEWS, would the following use case be possible?:

Let's say I have to use a geospatial standardized data model based on
shapefiles and descriptions of relationships (one-to-one and one-to-
many). I would like to import the shapefiles into a sqlite/spatialite
database as virtual tables and define several views describing the one-
to-one and one-to-many relationships. In this way, using a GIS app
like QGIS (or even GeoServer since it now supports SpatiaLite) I could
take advantage of the relationships between the shapefiles.
Furthermore I would want to be able to use the sqlite/spatialite
database as a self-executable-sql shell that I could drop alongside
with any other group of shapefiles that follow the standard data
model, so that I could distribute this shell & any standard shapefiles
to any user. Advanced users would simply add the VIEWS to their GIS
app, without the need to re-create the SQL syntax or even execute it.
Beginners, or simply users of GIS apps that do not support spatialite,
would just use the shapefiles.

I would greatly appreciate your thoughts.

Thanks,

Milver

On Nov 26, 4:42 am, "a.furieri" <a.furi...@lqt.it> wrote:
> Hi DK,
>
> SQLite/SpatiaLite is a Relational Database:
> i.e. a fully compliant implementation of
> a well known and universally widespread
> data model (and related syntax), formally
> defined by the SQL international standard.
>
> Relational databases and SQL aren't at
> all newcomers, because they where invented
> some 40 years ago (1970), and since then
> both them are main "foundation pillars"
> in Computing.
>
> Attempting to explain SQL foundations in few
> words is an absolutely desperate task: but you
> can easily find lots of books and on-line doc,
> ranging from heavy academic press to slim&quick
> tutorials for beginners.
>
> Just a couple of very basic references to

> start with:http://en.wikipedia.org/wiki/Relational_databasehttp://www.sqlite.org/lang.html

> way. Please read the following docs:http://www.gaia-gis.it/spatialite-2.4.0/Using-Views-Basic.pdfhttp://www.gaia-gis.it/spatialite-2.4.0/Using-Views-Advanced.pdf

[a.furieri]

Hi Milver,

I understand your point of view,
but some clarification is required.

-------

yes, that's true: shapefiles are universally
widespread. anyway this popular format has
several strong intrinsics limitations, and
is clearly outdated and obsolescent nowadays.

just to quickly mention the main ones:
- at least three separate files are required in
order to ship a single layer
- so a complex coverage requires to use many
and many different files
- the DBF is a really rudimentary storage
system when compared to "real" DBMSs
- any support for locale charsets is absolutely
missing
- support for Spatial Reference System and
Spatial Indexes exists, but is not standardized
at all or working only when using ESRI Arc***
- it's not at all difficult finding badly
formatted shps
- lots of shps can only be used on case insensitive
filesystems (i.e. Windows), because they have
mismatching paths [uppercase/lowercase bad
handling]

all we know how painful and frustrating can be
attempting to nurse and care some obscure and
undocumented complex coverage based on shapefiles
showing odd artifacts (and making apps to crash ...)

simply to discover at the end of the history
that the whole dataset was a messy and crappy
thing of infamous quality.

-------------

OGC-SFS compliant Spatial DBMSs were invented
exactly to exit from the above chaos.
They are safest and quickest, and they are
absolutely *standard*: so they represent a
*big* improvement.
And they not only support basic data storage;
they implement a complete data processing system.

And not at all surprisingly ESRI for first is
progressively supporting more advanced solutions
based on DBMS technologies.

The unique disadvantage of using a Spatial DBMS
is complexity: and this may frighten beginners.
But SpatiaLite isn't complex at all: it's really
simple under any acception of the term.

Deploying and using a single SpatiaLite DB
containing lots of layers within a single
file is a big improvement.
And it's by far easier to use (even for beginners)
than attempting to manage a complex collection
of separate shapefiles (not to mention robustness
and affordability).

-----------

using the VirtualShape interface is *not* the
best way to use SpatiaLite.
this simply is a sort of "dirty trick" just
supporting minimal (read only) access to
external shapefiles via standard SQL queries.

but is not intended for real production usage:
it is more to be seen as an useful tool to be
mainly used during the preliminary stages of
setting up a complex database.

please note well: accessing an external shp
via VirtualShape is by far a less efficient
process than accessing a "real" internal
Geometry table.
and (as you already state) there are several
limitations (aka bugs) when you'll attempt
to establish complex relations (JOINs) on
a VirtualShape.

in other worlds, SpatiaLite cannot be
intended as a system allowing you to freely
process shapefiles on every possible way.
SpatiaLite actually is an OGC-SFS compliant
Spatial DBMS
yes, it supports some useful tool intended to
make shapefiles import as simple and easy as
possible.
but for any serious production deployment
you absolutely have to import your data into
a "real" DBMS internal table.

bye Sandro

[Milver]

Sandro,

Thank you so much for your prompt response! I certainly agree with
your assessment. I gotta mention that using a shapefile data model is
not my choice. My (large) client has required its clients to use a
shapefile data model. I'm trying to convince my client to focus on a
true ogc compliant database like sqlite/spatialite. This "dirty trick"
of using virtual tables, while it is not ideal for production, could
help *today* to ease a full transition into sqlite/spatialite while
still being able to use all the legacy data that was and is being
produced. Ideally, the first step would be to deploy a virtual-table
based shell of spatialite database immediately. The second step would
be to completely define the data model within the spatialite database
and to produce and store new data in the spatialite database. The
success of the second step is also tied to enabling my client's (many)
ESRI users to create (or worse case scenario export) the data into the
spatialite database which currently is not possible because I believe
ESRI doesn't support spatialite.

I would greatly appreciate your thoughts in the matter. I hope these
comments have clarified my objectives and position.

Thanks again,

Milver

[a.furieri]

Hi Milver,

your marketing strategy seems to be smart
enough: i.e. using as much as possible the
legacy shp support in order to soften any
resistance to innovation and ensuring a
smooth and painless transition.

Anyway, some technical limitation exist
(as you are well conscious):
- ESRI Arc*** doesn't support SpatiaLite
- VirtualShapes work reasonably well, but
they cannot be used for JOINs or VIEWs:
and solving this issue doesn't apparently
seems to be possible, because it looks
like a kind of specific idiosyncrasy
of the SQLite core.

Just as a suggestion: if you are interested
to support client-server apps, you can
consider to adopt PostGIS.
Any open source GIS can establish a PostGIS
connection.
But ArcGis too can directly access native PostGIS
geometries (via ArcSDE): this feature is explained
in a quite obscure way in ESRI's own documentation,
but it works for sure (I've personally checked this)

And this will open another different way allowing
to support a smooth and painless transition from
proprietary to free software.

bye Sandro

[Milver]

Sandro,

Appreciated your feedback. Without the capability to use JOIN and VIEW
with virtual tables, there is not much added value to the shapefiles.
Therefore the strategy would need to be adjusted to a full SpatiaLite
implementation. If this is the case then I would need to prove to my
client that the barriers of access to the data are low:

1. By ensuring there are robust GIS apps that can read, write,
display, and navigate through the data model, including ways to show
the results of the one-to-many relationships. QGIS is the only desktop
app that comes to mind, but I still have to test how these
capabilities have been implemented. Are there other OGC compliant
desktop GIS apps that take advantage of SpatiaLite advanced features?

2. By enabling ESRI users to import/export from/to SpatiaLite. Are you
aware of any conversion utilities? Given that ESRI is a closed system
the import/export utilities would need to be coded in ArcObjects.
However, ESRI has promised a free API for their file geodatabase
product which they have touted as a replacement for shapefile.

3. By facilitating web dissemination. GeoServer just released a plugin
for SpatiaLite. Has anyone tried it? (http://docs.geoserver.org/latest/
en/user/community/spatialite/index.html)
From SpatiaLite documentation and from your comments I can see that
using SpatiaLite may not be a robust server solution. However I was
wondering that if the requirements are not high (i.e. few simultaneous
users at a time) then it may be ok. You suggested the use of PostGIS/
PostgreSQL which is probably the best OGC database that straddles the
ESRI and the open server systems. However, a PostGIS database would
not fulfill the requirements of portability, no driver installs,
single file, etc. which makes spatialite a good candidate to replace
shapefiles. Nevertheless, are there good tools to migrate SpatiaLite/
SQLite databases to PostGIS/PostgreSQL?

Again, I appreciate a lot the feedback you are providing. I think that
a true OGC database solution is the way to go, but I need to build
momentum to make it happen.

Thanks,

Milver

[a.furieri]

Hi Milver,
just few sparse considerations:

a) for sure QGIS fully supports SpatiaLite:
I've read that OpenJumps too supports
SpatiaLite, but I've not yet performed
any actual testing by myself, so I cannot
say nothing about this.
Some rumors come from the GvSIG community
from time to time: but I ignore any further
detail.

b) SpatiaLite and PostGIS are "close relatives":
migrating data from the one to the other
(both ways) isn't difficult at all.
ogr2ogr already supports this task.
and probably during the next year I'll
be able to release some other data
interchange tool (may well be, a GUI
based user friendly tool)
So you can consider PostGIS as the "BigBoy",
and SpatiaLite as the "TinyBoy"; after all
both them are members of the same family,
using the one or the other simply depends
on circumstances.

c) I suppose that supporting SpatiaLite for
ESRI clients could be technically possible
using ArcObjects.
but I've never worked on ESRI products
(except to replace them ASAP with free
software), so I cannot say you nothing
simply because my ignorance is absolute.
And exactly the same is for any other
proprietary GIS sw: I'm sorry, but my
incompetence about them all is really
abyssal.

d) the most attractive "neutral" technology
allowing to freely intermix free and not-free
GIS apps seems to be the one based on OGC-compliant
WebServices (WMS / WFS).
they really are "standard" and "open", thus
ensuring an effective universal interoperability
nowadays quite any GIS client supports both WMS
and WFS, so a data infrastructure massively based
on OGC WebServices seems to really be the best
option under many points of view

e) obviously PostGIS / GeoServer / UMN MapServer
are the best candidates to be used to deploy
wide "corporate" WMS / WFS servers
but I feel that a different contest exists
(i.e. small LANs connecting just few workstations,
or stand-alone PCs)
happily, a new generation of "lightweight" (and
really easy to deploy and manage) WMS and WFS
servers is quickly growing: be prepared to get
some interesting new about this during the next
months :-)

bye Sandro

[David Fawcett]

I think that the limitations that you point out are primarily due to
choices made by ESRI, not particularly the fault of SpatiaLite.

On Mon, Nov 29, 2010 at 2:55 PM, Milver <valenzu...@gmail.com> wrote:
> Sandro,
>
> Appreciated your feedback. Without the capability to use JOIN and VIEW
> with virtual tables, there is not much added value to the shapefiles.
> Therefore the strategy would need to be adjusted to a full SpatiaLite
> implementation. If this is the case then I would need to prove to my
> client that the barriers of access to the data are low:

I see the virtual tables/shapefiles functionality of SpatiaLite to be
a convenience for some situations, but when it is so easy to import
the data directly into SpatiaLite, I don't think that they need to be
that robust.

>
> 2. By enabling ESRI users to import/export from/to SpatiaLite. Are you
> aware of any conversion utilities? Given that ESRI is a closed system
> the import/export utilities would need to be coded in ArcObjects.
> However, ESRI has promised a free API for their file geodatabase
> product which they have touted as a replacement for shapefile.
>

Since ESRI is a closed system (their very conscious choice). And the
use of ESRI by an organization is also a conscious choice. To get
data 'over the wall', you will need to convert it to a format that
ESRI can read. ESRI has been promising to 'open up' the file
geodatabase format for several years. At first, I believe that they
talked about publishing the spec. They are now talking about
publishing an API. I believe that the reasons behind not opening up
the file geodatabase format are very much from a business strategy
standpoint, not a technical limitation.

There are ESRI solutions for dealing with features and related tables,
they are just expensive...

Like Sandro said, QGIS handles SpatiaLite data very nicely.

> 3. By facilitating web dissemination. GeoServer just released a plugin
> for SpatiaLite. Has anyone tried it? (http://docs.geoserver.org/latest/
> en/user/community/spatialite/index.html)
> From SpatiaLite documentation and from your comments I can see that
> using SpatiaLite may not be a robust server solution. However I was
> wondering that if the requirements are not high (i.e. few simultaneous
> users at a time) then it may be ok. You suggested the use of PostGIS/
> PostgreSQL which is probably the best OGC database that straddles the
> ESRI and the open server systems. However, a PostGIS database would
> not fulfill the requirements of portability, no driver installs,
> single file, etc. which makes spatialite a good candidate to replace
> shapefiles. Nevertheless, are there good tools to migrate SpatiaLite/
> SQLite databases to PostGIS/PostgreSQL?
>
> Again, I appreciate a lot the feedback you are providing. I think that
> a true OGC database solution is the way to go, but I need to build
> momentum to make it happen.

One thing that I would be interested in seeing would be an OGC
standard for modeling topology in a spatial database. Of course, it
would be great if this started with the PostGIS and SpatiaLite folks
getting together to figure out the best way of doing it vs. ESRI 'open
sourcing' their model ala Google and KML...

I work in a mixed ESRI/OpenSource world and I like your idea of
working to move your client to more OpenSource solutions. It can
definitely be tough to make this happen with the closed systems.

David.

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

On Tue, 30 Nov 2010 09:51:40 -0600, David Fawcett wrote

> One thing that I would be interested in seeing would be an OGC
> standard for modeling topology in a spatial database. Of course, it
> would be great if this started with the PostGIS and SpatiaLite folks
> getting together to figure out the best way of doing it
>

Hey David, may be are you a sorcer ?
do you practice necromancy and black magic wizardry ?

I'm currently working on a new tool named
"spatialite_topogml": can you guess what
is intended for ???

and that's not all: my own approach on the
spatialite's side only is half of the whole
project, because Sandro Santilli (aka strk,
for PostGIS fans) is simultaneously working
on the PostGIS and GEOS side :-)

so, GML 3.0 topology is coming on the
Earth ... be frightened !!!

bye Sandro

[Dekay Kim]

Hi Sandro,

 

Thanks for the kind reply.

 

You response was extremely helpful.

 

One quick note that I discovered about View:

 

It seems that the created geometric view (one that can be seen on QGIS) is fully removed from the DB even after removing it by right-clicking on the corresponding view and clicking on "Drop View"

 

I guess the empty schema still persists so I cannot create the view with the name that is same as the one of the removed view, and I can also see the dropped views in the importable list when I open the spatialite db in QGIS.

 

Any suggestion?

 

Thanks!

 

DK

 

 

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To post to this group, send email to spatiali...@googlegroups.com.
To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/spatialite-users?hl=en.

--
Dekay Kim

[Dekay Kim]

Hi,

 

I am just wondering if we can define something like "domain" in sqlite table.

 

I know I can do something very similar by defining foreign field in one table and have it refer to the other table that contains the domain values using the "REFERENCES" keyword.

 

However, this method requires extra table to be explicitly defined.

 

In typical sql, we can do something like "CREATE DOMAIN ..."

 

and I figured that this statement was not supported in sqlite (http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql)

 

Does the current sqlite support a counterpart statement corresponding to "CREATE DOMAIN..."?

 

Your answer is always very much appreciated.

 

Thanks!

 

DK

--
Dekay Kim

[a.furieri]

Hi Kim,

"Spatial VIEWs" aren't plain VIEWs: simply
dropping the VIEW isn't enough.
You must remove the corresponding row from
the VIEWS_GEOMETRY_COLUMNS table as well.

bye Sandro

[a.furieri]

Hi Kim,

No, SQLite doesn't supports a CREATE DOMAIN
statement.

Anyway I'm not really sure to fully understand
your question. I see from the Postgres documentation
that CREATE DOMAIN is intended to define new
data-types

If the above is true, I cannot figure any possible
connection between DOMAIN and FOREIGN
KEY / REFERENCES

Can you get some further detail / example ?

bye Sandro

[Dekay Kim]

Thanks for the kind reply.

 

Just to clarify what I am trying to do.

 

My basic purpose is as follows:

 

1. Users try to edit the attribute of a given field.

2. When they click on the cell in the table, a dropdown box appears and give the user the available choices for that field.

3. I simply called the "available choices" as "domain"

 

Please let me know if further needs to be clarified.

 

Your answer will be greatly appreciated.

 

Thanks!

DK

 

 

 

 

bye Sandro

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To post to this group, send email to spatiali...@googlegroups.com.
To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/spatialite-users?hl=en.

--
Dekay Kim

[a.furieri]

Hi Kim,

now your question is absolutely clear:
more or less this is the same as MySQL
ENUM, i.e. a mechanism allowing to define
a list of acceptable values for a given
column.

no, sqlite doesn't directly supports a
feature like this.
but a very similar option is supported
instead:

CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
gender TEXT CHECK (gender IN ('male', 'female'))
);

INSERT INTO test (id, name, gender)
VALUES (NULL, 'Adam', 'male');

INSERT INTO test (id, name, gender)
VALUES (NULL, 'Eva', 'female');

INSERT INTO test (id, name, gender)
VALUES (NULL, 'God', 'N.A.');

INSERT INTO test (id, name, gender)
VALUES (NULL, 'the apple', 'neutral');

INSERT INTO test (id, name, gender)
VALUES (NULL, 'the snake', 'who knows ?');

the latest three INSERTs will actually fail,
reporting: CONSTRAINT FAILED


bye Sandro

[Dekay Kim]

Sandro,

Thanks alot!

I truly appreciate your precise answer and also fantastic responsiveness.

Thank you!

DK

bye Sandro

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To post to this group, send email to spatiali...@googlegroups.com.
To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/spatialite-users?hl=en.

--
Dekay Kim

[David Fawcett]

Definitely not a wizard. In fact, if you asked my wife, she could
confirm poor skills in the clairvoyance department...

Since you are granting all of my wishes this week, is there anything
that you can do about World peace?

David.

[David Fawcett]

This is great news. I am particularly excited about the use of open
standards and the cooperation with the PostGIS tribe. It will be very
cool to have a single-file spatial database with topology, etc.

David.

On Tue, Nov 30, 2010 at 10:18 AM, <a.fu...@lqt.it> wrote: