[GRASS-user] Join sqlite table by coordinate pair (X,Y)

[Moritz Lennert]

On 22/10/12 15:40, Micha Silver wrote:
>   On 10/22/2012 01:20 PM, Johannes Radinger wrote:
>> Hi,
>>
>> I have a points vector file with the columns X and Y which are
>> populated with the respective coordinates.
>> Additionally I have a .csv file for the same points, also with the X
>> and Y column and a lot more columns
>> providing additional information. These two files don't share a single
>> unique key which can be used for joining.
>>
>> Thus I'd like to use the X-Y pair as a key for importing and then
>> joining the csv table
>> to the vector. Is there any possibility to use two columns as a
>> combined key instead of one
>> (probably an SQL statement for db.execute)? Or do I need to first
>> create a new column
>> and combine x and y into a key for the vector and the csv?
>
> If the X-Y values are *exactly* the same then you probably could do it
> directly in sqlite:
> First import the csv table into your sqlite.db
> sqlite>.import file.csv csv_table
> sqlite>.schema csv_table
>
> Now create columns in the vector table for each new column from the csv
> table
> sqlite> ALTER TABLE vector ADD COLUMN from_csv_col1 text;
> sqlite> ALTER TABLE vector ADD COLUMN from_csv_col2 integer;
> ...etc...
>
> Now update the values in each new column with the matching values from
> the csv table:
> sqlite> UPDATE vector SET from_csv_col1=(SELECT c.col1 FROM csv_table AS c
> ... WHERE c.x_coord=vector.x_coord AND c.y_coord=vector.y_coord);
>
> and so on for all the new columns.

Or (if you're lazy like me) just create a new table resulting from the 
join of the two:

CREATE TABLE joined-table AS SELECT t1.*, t2.* FROM existing_table t1 
JOIN csv_table t2 ON (t1.x=t2.x AND t1.y=t2.y)

and then link the new table to your vector map.

If that doesn't work (i.e. as Micha hints x and y have to be exactly 
identical and I'm not even sure all db backends accept identities of 
double precision values - example from the PostgreSQL docs: "Comparing 
two floating-point values for equality might not always work as 
expected.") you can also import your csv file as a vector map with 
v.in.ascii and then use v.distance to copy the cat values from one to 
the other so as to have a common key.

Moritz


> HTH
>
>> Any suggestions how that can be done in GRASS (6.5, sqlite) in a simple way?
>>
>> Best regards,
>> Johannes
>> _______________________________________________
>> grass-user mailing list
>> grass-user at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/grass-user
>>
>> This mail was received via Mail-SeCure System.
>>
>>
>
>
>
> _______________________________________________
> grass-user mailing list
> grass-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/grass-user