[gdal-dev] GPKG: CREATE TABLE AS not working as expected

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] GPKG: CREATE TABLE AS not working as expected

Tobias Wendorff
Hi there!

I'm trying to do this on latest ogr2ogr build:
ogrinfo -al -dialect sqlite -sql "CREATE TABLE sorted AS SELECT * FROM
mytable ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(geom), 4326), 10)"
test.gpkg

The command runs without a problem, but the final table doesn't contain
any content in geometry column... Even a slimmed down example with
"CREATE TABLE test AS SELECT * FROM mytable;" doesn't work as expected.

Can someone give me a tip on how to get CREATE TABLE AS up and running?

Best regards,
Tobias

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: GPKG: CREATE TABLE AS not working as expected

Even Rouault-2

On jeudi 28 d├ęcembre 2017 17:40:18 CET Tobias Wendorff wrote:

> Hi there!

>

> I'm trying to do this on latest ogr2ogr build:

> ogrinfo -al -dialect sqlite -sql "CREATE TABLE sorted AS SELECT * FROM

> mytable ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(geom), 4326), 10)"

> test.gpkg

>

> The command runs without a problem, but the final table doesn't contain

> any content in geometry column... Even a slimmed down example with

> "CREATE TABLE test AS SELECT * FROM mytable;" doesn't work as expected.

>

> Can someone give me a tip on how to get CREATE TABLE AS up and running?

 

CREATE TABLE will bypass most driver-level logic that is needed for a spatial table to be properly recognized as such. You could fix that by manually registering the table in gpkg_contents and gpkg_geometry_columns table, but the most simple way of accomplishing what you want to do is:

 

ogr2ogr -update test.gpkg test.gpkg -sql "SELECT ..." -nln sorted

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: GPKG: CREATE TABLE AS not working as expected

Tobias Wendorff
Thanks for your answer.

Am Do, 28.12.2017, 18:41 schrieb Even Rouault:
>
> CREATE TABLE will bypass most driver-level logic that is needed for
> a spatial table to be properly recognized as such. You could fix that
> by manually registering the table in gpkg_contents and
> gpkg_geometry_columns table,

Ah okay. I already thought it wouldn't be as easy as PostGIS ;)

> but the most simple way of accomplishing what you want to do is:
> ogr2ogr -update test.gpkg test.gpkg -sql "SELECT ..." -nln sorted

I just tried it, but the target table has the same order as the
original table:

ogr2ogr -update test.gpkg test.gpkg -sql "SELECT * FROM mytable ORDER BY
ST_GeoHash(ST_Transform(ST_Envelope(geom), 4326), 10)" -nln sorted

This gives me the correct sorting:
ogrinfo -ro test.gpkg -sql "SELECT * FROM mytable ORDER BY
ST_GeoHash(ST_Transform(ST_Envelope(geom), 4326), 10)"

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: GPKG: CREATE TABLE AS not working as expected

jratike80
Tobias Wendorff wrote
> I just tried it, but the target table has the same order as the
> original table:
>
> ogr2ogr -update test.gpkg test.gpkg -sql "SELECT * FROM mytable ORDER BY
> ST_GeoHash(ST_Transform(ST_Envelope(geom), 4326), 10)" -nln sorted
>
> This gives me the correct sorting:
> ogrinfo -ro test.gpkg -sql "SELECT * FROM mytable ORDER BY
> ST_GeoHash(ST_Transform(ST_Envelope(geom), 4326), 10)"

Hi,

I would try with SQL that does not select the FID instead of "select *". See
preserve_fid in http://www.gdal.org/ogr2ogr.html. Not guaranteed to work.

-Jukka Rahkonen-





--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: GPKG: CREATE TABLE AS not working as expected

Tobias Wendorff
Am Do, 28.12.2017, 23:50 schrieb jratike80:
> I would try with SQL that does not select the FID instead of "select *".
> See preserve_fid in http://www.gdal.org/ogr2ogr.html. Not guaranteed
> to work.

The other way did it: adding "-unsetFid" seems to loosen up the order
to FID and creates a new one based on the new sorting.

Now my GPKG is spatially clustered. Thanks for leading me to this :)



_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: GPKG: CREATE TABLE AS not working as expected

Ian Turton
Can someone add this to the documentation page?

Ian

On 29 December 2017 at 05:31, Tobias Wendorff <[hidden email]> wrote:
Am Do, 28.12.2017, 23:50 schrieb jratike80:
> I would try with SQL that does not select the FID instead of "select *".
> See preserve_fid in http://www.gdal.org/ogr2ogr.html. Not guaranteed
> to work.

The other way did it: adding "-unsetFid" seems to loosen up the order
to FID and creates a new one based on the new sorting.

Now my GPKG is spatially clustered. Thanks for leading me to this :)



_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev



--
Ian Turton

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev