Any reason not to use SpatiaLite as the basis?
Brett Antonides
Has anyone looked into SQLite, SpatiaLite, and Android/iOS enough to say why we shouldn't use SpatiaLite as a starting point?
From a quick Google search there appear to be a couple open source projects on Google Code and GitHub that are trying to port SpatiaLite to Android.
Thoughts?
Brett
Paul Ramsey
<brett.a...@lmnsolutions.com> wrote:
> Just putting the question out there.
>
> Has anyone looked into SQLite, SpatiaLite, and Android/iOS enough to say why
> we shouldn't use SpatiaLite as a starting point?
Yes, because we want to specify a format, not a software. But there's
an important distinction to be drawn here:
- there's spatialite the .so/.dll file that sqlite links in order to
be able to run spatial functions inside the database. that's software.
we shouldn't be dragging that in.
- there's the way spatialite organizes the data it stores inside a
sqlite database. that's format, and it could be powerful to start with
that format, as it already specifies the WKB to be used, strings used
for geometry types, and so on. and it largely follows the sfsql spec.
I don't want to oversell the benefits of aping the spatialite data
organization though, it would just get us immediate compatibility with
projects that already read that orgnization: qgis, ogr, spatialite
itself... I think that's it. So we if have a choice between doing a
better job and doing spatialite, I might be includes to do the former
rather than the latter.
P.
pa...@imagemattersllc.com
More comments in-line.
Paul D
On 2/24/2012 1:10 PM, Paul Ramsey wrote:
> On Fri, Feb 24, 2012 at 10:02 AM, Brett Antonides
> <brett.a...@lmnsolutions.com> wrote:
>> Just putting the question out there.
>>
>> Has anyone looked into SQLite, SpatiaLite, and Android/iOS enough to say why
>> we shouldn't use SpatiaLite as a starting point?
> Yes, because we want to specify a format, not a software. But there's
> an important distinction to be drawn here:
>
> - there's spatialite the .so/.dll file that sqlite links in order to
> be able to run spatial functions inside the database. that's software.
> we shouldn't be dragging that in.
instigators need a solution yesterday, and SpatiaLite/RasterLite look
like an 80% solution out of the box.
So I tried to complete an updated version of the port to Android, but
broke my time-limited pick on porting the GEOS C++ lib (header hell).
Please speak up if you know of a viable port.
> sqlite database. that's format, and it could be powerful to start with
> that format, as it already specifies the WKB to be used, strings used
> for geometry types, and so on. and it largely follows the sfsql spec.
tile pyramids per db by using the myPyramid_rasters and
myPyramid_metadata naming convention, whereas MBTiles for example is
limited by design to one pyramid per db. I've done a proof of concept
MBTiles View of RasterLite Images from TrueMarble (250m resolution)
and can display it in MBTilesViewer. But that only works for one tile
set at a time. IMHO MBTiles can be an alternative interface, but the
ability to have multiple sets of tiles per db that are accessible at
the same time is very desirable.
> organization though, it would just get us immediate compatibility with
> projects that already read that orgnization: qgis, ogr, spatialite
> itself... I think that's it. So we if have a choice between doing a
> better job and doing spatialite, I might be includes to do the former
> rather than the latter.
schemas and try to identify shortcomings that would make a better job
worth the effort. We might also consider allowing views as well as
tables, as MBTiles does, to uncouple the interface from the
implementation (at the cost of performance).
Paul Ramsey
> However, the project
> instigators need a solution yesterday, and SpatiaLite/RasterLite look
> like an 80% solution out of the box.
> So I tried to complete an updated version of the port to Android, but
> broke my time-limited pick on porting the GEOS C++ lib (header hell).
> Please speak up if you know of a viable port.
No info here... an Android implementation would seem like an excellent
opportunity to build a non-Spatialite implementation, using
GeoTools/JTS as your core model... (though geotools on a mobile,
perhaps impossible! :) And as you say, you are time constrained.
> On the raster side, it also has the advantage of supporting multiple
> tile pyramids per db by using the myPyramid_rasters and
> myPyramid_metadata naming convention, whereas MBTiles for example is
> limited by design to one pyramid per db. I've done a proof of concept
> MBTiles View of RasterLite Images from TrueMarble (250m resolution)
> and can display it in MBTilesViewer. But that only works for one tile
> set at a time. IMHO MBTiles can be an alternative interface, but the
> ability to have multiple sets of tiles per db that are accessible at
> the same time is very desirable.
Well beyond my knowledge right now, I'll start pulling down the
implementations and poking them...
> I think we should look at the SpatiaLite table name conventions and
> schemas and try to identify shortcomings that would make a better job
> worth the effort. We might also consider allowing views as well as
> tables, as MBTiles does, to uncouple the interface from the
> implementation (at the cost of performance).
One major concern I have with the Spatialite/Rasterlite approach is
the "implementation is the standard" issue. The expectation that *any*
S/R file should then work anywhere, while the S/R implementor seems
happy to just keep on hacking without reference to any implementation
but his own. There's no standardization process at that point really,
just a software process (ShapeFile vs FGDB API). Anyone who
independently implements the de jure standard will be screwed when the
de facto standard shifts.
I'm afraid that's just a whinge, I don't have any great solution.
P.
Brad Hards
wrote:
>
> Has anyone looked into SQLite, SpatiaLite, and Android/iOS enough to say
> why we shouldn't use SpatiaLite as a starting point?
by Pepijn Van Eeckhoudt (of Luciad) and committed on 25 Jan 2012.
Brad
Paul Daisey
Paul Daisey
Looking in Android.mk I see
LOCAL_CFLAGS := \
-fvisibility=hidden \
-DOMIT_GEOCALLBACKS \
-DOMIT_GEOS \
-DOMIT_PROJ \
-DOMIT_EPSG \
-DOMIT_ICONV \
-DVERSION="\"3.0.2\""
which indicates the Android port does without GEOS, PROJ, and ICONV support, all of which we'd like to have. The GEOS lib port is our current bottleneck. Do you know of one that has succeeded?
Thanks again.
On Thursday, March 1, 2012 4:32:01 PM UTC-5, Brad Hards wrote:
Paul Ramsey
current (not the older ones) file layout and indexing scheme SL is
using?
P.
Brad Hards
> Or can you verify that $fossil clone
> https://www.gaia-gis.it/fossil/libspatialite libspatialite.fossil got me
> the changes?
> Looking in Android.mk I see
> LOCAL_CFLAGS := \
> -fvisibility=hidden \
> -DOMIT_GEOCALLBACKS \
> -DOMIT_GEOS \
> -DOMIT_PROJ \
> -DOMIT_EPSG \
> -DOMIT_ICONV \
> -DVERSION="\"3.0.2\""
>
> which indicates the Android port does without GEOS, PROJ, and ICONV
> support, all of which we'd like to have. The GEOS lib port is our current
> bottleneck. Do you know of one that has succeeded?
I don't. Pepijn's purpose didn't require them.
Brad
Brad Hards
> When you say "using SpatiaLite as a basis" do you mean documenting the
> current (not the older ones) file layout and indexing scheme SL is
> using?
Can you explain the question a bit more? I can't make out the context in which
the question is being asked (or who it is being asked of) - the text you're
quoting doesn't appear in the referenced emails.
Brad
Paul Ramsey
in the group, but is on the same subject.
P.
Paul Daisey
OK, thanks.
Paul
Paul Daisey
I expect that if we start there, SpatiaLite will be the first operating implementation, but there certainly can be others.
Brad Hards
> > > Looking in Android.mk I see
> > > LOCAL_CFLAGS := \
> > >
> > > -fvisibility=hidden \
> > > -DOMIT_GEOCALLBACKS \
> > > -DOMIT_GEOS \
> > > -DOMIT_PROJ \
> > > -DOMIT_EPSG \
> > > -DOMIT_ICONV \
> > > -DVERSION="\"3.0.2\""
> > >
> > > which indicates the Android port does without GEOS, PROJ, and ICONV
> > > support, all of which we'd like to have. The GEOS lib port is our
>> > current bottleneck. Do you know of one that has succeeded?
http://code.google.com/p/android/issues/detail?id=20859
?
If so, maybe try with GEOS 3.2.2 (instead of 3.3.2). That will block some of
the newer geos-based features in spatialite, but should allow most of it to
build and work OK.
Brad
Paul Daisey
Dear Paul, feel absolutely free to share my considerations with anyone else :-D Best Regards, Sandro > Best regards, > Paul > > On 3/8/2012 3:48 PM, Alessandro Furieri wrote: > >> Dear Paul, >> >> I beg your pardon for my delayed reply. >> I can only say that during the latest days I was deeply >> involved in a thorough final debugging session before >> releasing the next splite v.3.1.0 (I've released the >> first Release Candidate just today). >> >> Anyway, a more serious problem prevented me to answer to >> your questions: in the latest weeks I was just in the middle >> of negotiating my future work plan withthe Tuscany Region govt., >> so I was not at all sure about future developments interesting >> both SpatiaLite and RasterLite. >> >> Now all this is definitively resolved (Tuscany Region will >> generously >> fund my work during 2012), and I'm now able to share my future >> plans >> with you all. >> >> Just a short introduction about Tuscany Region [1] activities >> supporting GFOSS developments: >> - their Geographic Information Service was previously based >> on Oracle Spatial, ESRI ArcGIS and ArcIMS. >> - due to heavy budget cuts, they are now switching to a new >> infrastructure mainly based on PostGIS, MapServer, GeoServer >> and QGIS >> - they are strongly pushing GML and GML-TOPO as a long-term >> storage format, and GDAL/OGR is the standard tool identified >> for GML handling >> >> Anyway, both SpatiaLite and RasterLite are heavily involved in the >> Tuscan basic GIS instrumentation: >> - spatialite's DB-files are widely used as SHP replacements, >> so to easily share and redistribute geographic datasets between >> different administrations (Counties, Local Councils, Agencies). >> - spatialite's own tools (both CLI and GUI) are increasingly used >> in order to support spatial data processing and analysis: as many >> officials discovered by direct experience, this kind of activities >> is better suited to desktop environments, avoiding as far as >> possible >> any exceeding complexity imposed by client-server architectures. >> - rasterlite is evaluated as a very promising replacement for any >> other conventional raster format (GeoTIFF, ECW, MrSID). >> >> ------ >> >> During 2011 Tuscany Region already financed several open source >> developments, mainly aimed to get a better integration between >> different GFOSS tools. >> My own contributions in this context were: >> a) effectively supporting SpatiaLite in GDAL/OGR [2] >> b) optimizing the GDAL/OGR GML-TOPO parser (i.e. implementing >> xlink:href resolution in an efficient way) [3] >> >> Now Tuscany Region is going to finance a further development cycle >> specifically focused on SpatiaLite and RasterLite. >> We have just consensually agreed to go implementing the following >> features during 2012: >> >> 1) spatialite: importing the PostGIS own libWgeom library, so to >> support the same Spatial data processing core, and exactly in >> the same way. >> expected added value for Tuscany Region: freely switch from >> PostGIS and SpatiaLite back and forth, using GDAL/OGR as a >> universal import/export tool. >> >> 2) spatialite: supporting enhanced topology >> expected added value for Tuscany Region: same as above. >> >> 3) supporting ISO 91115/INSPIRE metadata and SLD (styled layer >> descriptor) >> both them are XML based, so the project goal is to insert >> XML documents as BLOBs directly into the DB. >> then implementing direct SQL access allowing to query XML >> values as a key:value dictionary. >> expected added value for Tuscany Region: being able to deploy a >> complex geographic coverage as single DB-file, directly storing >> any related metadata and pre-defined graphical rendering (this >> feature is expected to be supported ASAP by QGIS). >> >> 4) RasterLite-2: the most notable improvement. >> this is intended to significantly expand current RasterLite >> capabilities, so to be able to support any possible raster >> format. not only "conventional" B&W / RGB aerial ortophotos, >> but multi-band satellite imagery, data grids (DEM, DSM, DTM ...) >> and any other "odd" raster data as well. >> * arbitrary pixel layout: arbitrary number of components, >> arbitrary bits-per-sample (ranging from 1 to 64 bits). >> * effective and sophisticated multi-resolution "pyramid" >> layout >> * uncompressed data, lossless and lossy (JPEG) compressions >> * extensive use of Adam-7 interlacing so to support 1:1, 1:2, >> 1:4 and 1:8 resolutions for each pyramid level (thus >> significantly reducing storage requirements) >> * extensive tiling of huge rasters >> * spatial index internal support >> * full API support for both reading and writing >> expected added value for Tuscany Region: being able to get rid >> of costly proprietary codes as ECW and MrSID, then gradually >> replacing complex (and fragile) mosaics of many GeoTIFF with a >> more robust and easy-to-handle surrogate. >> >> 5) integrating RL-2 support (both read and write) in GDAL, so to >> allow immediate and easy reuse of rasterlite-2 datasources by >> other GFOSS components. >> expected added value for Tuscany Region: being able to quickly >> deploy this new technology in the simplest and painless way. >> >> ---------------- >> >> Coming now to your specific questions: >> >> > That recommendation prompted me to send this email to ask you to >> share >> > with us your expectations for evolution of SpatiaLite / >> RasterLite >> > tables, views, triggers and indexes. >> > Do you expect to make any changes to these over the next several >> years? >> > >> >> SpatiaLite simply is an extension to SQLite. >> I'm not at all able to foresee if the SQLite's folks will introduce >> some revolutionary change before or after: may be yes, may be no >> ... >> who knows ? >> >> libspatialite is required anyway so to add GeoSpatial capabilities >> to the bare SQLite core (more or less, exactly as PostGIS does on >> behalf of PostgreSQL). >> And libspatialite is required too in order to support any trigger >> enforcing spatial coherence and consistency, and to grant proper >> R*Tree synchronization. >> >> Just as a return from my direct experience: SpatiaLite passed a >> strong >> discontinuity between versions 2.3.1. and 2.4.x, when 3D geometries >> were initially supported: few changes were then introduced in the >> spatial_ref_sys and geometry_columns layout, a catastrophic >> revolution >> affected the internal binary format representing geometries. >> A second remarkable discontinuity was encountered when SQLite >> directly introduced R*Tree support: this caused a deep refactoring >> of earlier trigger implementations. >> Anyway, no destructive change was never introduced: if you always >> use the appropriate SQL functions, you can practically never notice >> how many dramatical changes have been introduced during the years. >> And you can safely open and process still today any DB-file created >> >> many years ago using anyone of the very first earlier prototypical >> versions. >> Anyway, all this is happily back our shoulders nowadays; I >> personally >> feel it would be very unlike to pass another similar revolution yet >> >> again in the future. >> >> As I learned during my GDAL/OGR experiences, a common misconception >> about spatialite is the one considering splite simply as "a data >> format" or "yet another sqlite-based data container". >> This one is a dangerously misleading approach: reading a spatialite >> >> DB using only the bare sqlite is surely possible (but not >> encouraged). >> On the other side, any attempt to write a spatialite DB avoiding at >> all to load libspatialite will surely end in disaster. >> >> I suppose no sane minded developer will never attempt to access a >> PostGIS DBMS while attempting at the same time to disable PostGIS >> itself (and maybe, skipping libpq). >> the same is exactly for splite: when you access a spatialite DB, >> you are expected to use libspatialite someway: may well be you >> should ignore the API-level, but you are expected at least to use >> the appropriate SpatiaLite's own SQL functions. >> >> A simple practical example coming from my first hand experience: >> the previous implementation of the GDAL/OGR driver supporting >> SpatiaLite attempted to directly perform some fancy >> INSERT INTO geometry_columns ...... >> in order to define geometry columns: the result was simply to >> create broken and unusable DB files. >> >> After applying my own patches, now GDAL 1.9.0 uses the expected >> SELECT AddGeometryColumn(....) >> in order to correctly define any geometry column. >> And now OGR works nicely; you can freely link GDAL to libspatialite >> >> v.2.3.x, v.2.4.x or v.3.x.x irrespectively, and it works anyway. >> >> Please note: more or less the same versioning issues affect any >> other >> DBMS: e.g. using PostgreSQL/PostGIS, when you apply a major version >> >> update to the psql server you are required to perform a full dump >> and recover. >> in other words: you have to reload the whole DBMS starting from >> scratch, because the binary storage formats are reciprocally not >> compatible. >> >> In SQLite/SpatiaLite you can simply ignore all this: when opening a >> >> DB-file the supporting libraries will automatically recognize if it >> >> was eventually created using an earlier version, then silently >> applying any required change / update / enhancement. >> From my own perspective, it's a big improvement, not a defect. >> >> --------------- >> >> The same approach does not applies to RasterLite: yes, its widely >> based on DBMS technologies, but I feel wisest thinking of >> RasterLite >> simply as "yet another image format" supported by it's own APIs. >> After all, a RasterLite datasource simply is a single file; exactly >> as any other TIFF, JPEG, PNG or ECW image. >> >> You usually access TIFFs using libtiff, JPEGs using libjpeg and >> ECWs >> or MrSIDs using the appropriate proprietary codecs. >> The same is for RasterLite: you are expected to use librasterlite >> APIs >> in order to read/write a rasterlite datasource. >> Cross compatibility for different versions will be surely honored >> at >> the API level: but the underlying DB layout could probably change, >> if this can support more advanced future options or better >> performances. >> >> Yet again: think of TIFF. This format supports an impressive range >> of different codecs, pixel formats and pixels layouts (by separate >> planes, stripped, tiled ...) >> But using the appropriate API-level calls you can safely ignore at >> all any fine-grained implementation detail. >> >> --- >> >> I'll be absolutely glad to answer to any further question about >> SpatiaLite and RasterLite. >> I suppose that my co-developer Brad Hards will be absolutely happy >> to share with you his direct experiences about using spatialite for >> >> military applications and purposes. >> >> Best regards, >> Sandro >> >> [1] http://en.wikipedia.org/wiki/Tuscany [6] >> [2] http://www.gdal.org/ogr/drv_sqlite.html [7] >> [3] http://www.gdal.org/ogr/drv_gml.html [8]
Paul Ramsey
>> On 3/8/2012 3:48 PM, Alessandro Furieri wrote:
>>> As I learned during my GDAL/OGR experiences, a common misconception
>>> about spatialite is the one considering splite simply as "a data
>>> format" or "yet another sqlite-based data container".
>>> This one is a dangerously misleading approach: reading a spatialite
>>>
>>> DB using only the bare sqlite is surely possible (but not
>>> encouraged).
>>> On the other side, any attempt to write a spatialite DB avoiding at
>>> all to load libspatialite will surely end in disaster.
>>>
>>> I suppose no sane minded developer will never attempt to access a
>>> PostGIS DBMS while attempting at the same time to disable PostGIS
>>> itself (and maybe, skipping libpq).
>>> the same is exactly for splite: when you access a spatialite DB,
>>> you are expected to use libspatialite someway: may well be you
>>> should ignore the API-level, but you are expected at least to use
>>> the appropriate SpatiaLite's own SQL functions.
Sort of invalidates a major thrust of this project, no?
"Just use sqlite with spatialite" is not functionally any different
from "just use the FGDB API".
Where from here?
P.
Brad Hards
> >>> the same is exactly for splite: when you access a spatialite DB,
> >>> you are expected to use libspatialite someway: may well be you
> >>> should ignore the API-level, but you are expected at least to use
> >>> the appropriate SpatiaLite's own SQL functions.
>
> Sort of invalidates a major thrust of this project, no?
> "Just use sqlite with spatialite" is not functionally any different
> from "just use the FGDB API".
>
> Where from here?
Maybe its time to go up a level.
Geopackage is a solution.
What is the problem we're trying to solve? Is there a CONOPS / Concept of use
/ operational need statement somewhere that can be made public?
I'm assuming that the goal is to help with some kind of user need.
Careful consideration of that need might tell you whether using
sqlite/spatialite meets the need. There could well be a case for saying "the
scope is to define an appropriate, standardised schema for spatialite 3.x".
> >>> I suppose that my co-developer Brad Hards will be absolutely happy
> >>> to share with you his direct experiences about using spatialite for
> >>> military applications and purposes.
Spatialite is already used in Falconview 5.x (as the new localpoints format),
and is in XPlan. Maybe interoperability with a primary mission planning tool
would be useful? I also have a plugin for JMPS that can read that format.
There are a few things that I'd like to fix, but local points works well, and
solves a lot of problems (especially for land and air-land users).
So maybe we would be better off producing an Interface Change Notice for the
Falconview LocalPoints ICD?
If the concern over spatialite is that there could be a change that breaks
compatibility, then we can look at various options (in particular, robust
tests that cover the defined requirements).
Brad
Paul Ramsey
> Maybe its time to go up a level.
>
> Geopackage is a solution.
>
> What is the problem we're trying to solve? Is there a CONOPS / Concept of use
> / operational need statement somewhere that can be made public?
>
> I'm assuming that the goal is to help with some kind of user need.
My definition, not of the user need, but the programmer need, would be
"a package format that with the minimum number of dependencies allows
me to transport, store, edit, and quickly retrieve geospatial data".
The "minimun number of dependencies" part is the part where I see
spatialite as a bad fit (in the same way that PostGIS, with it's big
server and various support libraries is a bad fit). But frankly, if
mobile phone folks are getting over the hump there's not a lot of
other *more* constrained environments that are going to falter on the
extra dependencies spatialite drags in.
At which point the whole exercise collapses. You don't need to define
a format, it's an API-level problem, and the API is already defined by
a third party. So shut down this list and get to work, right?
P.
Jeff Yutzler
What is the problem we're trying to solve? Is there a CONOPS / Concept of use/ operational need statement somewhere that can be made public?
The primary purpose of a GPKG is to provision soldiers with all geospatial data and information they need for their missions in a self-describing single-file form ready for immediate use. GPKG provisioning will be across available high bandwidth networks and on removable media in Disconnected, Intermittent, and Limited (DIL) environments. Secondary GPKG purposes include support for geospatial data distribution, collection of observations, and distribution of change-only updates. A GPKG standard will enable common, vendor-neutral, widely implemented capabilities for GPKGs and services. This solution will be a truly open standard which leverages a completely free reference implementation available to the public.
Jeff Yutzler
Image Matters LLC
Mobile: (703) 981-8753
Office: (703) 428-6731
Brad Hards
> My definition, not of the user need, but the programmer need, would be
> "a package format that with the minimum number of dependencies allows
> me to transport, store, edit, and quickly retrieve geospatial data".
>
> The "minimun number of dependencies" part is the part where I see
> spatialite as a bad fit (in the same way that PostGIS, with it's big
> server and various support libraries is a bad fit). But frankly, if
> mobile phone folks are getting over the hump there's not a lot of
> other *more* constrained environments that are going to falter on the
> extra dependencies spatialite drags in.
We can trivially remove freexl if you don't care about .xls files (although I
recall Paul Hastert tells interesting stories about the important of Excel in
locked-down network environments).
Proj might not be needed if we are always in WGS-84 (or the client has to deal
with translation / reprojection if needed).
GEOS might not be needed if the "edit" part of the problem isn't all that
important.
iconv might not be needed if we were to stick to UTF8.
Of course, which of those we need depends on what we need to do to meet the
user need...
Brad
Brad Hards
> The primary purpose of a GPKG is to provision soldiers with all geospatial
> > data and information they need for their missions in a self-describing
> > single-file form ready for immediate use. GPKG provisioning will be
> > across available high bandwidth networks and on removable media in
> > Disconnected, Intermittent, and Limited (DIL) environments. Secondary
> > GPKG purposes include support for geospatial data distribution,
> > collection of observations, and distribution of change-only updates. A
> > GPKG standard will enable common, vendor-neutral, widely implemented
> > capabilities for GPKGs and services. This solution will be a truly open
> > standard which leverages a completely free reference implementation
> > available to the public.
I'm still not sure I see the CONOPS level description though. Is there an OV-1
level diagram or description?
Who are the actors in this?
> To synopsize:
> 1. Provision vector data for direct use everywhere (e.g. mobile)
*Why* are we trying to distribute vector data? What do we think the soldiers
are going to do with these blobs?
> 2. Serve as a medium for distribution of vector data between systems
Which systems? Are we trying to cover from GCCS-J and JADOCS down to
microDAGR?
> 3. Be vendor-neutral
>
> The vendor-neutral aspect is the tricky part. Being locked into a single
> vendor is not going to work for the US Army because it is too difficult to
> control costs. A free solution could work but the API would still need to
> be standardized in order to guarantee that the technology will work as
> expected when integrated. OpenGeo is on the record as preferring a
> standard format vs. API and I certainly understand why from a developer's
> standpoint.
I understand the problem about vendor lock-in, but spatialite isn't really
vendor lock-in. It probably better considered as format lock-in, which I
understand as an explicit goal here. All vendors are free to use the existing,
liberally licensed implementation.
Integration problems are orthogonal to that licensing issue. When it comes
down to it, the API/format reality is the API/format that is implemented (even
if you have multiple implementers), not the format that is standardised (e.g.
KML, where reality is what works in google earth, not some document with an
OGC number on it).
> What is the path of least resistance? Standardizing the API or
> standardizing the format (despite the owner's statement that it would be
> insane to use the format without the API)?
I've spent months of my own time writing tests for spatialite. I think you
could build a reader that provided basic "container access" without using the
spatialite API. But that will leave you with sqlite in place of spatialite.
Does that really help?
I think we should just accept "we'll implement it with spatialite", and get on
with defining the standard schema that will give us interoperability.
Brad
Jeff Yutzler
I'm still not sure I see the CONOPS level description though. Is there an OV-1level diagram or description?
Who are the actors in this?
*Why* are we trying to distribute vector data? What do we think the soldiers
> To synopsize:
> 1. Provision vector data for direct use everywhere (e.g. mobile)
are going to do with these blobs?
> 2. Serve as a medium for distribution of vector data between systemsWhich systems? Are we trying to cover from GCCS-J and JADOCS down to
microDAGR?
Brad Hards
> the Army Geospatial Enterprise CONOPS and OV-1. Naturally the AGE CONOPS
> is too big to e-mail and the OV-1 is too cluttered and confusing to be
> informative.
> > > 1. Provision vector data for direct use everywhere (e.g. mobile)
Why? (I'm going to keep asking this, just like the three year old child. Sorry
its going to be annoying).
> > *Why* are we trying to distribute vector data? What do we think the
> > soldiers
> > are going to do with these blobs?
>
> > Three things:
> 1. Portrayal (overlays)
So to show icons and lines on a 2D or 3D map?
> 2. Geospatially Enabled Analytics
Sorry, too many buzzwords - please explain what you mean.
Are we talking about supporting some kind of an intel / headquarters analysis?
Or is this something that you intend would be used below the headquarters (say
brigade / battalion) level (e.g. tell me if that route is within 1000m of any
IED incident location within the last 12 months)?
> 3. Collection of observations ("Soldier as a Sensor") so that they can be
> delivered upstream
Hmm, I didn't get this from the spec document that Paul D. sent out. It
doesn't really show any linkage of data back from the client to the parent
server.
Is the intent that the data would be transferred back using geopackage? Or is
geopackage just the reference set and the observations get sent back using
some other tool? Are the vector feature observations equal in importance to
the "upstream" vector features? Do we need to support peer-to-peer
synchronisation as well as provision to an upstream source?
> Ideally yes, but primary focus is on the Mobile/Handheld Computing
> Environment because there is currently no reasonable solution there.
Which systems? Android? IOS? Minimum versions?
> I'm stepping back from the developer issues.
I think understanding requirements is about the biggest developer issue there
is :-)
Brad
Jeff Yutzler
On Sunday 11 March 2012 02:05:56 Jeff Yutzler wrote:So its a directly derived requirement from the AGE CONOPS?
> the Army Geospatial Enterprise CONOPS and OV-1. Naturally the AGE CONOPS
> is too big to e-mail and the OV-1 is too cluttered and confusing to be
> informative.
> > > 1. Provision vector data for direct use everywhere (e.g. mobile)Why? (I'm going to keep asking this, just like the three year old child. Sorry
its going to be annoying).
Through the AGE, Commanders and Soldiers will be able to exploit a net-centric, enterprise environment that will allow geospatial information to be collected once, then processed, exploited, and shared among all Soldiers and their organizations in a timely manner, thereby reducing redundancy, conserving scarce resources and personnel, and improving the probability of mission success.
> > *Why* are we trying to distribute vector data? What do we think theSo to show icons and lines on a 2D or 3D map?
> > soldiers
> > are going to do with these blobs?
>
> > Three things:
> 1. Portrayal (overlays)
> 2. Geospatially Enabled Analytics
Sorry, too many buzzwords - please explain what you mean.
Are we talking about supporting some kind of an intel / headquarters analysis?
Or is this something that you intend would be used below the headquarters (say
brigade / battalion) level (e.g. tell me if that route is within 1000m of any
IED incident location within the last 12 months)?
Hmm, I didn't get this from the spec document that Paul D. sent out. It
> 3. Collection of observations ("Soldier as a Sensor") so that they can be
> delivered upstream
doesn't really show any linkage of data back from the client to the parent
server.
Two-way data flow is essential for achieving relevant situational awareness across platforms, CP and for training and mission rehearsal
Is the intent that the data would be transferred back using geopackage?
Initially, BC systems will pass applicable geospatial data collected to a Geospatial Engineering Team (GET) to validate and for ingestion into the Geospatial Foundation (GF). The result is BC GF data interoperability, synchronization, and a unified geospatial foundation layer in the COP of increased quality, timeliness, for a more efficient decision process.
Or is
geopackage just the reference set and the observations get sent back using
some other tool? Are the vector feature observations equal in importance to
the "upstream" vector features?
Do we need to support peer-to-peer
synchronisation as well as provision to an upstream source?
> Ideally yes, but primary focus is on the Mobile/Handheld ComputingWhich systems? Android? IOS? Minimum versions?
> Environment because there is currently no reasonable solution there.
Scott C
Brad Hards
> NGA and other agencies/Services are creating apps
> that utilize spatial data.
list (and any identified data needs by application) might help to scope this.
> In addition to that need you have to reconcile
> that other non-DoD agencies are also players in this. Those agencies
> (USAID, DoS, etc. go to NGA to get their data as well.
Its broader than that. Non-USG (FMS nations and perhaps NGOs) might also be
involved here.
Brad
Brad Hards
> Geospatially Enabled Analytics apply known algorithms and processes to
> derive information from existing geospatial data (anything that exists in
> place and time). The objective is to get "decision aids" into the hands of
> the soldier as quickly as possible given the hardware and infrastructure
> constraints. Decision aids is a broad category that includes
> geocoding/reverse geocoding, mobility analysis, visibility analysis, and
> much more.
source for decision aids implemented by an application?
Brad
Brad Hards
>
> Yes.
A couple of further clarifications:
1. In a 3D view, might points and lines have vertical extent (like the way KML
tesselate works)?
2. Will geopackage objects have altitude specified in terms of HAE? Any need
for AGL (or sea level, or ocean floor, or MSL)?
3. Do you expect that the icons will be from a narrow / predefined set (either
globally or by COI), or more loosely allocated (i.e. possibly anything,
allocated at runtime)?
The Falconview localpoints implementation has a limitation in that the list of
icons isn't defined (basically, you just specify icon by filename), and there is
no way to know what icons are installed on the recipients machine. One idea
I've considered is to have a standard set of icons, but to allow embedding
custom icons (as a BLOB) with a name-based lookup.
Brad
Scott C
On Sunday, March 11, 2012 11:52:36 PM UTC-4, Brad Hards wrote:
On Monday 12 March 2012 12:36:12 Scott C wrote:
> NGA and other agencies/Services are creating apps
> that utilize spatial data.
What applications? I'm not looking for an exhaustive list, but an indicative
list (and any identified data needs by application) might help to scope this.
> In addition to that need you have to reconcile
> that other non-DoD agencies are also players in this. Those agencies
> (USAID, DoS, etc. go to NGA to get their data as well.
Its broader than that. Non-USG (FMS nations and perhaps NGOs) might also be
involved here.
Brad
Jeff Yutzler
Jeff Yutzler
On 3/12/12, Brad Hards <br...@frogmouth.net> wrote:
> A couple of further clarifications:
> 1. In a 3D view, might points and lines have vertical extent (like the way
> KML tesselate works)?
Yes. We are planning to use the OGC Simple Features profile:
http://portal.opengeospatial.org/files/?artifact_id=25355
Here is the key section of that document:
The instantiable subclasses of Geometry defined in this Standard are
restricted to 0, 1 and 2-dimensional geometric objects that exist in
2, 3 or 4-dimensional coordinate space (ℜ2, ℜ3 or ℜ4). Geometry values
in R2 have points with coordinate values for x and y. Geometry values
in R3 have points with coordinate values for x, y and z or for x, y
and m. Geometry values in R4 have points with coordinate values for x,
y, z and m. The interpretation of the coordinates is subject to the
coordinate reference systems associated to the point. All coordinates
within a geometry object should be in the same coordinate reference
systems. Each coordinate shall be unambiguously associated to a
coordinate reference system either directly or through its containing
geometry. The z coordinate of a point is typically, but not
necessarily, represents altitude or elevation. The m coordinate
represents a measurement. All Geometry classes described in this
standard are defined so that instances of Geometry are topologically
closed, i.e. all represented geometries include their boundary as
point sets. This does not affect their representation, and open
version of the same classes may be used in other circumstances, such
as topological representations.
>
> 2. Will geopackage objects have altitude specified in terms of HAE? Any need
> for AGL (or sea level, or ocean floor, or MSL)?
>
It would seem to me that the vertical datum (like the coordinate
reference system) should be independent of the container. I would
expect a community of interest to define a profile that specifies,
let's say, WGS84 and HAE.
> 3. Do you expect that the icons will be from a narrow / predefined set
> (either globally or by COI), or more loosely allocated (i.e. possibly anything,
> allocated at runtime)?
>
To me the presentation aspects are out of scope. We're not trying to
reinvent KML.
Regards,
Paul Daisey
On Friday, February 24, 2012 1:02:40 PM UTC-5, Brett Antonides wrote:
Just putting the question out there.
Has anyone looked into SQLite, SpatiaLite, and Android/iOS enough to say why we shouldn't use SpatiaLite as a starting point?
From a quick Google search there appear to be a couple open source projects on Google Code and GitHub that are trying to port SpatiaLite to Android.
Thoughts?
Brett
On Friday, February 24, 2012 1:02:40 PM UTC-5, Brett Antonides wrote:
Just putting the question out there.
Has anyone looked into SQLite, SpatiaLite, and Android/iOS enough to say why we shouldn't use SpatiaLite as a starting point?
From a quick Google search there appear to be a couple open source projects on Google Code and GitHub that are trying to port SpatiaLite to Android.
Thoughts?
Brett
a.furieri
Lets start with a rough taxonomy.
Genuine File Formats:
These are based on some official standard (or at least on some
publicly
documented de-facto standard) and not necessarily requiring any
supporting API
(anyone can freely develop an independent read/write implementation
based on
the specifications).
GeoData examples (in hierarchical order):
(1) GML and/or GeoJSON (partly KML)
(2) (E)WKT/(E)WKB (just supporting geometries, some other notation
to be
applied for data attributes, e.g. CSV)
(3) the obsolete but still widely used Shapefile
pros: simple, easy to implement, universally portable
cons: not very efficient, no support for in-place updates. Very good
for
universal data exchange purposes. Usually needs to be converted into
some more
efficient "internal" format to implement real data processing
capabilities.
==================================================================
Pseudo File Formats:
Not necessarily defined as a complete formal standard (and may be
completely
undocumented), but supported anyway by some appropriate API
implementing
access methods to the datasource. A complete datasource is contained
within a
single monolithic file, and this file is intended to be cross-platform
usable.
No Spatial Data Analysis capability is supported: the intended goal
simply is
supporting low-level storage for spatial features.
GeoData examples:
- ESRI FGBD
- FDO RFC16 [http://trac.osgeo.org/fdo/wiki/FDORfc16]
pros: not too complex
cons: not really a format under a strict definition, because some
specific API
is required anyway so to access the datasource. FGDB strictly depends
on
proprietary (closed source) APIs. On the opposite side FDO RFC16
simply
depends on SQLite (public domain licensed) and seems to be a wisest
solution
if avoiding any proprietary lock-in is a bonus.
Neither support any intrinsic spatial data processing capability, just
basic
storage.
==================================================================
Spatial DBMS:
Based on SQL and OCG/SFS international standards. When the whole DB
is
completely contained within a single monolithic file, it's very hard
to define a
strong conceptual differentiation from the previous case.
The main distinction is that a Spatial DBMS doesn't simply supports
basic
storage; it deploys an effective and complete self-standing Spatial
Data
Processing capability, and it does such a thing in a portable way.
Usually you can expect a genuine Spatial DBMS to be efficient and
fast, because
both ordinary and spatial index are supported. Quite obviously a
genuine
Spatial DBMS requires its own libraries to be deployed and used, but
on the
other side it's quite easy connecting to the DBMS using many
different
programming languages.
A Spatial DBMS is an intrinsically robust tool, because triggers
and constraints actively reject any attempt to insert invalid or
malformed data.
GeoData examples:
- SpatiaLite (sorry, I'm not aware of an equivalent)
pros: not too complex, lightweight (about 2MB of binary code),
No administration / configuration requirements, efficient, robust.
For SpatiaLite, it is genuinely free software open source, there are
absolutely no proprietary lock-in dangers. Completely platform
independent,
and has been successfully ported on Linux, Mac Os X, Windows, iOS,
Android and
WinMobile.
cons: not really a format under a strict definition, because some
specific API
is surely required.
============================================================
Is SpatiaLite the perfect solution for Mobile/SmartPhones/Tablets ?
Honestly, not yet.
Many users have already performed some successful porting to iOS,
Android and Windows Mobile/.NET: it surely works, but there isn't
yet a robust engineering (./configure and/or makefiles) supporting
these platforms.
Some (modest) community effort is surely required.
Porting SpatiaLite itself is absolutely an easy task: but many
problems arise while porting GEOS, PROJ.4 and iconv.
Please note: a minimal configuration skipping all the above libraries
is available, but this strongly impairs spatial data processing
capabilities. There may be a middle ground (trading off some
complexity for
some additional features, depending on what is needed for this task).
I've no direct first hand experience in this area, but I received
significant
feedback from the SpatiaLite users mailing list. There is no technical
reason
why a clean and easy port of these libraries to a mobile (or any
other)
environment. However at this stage, nobody doing the maintenance work
to
develop, test and update the corresponding build scripts. I doubt it
would
take much more than a few #ifdef macros in the sources, given the
recent work
on Android by Luciad.
In addition, I know that a Swiss developer (Marco Bernasocchi)
successfully
ported the whole QGIS stack (including SpatiaLite, GEOS, Proj.4 and
iconv) to
Android. See http://hub.qgis.org/projects/android-qgis
I note some concerns over API stability. API (and ABI) support is
important to
SpatiaLite developers, even to the extent of keeping API that is
potentially
dangerous (e.g. can result in buffer overflows - we do provide
documentation
warning of such risks of course). It would be nice to be able simply
delete
these functions, but we understand the impact that this kind of change
can
have on users of our libraries.
We are currently close to full test coverage across spatialite. Those
tests
probably already cover any functionality and API you would be likely
to use,
and we're happy to add extra tests as necessary to ensure that
GeoPackage
keeps working the way you expect.
Please let us know if you have further questions or concerns.
Sandro Furieri and Brad Hards
Birkel, Paul A.
See: http://forums.arcgis.com/threads/52389-File-Geodatabase-API-1.2-now-available (7 March 2012)
-----
Esri File Geodatabase API
Build Date: Feb 23, 2012
Build Version: 1.2.0.136
Version: v 1.2
Esri File Geodatabase API for Windows 32-bit and Windows 64-bit README
----------------------------------------------------------------------
SUPPORTED PLATFORMS:
The supported platforms are:
Windows 2003 Server Standard, Enterprise & Datacenter (32-bit and 64-bit) SP2
Windows 2008 Server Standard, Enterprise & Datacenter (32-bit and 64-bit) SP2
Windows 2008 R2 Server Standard, Enterprise, and Datacenter (64-bit)
Windows 7 Ultimate, Enterprise, Professional, Home Premium (32-bit and 64-bit)
Windows Vista Ultimate, Enterprise, Business, Home Premium (32-bit and 64-bit) SP2
Windows XP Professional Edition, Home Edition (32-bit) SP3
Windows XP Professional Edition, Home Edition (64-bit) SP2
SOFTWARE REQUIREMENTS:
Visual Studio 2010 (C++) Standard, Professional, or Team Editions required for development.
Visual Studio 2010 C and C++ Runtimes required for deployment.
.NET 4.0 Framework is required for the .NET wrapper.
-----
Paul Ramsey
(software-API-based dumb(ish) storage) and SpatiaLite
(software-API-based smart storage), which is a SQL-only API to an
otherwise non-spatial database (both FGDB and FDO are C++ APIs to an
underlying storage container that is not important to the end user).
So it's something that could be described in a document, and could
potentially be implemented independent of any particular piece of
software (though for practical data portability and interoperability,
inevitably some implementation, like SQLite, would end up being the
universal SQL engine). Frankly, this is what I thought this list was
built to discuss, but there are other drivers apparently in play
(ship! ship!) which seem to be hastening the tempo down a particular
path.
Is there utility in discussing a Platonic spatial data container with
a SQL interface, or is stating that "GeoPkg is SpatiaLite is GeoPkg
and that's the standard" sufficient? It's certainly practical.
P.
Jeff Yutzler
Paul Ramsey
> Is there a utility in the SQL-only interface? I'll let you be the judge of
> that. OGC Simple Features SQL [1] gives us a standard API to build against
> that enables the user to put feature data in and get feature data out of the
> database. The question to Sandro and/or Brad is whether SpatiaLite gives us
> a way to get to a solution that implements this SQL API.
> There is one other concern. In order for the spatial operators to be
> performant, you pretty much need to have spatial indexes. This is not
> covered by OGC which is probably just as well because it is such a
> database-specific thing [2]. In the old-school world with monolithic
> databases and dedicated DBAs, this works well enough. However, when you are
> talking portable databases, the software has to be able to do everything.
> In the case of SpatiaLite, their API would handle the administration
> capabilities as it does now. If in the final solution there is a standard
> API for the data but a closed API to the administration of the data store,
> is that meaningful enough to be worth the effort? It is not a question I
> can answer.
On the one hand, I have long held an internal Platonic ideal of a
SQL-only specification that could run using SQLite only, no extra
software required. Indexes provided with a side-table.
On the other hand, I'm on record (somewhere) grousing about why OGC
bothered to spend years specifying GML in its many incarnations when
an open source API to an interoperability format (and who cares what
the internals of the file are? as long as the code and code
development process are open) could have provided meaningful
software-level interoperability with far higher operational
consistency and far lower manpower inputs than the GML exercise did.
So I guess to the extent that SpatiaLite is in fact thin and portable
enough to serve in the multiple platforms and devices we are asking it
to, I've self-answered. If it works and it's open that's all that's
needed to solve the problem. What that won't get us is an OGC seal of
approval (or any other standards body, I would imagine) so the next
question is non-technical:
- Is a core requirement of this process that the final solution is
signed off on by a standards body? Or just that the final solution
works?
P.
a.fu...@lqt.it
> OGC Simple Features SQL [1] gives us a standard API
> to build against that enables the user to put feature data in and get
> feature data out of the database. The question to Sandro and/or Brad
> is whether SpatiaLite gives us a way to get to a solution that
> implements this SQL API.
>
> If in the final solution
> there is a standard API for the data but a closed API to the
> administration of the data store, is that meaningful enough to be
> worth the effort? It is not a question I can answer.
>
Hi Jeff,
a preliminary definition: what we can call a DBMS ?
and what we can call a Spatial DBMS instead ?
Q1: execution of standard SQL commands (both DDL and DML) is supported
?
NO: definitely, it's a generic data container, not a DBMS.
YES: it surely is a genuine DBMS
Q2: the capability to store a specialized Geometry data type is someway
supported ?
objection: not a serious and meaningful question.
any modern DBMS can surely store any kind of generic binary data as
opaque and unqualified BLOBs, this including pictures, sounds and
rather obviously geometries as well.
Q3: (same as above, much clearly reformulated) the capability to store
*and* effectively
process a specialized Geometry data type is effectively supported
just using
plain SQL ?
NO: may be it's a DBMS, but surely it's not a Spatial DBMS
YES: it can legitimately claim to be a Spatial DBMS
Q4: are the canonical WKT and WKB notations supported ?
is the OGC-SFS core of SQL functions implemented ?
is a Spatial Index effectively available ?
NO: it could Spatial DBMS someway, but surely not a standard one.
YES: all right: it's a genuine and full fledged Spatial DBMS.
and here is my answer based on factual evidence.
(no Plato here: simply good old Jhon Locke's english empiricism)
-------------------------
BEGIN;
CREATE TABLE countries (
code TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL);
INSERT INTO countries (code, name)
VALUES ('US', 'United States of America');
INSERT INTO countries (code, name)
VALUES ('UK', 'United Kingdom');
INSERT INTO countries (code, name)
VALUES ('IT', 'Italy');
COMMIT;
BEGIN;
CREATE TABLE towns (
town_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
country_code TEXT NOT NULL,
notes TEXT,
CONSTRAINT country_fk FOREIGN KEY (country_code)
REFERENCES countries (code));
CREATE INDEX country_idx ON towns (country_code);
SELECT AddGeometryColumn('towns', 'geom',
4326, 'POINT', 'XY');
SELECT CreateSpatialIndex('towns', 'geom');
COMMIT;
BEGIN;
INSERT INTO towns (town_id, name, country_code,
notes, geom) VALUES (NULL, 'Rome', 'IT',
'capital town of Italy',
ST_GeomFromText('POINT(12.30 41.90)', 4326));
INSERT INTO towns (town_id, name, country_code,
notes, geom) VALUES (NULL, 'London', 'UK',
'capital town of England',
ST_GeomFromText('POINT(0.05 51.52)', 4326));
INSERT INTO towns (town_id, name, country_code,
notes, geom) VALUES (NULL, 'New York', 'US', NULL,
ST_GeomFromText('POINT(-73.85 40.80)', 4326));
.... insert many other million towns ...
COMMIT;
SELECT t.name, c.name, ST_X(geom) AS longitude,
ST_Y(geom) AS latitude, ST_AsText(geom) AS wkt,
ST_Srid(geom) AS srid, ST_GeometryType(geom) AS type,
AsEWKT(geom) AS ewkt, AsGML(geom) AS gmlv2,
AsGML(3, geom) AS gmlv3, AsKML(geom) AS kml,
AsGeoJSON(geom) AS geoJson
FROM towns AS t
JOIN countries AS c ON (t.country_code = c.code)
WHERE ST_Distance(t.geom, MakePoint(5, 45)) < 25
AND t.ROWID IN (
SELECT ROWID FROM SpatialIndex
WHERE f_table_name = 'towns' AND
search_frame = BuildCircleMBR(5, 45, 25)
);
------------------------------------------------
the above SQL snippet is 100% pure SpatiaLite.
As far as I can figure, it looks to my eyes to be reasonably
conformant to OGC-SFS specifications.
Yes, I'm well aware: querying the Spatial Index in an
embedded sub-query sounds a little odd: anyway, this one
is a very specific feature of R*Trees in SQLite.
An R*Tree simply is a further separate and completely
unrelated table: and consequently using the Spatial Index on
SpatiaLite is a little bit more tricky than in other DBMSes.
Anyway, SQLite's R*Trees are damn fast and highly effective.
Please note well: there is absolutely no reason compelling
to use the SpatiaLite's own C APIs in order to get all this
running.
You simply have to invoke the SQLite's own "query execute"
method passing the corresponding SQL statement as a text string.
Otherwise you can invoke the similar methods supported by
Java-JDBC, PHP, Python, ODBC or .NET data connectors.
There is no need to say yet another time that libspatialite
itself *must* be loaded anyway as an extension module to
SQLite in order to get all the required code implementing
the OGC-SFS main-core and any related "spatial" stuff.
bye Sandro
post scriptum:
> In the old-school world with monolithic databases and dedicated DBAs,
> this works well enough.
>
I like this statement, because it brings me back to my very first youth
(I started my personal career as a developer in 1980).
there were no client-server archs at the time.
in order to start the SQL engine you were then required to link the
supporting library to your own code, so to build a single solid
monolithic
executable (dynamic linkage wasn't yet invented).
it worked well at the time: and I'm fond of SQLite because it
rediscovers
nowadays this simple uncomplex but highly effective ancient paradigm.
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
a.fu...@lqt.it
> On the one hand, I have long held an internal Platonic ideal of a
> SQL-only specification that could run using SQLite only, no extra
> software required. Indexes provided with a side-table.
>
Hi Paul R.,
your Platonic ideal has already materialized on Earth.
You can simply start from the FDO RFC16 format, then recycling
the corresponding OGR support.
http://www.gdal.org/ogr/drv_sqlite.html
very short summary:
- RFC16 simply requires basic SQLite support (CREATE TABLE, INSERT,
UPDATE, DELETE)
- a formal definition exists supporting geometry_columns and
spatial_ref_sys metadata tables
- any Geometry column is simply intended to store BLOB values
representing standard WKT or WKB expressions (accordingly to
the corresponding definition in geometry_columns).
pros:
- there is no need to deploy any further supporting library, libsqlite
is just enough.
- parsing and processing WKT/WKB expressions is widely supported
by many libraries and tools. and anyway developing yet another
independent implementation is not at all a difficult task.
cons:
- no real check and constraint evaluation: you can freely insert any
generic
text string or even a JPEG picture where a WKB MULTIPOLYGON is
expected
to be found: this is absolutely legitimate while performing a generic
SQLite's INSERT or UPDATE not supported by any specific trigger.
- no Spatial Index support (SQLite introduced its own R*Tree too late,
when RFC16 was already approved).
- no SQL functions like ST_Union, ST_Buffer or ST_Tranform.
some further kind of support is implicitly expected to be implemented
somewhere else (e.g., in the client/ap own bussiness logic).
RFC16 simply supports bare storage, not spatial data processing
capabilities.
bye Sandro
Brad Hards
> I feel like this taxonomy misses an option in between FDO/FGDB
> (software-API-based dumb(ish) storage) and SpatiaLite
> (software-API-based smart storage), which is a SQL-only API to an
> otherwise non-spatial database (both FGDB and FDO are C++ APIs to an
> underlying storage container that is not important to the end user).
What do you see as the pros and cons in this option?
How do you see it as different from spatialite with all the optional
dependencies turned off?
Brad
Paul Daisey
Comments and questions in-line below.
Thanks for your extensive participation,
Paul D
On Monday, March 19, 2012 9:46:34 PM UTC-4, a.furieri wrote:
<snip/>
SELECT AddGeometryColumn('towns', 'geom',
4326, 'POINT', 'XY');SELECT CreateSpatialIndex('towns', 'geom');
<snip/>
------------------------------------------------
the above SQL snippet is 100% pure SpatiaLite.
As far as I can figure, it looks to my eyes to be reasonably
conformant to OGC-SFS specifications.
AddGeometryColumn(FEATURE_TABLE_CATALOG, FEATURE_TABLE_SCHEMA, FEATURE_TABLE_NAME,
GEOMETRY_COLUMN_NAME, SRID)
Besides the argument mismatch, from a standards conformance perspective, unfortunately that document has been deprecated (http://www.opengeospatial.org/standards/deprecated) by 06-103r4_Implementation_Specification_for_Geographic_Information_-_Simple_feature_access_-_Part_1_Common_Architecture_v1.2.1.pdf and 06-104r4_Implementation_Specification_for_Geographic_Information_-_Simple_feature_access_-_Part_2_SQL_option_v1.2.1.pdf The latter now specifies creating a geometry table like this:
CREATE TABLE <table name> (
GID NUMERIC NOT NULL PRIMARY KEY,
XMIN <ordinate type>,
YMIN <ordinate type>,
ZMIN <ordinate type>,
MMIN <ordinate type>,
XMAX <ordinate type>,
YMAX <ordinate type>,
ZMAX <ordinate type>,
MMAX <ordinate type>,
WKB_GEOMETRY BIT VARYING(implementation size limit),
{<attribute> <attribute type>}*
)
creating feature tables like this:
CREATE TABLE <feature table name> (
<primary key column name> <primary key column type>,
… (other attributes for this feature table)
<geometry column name> <geometry type>,
… (other geometry columns for this feature table)
PRIMARY KEY <primary key column name>,
CONSTRAINT SRS_1 CHECK (SRID(<geometry column name>) in
( SELECT SRID from GEOMETRY_COLUMNS
where F_TABLE_CATALOG = <catalog>
and F_TABLE_SCHEMA = <schema>
and F_TABLE_NAME = <feature table name>
and F_GEOMETRY_COLUMN = <geometry column> )
… ( spatial reference constraints for other geometry columns in this feature table) )
and creating the geometries with:
7.2.4 SQL routines for constructing a geometry object given its Well-known Text Representation
The routines ST_WKTToSQL used to construct geometric objects from their text representations are specified by SQL/MM..
7.2.5 SQL routines for constructing a geometric object given its Well-known Binary Representation
The routines ST_WKBToSQL used to construct geometric objects from their Well-known Binary Representations are specified in SQL/MM.
Is it possible or likely that at some point you will modify SL to conform to these newer specification versions?
Are the SL functions
ST_GeomFromText( wkt String [ , SRID Integer] ) : Geometry and
ST_GeomFromWKB( wkbGeometry Binary [ , SRID Integer] ) : Geometry
the equivalent of the SQL/MM functions, or do they have different signatures? ( I don't have a copy of SQL/MM)
Yes, I'm well aware: querying the Spatial Index in an
embedded sub-query sounds a little odd: anyway, this one
is a very specific feature of R*Trees in SQLite.
Paul Daisey
On Monday, March 19, 2012 5:24:14 PM UTC-4, Paul Ramsey wrote:
<snip/>
- Is a core requirement of this process that the final solution is
signed off on by a standards body? Or just that the final solution
works?
From my perspective, first we should reach agreement on what the requirements are (gasp!) and which can be met, second we should identify a way to meet the requirements, based on standards body de-jure standards if possible, or based on open de-facto specifications if not, third we should document the technical solution, fourth we should elicit implementations of the solution by different parties, and last we should document the solution according to the practices of a standards body like OGC and execute that body's standard approval process.
Yes we are under intense pressure to get this done yesterday. "Why do we never have time to do it right, but we always have time to do it over?" I guess in this case because we'll not only miss the window of opportunity bus, but also get run over by it.
Thank you for contributing so much to this discussion. I too want to "Keep it Simple, Stupid", but meeting as many of the identified requirements as possible is my first concern.
Best regards,
Paul D
a.fu...@lqt.it
> Hi Sandro:
> Comments and questions in-line below.
> Thanks for your extensive participation,
> Paul D
>
> On Monday, March 19, 2012 9:46:34 PM UTC-4, a.furieri wrote:
>
>> SELECT AddGeometryColumn('towns', 'geom',
>> 4326, 'POINT', 'XY');
>>
>> SELECT CreateSpatialIndex('towns', 'geom');
>>
>> the above SQL snippet is 100% pure SpatiaLite.
>> As far as I can figure, it looks to my eyes to be reasonably
>> conformant to OGC-SFS specifications.
>
> The AddGeometryColumn() statement has the same name but different
> arguments than the following from
> 99-049_OpenGIS_Simple_Features_Specification_For_SQL_Rev_1.1.pdf:
> AddGeometryColumn(FEATURE_TABLE_CATALOG, FEATURE_TABLE_SCHEMA,
> FEATURE_TABLE_NAME,
> GEOMETRY_COLUMN_NAME, SRID)
>
>
Yes Paul, you are absolutely right.
It's not fully conformant, but please consider that:
- there are absolutely no CATALOGs on SQLite
- and there are no SCHEMAs at all
Consider as well that PostGIS introduced a further "dimensions"
optional arg.
If a strict conformance is required, implementing further flavors
(e.g. exactly as the ones supported by PostGIS) is absolutely trivial.
Obviously, the CATALOG and SCHEMA args will be constantly ignored,
because they are completely meaningless in SQLite.
> from a standards conformance
> perspective, unfortunately that document has been deprecated
> (http://www.opengeospatial.org/standards/deprecated) by
>
> 06-103r4_Implementation_Specification_for_Geographic_Information_-_Simple_feature_access_-_Part_1_Common_Architecture_v1.2.1.pdf
> and
>
> 06-104r4_Implementation_Specification_for_Geographic_Information_-_Simple_feature_access_-_Part_2_SQL_option_v1.2.1.pdf
> The latter now specifies creating a geometry table like this:
>
> CREATE TABLE (
> GID NUMERIC NOT NULL PRIMARY KEY,
> XMIN ,
> YMIN ,
> ZMIN ,
> MMIN ,
> XMAX ,
> YMAX ,
> ZMAX ,
> MMAX ,
> WKB_GEOMETRY BIT VARYING(implementation size limit),
> { }*
> )
>
Please note well: there are no data types on SQLite :-D
yes, you can define some column to be e.g. of the INTEGER
type, but this simply is 'syntactic fluff', with no real
functional consequence (except that for PRIMARY KEYS).
You can still insert a TEXT or FLOAT with no complaint at all;
and even a BLOB, if you wish to do so :-D
Be warned: in SQLite there is no way to define e.g. VARCHAR(30);
this is silently assumed to simply mean TEXT, thus allowed to
store from 0 to several billion bytes :-D
Accordingly to all this: WKB_GEOMETRY BIY VARYING will simply
be assumed to be in SQLite's own perspective:
"it's just another unqualified multi-format column"
> creating feature tables like this:
>
> CREATE TABLE (
> ,
> … (other attributes for this feature table)
> ,
> … (other geometry columns for this feature table)
> PRIMARY KEY ,
> CONSTRAINT SRS_1 CHECK (SRID() in
> ( SELECT SRID from GEOMETRY_COLUMNS
> where F_TABLE_CATALOG =
> and F_TABLE_SCHEMA =
> and F_TABLE_NAME =
> and F_GEOMETRY_COLUMN = )
> … ( spatial reference constraints for other geometry columns in this
> feature table) )
>
Paul, really a nice brilliant idea: I'm not really sure about
this (some testing is surely required), but defining CHECK constraints
supporting Geometry columns seem to be a valid and practical solution.
To be carefully evaluated in the very near future ;-)
> and creating the geometries with:
>
> 7.2.4 SQL routines for constructing a geometry object given its
> Well-known Text Representation
> The routines ST_WKTToSQL used to construct geometric objects from
> their text representations are specified by SQL/MM..
> 7.2.5 SQL routines for constructing a geometric object given its
> Well-known Binary Representation
> The routines ST_WKBToSQL used to construct geometric objects from
> their Well-known Binary Representations are specified in SQL/MM.
>
> Is it possible or likely that at some point you will modify SL to
> conform to these newer specification versions?
>
> Are the SL functions
> ST_GeomFromText( wkt _String_ [ , SRID _Integer_] ) : _Geometry and_
> ST_GeomFromWKB( wkbGeometry _Binary_ [ , SRID _Integer_] ) : Geometry
> the equivalent of the SQL/MM functions, or do they have different
> signatures? ( I don't have a copy of SQL/MM)
>
I'm unable to directly consult the SQL/MM specs: anyway I've found
several useful hints on the WEB simply googling around.
The official documentation from PostGIS, ESRI ArcGIS and ArcSDE,
IBM Informix Spatial DataBlade and IBM DB2 consensually affirm:
ST_WKTToSQL() constructs an ST_Geometry given its well-known text
representation. The SRID of the ST_Geometry is 0.
ST_WKBToSQL() constructs an ST_Geometry given its well-known binary
representation. The SRID of the ST_Geometry is 0.
So these SQL/MM functions simply are alias-names for the corresponding
"old-styled" OGC-SFS functions.
Please note: the optional SRID arg is not supported in SQL/MM, and
SRID=0 seems to be unconditionally assumed.
From the SpatiaLite's own perspective: implementing these SQL/MM
aliases
is absolutely trivial, just requiring to define two further IF
statements
in the sqlite's own interface dispatching extended Spatial SQL
functions.
I'll surely implement both them in the next-to-come v.3.1.0, which
currently is still in its Release Candidate stage.
>> Yes, I'm well aware: querying the Spatial Index in an
>> embedded sub-query sounds a little odd: anyway, this one
>> is a very specific feature of R*Trees in SQLite.
>
> Neither version of the SF/SQL standard covers creating spatial
> indexes. I'm the fool ready to rush in where angles fear to tread,
> and
> am comfortable with specifying a requirement that
> implementation-specific SQL commands for creating and deleting R*Tree
> spatial indexes on geometry columns are to be provided in a manner
> supported by the underlying RDBMS.
>
I personally think that Spatial Index is inherently
implementation-specific
and very hard/difficult to standardize.
SQLite's own R*Tree surely is rather "extravagant" (a distinct separate
table),
but is really effective.
SpatiaLite on its own implements several triggers strongly ensuring
that each
INSERT, UPDATE or DELETE will immediately affect both the main table
and the
corresponding R*Tree at the same time, so to robustly keep the R*Tree
in a
nicely consistent synchronized state.
On the "query" side accessing the R*Tree isn't at all implicit or
automatic:
you are required to explicitly SELECT the Spatial Index someway in any
query of your own.
I agree, hardly a standard approach: but a really reasonable one when
considering the overall lightness of the underlying code base and the
effectiveness of this "odd/extravagant" implementation.
bye Sandro
Paul Ramsey
compatible with our, er, reference implementation :)
http://trac.osgeo.org/fdo/wiki/FDORfc16
Achievable goal, and not really different from what I was thinking.
There's been a lot of talk about "manifests" but I wonder if
"geometry_columns" is not itself already a manifest, and if we want
more detail on the layers, could we not include another
geometry_columns columns and slap an ISO 19115 XML metadata file in
there. Or an XMP file! So many metadata standards...
P.
Paul Daisey
It seems to me that the Spatial DBMS based on SQL and OCG/SFS/ISO international standards is the best approach to storing vector data because it has worked out well in practice over more than a decade. The OS-Geo specified GEOS lib that powers the spatial routines and relational predicates in SpatiaLite is also widely used in other RDBMS, GIS and utilities,providing common functionality. My question is do SpatiaLite exposes the IsClosed and Length routines on MultiCurve, and Area, Centroid and PointOnSurface routines on MutliSurface? Comments in the source code indicate support, but there is no mention of Multi* geometries other than GeomCollection on http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html.
Thanks,
Paul
a.fu...@lqt.it
> My question is do SpatiaLite exposes the IsClosed and Length routines
> on
> MultiCurve
>
Hi Paul,
freshly tested for you ;-)
[directly asking the code is surely better than asking the doc]
SELECT ST_IsClosed(ST_GeomFromText(
'MULTILINESTRING((1 1, 1 2), (2 1, 1 1))', 4326));
-----------
0
SELECT ST_IsClosed(ST_GeomFromText(
'MULTILINESTRING((1 1, 1 2, 2 2, 2 1, 1 1))', 4326));
-----------
1
SELECT ST_IsClosed(ST_GeomFromText(
'MULTILINESTRING((1 2, 2 2), (1 1, 1 2), (2 2, 2 1, 1 1))', 4326));
-----------
0
SELECT ST_IsClosed(ST_LineMerge(ST_GeomFromText(
'MULTILINESTRING((1 2, 2 2), (1 1, 1 2), (2 2, 2 1, 1 1))', 4326)));
-----------
1
*
* plese note:
*
* it looks like GEOS will always return FALSE while testing
ST_IsClosed()
* on behalf of any MultiCurve containing more than a single Curve; only
* MultiCurves containing a single Curve can actually return TRUE.
* alternatively, you can use ST_LineMerge() in order to reassemble a
* sparse collection of small lines into a single continuous line
*
SELECT ST_Length(ST_GeomFromText(
'MULTILINESTRING((1 1, 1 2), (2 2, 2 1, 1 1))', 4326));
-----------
3
> and Area, Centroid and PointOnSurface routines on
> MutliSurface?
>
SELECT ST_Area(ST_GeomFromText('
MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0)),
((10 10, 20 10, 20 20, 10 20, 10 10)))', 4326));
-----------
101
SELECT ST_X(g), ST_Y(g) FROM (
SELECT ST_Centroid(ST_GeomFromText(
'MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0)),
((10 10, 20 10, 20 20, 10 20, 10 10)))', 4326)) AS g);
-----------
14.856436 | 14.856436
SELECT ST_X(g), ST_Y(g) FROM (
SELECT ST_PointOnSurface(ST_GeomFromText(
'MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0)),
((10 10, 20 10, 20 20, 10 20, 10 10)))', 4326)) AS g);
-----------
15 | 15
> Comments in the source code indicate support, but there
> is no mention of Multi* geometries other than GeomCollection on
> http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html.
>
yes, that's true: for the sake of brevity, I often use the term
GeomColls
as a generalized synonym for "any possible kind of collection, this
including
MultiPoints, MultiLinestrings and MultiPolygons and
GeometryCollections".
bye Sandro
--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.
Paul Daisey
Thanks for the fresh test, I'll now recommend SpatiaLite as the reference implementation for vector features storage in GeoPackages.
Group:
See attached specification draft. Thoughts?
All:
Thanks,
Paul