[SpatiaLite-Users] Create R Tree Index

[mozo...@hotmail.fr]

Hi and sorry for my poor english !

I'm trying to create some R Tree index but there is something i don't
understand. Do i just have to run SELECT
CreateSpatialIndex('NewTowns', 'geom'); command or i have to complete
all table as geometry_columns... by myself ?


I try both but SELECT CreateSpatialIndex('NewTowns', 'geom') always
return me a 0 (disabled)

Can someone explain me simply how to do ??


Thx a lot, Cordialy MoZo

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

[a.furieri]

Hi,

in order to support a Spatial Index (R*Tree)
the Geometry column has to be "fully qualified":
i.e. a corresponding definition *must* exist
into the geometry_columns meta-table.

Never attempt to perform a "manual" insertion
into geometry_columns, because this can easily
produce a corrupted DB.

Use RecoverGeometryColumn(), or the analogous
menu command available on spatialite-gui

bye Sandro

[mozo...@hotmail.fr]

Thx for answer !

What "Fully qualified" mean ? I'm trying to use RecoverGeometryColumn
on a column but i've got this error "geometry column doesn't statisfie
required constraint". The column type is GEOMETRY, is that a
problem ? because i can't find geometry in the geometry type list.

I test it with a fake table with a POINT type column and it work... I
need to use the generic GEOMETRY type, is that possible ?

Thx a lot, Cordialy MoZo

[mozo...@hotmail.fr]

the "geometry column doesn't statisfie required constraint" seems to
be because i still got data in the table. I've create a test table
with just an ID and a POINT column but i can't insert any data
(constraint failed) since the index is created.

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

On Fri, 21 May 2010 05:14:17 -0700 (PDT), mozo...@hotmail.fr wrote

> the "geometry column doesn't statisfie required constraint" seems to
> be because i still got data in the table. I've create a test table
> with just an ID and a POINT column but i can't insert any data
> (constraint failed) since the index is created.
>

please, check the following example:

CREATE TABLE test_table (
ID INTEGER PRIMARY KEY NOT NULL);

SELECT AddGeometryColumn('test_table',
'Geometry', 4326, 'POINT', 'XY');

SELECT CreateSpatialIndex('test_table',
'Geometry');

INSERT INTO test_table (ID, Geometry)
VALUES(1, GeomFromText('POINT(1 1)', 4326));
INSERT INTO test_table (ID, Geometry)
VALUES(2, GeomFromText('POINT(2 2)', 4326));
INSERT INTO test_table (ID, Geometry)
VALUES(3, GeomFromText('POINT(3 3)', 4326));

SELECT ID, AsText(Geometry) FROM test_table;

bye Sandro

[mozo...@hotmail.fr]

Thx a lot for ure help a.furieri !!!

By creating index like that (without create columns before index) it's
work !! I test with a GEOMETRY type (SELECT
AddGeometryColumn('test_table', 'Geometry', 4326, 'GEOMETRY', 'XY'); )
and it's work too !!

THX a lot a.furieri !!

Best regards

[mozo...@hotmail.fr]

Sry for that but what the 4326 means ? SRID ? what it is exactly ?

thx again

[a.furieri]

Hi MoZo,

this one is a really complex question :-)

just for a preliminary introduction, please see:
http://en.wikipedia.org/wiki/Spatial_referencing_system
http://en.wikipedia.org/wiki/SRID
http://en.wikipedia.org/wiki/European_Petroleum_Survey_Group

then check:

SELECT * FROM spatial_ref_sys;

as you can see, SpatiaLite supports the latest EPSG dataset:
there are more than 3,500 SRID definitions :-)

the SRID=4326 "WGS 84" is a very common fall-back, mainly because
it covers all the world, and because it's the SRS adopted
by GPS devices.

quite any other SRID is strictly bounded within a single
nation (or within a single fuse).

Just as an example (I live in Italy) I'm accustomed to
use the following SRIDs:
- 3003 "Monte Mario / Italy zone 1"
- 3004 "Monte Mario / Italy zone 2"
[old military maps]

- 32632 "WGS 84 / UTM zone 32N"
- 32633 "WGS 84 / UTM zone 33N"
[more recent official maps]

- 25832 "ETRS89 / UTM zone 32N"
- 25833 "ETRS89 / UTM zone 33N"
[the latest standard for the European Union]

I'm not sure, but I suppose that 32630/32631/32632
and/or 28830/28831/28932 can be reasonable SRIDs
for France.

bye Sandro

[mozo...@hotmail.fr]

Thx for all these precision !! You're the one :) !!

Best regards, cordialy MoZo