MySQL spatial query

Question

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

ALTER TABLE roadData ADD pt POINT ;

and now my table has null values :

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?

sagi · Answer 1 · 2016-10-09T14:19:40.993

1

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,')'))

edited Oct 09 '16 at 14:19

answered Oct 09 '16 at 14:16

sagi

thanks sagi actually the answer u told for cross join was taking lot of time. so @bamar said to use spatial so trying that. – Arjun Chaudhary Oct 09 '16 at 14:19
And ALTER TABLE geom ADD SPATIAL INDEX(g); for creating index – Arjun Chaudhary Oct 09 '16 at 14:19
For spatial index : ALTER TABLE roadData ADD SPATIAL INDEX(pt); – Arjun Chaudhary Oct 09 '16 at 14:28
OR CREATE SPATIAL INDEX sp_index ON roadData (pt); – Arjun Chaudhary Oct 09 '16 at 14:29
Why not `SET t.pt = ST_Point(t.longitude, t.latitude)`? Let's make things complicated and waste a lot of resources :-) – Kaii Oct 09 '16 at 14:56
I'm not familiar with any of these functions, I just assumed how the syntax should be to help him:) @kaii – sagi Oct 09 '16 at 17:54

1 Answers1