rtree oop branch

Creating ProjPicker DB and Spatialite

Right now, I am constructing spatialite calls directly from the sqlite3 module.

This means that we have to convert our geometry to Spatialite geometry syntax. This can be done by removing every other comma and by removing all braces/brackets except the outside ones.

An example insert with spatialite will be

insert into projbbox (auth_code, name, Geometry) values (1, '1',  GeomFromText('POLYGON((101.23 171.82, 201.32 101.5, 215.7 201.953, 101.23 171.82))')

To read it we use

select auth_code, name, AsText(geom) from projbbox

Creation of ProjPicker DB

We can create the intial database of projected_crs codes

./build_db.py projected_crs -p 2

where -p indicates the number of points in the densified bounding box. We use two points here just to provide a smaller output for the purpose of example. The default number of points is 100.

Right now we have two tables; projbbox and densbbox.

CREATE TABLE projbbox (
                    auth_code varchar(100) primary key,
                    name varchar(100) not null,
                    south_latitude real not null,
                    west_longitude real not null,
                    north_latitude real not null,
                    east_longitude real not null
                );
CREATE TABLE densbbox (
                    auth_code varchar(100) primary key,
                    name varchar(100) not null,
                    geom BLOB NOT NULL
                );

The projbbox table will hold the latitude and longitude coordinates for the simple north facing bounding box provided in PROJ.8, while densbbox will hold the densified bbox which is created by projpicker.

We can check the data of EPSG:9674 as follows:

sqlite> .headers on
sqlite> .load mod_spatialite
sqlite> select * from projbbox where auth_code = 9674;
auth_code|name|south_latitude|west_longitude|north_latitude|east_longitude
9674|USA - Oregon and Washington|41.98|49.05|-124.79|-116.47
sqlite> select *, AsGeoJSON(geom) from densbbox where auth_code = 9674;
auth_code|name|geom|AsGeoJSON(geom)
9674|USA - Oregon and Washington||{"type":"Polygon","coordinates":[[[49.04999999999999,-124.79],[-116.4699999999999,-124.79],[-116.4699999999999,-124.79],[-116.4699999999999,41.97999999999999],[-116.4699999999999,41.97999999999999],[49.04999999999999,41.97999999999999],[49.04999999999999,41.97999999999999],[49.04999999999999,-124.79]]]}