Hi!
I'm trying to use the database of the OpenGeoDB project
(http://opengeodb.hoppe-media.com/index.php?FrontPage_en)
with SQLite.
Simple example queries from opengeodb work ok, like
SELECT text_val
FROM geodb_textdata
WHERE text_type=500100000 /* NAME */ AND
loc_id=27431;
But the less trivial queries are very slow:
SELECT code.text_val as "area code", name.text_val as "town"
FROM geodb_textdata code, geodb_textdata name
WHERE name.loc_id=code.loc_id AND
code.text_type=500300000 /* AREA_CODE */ AND
name.text_type=500100000 /* NAME */
ORDER by 2;
Results just dripple out slowly. Particularly I have to use it
*without* the "ORDER BY", else the result takes "forever". Postgresql
solves it much faster, with or without "ORDER BY".
The only difference in the data is that I used "INTEGER" instead of
Postgres' "Boolean" type.
The OpenGeoDB examples are taken from here:
http://sourceforge.net/docman/display_doc.php?docid=27614&group_id=132421
If you want to look at the DB, I have importable data here:
http://renormalist.net/opengeodb/opengeodb-sqlite.sql.gz
http://renormalist.net/opengeodb/opengeodb-postgres.sql.gz
(2 MB each, 26 MB after gunzipping)
The Database is described here:
http://sourceforge.net/docman/index.php?group_id=132421
Is there something I can optimize in SQLite? E.g., I'm not sure, that
the indexes really work. Syntax seems to match the documentation, but
I'm a sqlite newbie and no db wizard.
(Greeti+Tha)nX
Steffen
--
Steffen Schwigon <[EMAIL PROTECTED]>
Dresden Perl Mongers <http://dresden-pm.org/>
