Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 QuantumGIS.

My first question is it a real sqlite database or is it a so called spatialite database?

But my main question is 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)?

Thanks for any answers...

share|improve this question
    
may be this will be helpful as well enterra-inc.com/techzone/handling_sql_issues –  user8629 Jul 4 '12 at 8:43

3 Answers 3

National Earth SQLite database uses Well-known binary format in GEOMETRY column. It's well described on this Wikipedia page, however you can also find some libraries to handle it, for example http://geophp.net/

If you want to use it with SpatiaLite, here is example of SQL query:

SELECT name, GeometryType(GeomFromWKB(GEOMETRY)) FROM ne_10m_admin_0_countries

and the result would be something like that:

Afganistan, Polygon
.
.
USA, MultiPolygon
.
.

Here is full reference of functions available in SpatiaLite: http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.1.0.html

Of course, you cannot run this query in pure SQLite (without SpatiaLite extension). It won't work this way. If you don't want to use SpatiaLite, you have to decode GEOMETRY column as WKB format, and implement the functions you need by yourself.

share|improve this answer

The spatialite data will be binary. Your best bet is to use the API to access the geometry rather than try to crack it from outside. It's easier and quicker this way. You can request the data in a suitable format such as WKT, WKB or GeoJSON for instance.

Spatialite is very small and completely free and has C and Python APIs, ot to mention a command line interface and even a basic GUI. Together you have any number of ways to extract the geometry data and integrate Spatialite into other scripts.

share|improve this 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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.