CODEWITHSUNDEEP
mysqlgeometry
Deepak
Deepak

Reputation: 6802

Geometry in MySQL

I understood what POINT() is in MySQL but I just cant figure out what is this format means 1 POINT (8.04665153 -44.39902520) I have the following CSV

104|104||CURR|N|14102|4428||71816|71816|1|1 POINT (8.04665153 -44.39902520)|

I am using the following query to load the CSV into my table

LOAD DATA LOCAL INFILE '/data/disc/sad1.csv' INTO TABLE sad1 
FIELDS TERMINATED BY '|' ENCLOSED BY '"' lines terminated by '\n' IGNORE 23 LINES
(@var1, @var2,@var3,@var4,@var5,@var6,@var7,@var8,@var9,@var10,@var11,@var12)
SET `house_number` = @var1,
    `range_low` = @var2,
    `range_high` = @var3,
    `status` = @var4,
    `unofficial_flag` = @var5,
    `rcl_id` = @var6,
    `rna_id` = @var7,
    `alt_id` = @var8,
    `se_row_id` = @var9,
    `id` = @var10,
    `audit_id` = @var11,
    `shape` = GeomFromText(@var12);

I am getting NULL for the shape field. When I changed 1 POINT (8.04665153 -44.39902520) to POINT (8.04665153 -44.39902520) I am getting some data filled in. How should I treat the prefix 1 in the above data ?

Upvotes: 0

Views: 633

Answers (1)

MvG
MvG

Reputation: 60858

I am no sure what this prefix 1 in ypur input data signifies. The well-known text representation of geometry, as defined in section 7 of the SFA part 1 standard, does not include such a prefix. It might be that this digit indicates the number of features. Or it might denote the integer code for the point type, as specified in table 7 from section 8.2.3 of that standard.

In any case, as long as the prefix is 1 in all the cases, and the creator of your input csv data does not provide additional details on the meaning of that value, your best bet is to simply drop these prefixes. You might do that using a preprocessing phase on the CSV, substituting POINT for 1 POINT throughout the document. There is a remote chance that you might accidentially substitute an occurrence somewhere in a text column.

If you want to be safe, or want to avoid the extra step, you can also do the change in the SQL query, using an expression like this:

`shape` = GeomFromText(IF(@var12 LIKE '1 POINT%', SUBSTRING(@var12, 3), @var12))

This will check whether the corresponding column starts with 1 POINT, and if so, omit the first two characters from the column text.

Upvotes: 1

Related Questions