error of loading geometry data from csv file to Spatialite

[usac...@gmail.com]

I have a database in SQL server 2008 R2. The database has a table with 5 columns and one column is "geometry", like :

  0xE610000001042C1C00000000004033DF60C000000080 ...

I have exported it to a csv file and would like to import it to a database in Spatialite 1.2.1.

I have created a database in Spatialite 1.2.1. The database has a table with the same structure as the one on SQL server.

But, when I imported the csv file to the table, I got error:

I do something wrong ?

I am new to Spatialite.

Thanks !

[usac...@gmail.com]

there is only one row in the csv file. The "geometry" field is very long, which has 65536 characters. If I replaced the long string with a very short one, no error for loading but the table is empty.

[mj10777]

On Sunday, 3 January 2016 01:30:43 UTC+1, usac...@gmail.com wrote:

there is only one row in the csv file. The "geometry" field is very long, which has 65536 characters. If I replaced the long string with a very short one, no error for loading but the table is empty.

This will be caused by the field being to long (>= 65536) in:

TextCsv.cpp

MyFrame::LoadText

...

  char dummy[65536];

...

 for (i = 0; i < text->max_fields; i++)

  {  
  ...
   else if (type == VRTTXT_TEXT)

                {

                  void *ptr = (void *) value;

                  strcpy(dummy, value);

                  free(ptr);

                  CleanSqlString(dummy);

                  strcat(sql, "'");

                  strcat(sql, dummy);

                  strcat(sql, "'");

              }
   ...
 }

failing at the statement:

strcpy(dummy, value);

when the field is longer than 65536.

If your string is 65536 bytes long, then there will be no terminating null byte.

Thus it fails in 

Shapefiles.cpp

CleanSqlString

where strlen is used to determine the length (looking for the terminating null byte)

- it will of course find one somewhere (possibly 65536 MB further on)

-- and copy that data to the reserved  'new_value', defined as char new_value[65536]; 

--- eventually it will overwrite something that the system considers vital

---> BOOM

So this will have to worked on

- allocating memory in MyFrame::LoadText and MyFrame::CleanSqlString for each field

-- and freeing it when no longer needed

 

Csv files were really not designed for the importing of binary data ...

Mark

[usac...@gmail.com]

Thanks!

But, why it still failed if I cut off 75% of the whole geometry string ?

It still failed even when I loaded a table without gepmetry data.

Example,

ID,CNTRY_NAME,POP_CNTRY,AREA_SQKM,COLOR_MAP
194,United States,258833000,9449362,5

After I loaded it, no errro poped up but no table is shown up in the GUI after refreshing.

thanks

 

On Saturday, January 2, 2016 at 7:29:05 PM UTC-5, usac...@gmail.com wrote:

[Mark Johnson]

2016-01-03 5:43 GMT+01:00 <usac...@gmail.com>:

Thanks!

But, why it still failed if I cut off 75% of the whole geometry string ?

It still failed even when I loaded a table without gepmetry data.

Example,

ID,CNTRY_NAME,POP_CNTRY,AREA_SQKM,COLOR_MAP
194,United States,258833000,9449362,5

After I loaded it, no errro poped up but no table is shown up in the GUI after refreshing.

Mayby the string  CNTRY_NAME must be enclosed in ''

- 'United States'

I would have to file to test this with to make sure what the cause is.

I am now working on a sample which may simplify this for you

- but it may take an hour or so until completed.

Mark

thanks
 

On Saturday, January 2, 2016 at 7:29:05 PM UTC-5, usac...@gmail.com wrote:

I have a database in SQL server 2008 R2. The database has a table with 5 columns and one column is "geometry", like :

  0xE610000001042C1C00000000004033DF60C000000080 ...

I have exported it to a csv file and would like to import it to a database in Spatialite 1.2.1.

I have created a database in Spatialite 1.2.1. The database has a table with the same structure as the one on SQL server.

But, when I imported the csv file to the table, I got error:

I do something wrong ?

I am new to Spatialite.

Thanks !

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/RDBRoECdDpE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

[usac...@gmail.com]

Thanks,

I have added 'United States' to the same file but still no table shown up in GUI.

I am using spatialite-gui v1.2 (by Alesssandro Furieri - 2008).

SpatiaLite version is 2.3.0.

I can loaded the same file to SQLite through SQLite studion 3.0.7.

On Saturday, January 2, 2016 at 7:29:05 PM UTC-5, usac...@gmail.com wrote:

[mj10777]

On Sunday, 3 January 2016 05:58:32 UTC+1, usac...@gmail.com wrote:

Thanks,

I have added 'United States' to the same file but still no table shown up in GUI.

I am using spatialite-gui v1.2 (by Alesssandro Furieri - 2008).

SpatiaLite version is 2.3.0.

The present spatialite version is  4.3.0 and spatialite_gui is 1.7.1

- I would strongly advice you not to update this

-- everything has changed in the mean time

Windows binary can be found here:

http://www.gaia-gis.it/gaia-sins/

Mark

[usac...@gmail.com]

Thanks,

I have downloaded spatialite_gui 2.000-devel.

I can load the table :

ID,CNTRY_NAME,POP_CNTRY,AREA_SQKM,COLOR_MAP
194,United,258833000,9449362,5

But, it is empty.
Afterloading, a window poped up:

    CSV/TXT loaded
    0 inserted rows

[usac...@gmail.com]

Thanks,

About the length of the column "BOUNDARY", it is used to hold boundary data (latitude and longitude ) of administration region such as county, state or postal code in USA.

The length may be longer than 65535.

How to import the long data sequence to spatialite database ?

But, I can import the same csv file (with the long geometry data column) to SQLite 3.8.10 through SQLite studio 3.7.0, without any problems.

[usac...@gmail.com]

Thanks,

What kind of file is better for loading binary data such as "Geometry" to spatialite ?

On Saturday, January 2, 2016 at 10:33:13 PM UTC-5, mj10777 wrote:

[mj10777]

On Sunday, 3 January 2016 06:22:28 UTC+1, usac...@gmail.com wrote:

Thanks,

I have downloaded spatialite_gui 2.000-devel.

I can load the table :

ID,CNTRY_NAME,POP_CNTRY,AREA_SQKM,COLOR_MAP
194,United,258833000,9449362,5

But, it is empty.
Afterloading, a window poped up:

    CSV/TXT loaded
    0 inserted rows

String should be inclosed in '', otherwise an integer or double is assumed

ID,CNTRY_NAME,POP_CNTRY,AREA_SQKM,COLOR_MAP

194,'United States',258833000,9449362,5

[mj10777]

On Sunday, 3 January 2016 06:22:28 UTC+1, usac...@gmail.com wrote:

Thanks,

I have downloaded spatialite_gui 2.000-devel.

You will also need the latest spatialite version corresponding to 2.000-devel (x86 or 

spatialite-4.4.0-RC0-win-x86.7z

spatialite_gui-4.4.0-RC0-win-x86.7z

[mj10777]

On Sunday, 3 January 2016 06:33:36 UTC+1, usac...@gmail.com wrote:

Thanks,

What kind of file is better for loading binary data such as "Geometry" to spatialite ?

I am working on a solution at the moment a method 

- export from other system

-- and to import into spatialite

using sql-scripts.

Will some time to complete.

[mj10777]

On Sunday, 3 January 2016 06:32:14 UTC+1, usac...@gmail.com wrote:

Thanks,

About the length of the column "BOUNDARY", it is used to hold boundary data (latitude and longitude ) of administration region such as county, state or postal code in USA.

The length may be longer than 65535.

How to import the long data sequence to spatialite database ?

But, I can import the same csv file (with the long geometry data column) to SQLite 3.8.10 through SQLite studio 3.7.0, without any problems.

As stated before, this a problem that needs to be resolved.

[usac...@gmail.com]

Sure, I will try them.

[mj10777]

On Sunday, 3 January 2016 06:47:53 UTC+1, usac...@gmail.com wrote:

Sure, I will try them.

Have a look at the message:

- 'Importing geometries from other system with sql-scripts'

You will need to adapt the table/fields name to your needs

- and check that the suggested Syntax changes for MS SQL-Server 2008 R2 really work

The created sql-script with the INSERT Statements should work without a problem with spatialite

- do not use spatialite_gui to read the script

-- but call it as shown in the message

Pleas inform me of the results

- specially if the Syntax is not correct

Good luck,

Mark

[usac...@gmail.com]

Thanks!,

I will try the instructions in the message.

Also, my final goal is to do query on the Spatialite database from C# visual studio 2013 (.net 4.5) WPF.

I know that for accessing SQLite, I need to download System.Data.SQLite from visual studio pacakge manager and add the assembly in C# code.

How can I access Spatialite database from C# ?

thanks!

[usac...@gmail.com]

Hi, Mark,

Thanks for your help.
I am very new to Spatialite.

I just followed the instructions in the message.

But, I cannot figure out how to reproduce what you have done in the message.

I have downloaded spatialite.exe from "spatialite-tools-win-x86-2.3.1".

I copied the exe to the folder where the spatialite database is located.

I opened spatialite.exe terminal and ran the commands shown in the picture.

But, no results show up.

Thanks

[usac...@gmail.com]

Hi, Mark,

Please check the attached screenshots of running the SQL command in your message of 'Importing geometries from other system with sql-scripts'.

I am new to Spatialite and its tools.

Any help would be appreciated.  

[mj10777]

On Sunday, 3 January 2016 18:59:49 UTC+1, usac...@gmail.com wrote:

Hi, Mark,

Please check the attached screenshots of running the SQL command in your message of 'Importing geometries from other system with sql-scripts'.

I am new to Spatialite and its tools.

You are still using an very old version

- the library

- the tools

- and spatialite_gui must of the same version

From the former post I saw that you downloaded the

- 4.4.0 beta (4.4.0-RC0)

Therefore you must download from here:

http://www.gaia-gis.it/gaia-sins/windows-bin-x86-test/

http://www.gaia-gis.it/gaia-sins/windows-bin-x86-test/spatialite_tool-4.4.0-RC0-win-x86.7z 

From the above images

- first line last image

-- you are still using spatialite 2.3.1 - which should not be used

--

Did you complete the first part

- creating the  'insert.comuni_imported.sql' on your MS SQL-Server 2008 R2?

or are you trying to work through the sample?

Mark