[gdal-dev] "Regular" SQLite DB Format

[Even Rouault]

Le 25/10/2024 à 23:40, Patrick Young via gdal-dev a écrit :
> Hello,
>
> I noticed in the docs 
> https://gdal.org/en/latest/drivers/vector/sqlite.html#regular-sqlite-databases 
> that there is some support for treating a plain sqlite db as a vector 
> format.
>
> I've been dumping some vector data into a table with a geometry column 
> full of WKB plus some properties, but I can't seem to get OGR to 
> recognize the geometry column for what it is (just treated as 
> binary).  Is this possible or does one need to shape the data to 
> something more in line with https://trac.osgeo.org/fdo/wiki/FDORfc16 ?

Yes, you need to create the geometry_columns and spatial_ref_sys special 
tables:

$ ogr2ogr poly.db autotest/ogr/data/poly.shp -lco FORMAT=WKB

$ echo .dump | sqlite3 poly.db
CREATE TABLE geometry_columns (     f_table_name VARCHAR, 
f_geometry_column VARCHAR,      geometry_type INTEGER, coord_dimension 
INTEGER,      srid INTEGER,     geometry_format VARCHAR );
INSERT INTO geometry_columns VALUES('poly','GEOMETRY',3,2,27700,'WKB');
CREATE TABLE spatial_ref_sys        (     srid INTEGER UNIQUE, auth_name 
TEXT,     auth_srid TEXT,     srtext TEXT);
INSERT INTO spatial_ref_sys VALUES(27700,'EPSG','27700','PROJCS["OSGB36 
/ British National 
Grid",GEOGCS["OSGB36",DATUM["Ordnance_Survey_of_Great_Britain_1936",SPHEROID["Airy 
1830",6377563.396,299.3249646,AUTHORITY["EPSG","7001"]],AUTHORITY["EPSG","6277"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4277"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",49],PARAMETER["central_meridian",-2],PARAMETER["scale_factor",0.9996012717],PARAMETER["false_easting",400000],PARAMETER["false_northing",-100000],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","27700"]]');
CREATE TABLE IF NOT EXISTS 'poly' ( "ogc_fid" INTEGER PRIMARY KEY 
AUTOINCREMENT, 'GEOMETRY' BLOB, 'area' FLOAT, 'eas_id' BIGINT, 'prfedea' 
VARCHAR(16));
INSERT INTO poly 
VALUES(1,X'01030000000100000014000000000000602f491d41000000207f2d5241000000c028471d41000000e0922d5241000000007c461d4100000060ae2d524100000080c9471d4100000020b62d5241000000209c4c1d41000000e0d82d5241000000608d4c1d41000000a0dd2d5241000000207f4e1d41000000a0ea2d524100000020294f1d4100000080ca2d524100000000b4511d41000000e0552d5241000000c016521d4100000080452d5241000000e0174e1d41000000202e2d524100000020414d1d41000000e04c2d5241000000e04b4d1d41000000605e2d524100000040634d1d41000000e0742d5241000000a0ef4c1d41000000e08d2d5241000000e04e4c1d41000000e0a12d5241000000e0b04b1d4100000060b82d524100000080974a1d4100000080ae2d524100000080cf491d4100000080952d5241000000602f491d41000000207f2d5241',215229.26600000000325,168,'35043411');
[ ... snip ... ]

Even

-- 
http://www.spatialys.com
My software is free, but my time generally not.
Butcher of all kinds of standards, open or closed formats. At the end, this is just about bytes.