Arjun Chaudhary
Arjun Chaudhary

Reputation: 2453

MySQL spatial query

I had a normal table. Now i added a spatial column

ALTER TABLE roadData ADD pt POINT ;

and now my table has null values : enter image description here

Now in MySQL tutorial insert statement is used:

INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));

but i want to to do it in already existing table removing null values and insert point data based on Latitude and Longitude give in each row. Like

INSERT INTO pt VALUES (ST_GeomFromText('POINT(latitude longitude)'));

and also make a spatial index. How can I do it?

Upvotes: 0

Views: 438

Answers (1)

sagi
sagi

Reputation: 40491

That's an UPDATE STATEMENT and not an INSERT STATEMENT , try this:

UPDATE geom t
SET t.pt = ST_GeomFromText(CONCAT('POINT(',t.longitude,t.latitude,')'))

Upvotes: 1

Related Questions