[gdal-dev] Problem with merging SHAPEs into SpatiaLite

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

[gdal-dev] Problem with merging SHAPEs into SpatiaLite

Casper Børgesen-2

Hi devs

 

I am trying to merge a bunch of SHAPE files into a few SpatiaLite files. To make it more simple I have two kinds of data sets A and B and each data set has two SHAPE files. So my result should end up being a SpatiaLite file for each data set.

 

I am using the following commands to create the SpatiaLite files:

 

If it’s the first file:

ogr2ogr -f SQLite -dsco SPATIALITE=YES -gt 100000 -lco SPATIAL_INDEX=NO out_A.spatialite A_1.shp -nln A

Else:

ogr2ogr -gt 100000 -update -append A.spatialite A_2.shp -nln A

 

If it’s the first file:

ogr2ogr -f SQLite -dsco SPATIALITE=YES -gt 100000 -lco SPATIAL_INDEX=NO out_B.spatialite B_1.shp -nln B

Else:

ogr2ogr -gt 100000 -update -append B.spatialite B_2.shp -nln B

 

For some reason I haven’t found yet, the last command fails with the following message (both A’s work just fine):

 

ERROR 1: Layer B already exists, CreateLayer failed.

Use the layer creation option OVERWRITE=YES to replace it.

ERROR 1: Terminating translation prematurely after failed

translation of layer B (use -skipfailures to skip errors)

 

The two data sets are both linestrings but has different attributes. Both 1 and 2 in each data set has been created programmatically the same way.

 

I am using GDAL 2.2.2 (x64) on Windows 7 from OSGeo4W.

 

What could be the reason for this error?

 

Regards, Casper

 


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

Re: Problem with merging SHAPEs into SpatiaLite

jratike80
Casper Børgesen-2 wrote

> Hi devs
>
> I am trying to merge a bunch of SHAPE files into a few SpatiaLite files.
> To make it more simple I have two kinds of data sets A and B and each data
> set has two SHAPE files. So my result should end up being a SpatiaLite
> file for each data set.
>
> I am using the following commands to create the SpatiaLite files:
>
> If it's the first file:
> ogr2ogr -f SQLite -dsco SPATIALITE=YES -gt 100000 -lco SPATIAL_INDEX=NO
> out_A.spatialite A_1.shp -nln A
> Else:
> ogr2ogr -gt 100000 -update -append A.spatialite A_2.shp -nln A
>
> If it's the first file:
> ogr2ogr -f SQLite -dsco SPATIALITE=YES -gt 100000 -lco SPATIAL_INDEX=NO
> out_B.spatialite B_1.shp -nln B
> Else:
> ogr2ogr -gt 100000 -update -append B.spatialite B_2.shp -nln B
>
> For some reason I haven't found yet, the last command fails with the
> following message (both A's work just fine):
>
> ERROR 1: Layer B already exists, CreateLayer failed.
> Use the layer creation option OVERWRITE=YES to replace it.
> ERROR 1: Terminating translation prematurely after failed
> translation of layer B (use -skipfailures to skip errors)
>
> The two data sets are both linestrings but has different attributes. Both
> 1 and 2 in each data set has been created programmatically the same way.
>
> I am using GDAL 2.2.2 (x64) on Windows 7 from OSGeo4W.
>
> What could be the reason for this error?
>
> Regards, Casper

Could you share a small test dataset for reproducing the issue?

-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: Problem with merging SHAPEs into SpatiaLite

Casper Børgesen-2
Hi Jukka

I tried only to discover that the error is probably about an invalid (?) layer name (-nln). ogr2ogr doesn't complain if I give it a layer name with a dash in the middle, like 'some-thing'. When appending data to the layer using the same layer name, I get the error message.

I don't know if this type of layer names are indeed invalid and if so, the current behaviour of ogr2ogr not complaining is intended.

Regards, Casper

-----Original Message-----
From: gdal-dev [mailto:[hidden email]] On Behalf Of jratike80
Sent: 3. oktober 2017 20:52
To: [hidden email]
Subject: Re: [gdal-dev] Problem with merging SHAPEs into SpatiaLite

Casper Børgesen-2 wrote

> Hi devs
>
> I am trying to merge a bunch of SHAPE files into a few SpatiaLite files.
> To make it more simple I have two kinds of data sets A and B and each data
> set has two SHAPE files. So my result should end up being a SpatiaLite
> file for each data set.
>
> I am using the following commands to create the SpatiaLite files:
>
> If it's the first file:
> ogr2ogr -f SQLite -dsco SPATIALITE=YES -gt 100000 -lco SPATIAL_INDEX=NO
> out_A.spatialite A_1.shp -nln A
> Else:
> ogr2ogr -gt 100000 -update -append A.spatialite A_2.shp -nln A
>
> If it's the first file:
> ogr2ogr -f SQLite -dsco SPATIALITE=YES -gt 100000 -lco SPATIAL_INDEX=NO
> out_B.spatialite B_1.shp -nln B
> Else:
> ogr2ogr -gt 100000 -update -append B.spatialite B_2.shp -nln B
>
> For some reason I haven't found yet, the last command fails with the
> following message (both A's work just fine):
>
> ERROR 1: Layer B already exists, CreateLayer failed.
> Use the layer creation option OVERWRITE=YES to replace it.
> ERROR 1: Terminating translation prematurely after failed
> translation of layer B (use -skipfailures to skip errors)
>
> The two data sets are both linestrings but has different attributes. Both
> 1 and 2 in each data set has been created programmatically the same way.
>
> I am using GDAL 2.2.2 (x64) on Windows 7 from OSGeo4W.
>
> What could be the reason for this error?
>
> Regards, Casper

Could you share a small test dataset for reproducing the issue?

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

Re: Problem with merging SHAPEs into SpatiaLite

Even Rouault-2

On mercredi 4 octobre 2017 08:58:25 CEST Casper Børgesen wrote:

> Hi Jukka

>

> I tried only to discover that the error is probably about an invalid (?)

> layer name (-nln). ogr2ogr doesn't complain if I give it a layer name with

> a dash in the middle, like 'some-thing'. When appending data to the layer

> using the same layer name, I get the error message.

> I don't know if this type of layer names are indeed invalid and if so, the

> current behaviour of ogr2ogr not complaining is intended.

 

I cannot reproduce on Linux (and can't see why that would be Windows specific)

 

The following works fine for me:

ogr2ogr -f gpkg test.gpkg ../autotest/ogr/data/poly.shp -nln "a-b"

ogr2ogr -gt 100000 -update -append test.gpkg ../autotest/ogr/data/poly.shp -nln "a-b"

 

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: Problem with merging SHAPEs into SpatiaLite

Casper Børgesen-2

Hi Even

 

I have prepared a very small test data set:

 

2 shape files

1 BAT file

1 screenshot of my output

 

You can find it here: https://ufile.io/ravt2

 

I don’t have access to a Linux machine at the moment, so I cannot test it on Linux. But I am curious to know if it works on Linux J

 

Regards, Casper

 

From: Even Rouault [mailto:[hidden email]]
Sent: 5. oktober 2017 12:34
To: [hidden email]
Cc: Casper Børgesen <[hidden email]>
Subject: Re: [gdal-dev] Problem with merging SHAPEs into SpatiaLite

 

On mercredi 4 octobre 2017 08:58:25 CEST Casper Børgesen wrote:

> Hi Jukka

>

> I tried only to discover that the error is probably about an invalid (?)

> layer name (-nln). ogr2ogr doesn't complain if I give it a layer name with

> a dash in the middle, like 'some-thing'. When appending data to the layer

> using the same layer name, I get the error message.

> I don't know if this type of layer names are indeed invalid and if so, the

> current behaviour of ogr2ogr not complaining is intended.

 

I cannot reproduce on Linux (and can't see why that would be Windows specific)

 

The following works fine for me:

ogr2ogr -f gpkg test.gpkg ../autotest/ogr/data/poly.shp -nln "a-b"

ogr2ogr -gt 100000 -update -append test.gpkg ../autotest/ogr/data/poly.shp -nln "a-b"

 

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: Problem with merging SHAPEs into SpatiaLite

Even Rouault-2

On jeudi 5 octobre 2017 10:52:42 CEST Casper Børgesen wrote:

> Hi Even

>

> I have prepared a very small test data set:

>

> 2 shape files

> 1 BAT file

> 1 screenshot of my output

>

> You can find it here: https://ufile.io/ravt2

>

> I don't have access to a Linux machine at the moment, so I cannot test it on

> Linux. But I am curious to know if it works on Linux :)

 

Oh I wrongly assumed you used geopackage whereas you use sqlite

 

The difference is that the sqlite driver applies by default name laundering which will turn "a-b" into a_b in the creation stage. So your append -nln "a-b" will match a non existing layer

 

Add -lco LAUNDER=NO to the creation stage and that will solve it.

 

>

> Regards, Casper

>

> From: Even Rouault [mailto:[hidden email]]

> Sent: 5. oktober 2017 12:34

> To: [hidden email]

> Cc: Casper Børgesen <[hidden email]>

> Subject: Re: [gdal-dev] Problem with merging SHAPEs into SpatiaLite

>

> On mercredi 4 octobre 2017 08:58:25 CEST Casper Børgesen wrote:

> > Hi Jukka

> >

> >

> >

> > I tried only to discover that the error is probably about an invalid (?)

> >

> > layer name (-nln). ogr2ogr doesn't complain if I give it a layer name with

> >

> > a dash in the middle, like 'some-thing'. When appending data to the layer

> >

> > using the same layer name, I get the error message.

> >

> > I don't know if this type of layer names are indeed invalid and if so, the

> >

> > current behaviour of ogr2ogr not complaining is intended.

>

> I cannot reproduce on Linux (and can't see why that would be Windows

> specific)

>

>

>

> The following works fine for me:

>

> ogr2ogr -f gpkg test.gpkg ../autotest/ogr/data/poly.shp -nln "a-b"

>

> ogr2ogr -gt 100000 -update -append test.gpkg ../autotest/ogr/data/poly.shp

> -nln "a-b"

>

>

>

> Even

>

>

>

> --

>

> Spatialys - Geospatial professional services

>

> http://www.spatialys.com

 

 

--

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: Problem with merging SHAPEs into SpatiaLite

jratike80
Hi,

I have a bit similar problem with making queries with SQLite SQL dialect if
shapefile name has numbers.
For example this succeeds fine with GDAL 2.3dev on Windows:

ogrinfo -sql "select * from test2000" test2000.shp

but this does not


ogrinfo -dialect sqlite -sql "select * from test2000" test2000.shp
INFO: Open of `test2000.shp'
      using driver `ESRI Shapefile' successful.
ERROR 1: Cannot create virtual table for layer 'test2000' : CREATE VIRTUAL:
inva
lid SQL statement : CREATE TABLE "test2000"("KUNTANUMER"
VARCHAR(3),"KUNTANIMI"
VARCHAR(18),"MAAKUNTA" VARCHAR(2),"AVI" VARCHAR(2),"ELY"
VARCHAR(2),"OGR_STYLE"
VARCHAR(37),OGR_STYLE VARCHAR HIDDEN,"GEOMETRY" BLOB_POLYGON_XYZ_0,
OGR_NATIVE_D
ATA VARCHAR, OGR_NATIVE_MEDIA_TYPE VARCHAR)
ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select * from test2000):
  no such table: test2000

-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: Problem with merging SHAPEs into SpatiaLite

Even Rouault-2

On jeudi 5 octobre 2017 05:20:02 CEST jratike80 wrote:

> Hi,

>

> I have a bit similar problem with making queries with SQLite SQL dialect if

> shapefile name has numbers.

> For example this succeeds fine with GDAL 2.3dev on Windows:

>

> ogrinfo -sql "select * from test2000" test2000.shp

>

> but this does not

 

>

>

> ogrinfo -dialect sqlite -sql "select * from test2000" test2000.shp

> INFO: Open of `test2000.shp'

> using driver `ESRI Shapefile' successful.

> ERROR 1: Cannot create virtual table for layer 'test2000' : CREATE VIRTUAL:

> inva

> lid SQL statement : CREATE TABLE "test2000"("KUNTANUMER"

> VARCHAR(3),"KUNTANIMI"

> VARCHAR(18),"MAAKUNTA" VARCHAR(2),"AVI" VARCHAR(2),"ELY"

> VARCHAR(2),"OGR_STYLE"

> VARCHAR(37),OGR_STYLE VARCHAR HIDDEN,"GEOMETRY" BLOB_POLYGON_XYZ_0,

> OGR_NATIVE_D

> ATA VARCHAR, OGR_NATIVE_MEDIA_TYPE VARCHAR)

> ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select * from test2000):

> no such table: test2000

>

 

Nothing to do with the presence of numbers in the layer name. The issue here comes from the fact that your shapefile has a OGR_STYLE column, which confuses the SQLite SQL dialect wrapper that also tries to create a hidden OGR_STYLE column.

I've just pushed a fix in trunk.

 

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: Problem with merging SHAPEs into SpatiaLite

Casper Børgesen-2
In reply to this post by Even Rouault-2

Hi Even

 

Thanks for your reply, I will try the option. I just wonder, if the driver performs automatical laundering, shouldn’t it automatically transform any requests too? So when asked if a layer exists, launder the incoming name and compare to that or does it have other complications down the road?

 

Regards, Casper

 

From: Even Rouault [mailto:[hidden email]]
Sent: 5. oktober 2017 13:15
To: Casper Børgesen <[hidden email]>
Cc: [hidden email]
Subject: Re: [gdal-dev] Problem with merging SHAPEs into SpatiaLite

 

On jeudi 5 octobre 2017 10:52:42 CEST Casper Børgesen wrote:

> Hi Even

>

> I have prepared a very small test data set:

>

> 2 shape files

> 1 BAT file

> 1 screenshot of my output

>

> You can find it here: https://ufile.io/ravt2

>

> I don't have access to a Linux machine at the moment, so I cannot test it on

> Linux. But I am curious to know if it works on Linux :)

 

Oh I wrongly assumed you used geopackage whereas you use sqlite

 

The difference is that the sqlite driver applies by default name laundering which will turn "a-b" into a_b in the creation stage. So your append -nln "a-b" will match a non existing layer

 

Add -lco LAUNDER=NO to the creation stage and that will solve it.

 

>

> Regards, Casper

>

> From: Even Rouault [[hidden email]]

> Sent: 5. oktober 2017 12:34

> To: [hidden email]

> Cc: Casper Børgesen <[hidden email]>

> Subject: Re: [gdal-dev] Problem with merging SHAPEs into SpatiaLite

>

> On mercredi 4 octobre 2017 08:58:25 CEST Casper Børgesen wrote:

> > Hi Jukka

> >

> >

> >

> > I tried only to discover that the error is probably about an invalid (?)

> >

> > layer name (-nln). ogr2ogr doesn't complain if I give it a layer name with

> >

> > a dash in the middle, like 'some-thing'. When appending data to the layer

> >

> > using the same layer name, I get the error message.

> >

> > I don't know if this type of layer names are indeed invalid and if so, the

> >

> > current behaviour of ogr2ogr not complaining is intended.

>

> I cannot reproduce on Linux (and can't see why that would be Windows

> specific)

>

>

>

> The following works fine for me:

>

> ogr2ogr -f gpkg test.gpkg ../autotest/ogr/data/poly.shp -nln "a-b"

>

> ogr2ogr -gt 100000 -update -append test.gpkg ../autotest/ogr/data/poly.shp

> -nln "a-b"

>

>

>

> Even

>

>

>

> --

>

> Spatialys - Geospatial professional services

>

> http://www.spatialys.com

 

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


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