[GIS] Reading the GEOMETRY table inside SQLite database

androiddatabasegeometryqgissqlite

I'm working on an android app which should display some lineobjects stored in a SQLite database. This SQLite database is created through an export in QGIS.

Is it a real SQLite database or is it a so called spatialite database?

How to read the column GEOMERTY? This column has the dataformat BLOB. When i open my database with a tool called SQLite database browser this column is empty.

When i open the same database with the Firefox extension SQLite Manager it gives me a value like

X'01020000000200000089C8500C60FC2A403D2E994C25404A405C51DE4DDCFC2A40BA85E1865A404A40'

And when i open this database on an android device it gives me a value like [B@408acb78 for the GEOMETRY column.

Which markup language is used for this column? Is it Well-known text, well-known binary or something totaly different?

Does anyone know some answers for my questions? Or maybe even someone worked on a similar project (display objects from a sqlitedb on an android device)?

Best Answer

If the file was created with QGIS, as you state, then it should be a Spatialite DB. Please refer to the following site for info regarding Spatialite:

Spatialite

Download the GUI, read the documentation and you will be well on your way to understanding the Spatialite format. There is also an excellent user group here. Have fun!

Related Solutions

[GIS] Using MS SQL 2005 as the data store in GeoServer

Well, I have read the article again and I think I finally understand. Indeed, converting the points from WKB to WKT is necessary, as in the former case GeoServer will ignore the varbinary(MAX) column. In the latter case, GeoServer will publish the point - marked in WKT - as an attribute (metadata) of type String. Therefore, when requesting the WFS service, the point coordinates are really present in the returned XML, they are just not marked as <gml:point>. It is still possible to parse them out manually, if one wishes to.

However, it is probably not possible to persuade GeoServer to recognize that this column is a geometry and not some metadata. Therefore, technologies such as OpenLayers which do expect to find a <gml:point> tag in the returned XML will not work with this.

[GIS] Help importing osm data into spatialite

Use the spatialite_osm_raw tool for importing OSM files (raw format tables, strictly corresponding to XML

"Also this tool is intended to parse a whole OSM dataset, thus creating the corresponding SpatiaLite's DB-file; but in this case the generated DB-file will adopt a completely different data layout than in the previous scenario. In this case will be adopted a data layout closely mapping OSM objects, their data attributes and their mutual relationships; fully preserving the original OSM data model, and carefully avoiding to introduce any unpleasant information suppression. The intended goal of this tool is not at all to produce a DB-file usable by GIS applications: it's instead the one to get a DB-file you can use in order to gather statistical infos, to support some post-processing task and so on."

Example:

    $ spatialite_osm_raw -o my_country.osm.pbf -d my_country.sqlite
SQLite version: 3.7.11
SpatiaLite version: 3.1.0
inserted 3047972 nodes
        166634 tags
inserted 233481 ways
        423462 tags
        3461321 node-refs
inserted 6068 relations
        14815 tags
        56617 refs
$

You can view the imported data with spatlite_gui

enter image description here

https://www.gaia-gis.it/fossil/spatialite-tools/index

Online Examples:

https://www.gaia-gis.it/fossil/spatialite-tools/wiki?name=spatialite_osm_raw

Best Answer

Related Solutions

[GIS] Using MS SQL 2005 as the data store in GeoServer

[GIS] Help importing osm data into spatialite

Related Question