[gdal-dev] "Regular" SQLite DB Format

[Patrick Young]

Ah thanks, yeah I see now with a '-dsco METADATA=NO' option ogrinfo doesn't
recognize the output geometry column as geometry.

On Fri, Oct 25, 2024 at 3:46 PM Even Rouault <even.rouault at spatialys.com>
wrote:

>
> 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.
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20241025/88978ac2/attachment.htm>