generate a map representation from a csv table without spatial information

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

generate a map representation from a csv table without spatial information

R_Tavares
Salut,

I have to generate a table from joining various 'source' tables columns to represent this information in the map, nonetheless I have a problem:

- I must generate a shp file with information from one of the tables that is in csv format type. If I include it in the Geocatalog the information in the various columns is assumed as 'string' (even it is long and double), consequently cannot join this table with other that the collumns are 'long' or 'double' formats to be able to generate a file in a format able to be represented in the map and then exported in SHP format.

For this reason, how can I generate the map representation considering the information included in the column of the table csv.

Merci pour votre aide,

Richard
Reply | Threaded
Open this post in threaded view
|

Re: generate a map representation from a csv table without spatial information

Gwendall Petit
Administrator
Bonjour Richard,

Your problem is well known and it deals with the cast of fields. In a .csv file, all fields are in string by default (you can not specify the type in advance).

So if you are using V3.0.2 (or before), you should have a look in the documentation HERE.
As you can see, there is a lot of SQL functions dedicated to the conversion of field (like StringToInt, StringToDouble, ...)

If you are using V4.0, the syntax has changed. Now, to cast (or convert a field) it's easier. You just have to use the :: operator. Here is an example :

CREATE TABLE test AS SELECT *, field_A :: int as field_A_int FROM study_area;
In this example, I get all fields from the table called "study_area", and in addition, I convert a field called "field_A" into a new integer field (which is called "field_A_int). Note that we don't need to know the type of "field_A". If there is no conflict (like, convert a text message into numbers), OrbisGIS will execute the instruction.

Hope it helps

Regards

Gwendall
Reply | Threaded
Open this post in threaded view
|

Re: generate a map representation from a csv table without spatial information

R_Tavares
Bonjour Gwendall,

Thank for the information. I'll test it form my data and hope to do all correct.

Regards,

Richard
Reply | Threaded
Open this post in threaded view
|

Re: generate a map representation from a csv table without spatial information

R_Tavares
In reply to this post by Gwendall Petit
Bonjour Gwendall,

Sorry to bother once again. I've done what you recommended and added the follwoing code

namesource="other15";

SQL("CREATE TABLE other_test AS SELECT *, gid :: int as gid1, density :: int as densi FROM "+namesource+";");

nonetheless it gives the following error message:

Data access error:
Caused by: org.gdms.data.types.IncompatibleTypesException: Cannot convert value to int: [C@1ef277a
Caused by: Cannot convert value to int: [C@1ef277a
Caused by: For input string: "0.063452524"

I send the related file attached.

Could you help me to make it possible to convert?

Thanks in advance;

Best regards,

Richard


Le 11/01/2013 09:00, Gwendall Petit [via OrbisGIS] a écrit :
Bonjour Richard,

Your problem is well known and it deals with the cast of fields. In a .csv file, all fields are in string by default (you can not specify the type in advance).

So if you are using V3.0.2 (or before), you should have a look in the documentation HERE.
As you can see, there is a lot of SQL functions dedicated to the conversion of field (like StringToInt, StringToDouble, ...)

If you are using V4.0, the syntax has changed. Now, to cast (or convert a field) it's easier. You just have to use the :: operator. Here is an example :

CREATE TABLE test AS SELECT *, field_A :: int as field_A_int FROM study_area;
In this example, I get all fields from the table called "study_area", and in addition, I convert a field called "field_A" into a new integer field (which is called "field_A_int). Note that we don't need to know the type of "field_A". If there is no conflict (like, convert a text message into numbers), OrbisGIS will execute the instruction.

Hope it helps

Regards

Gwendall



To unsubscribe from generate a map representation from a csv table without spatial information, click here.
NAML


other15.csv (9K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: generate a map representation from a csv table without spatial information

Gwendall Petit
Administrator
Richard,

It seems that your "density" field could not be converted into an integer. So if you convert it to a double, you will see that it works well.

CREATE TABLE other_test AS SELECT *, gid :: int as gid1, density :: double as densi FROM other15;
Regards,

Gwendall
Reply | Threaded
Open this post in threaded view
|

Re: generate a map representation from a csv table without spatial information

R_Tavares
Bonjour Gwendall,

Thank for your prompt response, it helped a lot!

Regards,

Richard

Le 11/01/2013 13:33, Gwendall Petit [via OrbisGIS] a écrit :
Richard,

It seems that your "density" field could not be converted into an integer. So if you convert it to a double, you will see that it works well.

CREATE TABLE other_test AS SELECT *, gid :: int as gid1, density :: double as densi FROM other15;
Regards,

Gwendall


To unsubscribe from generate a map representation from a csv table without spatial information, click here.
NAML

Reply | Threaded
Open this post in threaded view
|

Re: generate a map representation from a csv table without spatial information

ebocher
Administrator
Dear Richard,

Don't forget to remove the * field between select and from.

Erwan
Reply | Threaded
Open this post in threaded view
|

Re: generate a map representation from a csv table without spatial information

Gwendall Petit
Administrator
ebocher wrote
Don't forget to remove the * field between select and from.

Erwan
Optional way, if you don't want to keep string fields
Reply | Threaded
Open this post in threaded view
|

Re: generate a map representation from a csv table without spatial information

R_Tavares
Salut Gwendall et Erwan

ça marche bien.

Merci.

Richard

Le 11/01/2013 14:13, Gwendall Petit [via OrbisGIS] a écrit :
ebocher wrote
Don't forget to remove the * field between select and from.

Erwan
Optional way, if you don't want to keep string fields


To unsubscribe from generate a map representation from a csv table without spatial information, click here.
NAML