[GRASS-user] Join sqlite table by coordinate pair (X,Y)
mlennert at club.worldonline.be
Tue Oct 23 00:22:50 PDT 2012
On 22/10/12 15:40, Micha Silver wrote:
> On 10/22/2012 01:20 PM, Johannes Radinger wrote:
>> 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
> sqlite> ALTER TABLE vector ADD COLUMN from_csv_col1 text;
> sqlite> ALTER TABLE vector ADD COLUMN from_csv_col2 integer;
> 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.
>> Any suggestions how that can be done in GRASS (6.5, sqlite) in a simple way?
>> Best regards,
>> grass-user mailing list
>> grass-user at lists.osgeo.org
>> This mail was received via Mail-SeCure System.
> grass-user mailing list
> grass-user at lists.osgeo.org
More information about the grass-user