I want to use the binary version of GDAL to avoid programming in C or Java here. So I have created a SQL file and I want to run it using the following command :

ogrinfo.exe empty.gpkg -sql @my_sql_file.txt

I am about to populate some features in a sample table, lets name it "road".

Here is my question :

Do I have to create a BLOB out of a set of coordinates to be able to insert a polyline in my road table? If yes how should I do this?

I couldn't find any relevant document neither in GDAL nor in OGC website.

Here it is what I have got so far :

CREATE TABLE road (id int NOT NULL,name text(50),geom BLOB,PRIMARY KEY (id));
INSERT INTO gpkg_contents (table_name, identifier, data_type, srs_id) VALUES ( 'road', 'my_view', 'features', 4326);
INSERT INTO gpkg_geometry_columns (table_name, column_name, geometry_type_name, srs_id, z, m) values ('road', 'geom', 'POLYLINE', 4326, 0, 0);
  • What form is your "road" data in currently? Rather than trying to roll your own gpkg writer with sql, couldn't you just use ogr2ogr to populate your gpkg layer directly from the "road" data? – user2856 Jan 26 at 5:43
  • @Luke Well, I am developing a GPKG export feature for GE Smallworld and I have the road layer data in a Smallworld database. So I need to parse them into a set SQL statements somehow. I do not want to use a shape file as a middle translation format, cause field names with length more than 10 character will be truncated. Looks like I do not have a choice but to write a java code with gdal libs and get involved with BLOB format, right? – Iman Jan 26 at 12:47
  • @Luke Thanks look, please post it as an answer so I can accept it. – Iman Jan 27 at 12:16

If you're writing an intermediate sql file anyway, instead write into an intermediate format that GDAL/OGR can read which doesn't have the same field and data limitations as shapefiles, such as CSV with WKT geometry. You can then use ogr2ogr rather than trying to roll your own gpkg writer.

The gpkg geometry storage is WKB, so you might as well just write WKT to a CSV anyway. Be careful of data types, if they're not getting recognised by OGR you can add a CSVT file (see first link).

Your Answer

