|
|
Here we will create table with geometry column and insert some data into it.
The geographic data is points based on Lat-Long coordinates.
CREATE TABLE poi_milan
(idno INTEGER, name CHARACTER(80), ptyp CHARACTER(50),
lat FLOAT (16,8), lon FLOAT (16,8), g GEOMETRY);
-- insert data
INSERT INTO poi_milan (idno,name,ptyp,lat,lon)
VALUES (1,'Duomo','05 - church',9.191837781,45.464282256);
INSERT INTO poi_milan (idno,name,ptyp,lat,lon)
VALUES (2,'Biblioteca Ambrosiana','04 - building', 9.185365986,45.463248669);
INSERT INTO poi_milan (idno,name,ptyp,lat,lon)
VALUES (3,'Borsa di Milano','04 - building', 9.183246694,45.465179773);
INSERT INTO poi_milan (idno,name,ptyp,lat,lon)
VALUES (4,'Castello Sforzesco','04 - building', 9.179472633,45.470286454);
INSERT INTO poi_milan (idno,name,ptyp,lat,lon)
VALUES (5,'Cimitero Monumentale','03 - green', 9.178310878,45.486850940);
INSERT INTO poi_milan (idno,name,ptyp,lat,lon)
VALUES (6,'Fiera Di Milano','04 - building', 9.153554429,45.477649198);
INSERT INTO poi_milan (idno,name,ptyp,lat,lon)
VALUES (7,'Galleria Vittorio Emanuele','04 - building', 9.189949093,45.465694902);
INSERT INTO poi_milan (idno,name,ptyp,lat,lon)
VALUES (8,'Giardini pubblici','03 - green', 9.199755024,45.474053542);
INSERT INTO poi_milan (idno,name,ptyp,lat,lon)
VALUES (9,'La Scala','04 - art', 9.189474165,45.467526176);
INSERT INTO poi_milan (idno,name,ptyp,lat,lon)
VALUES (10,'Meazza (San Siro) Stadio','06 - sport', 9.123891471,45.478185243);
|
Create geometry using GeomFromText() function:
UPDATE poi_milan
SET g = GeomFromText(
CONCAT('POINT(',
CAST(lat AS CHARACTER(20)),
' ',
CAST(lon AS CHARACTER(20)),')')
);
|
See results:
|
SELECT idno, ASText(g)
FROM poi_milan;
|
sqlexamples.info
|